C#中连接MySQL数据

小结一下MySQL在C#中是如何连接的，并做一些简单的选择（SELECT）、插入（ INSERT）、更新（ UPDATE）、删除（DELETE ）

（一）连接

a) Firstly, you should install MySQL. To use the methods in the MySQL Connector/NET you should add a reference to it. Right click your project in the Solution Explorer and click Add Reference… In the .NET tab, chose MySql.Data and click ok.

b) In your code, you should add the line using MySql.Data.MySqlClient; in order to be able to use methods for accessing MySql.

using MySql.Data.MySqlClient;

c) To Connect to a MySql Database:

 1 String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
 2 MySqlConnection con = null;
 3 try
 4 {
 5 con = new MySqlConnection(str);
 6 con.Open(); //open the connection
 7 }
 8 catch (MySqlException err) //We will capture and display any MySql errors that will occur
 9 {
10 Console.WriteLine("Error: " + err.ToString());
11 }
12 finally
13 {
14 if (con != null)
15 {
16      con.Close(); //safely close the connection
17 }
18 } //remember to safely close the connection after accessing the database

localhost是服务器地址。

（二）其他操作

a) 执行已准备的一些MySQL指令To code some MySQL commands using Prepared Statements:

 1 String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
 2 MySqlConnection con = null;
 3 try
 4 {
 5 con = new MySqlConnection(str);
 6 con.Open(); //open the connection
 7 //This is the mysql command that we will query into the db.
 8 //It uses Prepared statements and the Placeholder is @name.
 9 //Using prepared statements is faster and secure.
10 String cmdText = "INSERT INTO myTable(name) VALUES(@name)";
11 MySqlCommand cmd = new MySqlCommand(cmdText,con);
12 cmd.Prepare();
13 //we will bound a value to the placeholder
14 cmd.Parameters.AddWithValue("@name", "your value here");
15 cmd.ExecuteNonQuery(); //execute the mysql command
16 }
17 catch (MySqlException err)
18 {
19 Console.WriteLine("Error: " + err.ToString());
20 }
21 finally
22 {
23 if (con != null)
24 {
25      con.Close(); //close the connection
26 }
27 } //remember to close the connection after accessing the database

*Note: You can also try this kind of cmdText, “INSERT INTO myTable VALUES(@name,@age,@contact)”;
Then add the cmd.Parameters.AddWithValue for each placeholder @name,@age,@contact.
If you don't want a column in your table to be bypassed you may use NULL. e.g. “INSERT INTO myTable VALUES(NULL,@name,@age,@contact)”;

b) 使用MySqlDataReader检索数据To Retrieve Data using MySqlDataReader:

 1 String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
 2 MySqlConnection con = null;
 3 //MySqlDataReader Object
 4 MySqlDataReader reader = null;
 5 try
 6 {
 7 con = new MySqlConnection(str);
 8 con.Open(); //open the connection
 9 //We will need to SELECT all or some columns in the table
10 //via this command
11 String cmdText = "SELECT * FROM myTable";
12 MySqlCommand cmd = new MySqlCommand(cmdText,con);
13 reader = cmd.ExecuteReader(); //execure the reader
14 /*The Read() method points to the next record It return false if there are no more records else returns true.*/
15 while (reader.Read())
16 {
17 /*reader.GetString(0) will get the value of the first column of the table myTable because we selected all columns using SELECT * (all); the first loop of the while loop is the first row; the next loop will be the second row and so on...*/
18      Console.WriteLine(reader.GetString(0));
19 }
20 }
21 catch (MySqlException err)
22 {
23 Console.WriteLine("Error: " + err.ToString());
24 }
25 finally
26 {
27 if (reader != null)
28 {
29      reader.Close();
30 }
31 if (con != null)
32 {
33      con.Close(); //close the connection
34 }
35 } //remember to close the connection after accessing the database

c) 执行某一些SQL语句To Execute Some MySql Statements:

SELECT:

1 //This is the simple code of executing MySql Commands in C#
2 String cmdText = "SELECT id,name,contact FROM myTable"; //This line is the MySql Command
3 MySqlCommand cmd = new MySqlCommand(cmdText, con);
4 cmd.ExecuteNonQuery(); //Execute the command

UPDATE:

1 //example on how to use UPDATE
2 cmd = new MySqlCommand("UPDATE ab_data SET banned_from='" + from + "' , banned_until='" + until + "' WHERE name='" + name + "'", con);
3 cmd.ExecuteNonQuery();

DELETE:

1 //example on how to use DELETE
2 cmd = new MySqlCommand("DELETE FROM tbName WHERE colName = someValue",con);
3 cmd.ExecuteNonQuery();

注：在使用数据库前，必须要确保MySQL数据库服务已经打开（通过con.Open()），并一定要安全地关闭（con.Close()）。

Adjusted from : http://forum.codecall.net/topic/71422-connecting-to-a-mysql-database-in-c/