Introduction to ADO

In various lessons, we have learned how to create lists and collections. A database is a list or a group of lists of objects organized to make the list(s) and its (their) values easy to create and manage. In the computer world, this suggests, rightly, that the list(s) and its(their) values is(are) stored in a machine. Based on this importance, almost every company keeps some type of database, whether it includes its employees, its customers, or the products it sells.

A database is a project that holds one or more lists of items. There can be other issues involved, such as how the data would be made available to the users, what computer(s) would access the data, what types of users would access the database. The database could reside in one computer and used by one person. A database can also be stored in one computer but accessed by different computers on a network. Another database can be created and stored in a server to be accessed through the Internet. These and other related scenarios should be dealt with to create and distribute the database.

To make it possible to create computer databases, Microsoft developed various libraries and programming environments. Microsoft JET is a library used to create and manage Microsoft Access types of databases using a language or a programming environment of your choice. This means that the library can be used from either Microsoft Access, another Microsoft development studio, or an environment from another company. To make this possible, you must first obtain the library. In most cases, you should have this library already installed in your computer. If not, it is freely available by downloading from the Microsoft web site. Once there, simply do a search on "Microsoft JET". After downloading it, install it. 

Microsoft ActiveX Data Objects or ADO, is a library used to manage databases. To make it possible, it uses the driver that is part of Microsoft JET.

Microsoft ActiveX Data Object Extensions for Data Definition Language and Security abbreviated ADOX, is an addition to ADO. It can be used to create and manage a database, providing some of the same operations as ADO but also some operations not possible in ADO.

Any computer language or programming environment that wants to use Microsoft JET provides its own means of accessing the library. In fact, it is not unusual to manually write code that takes advantage of Microsoft JET. This is usually how some programmers would create and manage a web database (through Active Server Pages (ASP)). Still, in most cases, and depending on the language you decide to use to create and manage your database, you would need either only an interpreter (which is the case if you plan to use VBScript, JavaScript, or another interpreted language) or a compiler.

Based on its flexibility, you can use the .NET Framework to create and manage Microsoft JET databases. The .NET Framework is a group of many libraries under one name. When creating a project, you choose what libraries you would use, based on your goal, to perform the necessary tasks. If you are using a visual environment, you can "visually" reference the library. Based on this, to create an application that uses the ADO library, you can add its reference in the Solution Explorer.

To get a database, you can either use one that exists already or you can create your own. ADO by itself doesn't provide the means to create a database. To create one, you can use the Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX. Before using ADOX, you must reference it in your C# project. To do this, on the main menu of Microsoft Visual C#, you can click Project -> Add Reference... As an alternative, in the Solution Explorer, you can right-click the name of the project and click Add References...

In the COM tab of the Add Reference dialog box, locate the Microsoft ADO Ext. 2.8 for DDL and Security:

After referencing the library, you can use its classes. The classes of the ADOX library are stored in a namespace named ADOX.

To create and manage various objects of a database, the ADOX namespace provides an interface named Catalog and its derived class is named CatalogClass. To use this class, you can first declare its variable. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            ADOX.CatalogClass catDatabase;

            return 0;
        }
    }
}

After declaring the class, you can initialize it using the new operator:

using System;

public class Program
{
    static int Main()
    {
        ADOX.CatalogClass catDatabase;

        catDatabase = new ADOX.CatalogClass();

        return 0;
    }
}

You can also include the ADOX namespace and omit it in your declaration. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            return 0;
        }
    }
}

To support database creation, the ADO library provides an interface named Catalog. In the .NET Framework, this interface is type-defined as the CatalogClass class.

To support the creation of a database, the CatalogClass class is equipped with a method named Create. Its syntax is:

public object Create(string ConnectionString)

The Create() method takes one argument referred to as the connection string. Here is an example of calling it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            catDatabase.Create("");

            return 0;
        }
    }
}

This string is made of sections separated by semi-colons. The formula used by these sections is:

