Persisting the data processed by an application has become the norm of the day. The storage can be either file-system using normal files or databases. The functionalities provided by database packages make them more attractive proposition. With the advent of Open Source database products such as MySQL, use of database for data persistence more or less ubiquitous. Hence, no language or platform can ignore providing libraries to access databases especially MySQL and .Net, as a platform and C#, as a language, are no exceptions. There are three main Data Providers, as the database access APIs are known as in .Net, which are SQL Data Provider, OleDB Data Provider and ODBC Data Provider. Of these I would be focusing on OleDB Data Provider and on using it to work with MySQL database. First section would provide insight into the various APIs that forms the OleDB. Second section would detail the steps required to access MySQL using OleDB. In the last section, I would develop a real-world application that implements the theory provided in first two 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 System.Data namespace specifically all the classes of OleDB come under System.Data.OleDb namespace. OleDB had been around before .Net come picture. What OleDB provider does is that it provides mechanism to access OleDB data source (Databases that could be connected through OleDB) in managed space of .Net. in essence, OleDB Data Provider sits between .Net based application and OleDB. The main classes that form 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 the instance of the other. For example, OleDbCommand provides instance of OleDbDataReader.
1. OleDbConnection:
It 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 would be disabled. To create an instance of OleDbConnection, its constructor has to be called with connection string. Connection string specifies the parameters needed to connect with the data source. Following statement shows such an example:
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.
2. OleDbCommand:
OleDbCommand represents a command to be executed against a data source connected through OleDbConnection instance. In the context of databases the command can be an SQL statement or a Stored Procedure. To get an instance of OleDbCommand, its constructor has to be called with instance of OleDbConnection class and the string containing the SQL query to be executed. For example, the following statement creates an instance of OleDbCommand named command:
string queryString = “SELECT OrderID, CustomerID FROM Orders”;
OleDbCommand command = new OleDbCommand(queryString, conn);
3. OleDbDataAdapter:
It represents a set of commands and a connection that is used to fill a DataSet. In other words it is a bridge between DataSet and the data source to retrieve and update the data. The constructor of the OleDbDataAdapter needs to be called with SQL select statement and OleDbConnection instance. To cite an example following creates an instance of OleDbDataAdapter named adapter
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, conn);
4. OleDbDataReader:
It provides a mechanism to read forward only stream of records and columns from the data source. To obtain an instance of OleDbDataReader, executeReader() method of OleDbCommand has to be called. Following statement does the same:
OleDbDataReader reader = command.ExecuteReader();
One point to keep in mind while using OleDbDataReader is that while it is being used, the corresponding connection would be kept busy, as it uses a stream to communicate with data source.
So now as the main classes have been discussed, next step is in understanding how MySQL and OleDB Data Provider link with each other. What OleDB Data Provider does exactly is that it 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 vendor of the database. In this case the vendor is MySQL. Hence unless MySQL provides the OleDB provider, OleDB Data Provider wont be able to communicate with database server. The Provider supplied by MySQL has to be registered with .Net so that OleDB Data Provider can call the Provider. The other term for the OleDB Provider is Database Driver. In case of MySQL it is also known as MySQL connector. Next I will be discussing the steps to access MySQL.
Accessing MySQL – Step by Step:
As the Data Provider in discussion is OleDB, the steps to access MySQL doesn’t change from accessing any other databases. The point of difference comes in connection string. Lets 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 picture at first step. And it is the connection string that decides which underlying Driver has to be called.
1. Creating the Connection object:
Creating a connection object really means obtaining an instance of OleDbConnection class. The constructor takes connection string as parameter. Connection string is composed of the following:
a. Provider – it specifies the vendor of the driver. In case of MySQL, the value
would be MySqlProv.
b. Data Source – the name of the machine where the server is residing.
c. User Id – the user name with which to connect to the database
d. Password – the password with which to connect with database
The connection string is a collection of name value pairs separated by semicolon. For example, to connect with a database at localhost with user name as root and no password, the connection string would be:
string strConnect = “Provider=MySqlProv;” +
“Data Source=localhost;” +
“User id=root;” +
“Password=;”
And OleDbConnection instance that can be obtained by using the connection string would be thus:
OleDbConnection conn = new OleDbConnection( strConnect);
2. Instantiating Command Object:
The next step in accessing MySQL is creating an instance of OleDbCommand class so that an SQL statement to be executed at 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);
3. Obtaining Data Reader Object:
Next step is to retrieve the result. But 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 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, ExecuteReader() method of OleDbCommand instance. So accordingly to get an instance named reader, the statement would be:
OleDbDataReader reader = command.ExecuteReader();
4. Retrieving the records:
The records can be retrieved using the Read() method of OleDbDataReder. It returns true if more records are available else returns false. To access the specific column GetString() method of OleDbDataReder. It takes column no. as the argument. For example, following code block reads the value of second column of each row (columns are zero indexed):
while( reader.Read())
Console.WriteLine(reader.GetString(1));
For extracting data from columns having type different from varchar, OleDb Data Provider gives different .Net types mapped to SQL types.
That brings us to the end of second section. In the next section, I would be developing a small application that would use MySQL OleDB Data Provider to access MySQL database server.
MySQL Access – In Real World:
The example I will be developing would primarily focus on a class that returns OleDbConnection, OleDbCommand and OleDbDataReader instances. It contains two classes:
Data – It creates and returns instances of OleDbConnection, OleDbCommand and
OleDbDataReader.
DataTest – It tests the functionalities provided by Data class.
Lets start with Data class. Its parameterized constructor creates the connection string from the parameters passed and instantiates OleDbConnection connection class using the string. The getDataReader method returns a OleDbDataReader instance based on the OleDbCommand instance passed. Here is the class:
using System;
using System.Data;
using System.Data.OleDb;
namespace MySQLApp
{
///
/// Creates and returns OleDbConnection, OleDbCommand and ///OleDbDataReader
///
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 the DataTest. It creates an instance of the Data class and executes an SQL statement using it. Here is the code:
using System;
namespace MySQLApp
{
///
///Creates an instance of Data class to execute a simple SQL ///statement.
///
class DataTest
{
///
/// The main entry point for the application.
///
[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 would form the basis of the advanced operations using DataAdapter in the next part of this discussion. Till then…
This article can be found at:
http://www.aspfree.com/c/a/Database/
Database-Programming-in-C-Sharp-with-MySQL-Using-OleDB/
