The Connection to a Database

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 and its derived class named ConnectionClass.

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

System::Void btnConnection_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO;
				 
	 conADO = gcnew ADODB::Connection;
}

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:

void Open(Syst3em::String ^ ConnectionString,
     System::String ^ UserID,
     System::String ^ Password,
     int Options

As you can see, this method takes four arguments.

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:

System::Void btnConnection_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO;
	 String ^ strConnection;

	 conADO = gcnew ADODB::Connection;
	 strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;". . .
}

You can also include the value of the provider in single-quotes to delimit it. If you were working on an MSDE or a Microsoft SQL Server database, the provider would be SQLOLEDB.

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:

Provider='Microsoft.JET.OLEDB.4.0';Data Source='C:\\Programs\\Example1.mdb';"

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.

You can pass this connection string as the first (and probably the only) argument to the method. Here is an example:

System::Void btnConnection_Click(System::Object^ sender, System::EventArgs^ e)
{
	 ADODB::Connection ^ conADO;
	 String ^ strConnection;

	 conADO = gcnew ADODB::Connection;
	 strConnection = (L"Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source='C:\Programs\Exercise1.mdb'")
}

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.

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

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

ADODB::Recordset ^ Execute(System::String ^ CommandText,
 			   System::Object ^ % RecordsAffected,
  			   int Options)

The first argument, CommandText, can be a SQL statement. We will study SQL in future lessons. The second and the third arguments are optional. Here is an example:

System::Void btnConnection_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO;
	 String ^ strConnection, ^ strStatement;
	 Object ^ obj = gcnew Object;

	 conADO = gcnew ADODB::Connection;
	 strConnection = (L"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source='C:\\Programs\\Exercise1.mdb'");
	 strStatement = "Blah Blah Blah";

	 conADO->Open(strConnection, L"", L"", 0);
	 conADO->Execute(strStatement, obj, 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 an object called a record set. We will study record sets in future lessons.

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 interface is equipped with the Close() method. This can be done as follows:

System::Void btnConnection_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO;
	 String ^ strConnection, ^ strStatement;
	 Object ^ obj = gcnew Object;

	 conADO = gcnew ADODB::Connection;
	 strConnection = (L"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source='C:\\Programs\\Exercise1.mdb'");
	 strStatement = "Blah Blah Blah";

	 conADO->Open(strConnection, L"", L"", 0);
	 conADO->Execute(strStatement, obj, 0);
	 conADO->Close();
}