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.

Next: MySQL Access in the Real World >>
 

More Database Articles
More By A.P.Rajshekhar

Comments On: Database Programming in C# with MySQL : Using OleDB

· In this article I have discussed about using OleDB Data Provider to access MySQL...    · But in this Article i m getting this error"The 'MySqlProv' provider is not...   >>> Post your comment now!