Key1=Value1;Key2=Value2;Key_n=Value_n;

The first part of the connection string is called the provider. It is software that handles the database. To specify it, assign the desired name to the provider key. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            catDatabase.Create("Provider=");

            return 0;
        }
    }
}

There are various providers in the database industry. One of them is Microsoft SQL Server and it is represented by SQLOLEDB. If you want to create a Microsoft SQL Server database, specify this provider. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            catDatabase.Create("Provider=SQLOLEDB;");

            return 0;
        }
    }
}

When creating this type of database, there are some other pieces of information you must provide in the connection string.

Another provider is the Microsoft JET database engine represented as Microsoft.JET.OLEDB.4.0. To create a database for it,  specify its provider accordingly. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            catDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;");

            return 0;
        }
    }
}

A database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. In some of your database operations, you will be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.

If you are creating a database, the second part of the connection string can be used to specify the path and the name of the database. This section must start with the Data Source key and assigned the path that consists of the drive and the folder(s). After the last folder, the name of the database must have the .mdb extension. For example, to create a database called Exercise1 that would reside in a folder called Programs on the C: drive, you can specify the connection string as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();

            catDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source='C:\\Programs\\Exercise1.mdb'");

            return 0;
        }
    }
}

This would produce:

A new Microsoft JET database named Exercise1.mdb has been created
Press any key to continue . . .

Instead of directly passing a string to the Create() method, you can first declare a string variable, initialize it with the necessary provider/data source, and then pass that string variable to the Create() method. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            CatalogClass catDatabase = new CatalogClass();
            string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source='C:\\Programs\\Exercise1.mdb'";

            catDatabase.Create(strDatabase);
            Console.WriteLine("A new Microsoft JET database named " +
                              "Exercise1.mdb has been created\n");

            return 0;
        }
    }
}

If you plan to access your database from another programming environment, then you should create an ODBC data source. To do this, in the Control Panel or the Administrative Tools, double-click Data Source (ODBC) to open the ODBC Data Source Administrator:

To proceed, click the Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, click Microsoft Access Driver (*.mdb):

Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. The name would be used by the applications that need to access the database. This means that you should pay attention to the name you give. In the Description text box, you can enter a short sentence anyway you like. To specify the database that would be used, click Select and select an mdb database. Here is an example:

After selecting the necessary database, if you need to be authenticated in order to use the database (if the database is protected), click the Advanced button. By default, a database is meant to allow anybody to use it. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials:

After using the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was).

After entering the necessary information and selecting the desired database, you can click OK twice.

As mentioned earlier, Microsoft ActiveX Data Objects, or ADO, is a library used to create databases. Before using this library, you must import it in your application. To do this, you can right-click the References node in the Solution Explorer for your project and click Add Reference... In the COM tab of the Add Reference dialog box, select the latest Microsoft ActiveX Data Object Library:

And click OK. This would add the ADO reference to your project.

The ADO library is represented in the .NET Framework by the ADODB namespace. It contains various classes that you can access by qualifying them through this namespace. As done with the other classes that belong to a namespace, if you want, in the top section of the file where you would use ADO, you can add the ADODB namespace. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            return 0;
        }
    }
}

To use or access a database, a user typically launches it and opens the necessary object(s) from it. You too will need to access a database but with code. To programmatically access a database using the ADO library, you must first establish a connection. To support this, the ADODB namespace provides an interface named Connection. In the .NET Framework, the Connection interface is defined by a class named ConnectionClass.

To create a connection to a database, declare a variable of type Connection or ADODB.ConnectionClass and initialize it using the new operator. This would be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            ADODB.Connection conDatabase = new ADODB.Connection();

            return 0;
        }
    }
}

This can also be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            return 0;
        }
    }
}

After declaring and initializing the Connection object, you can then open the connection. To support this, the Connection interface (the ConnectionClass class) is equipped with a method named Open. The syntax of the Connection.Open method is:

