How to access the MySQL database in. net

Introduction if you have worked in a large group of companies, you will have the opportunity to access MySQL, although it does not support transaction processing, 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 ". Well, there are still some stories. Fortunately, we have other options. Here we will introduce two methods to access the MySQL database. Use ODBC. netodbc. net (full name ODBC. NET data provider) is a free.. NET Framework additional components, which need to be downloaded on the Microsoft Website: 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: ODBC in Object design. 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. Most people may not know about mysqldrivercs. 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:

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(10, '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();}

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. Some readers have actually 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. This article summarizes the two methods for accessing MySQL databases, and provides a simple test on their performance, hoping to help readers develop MySQL databases.. NET application provides a valuable reference. Original article: http://www.microsoft.com/china/community/Column/63.mspx