How to be in. NET to access the MySQL database

mysql| Access | data | Database Summary

As a small relational database, MySQL has won the favor of a large number of small and medium-sized enterprises and individual users with the features of low price, powerful function, decent speed and open source. But in. NET access to the MySQL database is not as convenient as it might be, because the compatibility of. NET OLE DB Data provider and MySQL is not ideal. This article introduces two kinds of in. NET to access the MySQL database, and the two methods do a simple performance comparison.

Introduction

If you are not only working in a large group, you will have access to MySQL, although it does not support transaction processing, stored procedures, but it provides the functionality will be able to meet most of your needs, in addition, concise MySQL has some of its unique advantages, in some cases, It is even faster than a large database.

So how in. NET to access the MySQL database? Many people may soon say: OLE DB, but in fact, the. NET OLE DB Data provider does not have access to MySQL, and if you do, the system prompts you: "NET Data OLE DB provider (SYSTEM.DATA.ODBC) does not support MSDASQL provider (Microsoft OLE DB provider for ODBC drivers). "What's the reason I don't know, according to Mysqldrivercs's author is that it was" abandoned by the owner ", hehe, maybe some stories.

Fortunately, we have other options, here are two ways to access the MySQL database.

Using Odbc.net

Odbc. NET (full name ODBC. NET Data Provider) is a free. NET Framework add-on that needs to be downloaded to Microsoft's Web site: http://download.microsoft.com/download /dasdk/install/1.0.4030.0/w98nt42kmexp/en-us/odbc_net.msi, it requires the system to have MDAC 2.7 or later installed. Additionally, you will need to install the ODBC driver for MySQL, with the download address: http://www.mysql.com/downloads/api-myodbc-2.50.html, and you will need to configure DSN in the ODBC Data Source Administrator.

In the design of objects, ODBC. NET also like Oledb,sql, respectively, for OdbcConnection, OdbcCommand, OdbcDataAdapter, OdbcDataReader, usage is exactly the same, if you want to use ODBC. NET to replace the previous OLE DB. NET Data Provider, you can actually modify your program by looking for replacements.

The following is a code example:

Try
{
String constr = "dsn=mysql;" + "uid=;" + "pwd=";;
conn = new OdbcConnection (CONSTR);
Conn. Open ();
string query = "INSERT INTO test.dbtable values10, ' disksidkfsdi ', ' asdfaf ', ' adsfasdf ')";
string tmp = NULL;
OdbcCommand cmd = new OdbcCommand (query, conn);
for (int i = 0; i < 100000; i++)
{
Cmd. ExecuteNonQuery ();
}
Cmd. Dispose ();
Conn. Close ();
query = "SELECT * from test.dbtable";
OdbcCommand cmd2 = Newodbccommand (query, conn);
Conn. Open ();
OdbcDataReader reader = cmd2. ExecuteReader ();
while (reader. Read ())
{
TMP = Reader[0]. ToString ();
TMP = reader[1]. ToString ();
TMP = reader[2]. ToString ();
TMP = Reader[3]. ToString ();
}
Conn. Close ();
query = "Delete from test.dbtable";
OdbcCommand cmd3 = Newodbccommand (query, conn);
Conn. Open ();
Cmd3. ExecuteNonQuery ();
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Conn. Close ();
}
As long as it is written in C # of the database application must be able to know, the above code executed 100,000 times to insert data and read data, and finally the data records all deleted operations.


Using Mysqldrivercs

Probably most people don't know this stuff, Mysqldrivercs is a free and open source. NET driver for the MySQL database. The SQL. NET data Provider, like SQL Server, is designed specifically for MySQL and can be called the MySQL. NET data Provider. Using him does not require additional to set up ODBC data source, basically as long as can connect to MySQL can be accessed through Mysqldrivercs.

Mysqldrivercs is a project on the SourceForge.net, but for some reason, the site is not accessible at home.

The following is a code example that uses Mysqldrivercs:


Mysqlconnection conn = null;
Try
{
String connstr = "Data source=mysql;" Password=root; User Id=root; Location=localhost ";
conn = new mysqlconnection (CONSTR);
Conn. Open ();
string query = "INSERT into test.dbtable values (ten, ' Disksidkfsdi ', ' asdfaf ', ' adsfasdf ')";
string tmp = NULL;
Mysqlcommand cmd = new Mysqlcommand (query, conn);
for (int i = 0; i < 100000; i++)
{
Cmd. ExecuteNonQuery ();
}
Cmd. Dispose ();
Conn. Close ();
query = "SELECT * from test.dbtable";
Mysqlcommand cmd2 = new Mysqlcommand (query, conn);
Conn. Open ();
Mysqldatareader reader = cmd2. Executereaderex ();
while (reader. Read ())
{
TMP = Reader[0]. ToString ();
TMP = reader[1]. ToString ();
TMP = reader[2]. ToString ();
TMP = Reader[3]. ToString ();
}
Conn. Close ();
query = "Delete from test.dbtable";
Mysqlcommand cmd3 = new Mysqlcommand (query, conn);
Conn. Open ();
Cmd3. ExecuteNonQuery ();
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Conn. Close ();
}

And the above code is almost exactly the same, the difference is that ODBC has become MySQL, in addition, it is important to note that the command of the ExecuteReader method in the Mysqldrivercs into a Executereaderex, There are some subtle differences please refer to the accompanying documentation for a detailed description.


Performance test

Some readers have actually seen the meaning of the code I wrote above, and, in fact, the purpose is to test the performance. The above two pieces of code execution time is: Odbc.net is about 24 seconds, Mysqldrivercs is about 17 seconds. The result is not surprisingly, as MySQL's dedicated data driver, Mysqldrivercs faster than odbc.net is reasonable.


Summarize

This paper introduces two methods of MySQL database access, and makes a simple test of their performance, hoping to provide a valuable reference for readers when using MySQL database to develop. NET applications.