DataBase: MySQL在.NET中的应用

首先需要下载MySQL:

1. 官方下载

dev.mysql.com/downloads/mysql/

2. 解压到你所想要安装的位置，在文件夹里创建my.ini文件

 1 [mysql]
 2 # 设置mysql客户端默认字符集
 3 default-character-set=gbk
 4 [mysqld]
 5 #设置3306端口
 6 port = 3306 
 7 # 设置mysql的安装目录
 8 basedir=D:mysqlmysql-5.6.17-winx64
 9 # 设置mysql数据库的数据的存放目录
10 datadir=D:mysqlmysql-5.6.17-winx64data
11 # 允许最大连接数
12 max_connections=200
13 # 服务端使用的字符集默认为8比特编码的latin1字符集
14 character-set-server=gbk
15 # 创建新表时将使用的默认存储引擎
16 default-storage-engine=INNODB

View Code

这里要把路径改掉

3. 用管理员身份运行cmd.exe，到bin文件里运行：mysqld install，在任务管理器的服务中开启mysql服务

4. 在cmd.exe中设置root密码：mysqladmin -u root -p password

5. 登录mysql：mysql -u root -p

6. 设置路径：将mysql所安装文件夹的bin路径加入

7. 可以用到的一些指令：show databases; show tables; describe [table]; source

8. 建议用下Navicat for mysql这个软件

在使用C#连mysql前需要下载.NET与mysql的连接器

http://dev.mysql.com/downloads/connector/net

一。数据读取

与MySQL进行数据读取的步骤是：

1. 连接数据源

2. 打开连接

3. 创建一个SQL查询命令

4. 用DataReader或者DataSet读取数据

5. 关闭连接

下面是以DataReader为例的数据读取

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 
10 namespace test4
11 {
12     class Program
13     {
14         static void Main(string[] args)
15         {
16             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
17             con.Open();
18             MySqlCommand cmd = new MySqlCommand("SELECT * FROM students", con);
19             MySqlDataReader reader = cmd.ExecuteReader();
20             while (reader.Read())
21             {
22                 Console.WriteLine("	{0}	{1}", reader["Name"], reader["Grade"]);
23             }
24             reader.Close();
25             con.Close();
26         }
27     }
28 }

View Code

下面是用DataSet来读取数据

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             DataSet ds = new DataSet();
21             adapter.Fill(ds, "students");
22             foreach (DataRow row in ds.Tables["students"].Rows)
23             {
24                 Console.WriteLine(row["Name"] + "	" + row["Grade"]);
25             }
26             con.Close();
27         }
28     }
29 }

View Code

一般我们偏向用DataSet来进行操作，因为数据更新用DataSet会更加方便

二。数据更新

不需要用SQL的update语句，直接用SqlCommandBuilder就可以更新数据库了

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
21             DataSet ds = new DataSet();
22             adapter.Fill(ds, "students");
23             Console.WriteLine("Grade before change: {0}", ds.Tables["students"].Rows[0]["Grade"]);
24             ds.Tables["students"].Rows[0]["Grade"] = "19";
25             adapter.Update(ds, "students");
26             Console.WriteLine("Grade after change: {0}", ds.Tables["students"].Rows[0]["Grade"]);
27             con.Close();
28         }
29     }
30 }

View Code

注意adapter.Update方法的datatable名字必须与前面的fill方法一致

增加一行，判断是不是已经存在了，if语句中为了保证Add()成功，必须在添加操作成功后马上调用Find()方法

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
21             DataSet ds = new DataSet();
22             adapter.Fill(ds, "students");
23             Console.WriteLine("# rows before change: {0}", ds.Tables["students"].Rows.Count);
24             DataColumn[] keys = new DataColumn[1];
25             keys[0] = ds.Tables["students"].Columns["Name"];
26             ds.Tables["students"].PrimaryKey = keys;
27             DataRow findRow = ds.Tables["students"].Rows.Find("wangnaiyu");
28             if (findRow == null)
29             {
30                 Console.WriteLine("wangnaiyu not found, will add to students table");
31                 DataRow newRow = ds.Tables["students"].NewRow();
32                 newRow["Name"] = "wangnaiyu";
33                 newRow["Age"] = "28";
34                 newRow["Grade"] = "70";
35                 ds.Tables["students"].Rows.Add(newRow);
36                 if ((findRow = ds.Tables["students"].Rows.Find("wangnaiyu")) != null)
37                 {
38                     Console.WriteLine("wangnaiyu successfully added to students table");
39                 }
40             }
41             else
42             {
43                 Console.WriteLine("wangnaiyu already present in database");
44             }
45             adapter.Update(ds, "students");
46             Console.WriteLine("# rows after change: {0}", ds.Tables["students"].Rows.Count);
47             con.Close();
48         }
49     }
50 }

View Code

