If you are not working in the big group company, you will have a chance to come into contact with MySQL, although it does not support transaction processing, stored procedures, but it provides the functionality to meet most of your needs, additional, concise MySQL also has some unique advantages, in some time, its speed or exceeds large databases.
So how do you access the mysql database in .NET? Perhaps many people will immediately say: Use OLEDB, but in fact, it uses .NET OLEDB DATA Provider and cannot access mysql. If you are using, the system will prompt you: "NET DATA OLE DB Provider (System.Data.odbc) Support MSDasQL provider (Microsoft OLE DB provider for ODBC drivers). ". What is why I don't know, according to the author of MySQLDRIVERCS is that it is "Abandoned by the Owner", huh, I am still some story. .
Fortunately, we have other options, here is to introduce two ways to access the MySQL database.
Use ODBC.NET
ODBC.NET (full name ODBC .NET DATA Provider) is a free .NET Framework additional component, you need to download on Microsoft's website, download address is: http://download/dasdk/install/Download/dasdk/install/ 1.0.4030.0/W98NT42kmexp/en-us/odbc_net.msi, it requires the system already installed MDAC 2.7 or higher. In addition, you also need to install MySQL ODBC drivers, download the address: http://www.mysql.com/downloads/api-myodbc-2.50.html, you also need to configure DSN in the ODBC Data Source Manager ", As shown below:
In the design of the object, ODBC.NET is also the same as OLEDB, SQL, etc., which is ODBCConnection, ODBCCMMAND, ODBCDataAdapter, ODBCDataReader, and usage, if you want to replace the previous OLEDB .NET Data Provider, fact You can modify your program by finding a replacement approach.
The following is an example of a code:
Try
{
String constr = "DSN = mysql;" "uid =;" "pwd =";
CONN = New ODBCCONNECTION (Constr);
Cn.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);
Cn.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);
Cn.open ();
cmd3.executenon query ();
}
Catch (Exception EX)
{
Messagebox.show (ex.Message);
}
Finally
{
CONN.CLOSE ();
}
As long as the person written by C # writes the database application must know that the above code executes 100,000 inserted data and read data, and finally records all the deleted operations.
Use MySQLDRIVERCS
Most people don't know this thing, MySQLDRIVERCS is a free open source .NET driver for my mysql database. And SQL .NET DATA Provider is the same as SQL Server, it is designed specifically for mysql, which can be called Mysql .NET DATA Provider. Using him does not need additional to set an ODBC data source, basically as long as you can connect to MySQL to access it through mysqldrivercs.
MySQLDRIVERCS is a project on SourceForge.net, but I don't know why, this site is not visited in China.
Below is an example of code using MySQLDRIVERCS:
Mysqlconnection conn = null;
Try
{
String connStr = "data source = mysql; password = root; user id = root; location = localhost";
CONN = New MySQLConnection (constr);
Cn.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);
Cn.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);
Cn.open ();
cmd3.executenon query ();
}
Catch (Exception EX)
{
Messagebox.show (ex.Message);
}
Finally
{
CONN.CLOSE ();
}
Almost exactly the same as the code above, the difference is ODBC to become mysql, in addition, the point to note is that the Command's ExecuteReader method becomes ExecuteReaderex in MySqldrivercs, and some subtle differences, please refer to the included document. Introduce.
Performance Testing
Some readers have actually seen the intention of the code written above, yes, in fact, the purpose is to perform performance testing. The execution time of the above two codes is: ODBC.NET is about 24 seconds, MySQLDriverCs is about 17 seconds. The result is not unexpected, as a dedicated data driver of MySQL, the speed of mysqldriver is much faster than odbc.net is rational.
to sum up
This article introduces two methods of mysql database access, and has a simple test for their performance, hoping to provide a valuable reference for readers when using the MySQL database development .NET application.