public void _Connection.Open(string ConnectionString,
     	  	             string  UserID,
     		             string  Password,
     		             int Options

As you can see, this method takes four arguments and all of them are required.

When establishing a connection to a database, you have two alternatives, you can use the first argument to the Connection.Open() method or you can separately create a connection string.

The connection string is text made of various sections separated by semi-colons. Each section is made of a Key=Value expression. Based on this, a connection string uses the following formula:

Key1=Value1;Key2=Value2;Key_n=Value_n;

One of the expressions you can specify in the connection string is the name of the provider. To do this, type Provider= followed by the provider you are using. For most databases we will create or use here, the provider will be Microsoft.JET.OLEDB.4.0. This means that our connection string can start with:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string  strConnection= "Provider=Microsoft.Jet.OLEDB.4.0;". . .

            return 0;
        }
    }
}

You can also include the value of the provider in single-quotes to delimit it.

The second part of the connection string specifies the data source. To provide this information, you can assign the path and name of the database to the Data Source attribute. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source='C:\\Programs\\Exercise1.mdb';";

            return 0;
        }
    }
}

It is important to note that the content of the connection string differs from one provider to another. If you were working on a Microsoft SQL Server database, your connection string would be different from the above done for a Microsoft JET database. For example, if you were working on an MSDE or a Microsoft SQL Server database, the provider would be SQLOLEDB.

After creating the connection string, you can then pass it to the Connection.Open() method as the first argument. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               	   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, );

            return 0;
        }
    }
}

When creating your database, if you are working in a secure environment and the database requires authentication, you may need to provide login credentials, which include a username and a password. Normally, these properties are mostly applied if you are working on a Microsoft SQL Server database.

To specify the login credentials when accessing the database, you can pass the second and the third arguments to the Open() method of the Connection class. If you don't have this information, you can pass each argument as an empty string. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               	   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", );
            return 0;
        }
    }
}

The fourth argument of the Connection.Open() method specifies whether the method should return after establishing the connection. If undecided, you can pass this argument as 0:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               	   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", 0);
            return 0;
        }
    }
}

After creating a connection to a database, you can specify what you want to do on the database. One of the most common operations you can perform is to submit a statement to it (the connection). This is also equivalent to executing the statement.

To execute a statement, the Connection class is equipped with the Execute() method. Its syntax is:

public Recordset _Connection.Execute(string CommandText,
 			             out object RecordsAffected,
  			             int Options);

The first argument, CommandText, can be a type of statement we will study in future lessons. The second argument, passed by reference, specifies the number of records that were affected by the operation. The third argument specifies how the provider should evaluate the command. If undecided, pass this argument as 0.

Here is an example of calling the Connection.Execute() method:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               	   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", 0);
            conADO.Execute(strStatement, out objAffected, 0);

            return 0;
        }
    }
}

When it is called, the Execute() method of the Connection class examines and executes its (first) argument, in this case strStatement. If this method succeeds, it returns a list of records.

When using a connection, it consumes resources that other applications may need. Therefore, after using it, you should close it and free the resources it was using so they can be made available to the other parts of the computer. To close a connection, the Connection class is equipped with the Close() method. This can be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            Connection conDatabase = new Connection();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               	   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", 0);
            conDatabase.Execute(strStatement, out objAffected, 0);

            conADO.Close();
            return 0;
        }
    }
}

Probably a better way would consist of using a try...finally block to close the connection. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;

namespace Exercise10
{
    public class Program
    {
        static int Main(string[] args)
        {
            object objAffected;
            string strStatement = "Something";
            Connection conDatabase = new Connection();

            try
            {
                string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                       "Data Source='C:\\Programs\\Exercise1.mdb';";

                conDatabase.Open(strConnection, "", "", 0);
                conDatabase.Execute(strStatement, out objAffected, 0);
            }
            finally
            {
                conDatabase.Close();
            }

            return 0;
        }
    }
}