c# 与 Mysql 的通讯方式总结

两种开发方式

1.使用 vs 自带的可视化工具，不推荐。

在 vs 的项目中添加 ‘数据集’，然后通过可视化的工具添加数据库为数据源，默认可添加 SQL Server 和 Oracle 等，添加 Mysql 前需要额外安装组件。

优点，自带 sql 语句中特殊字符的转义，不会出现 sql 注入的问题，配合数据绑定可以在项目前期快速推进项目进度。

缺点：可视化工具的使用并不流行，不利于开发团队的招募和项目后期的修改维护。许多常用的功能很难使用，如：存储过程，事务，连接池控制等。

2.引用 mysql.data.dll ，在此基础上进行开发，推荐。

完全由代码完成通讯过程。

优点：可以方便的使用各种功能，

缺点：在使用的过程中要注意许多坑，如：异常的捕获，sql 注入 ， 非托管资源的释放等。

---

 

---

 

完整的通讯过程

1.建立连接，连接分 “长连接” 和 “短连接” 

长连接在高频次的通讯时快速高效，但是占用资源，在并发访问下容易耗尽网络资源，对于带宽较低的局域网来说，如果大量使用长连接，会占用网速，影响使用体验。

短连接在使用时 open， 使用完成后 close，此时连接资源会进入连接池，等待下次连接时使用。虽然资源未被释放掉，但连接池的开销不大，是完全可以接受的。短连接一样需要考虑并发问题。

短连接在使用完成后可以直接 dispose，或者使用 using(){} 来限定连接的作用域，使用完成后自动释放掉，不进入连接池，这种方式资源占用最少，但在需要反复建立连接的情况下连接效率较低。

在实际开发的时候最好使用短连接，并在使用结束后关闭并放入连接池。

string connStr = "server=127.0.0.1; port=3306; user id=user1; Password=pass1; database=data1; pooling = true";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open(); 
conn.Close();

2.使用连接创建 sql 命令并执行

1.纯 sql 语句，执行后有三种返回方式：

cmd.ExecuteScalar();　　// 查询结果仅一行一列，直接接收

string connStr = "server=127.0.0.1; port=3306; user id=user1; Password=pass1; database=data1; pooling = true";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string name = "Tom";
cmd.CommandText = string.Format("select  count(*) from student where Name = '{0}';", name);
object obj = cmd.ExecuteScalar(); //可能未null
int count = 0;
if (!obj.Equals(DBNull.Value))
{
　　count = Convert.ToInt32(obj);
}  
conn.Close();  

 

 

MySqlDataReader reader = cmd.ExecuteReader();  // 通过 reader 获得大量数据

string connStr = "server=127.0.0.1; port=3306; user id=user1; Password=pass1; database=data1; pooling = true";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select  Name from student;");
MySqlDataReader reader = cmd.ExecuteReader();
List<string> list = new List<string>();
while (reader.Read())
{
    list.Add(reader.GetString("Name"));
}
reader.Close();
conn.Close();

 

cmd.ExecuteNonQuery();　　// 获得增删改语句执行后影响的行数

string connStr = "server=127.0.0.1; port=3306; user id=user1; Password=pass1; database=data1; pooling = true";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string name = "Tom";
cmd.CommandText = string.Format("update student SET Name='Tommy' where Name = '{0}';", name);
int count = cmd.ExecuteNonQuery();
conn.Close();

 

普通 sql 语句的缺点：如果上面例子中的 name 参数中带有单引号，整个 sql 语句将执行错误，因为未对特殊字符进行转义。主要特殊字符有单引号，反斜杠，# 等，而这些特殊字符在不同的使用情况下有时需要转义，有时不需要转义。

这就是 sql 注入，sql 注入的概念网上资料较多，在此不再赘述，而常用的解决方案是采用参数化的 sql 语句。

2.sql 语句和 sql 参数

带有参数的 sql 语句和 sql 参数会分开传入数据库服务器中，服务器先将 sql 语句进行编译，然后将 sql 参数导入编译后的 sql 语句中（在此过程中自动对特殊字符进行转义），从而从根源上防止了 sql 注入的发生。

string connStr = "server=127.0.0.1; port=3306; user id=user1; Password=pass1; database=data1; pooling = true";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select  count(*) from student where Name = @Name;";
MySqlParameter[] sps = new MySqlParameter[1];
sps[0] = new MySqlParameter("@Name", "Tom");
cmd.Parameters.AddRange(sps);
object obj = cmd.ExecuteScalar(); //可能未null
int count = 0;
if (!obj.Equals(DBNull.Value))
{
    count = Convert.ToInt32(obj);
}   
conn.Close();  

3.事务

多条 sql 语句的组合会出现某一条 sql 语句执行出错，而其他 sql 语句顺利执行的情况，这可能与预期不符合。此时需要使用事务。

事务在执行出错时可以回滚。

事务往往带有多条 sql 语句，在使用参数化的 sql 语句时要注意参数名称不能相同。

conn.Open();
MySqlCommand cmd = conn.CreateCommand();
MySqlTransaction myTrans = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = myTrans;
cmd.CommandText = "";
cmd.ExecuteNonQuery();
myTrans.Commit();
conn.Close();

 

 

4.存储过程

不论是 sql 语句还是事务，在传输指令的时候都需要耗费大量的时间，数据库编译这些指令也需要耗费大量时间，这不利于高访问量的数据库的运行。

存储过程是将 sql 指令写在数据库中，此时数据库直接完成编译，与数据库通讯是只需要传递参数即可。节省了传输时间和编译时间。

conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SpFillStudent";

MySqlParameter paraMoney;
paraMoney = cmd.Parameters.Add("@inMoney", MySqlDbType.Decimal);
paraMoney.Direction = ParameterDirection.Input;
paraMoney.Value = 50;

MySqlParameter paraTradeType;
paraTradeType = cmd.Parameters.Add("@inTradeType", MySqlDbType.String);
paraTradeType.Direction = ParameterDirection.Input;
paraTradeType.Value = "会员卡充值";

MySqlParameter paraPayDetailStr;
paraPayDetailStr = cmd.Parameters.Add("@inPayDetailStr", MySqlDbType.String);
paraPayDetailStr.Direction = ParameterDirection.Input;
paraPayDetailStr.Value = "";

MySqlParameter paraOutResult;
paraOutResult = cmd.Parameters.Add("@outResult", MySqlDbType.String);
paraOutResult.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
string result = (string)paraOutResult.Value;
conn.Close();