删除行

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
21             DataSet ds = new DataSet();
22             adapter.Fill(ds, "students");
23             Console.WriteLine("# rows before change: {0}", ds.Tables["students"].Rows.Count);
24             DataColumn[] keys = new DataColumn[1];
25             keys[0] = ds.Tables["students"].Columns["Name"];
26             ds.Tables["students"].PrimaryKey = keys;
27             DataRow findRow = ds.Tables["students"].Rows.Find("wangnaiyu");
28             if (findRow != null)
29             {
30                 Console.WriteLine("wangnaiyu already in students table");
31                 Console.WriteLine("Removing wangnaiyu ...");
32                 findRow.Delete();
33                 adapter.Update(ds, "students");
34             }
35             Console.WriteLine("# rows after change: {0}", ds.Tables["students"].Rows.Count);
36             con.Close();
37         }
38     }
39 }

View Code

访问多个表

这里用DataRelations类，DataSet建立关系是用DataSet.Relations.Add(DataRelation);的。而DataRelation的构造函数为DataRelation(string relationName, DataColumn parentColumn, DataColumn, childColumn);注意这里的父子关系不要弄错，父表中的一行对应子表中的多行，用DataRow.GetChildRows(DataRelation)可以得到的子表中对应的行DataRow[]。具体看下面的代码

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter stuAdapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             MySqlCommandBuilder stuBuilder = new MySqlCommandBuilder(stuAdapter);
21             MySqlDataAdapter orderAdapter = new MySqlDataAdapter("SELECT * FROM Orders", con);
22             MySqlCommandBuilder orderBuilder = new MySqlCommandBuilder(orderAdapter);
23             DataSet ds = new DataSet();
24             stuAdapter.Fill(ds, "students");
25             orderAdapter.Fill(ds, "Orders");
26             DataRelation stuOrderRel = ds.Relations.Add("StuOrders", ds.Tables["students"].Columns["Name"], ds.Tables["Orders"].Columns["Name"]);
27             foreach (DataRow stuRow in ds.Tables["students"].Rows)
28             {
29                 Console.WriteLine("Student Name: " + stuRow["Name"] + " Age: " + stuRow["Age"] + " Grade: " + stuRow["Grade"]);
30                 foreach (DataRow orderRow in stuRow.GetChildRows(stuOrderRel))
31                 {
32                     Console.WriteLine(" Order: " + orderRow["Order"]);
33                 }
34             }
35             con.Close();
36         }
37     }
38 }

View Code

如果要从子表中取得父表中的数据，可以通过GetParentRow()。

三。直接执行SQL命令

一般DataSet中存储的数据很大，如果操作不是很多，则用SQL命令来操作效率会快很多

可以通过下面的程序看看SQL语句

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM students", con);
20             MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
21             Console.WriteLine("SQL SELECT Command is: {0}
", adapter.SelectCommand.CommandText);
22             Console.WriteLine("SQL UPDATE Command is: {0}
", builder.GetUpdateCommand().CommandText);
23             Console.WriteLine("SQL INSERT Command is: {0}
", builder.GetInsertCommand().CommandText);
24             Console.WriteLine("SQL DELETE Command is: {0}
", builder.GetDeleteCommand().CommandText);
25             con.Close();
26         }
27     }
28 }

View Code

ExecuteScalar返回的是结果

ExecuteNonQuery返回的是修改操作影响的行数

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 using System.Data;
10 
11 namespace test4
12 {
13     class Program
14     {
15         static void Main(string[] args)
16         {
17             MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true");
18             con.Open();
19             MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM students", con);
20             Object res = cmd.ExecuteScalar();
21             Console.WriteLine("Count of students = {0}", res);
22             
23             cmd.CommandText = "UPDATE students SET Age = 29 WHERE Grade = 88";
24             int rowsAffected = cmd.ExecuteNonQuery();
25             Console.WriteLine("Rows Update = {0}", rowsAffected);
26             con.Close();
27         }
28     }
29 }

View Code

数据库的操作一般都需要加上异常机制，另外MySqlConnection也是一个需要关闭的类，用using可能会来得更方便些，在MySqlCommand语句里可以用@variable的方式增加变量，在后面用Command.Parameters来选择特定值。

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using MySql.Data;
 6 using MySql.Data.Entity;
 7 using MySql.Data.MySqlClient;
 8 using System.IO;
 9 
10 namespace MysqlTest
11 {
12     class Program
13     {
14         static void Main(string[] args)
15         {
16             using (MySqlConnection con = new MySqlConnection("server=localhost; database=persons; uid=root; pwd=0000; connect timeout=30; pooling=true"))
17             {
18                 MySqlCommand cmd = new MySqlCommand("SELECT * FROM students where Name = @Name", con);
19                 try
20                 {
21                     con.Open();
22                     cmd.Parameters.Add("@Name", MySqlDbType.VarChar);
23                     cmd.Parameters["@Name"].Value = "yingzhongwen";
24                     MySqlDataReader reader = cmd.ExecuteReader();
25                     while (reader.Read())
26                     {
27                         Console.WriteLine("	{0}	{1}", reader["Name"], reader["Grade"]);
28                     }
29                     reader.Close();
30                 }
31                 catch (Exception ex)
32                 {
33                     Console.WriteLine(ex.Message);
34                 }
35             }
36 
37         }
38     }
39 }

View Code

Parameter的操作还可以这样：

MySqlParameter parameters = new MySqlParameter("@Name", MySqlDbType.VarChar);
parameters.Value = "yingzhongwen";
cmd.Parameters.Add(parameters);