Until recently, to use a Microsoft Access database in an external application, programmers based their code on ADO or previous libraries related to OLE DB. With the release of the .NET Framework, you can now use such a database in any language that is part of this huge library. To use and maintain a Microsoft Access database, you can use the .NET Framework data provider for OLE DB.

If you are planning to use a Microsoft Access database in a .NET Framework application, probably the first thing you do is to create the database. You can do this directly in Microsoft Access.

To make it possible to connect an application to a database, the .NET Framework provides a series of classes through ADO.NET and each class is made for a specific purpose. The classes of ADO.NET are stored in a namespace named System::Data. Therefore, to make sure your application has access to these classes, you can first include this namespace.

The classes you can use to connect your application to a Microsoft Access database are stored in the System::Data::OleDb namespace. To use these classes, besides including this namespace in the file where needed, you should also import the System.Data.OleDb.dll library in your project.

To access the database, the first action you take consists of establishing a connection with the database. To support this, the System::Data::OleDb namespace provides the OleDbConnection class. To use it, you can declare a pointer to the class using its default constructor. This would mean that you probably don't yet have the necessary information to complete the connection. This would be done as follows:

If you are planning to access the connection from more than one event or method, you should declare it at the class level. Here is an example:

System::Data::OleDb::OleDbConnection *oleConn;



private: System::Void Form1_Load(System::Object *sender, System::EventArgs *e)

{

	 oleConn = new System::Data::OleDb::OleDbConnection();

}

As opposed to using the default constructor, to establish a connection, you can use the other constructor of the class. Its syntax is:

public: OleDbConnection(String* connectionString);

The argument to this constructor is a string that specifies how the connection would be established.

The connection string is used to specify how the connection would be performed. It is a string made of different sections. Each section is created using the formula Argument=Value. The sections are separated by semi-colons. This would produce:

"Argument1=Value;Argument=Value2;Argument_n=Value_n;"

This string doesn't following the rules of C++. Therefore, everything in it is case-insensitive. If you had declared an OleDbConnection variable using the default constructor, to create the connection string, you can declare a String variable, "fill it up" with the necessary information, and assign it to the OleDbConnection::ConnectionString property. This would be done as follows:

System::Void btnConnect_Click(System::Object *sender, System::EventArgs *e)

{

	 System::Data::OleDb::OleDbConnection *oleConn = new 

		System::Data::OleDb::OleDbConnection();

	 oleConn->ConnectionString = S"";

}

 

The Database Provider

The first parameter or one of the parameters of a connection is the database provider. To specify the provider, you create its section with:

Provider=ProviderValue;

There are various providers available. The one used for Microsoft Access is:

Microsoft.Jet.OLEDB.4.0

If you had declared an OleDbConnection variable using the default constructor, to specify the provider, you can include a "Provider=Microsoft.Jet.OLEDB.4.0" string to your OleDbConnection::ConnectionString property. Here is an example:

If you are using an OleDbConnection variable declared using the second constructor, you can include the provider in the connectionString argument of the constructor. Here is an example:

If the connection was already established and you want to find out what provider it is using, you can get the value of the OleDbConnection::Provider property. Here is an example:

System::Data::OleDb::OleDbConnection *oleConn;



private: System::Void btnConnect_Click(System::Object *  sender, System::EventArgs *  e)

{

	 oleConn->ConnectionString = S"Provider=Microsoft.JET.OLEDB.4.0;";

}



private: System::Void btnEnquire_Click(System::Object *  sender, System::EventArgs *  e)

{

	 String *strProvider = oleConn->Provider;

	 MessageBox::Show(strProvider);

}



private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)

{

	 oleConn = new System::Data::OleDb::OleDbConnection();

}

 

The Data Source

After specifying the provider, an important factor is the name of the database you want to connect to. This information is referred to as the data source. If you are creating a connection string, to specify the data source, assign the complete path of the database to the Data Source factor. Here is an example:

System::Void btnConnect_Click(System::Object *  sender, System::EventArgs *  e)

{

	 oleConn->ConnectionString = S"Provider=Microsoft.JET.OLEDB.4.0;"

			 S"Data Source=C:\\Programs\\Exercise.mdb;";

}

If you had declared an OlDbConnection variable using the other constructor, to specify the data source, you can include its section in the connectionString argument of the constructor. Here is an example:

If the connection has already been established and the data source was specified, to know the name of the data source, you can get the value of the OleDbConnection::DataSource property.

Operations on the Database Connection

Opening a Connection

The factors we have reviewed so far allow you to specify how the connection would be established and what database would be used. Before performing any necessary operation, you must open the connection. To support this, the OleDbConnection class is equipped with the Open() method whose syntax is:

public: virtual void Open();

Here is an example of calling this method:

Closing a Connection

After establishing a connection and while using it, it consumes resources. When you have finished using a connection and if you don't need anymore or you would not use it for a while, you should close it to release its resources and make them available to other applications. To close a connection, the OleDbConnection class provides the Close() method. Its syntax is:

public: virtual void Close();

Here is an example of calling this method:

System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)

{

	 oleConn->Close();

}

Commanding a Database

Introduction

After establishing a connection to a database, you can perform actions or operations on it. To make this possible and probably easier, the System::Data::OleDb namespace provides the OleDbCommand class. As its name suggests, this class is used to create commands or actions to be performed on the database. To create a command, the OleDbCommand class is equipped with four constructors. The first constructor is the default. It allows you to declare an OleDbCommand variable without specifying its assignment.

The Type of Command

As we will see in the next few sections, a command is usually created as a string but a command can also be created as a function, also called a stored procedure. To allow you to specify the type of command you want to create, the OleDbCommand class is equipped with a property named CommandType. This property is of type OleDbCommand, which is a structure that holds values that specify the type of command to be carried.

The CommandType enumerator has three members: StoredProcedure, TableDirect, and Text. For an OleDbCommand variable, the default value is Text.

The Command Text

To specify a command to be performed on a database, you create a string but you have two main alternatives to specify this string.

Instead of declaring an OleDbCommand variable using the default constructor, you can use the second constructor whose syntax is:

public: OleDbCommand(String* cmdText);

This constructor takes as argument a string that is the object of the command. If you had declared the OleDbCommand variable using the default constructor, to specify the command, you can assign a string to the OleDbCommand::CommandText property. We will see various examples of creating such a command text and using it.

The Connection to the Command

After creating a command, you must pass it to the database but the command has no way of find that database. The role of the connection, that we discussed earlier, is to carry the command to the database. Remember that the connection knows where the database is, through its data source factor.

To specify what connection would carry the command, if you decided to create the command using an OleDbCommand constructor other than the default, you can use the third constructor whose syntax is:

public: OleDbCommand(String* cmdText, OleDbConnection* connection);

If you had declared an OleDbCommand variable using its default constructor, to specify the connection, you can assign it to the OleDbCommand::Connection property. In both cases, you must provide an appropriate OleDbConnection value using the techniques we reviewed earlier.

The Execution of a Command

After creating the command and passing it to the database through the connection, you can then execute it. To support this, the OleDbCommand class is equipped with a method named ExecuteNonQuery(). Its syntax is:

public: virtual int ExecuteNonQuery();

This method is normally called to execute a command that carries a SQL statement.

The Command Timed Out

In some cases, some actions take longer than others to execute. For this type of command, the compiler would keep trying to execute a command until successful. If there is a problem, this operation can take long or too long. You can specify how long the compiler should wait to try executing the command, again.

The OleDbCommand::CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds). If you want a different value, assign it to your OleDbCommand variable.