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;

public class Program
{
    static int Main()
    {
        ADODB.Connection conDatabase = new ADODB.Connection();
        return 0;
    }
}

This can also be done as follows:

using System;
using ADODB;

public class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public static class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public static class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public static class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public class Program
{
    static int Main()
    {
        ConnectionClass conDatabase= new ConnectionClass();

        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 ADODB;

public class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public class Program
{
    static int Main()
    {
        object objAffected;
	string strStatement = "Something";
        ConnectionClass conDatabase = new ConnectionClass();

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

        conADO.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 ADODB;

public class Program
{
    static int Main()
    {
        object objAffected;
	string strStatement = "Something";
        ConnectionClass conDatabase = new ConnectionClass();

        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 ADODB;

public static class Program
{
    static int Main()
    {
        object objAffected;
        string strStatement = "Something";
        ConnectionClass conDatabase = new ConnectionClass();

        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
        {
            conADO.Close();
        }

        return 0;
    }
}