Database Programming in C# with MySQL : Using OleDB

There are three main Data Providers, as the database access APIs are known in .Net, which are the SQL Data Provider, the OleDB Data Provider and the ODBC Data Provider. Of these I will be focusing on the OleDB Data Provider and using it to work with a MySQL database. The first and second sections of this article will provide insight into the various APIs that form the OleDB. The third section will detail the steps required to access MySQL using OleDB. In the last section, I will develop a real-world application that implements the theory provided in the first three sections. That's the outline for this discussion.

OleDB: What is it?

OleDB is one of the three Data Providers supported by .Net. It is part of the System.Data namespace; specifically, all the classes of OleDB come under the System.Data.OleDb namespace. OleDB had been around before .Net came into the picture. The OleDB Provider provides a mechanism for accessing the OleDB data source (databases that could be connected through OleDB) in the managed space of .Net. In essence, the OleDB Data Provider sits between a .Net-based application and OleDB. The main classes that form the OleDB Data Provider are:

1. OleDbConnection
2. OleDbCommand
3. OleDbDataAdapter
4. OleDbDataReader

Most of the classes are arranged in a hierarchical manner, that is, one provides an instance of the other. For example, OleDbCommand provides an instance of OleDbDataReader.

OleDbConnection represents a connection with a data source such as a database server. Each connection represented by OleDbConnection's instance is unique. When an instance of OleDbConnection is created, all its attributes are given or set to their default values. If the underlying OleDB Provider doesn't support certain properties or methods, the corresponding properties and methods of OleDbConnection will be disabled. To create an instance of OleDbConnection, its constructor has to be called with a connection string. The connection string specifies the parameters needed to connect with the data source. The following statement shows an example of this:

OleDbConnection conn = new OleDbConnection(
     "Provider=MySqlProv;" +
     "Data Source=localhost;" + 
     "User id=UserName;" + 
     "Password=Secret;"
);

The above example provides a connection to MySQL server at local machine.

OleDbCommand represents a command to be executed against a data source connected through an OleDbConnection instance. In the context of databases the command can be a SQL statement or a stored procedure. To get an instance of OleDbCommand, its constructor has to be called with an instance of the OleDbConnection class and the string containing the SQL query to be executed. For example, the following statement creates an instance of an OleDbCommand named command:

string queryString = "SELECT OrderID, CustomerID FROM Orders";
OleDbCommand command = new OleDbCommand(queryString, conn);

OleDB continued

OleDbDataAdapter represents a set of commands and a connection that is used to fill a DataSet. In other words it is a bridge between a DataSet and the data source to retrieve and update the data. The constructor of the OleDbDataAdapter needs to be called with a SQL select statement and an OleDbConnection instance. To cite an example, the following creates an instance of an OleDbDataAdapter named adapter:

OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, conn);

OleDbDataReader provides a mechanism for reading forward only a stream of records and columns from the data source. To obtain an instance of OleDbDataReader, the executeReader() method of OleDbCommand has to be called. The following statement does the same:

OleDbDataReader reader = command.ExecuteReader();

Keep in mind that, while OleDbDataReader is being used, the corresponding connection will be busy, as it uses a stream to communicate with the data source.

Since the main classes have been discussed, the next step involves understanding how MySQL and the OleDB Data Provider link with each other. The OleDB Data Provider calls the underlying OleDB Provider. So it is the OleDB Provider that communicates with the data source. For each database system, the OleDB Provider has to be provided by the vendor of the database.

In this case the vendor is MySQL. Hence unless MySQL provides the OleDB Provider, the OleDB Data Provider won't be able to communicate with the database server. The Provider supplied by MySQL has to be registered with .Net so that the OleDB Data Provider can call the Provider. The other term for the OleDB Provider is Database Driver. In the case of MySQL it is also known as the MySQL connector. Next I will be discussing the steps required to access MySQL.  

Accessing MySQL, Step by Step

As the Data Provider in this discussion is OleDB, the steps required to access MySQL aren't any different from those for any other database. The point of difference comes in the connection string. Let's look at the steps:

1. Creating the Connection object
2. Instantiating the Command object
3. Obtaining the DataReader object
4. Retrieving the records

The connection string comes into the picture in the first step. It is the connection string that decides which underlying Driver has to be called.

Creating the Connection object

Creating a connection object really means obtaining an instance of the OleDbConnection class. The constructor takes the connection string as a parameter. The connection string is composed of the following:

