Page 2 of 2   « previous page

Let's use that connection string and fill a dataset with some data. I have re-created the sample Northwind database that comes with SQL Server on my MySQL database and I will be referencing the "customers" table from Northwind in my queries.

OdbcConnection connection = new OdbcConnection(connectionString);
OdbcCommand command = new OdbcCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM customers";
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
connection.Open();
adapter.Fill(ds);
connection.Dispose();

That's all there is to it! Does it look familiar? It should, be-cause this is no different from how you can fill a dataset regardless of whether the data comes from MySQL, SQL Server, Access, or Oracle. The only thing that's different is the class names that we are using: OdbcConnection instead of SqlConnection, OdbcCommand instead of SqlCommand, etc. At this point, you should be able to use MySQL using ODBC and completely forget what kind of database you are connecting to. Since MySQL supports the SQL standard, you don't need to learn anything new, un-less you want to use the MySQL extensions to the SQL language, which is beyond the scope of this article.

Using ODBC is a great way to easily connect to a MySQL database in your .NET code. However, by using ODBC, which is designed to connect to a multitude of data sources, you sacrifice speed. One way to boost performance is by using a data provider that is designed explicitly for your particular data source. In our case, MySQL offers a data provider for .NET that specifically targets its database, called "MySQL Connector/Net," and formerly called "ByteFX.Data" (http://dev.mysql.com/downloads/connector/net/1.0.html). This data provider is open source and developed entirely in managed code. I recommend to anyone who is using this data provider that they download the source code as well, and simply browse through the source. It's an interesting experience (at least to me) to see how a data provider is actually implemented. The cool thing about MySQL Connector/Net's being open source and having been written in managed code is that you can make changes to suit your needs if need be. I wanted to use the connector in my ASP.NET 2.0 projects, so I downloaded the source and compiled it against .NET 2.0, and I was ready to go! Just because there isn't a released version for .NET 2.0 doesn't mean you can't make your own.

MySQL Connector/Net implements all of the same ADO.NET provider interfaces that you would expect:

• MySqlConnection
• MySqlCommand
• MySqlDataReader
• MySqlDataAdapter
• MySqlCommandBuilder

string connectionString = "Server=localhost;Database=northwind;Uid=user;Pwd=pass;";

So using our new connection string and our new set of provider classes, let's get the same customer dataset that we got above.

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM customers";
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet ds = new DataSet();
connection.Open();
adapter.Fill(ds);
connection.Dispose();

Since the MySQL Connector/Net implements the same interfaces as the other ADO.NET interfaces, data access is as simple as changing the class names. While the changes only seem cosmetic, the underlying changes are significant. MySQL Connector/Net speaks the MySQL database protocol natively, so there is no need to install the MySQL ODBC Driver as we had to do in order to use the System.Data.Odbc namespace above. Because we don't need this separate library, we can attain better performance results as we are eliminating a logical layer from our data access.

With the advent of .NET and the ever-improving MySQL database server, developing applications with open source components is becoming easier. With several class libraries that support connecting to a MySQL database from our .NET code, and with the suite of applications and tools available directly from MySQL, a strong case can be made to lower your business's TCO by using an open source database over more expensive commercial options.

Page 2 of 2   « previous page