If you have not only worked in a large group company, you will definitely have the opportunity to come into contact with MySQL. Although it does not support transaction processing and stored procedures, the functions it provides will definitely meet most of your needs. In addition, it is concise MySQL also has some unique advantages. In some cases, its speed is even faster than that of large databases.
So how to access MySQL database in .NET? Maybe many people will immediately say: Use OLEDB, but in fact, using .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 provider (Microsoft OLE DB provider for Odbc driver). I don't know why. According to the author of MySQLDriverCS, it was "abandoned by the owner". Haha, maybe there are 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 component that needs to be downloaded from the Microsoft website. The download address is: http://download.microsoft.com/download/ dasdk/Install/1.0.4030.0/W98NT42KMeXP/EN-US/odbc_net.msi , which requires that the system has MDAC 2.7 or higher installed. In addition, you also need to install the ODBC driver for MySQL. The download address is: http://www.mysql.com/downloads/api-myodbc-2.50.html . You also need to configure the DSN in the "ODBC Data Source Manager".
In terms of object design, ODBC.NET is the same as OLEDB, SQL, etc., namely OdbcConnection, OdbcCommand, OdbcDataAdapter, OdbcDataReader. The usage is exactly the same. If you want to use ODBC .NET to replace the previous OleDb .NET Data Provider, in fact You can completely modify your program by searching and replacing.
Here is an example 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();
}
Anyone who has written a database application in C# must know that the above code performs operations of inserting data and reading data one hundred thousand times, and finally deletes all data records.
Most people may not know this
when using MySQLDriverCS
. MySQLDriverCS is a free and open source .NET driver for the MySQL database.Just like Sql .NET Data Provider is for Sql Server, it is specially designed for MySQL and can be called MySQL .NET Data Provider. There is no need to set up an additional ODBC data source to use it. Basically, as long as you can connect to MySQL, you can access it through MySQLDriverCS.
MySQLDriverCS is a project on SourceForge.NET, but for some unknown reason, this website cannot be accessed in China.
The following is a code example 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();
}
It is almost exactly the same as the above code. The difference is that Odbc has become MySQL. In addition, one thing to note is that the ExecuteReader method of Command has become ExecuteReaderEx in MySQLDriverCS. For some subtle differences, please refer to the attached document for details. introduction.
Performance testing
Some readers have actually seen the purpose of the code I wrote above. By the way, the purpose is actually to conduct performance testing. The execution time of the above two pieces of code is: about 24 seconds for ODBC.NET and about 17 seconds for MySQLDriverCS. The results are not surprising. As a dedicated data driver for MySQL, it is reasonable for MySQLDriverCS to be much faster than ODBC.NET.
Summary
This article introduces two MySQL database access methods and conducts a simple test on their performance. I hope it can provide readers with a valuable reference when using MySQL database to develop .NET applications.