1. Provider: specifies the vendor of the driver. In the case of MySQL, the value would be MySqlProv.
2. Data Source: the name of the machine on which the server resides.
3. User Id: the user name with which to connect to the database.
4. Password: the password with which to connect with database.

The connection string is a collection of name value pairs separated by a semicolon. For example, to connect with a database at localhost with the user name root and no password, the connection string would be:

string strConnect = "Provider=MySqlProv;" + 
       "Data Source=localhost;" + 
       "User id=root;" + 
       "Password=;"

An OleDbConnection instance that can be obtained by using the connection string would be thus:

OleDbConnection conn = new OleDbConnection( strConnect);

Instantiating the Command Object

The next step in accessing MySQL is creating an instance of the OleDbCommand class so that a SQL statement can be executed at the database. To obtain an instance of OleDbConnection, its constructor needs the SQL statement to be executed and the connection through which the database can be connected. For example, the following statements create an instance of OleDbCommand named command:

string strSQL= "Select * from user_master";
OleDbCommand command = new OleDbCommand(strSQL, conn);

Obtaining the Data Reader Object

The next step is to retrieve the result. For that, a stream is required that fetches data from the database. This requirement can be met by obtaining an object of OleDbDataReader. As discussed in the first section, it is a forward only stream, using which the rows and columns returned by the executed command can be read. To get an instance of OleDbDataReader, we use the ExecuteReader() method of OleDbCommand instance. So accordingly to get an instance named reader, the statement would be:

OleDbDataReader reader = command.ExecuteReader();

Retrieving the records

The records can be retrieved using the Read() method of OleDbDataReder. It returns true if more records are available, and otherwise returns false. To access the specific column use the GetString() method of OleDbDataReder. It takes column number as the argument. For example, the following code block reads the value of the second column of each row (columns are zero indexed):

while( reader.Read())
   Console.WriteLine(reader.GetString(1));

For extracting data from columns having a type different from varchar, the OleDb Data Provider gives different .Net types mapped to SQL types.

That brings us to the end of this section. In the next section, I will develop a small application that will use the MySQL OleDB Data Provider to access a MySQL database server.

MySQL Access in the Real World

The example I will be developing will primarily focus on a class that returns OleDbConnection, OleDbCommand and OleDbDataReader instances. It contains two classes: Data, which creates and returns instances of OleDbConnection, OleDbCommand and OleDbDataReader; and DataTest, which tests the functionalities provided by the Data class.

Let's start with the Data class. Its parameterized constructor creates the connection string from the parameters passed and instantiates the OleDbConnection connection class using the string. The getDataReader method returns an OleDbDataReader instance based on the OleDbCommand instance passed. Here is the class:

using System;
using System.Data;
using System.Data.OleDb;

namespace MySQLApp
{
   /// <summary>
   /// Creates and returns OleDbConnection, OleDbCommand
   /// and OleDbDataReader
   /// </summary>
   public class Data
   {
     private OleDbConnection connection=null;
     private OleDbCommand command=null;
     string connectionString=null;

     public Data()
     {
       connectionString="";
     }

     public Data(string host, string userId, string password)
     {
       connectionString=" Provider=MySqlProv; Data Source="+host+"; User id="+userId+"; Password="+password+";";

       connection=new OleDbConnection(connectionString);
       command=new OleDbCommand();
     }

     public OleDbCommand getCommand(string sqlString)
     {
       command.Connection=connection;
       command.CommandText=sqlString;
       return command;
     }

     public OleDbDataReader getReader(OleDbCommand command)
     {
       return command.ExecuteReader();
     }
   }
}

The next class is DataTest. It creates an instance of the Data class and executes a SQL statement using it. Here is the code:

using System;

namespace MySQLApp
{
   /// <summary>
   ///Creates an instance of Data class to execute a
   ///simple SQL statement.
   /// </summary>
   class DataTest
   {
     /// <summary>
     /// The main entry point for the application.
     /// </summary>
     [STAThread]
     static void Main(string[] args)
     {
       Data data=new Data("localhost","root","root123");
       data.getReader(data.getCommand("select * from 
        user"));

         while( reader.Read())
         Console.WriteLine(reader.GetString(1));

     }
   }
}

That brings us to the end of this discussion. The application developed here will form the basis of advanced operations using DataAdapter in the next part of this discussion. Till then…