Database

Database Programming in C# with MySQL : Using OleDB,

Persisting the data processed by an application has become the norm. The data can be stored either in a file system using normal files or in databases. The functionalities provided by database packages make them a more attractive proposition. With the advent of open source database products such as MySQL, the use of databases for data persistence has become more or less ubiquitous. Hence, no language or platform can ignore the need to provide 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 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:

OleDbConnection(oledbcon)
OleDbCommand(oledb com)
OleDbDataAdapter(oledb da)
OleDbDataReader(oledb dr)

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);