How to access the MySQL database in. NET

[From vstart's blog] If you have not worked in a large group company, you will certainly have the opportunity to access MySQL, although it does not support transaction processing and stored procedures, however, the functions provided by MySQL can satisfy most of your needs. In addition, simple MySQL has some unique advantages. In some cases, it is faster than large databases.

[From vstart's blog] If you have not worked in a large group company, you will certainly have the opportunity to access MySQL, although it does not support transaction processing and stored procedures, however, the functions provided by MySQL can satisfy most of your needs. In addition, simple MySQL has some unique advantages. In some cases, it is faster than large databases.

[From vstart's blog] If you have not worked in a large group company, you will certainly have the opportunity to access MySQL, although it does not support transaction processing and stored procedures, however, the functions provided by MySQL can satisfy most of your needs. In addition, simple MySQL has some unique advantages. In some cases, it is faster than large databases.

So how to access the MySQL database in. NET? Maybe many people will immediately say: OLEDB is used, but it is actually used. NET OleDb Data Provider cannot access MySQL. If you use it, the System will prompt you: "Net Data ole db Provider (System. data. odbc) does not support MSDASQL providers (Microsoft ole db providers for Odbc drivers ). ", I don't know why. According to the author of MySQLDriverCS, it is "abandoned by the owner.

Fortunately, we have other options. Here we will introduce two methods to access the MySQL database.

Use ODBC. NET

ODBC. NET (ODBC. NET Data Provider) is a free. NET Framework additional component that needs to be downloaded from the Microsoft website, which is release 2.7 or later. In addition, you also need to install the ODBC driver for MySQL, Which is http://www.mysql.com/downloads/api-mydbc-2.50.html, and configure the DSN in "ODBC data source Manager", as shown in:

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'src = "/files/uploadimg/20070425/0949220 .jpg">

In the design of objects, ODBC. NET is also the same as OLEDB and SQL, namely OdbcConnection, OdbcCommand, OdbcDataAdapter, and OdbcDataReader. The usage is also the same. If you want to use ODBC.. NET to replace the previous OleDb. NET Data Provider, in fact, you can find a replacement method to modify your program.

The following is a sample code:

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 you use C # To write a database application, you can understand that the code above has performed 100,000 data insertion and reading operations, and finally deleted all data records.

Use MySQLDriverCS

Most people may not know about this. MySQLDriverCS is a free and open-source. NET driver for MySQL databases. Like SQL. NET Data Provider, It is designed specifically for MySQL and can be called MySQL. NET Data Provider. You do not need to set the ODBC Data Source. Basically, you can use MySQLDriverCS to connect to MySQL.

MySQLDriverCS is a project on SourceForge. NET, but I don't know why. This website cannot be accessed in China.

The following is a sample code using MySQLDriverCS:

The code above is almost identical. The difference is that Odbc is changed to MySQL. In addition, note that the ExecuteReader method of Command is changed to ExecuteReaderEx in MySQLDriverCS, for more details about the differences, see the attached documentation.

Performance Testing

Some readers have seen the intention of the code I wrote above. By the way, the purpose is to conduct performance testing. The execution time of the above two sections of code is about 24 seconds for ODBC. NET and about 17 seconds for MySQLDriverCS. The results are not surprising. As a dedicated MySQL Data driver, MySQLDriverCS is much faster than ODBC. NET.

Summary

This article introduces two MySQL database access methods, and provides a simple test on their performance, hoping to help readers develop MySQL databases.. NET application provides a valuable reference.

[Related articles]

• JSP connection to Mysql database

• Use Excel to analyze MySQL Data

• Similarly, MySQL and ASP. NET are more powerful.