C connections operate the MySQL database instance of using the official driver

MySQL is very popular because of its free and sufficient performance. Of course, for small domestic companies, or even large companies, if they are too cautious about copyright and dare to take risks, they can install SqlServer, DB2 and Oracle at will. For the SqlServer database, because it is the same as MS, there is naturally built-in support in the.net class library. If MySQL has to find a third driver -- Provider in.net. Here, too, As a pre-research topic, I have documented two ways in which C# connects to MySQL, using the official MySQL and an open source MySQL driver on SourceForge. As for the ODBC method, it doesn't make much sense. It also requires installing an MySQL ODBC driver, and ODBC is so clumsy.

Use the MySQL official zone connection to operate the MySQL database

Download driver, can be in http: / / www mysql. com downloads/connector net / 6.3 html # downloads download, the current version is 6.3. You can choose to use either Microsoft Windows or.Net & Mono, the difference is that Microsoft Windows is an MSI file, which is a non-installed zip. However, the MSI file was not installed successfully on my 64-bit XP, so I used the latter.

This connector net is not only an MySQL driver, but also provides integrated support like Entity Frameword, ES52en. Net Web Providers, Compact Framework, VS 2008, as well as configuration examples of CSharp and VB. For detailed configuration and usage documentation, please refer to the MySql.Data.chm file in the installation directory.

Now from the simple example, then drive installed, or after package unlock, don't in the installation directory of mysql. data. cf. dll, mysql. data. entiry. dll, mysql. visualstudio. dll and mysql web. dll, we temporarily in the project introduced mysql. data. dll, use using MySql. Data. MySqlClient; Introduce namespaces. Then the simple code is as follows:

// Connection string 

 string connStr = "Server=localhost;Database=unmicc;Uid=unmicc;Pwd=xxxxxx;CharSet=utf8;";

 MySqlConnection con = new MySqlConnection(connStr);

 

 con.Open();// Open the connection 

 

 MySqlCommand cmd = new MySqlCommand("select now()",con);

 

 object time = cmd.ExecuteScalar(); // or  cmd.ExecuteReader();cmd.ExecuteNonQuery();

 

 MessageBox.Show(time.ToString());

 // or  Console.WriteLine(time.ToString());

 

 con.Close();

 

The above connection string should be easy to understand, and there are more configuration parameters such as port numbers, connection pool-related configuration, etc. Refer to Connection Options in the manual for details. Other operations is a standard ADO. NET, and can handle exception of each step catch MySql. Data. MySqlClient. MySqlException this type of exception. Other MySqlDataAdapter and MySqlDataReader for populating the data are also ready.

I think parameterized queries will definitely be mentioned in the formal application to prevent SQL injection, so take a look at how the OFFICIAL MySQL driver handles parameterized queries. It uses the same way as SqlServer Provider, using @author to identify parameters, and also supports AddWithValue(string name, object value). See the code:

string sql = "update wp_posts set post_author=@author and post_status=@status where id=@id";

 

 // You can use  ?  Take the form of, e.g., but  ?  The form of number is not recommended 

 //string sql = "update wp_posts set post_author=?author and post_status=?status where id=?id";

 

 MySqlCommand cmd = con.CreateCommand();

 cmd.CommandText = sql;

 

 cmd.Parameters.AddWithValue("@author", 1);

 //cmd.Parameters.AddWithValue("?author", 1);

 cmd.Parameters.AddWithValue("@status", "publish");

 //cmd.Parameters.AddWithValue("?status", "publish");

 cmd.Parameters.AddWithValue("@id", 23);

 //cmd.Parameters.AddWithValue("?id",23);

 

 cmd.ExecuteNonQuery();

Note that it can also be used ? author is used to identify parameters, but this is not recommended now, perhaps to unify the @author format. I don't know when it will be like JDBC 1, just use ? To serve as a placeholder.

For THE InnoDB storage engine, MySQL supports things. The official driver supports things in the following code:
[code]
MySqlTransaction trans = con.BeginTransaction(); // Enable things
trans.Commit(); // Submit normally
trans.Rollback(); // Rollback in case of exception
[code]
The next article describes how to operate the MySQL database using the open source ES142en.ES143en driver on SourceForge.