[ MySQL ] DAAB MySql支持测试(Proc+DataSet更新) 5.0.27+.Net Connector 5.0.2beta

    以前装的MySQL 5.0.24a版本，因为有些问题，所以卸载掉重新装了5.0.27，感觉比5.0.24a那个版本要稳定，测试一下，在5.0.24a上存在的问题已经没有了。另外，以前用的ByteFX.Data，这次看到MySQL .Net Connector有了5.0.2版本，改用这个，毕竟官方正式的驱动要更可靠。
    参考：MySQL的使用 MySQL 5.0.24a + ByteFX.Data 0.76，里面提到的两个问题，在5.0.27下面不需要再处理。
    另外，使用MySQL 5.0.27 + MySQL .Net Connector 5.0.2 beta需要注意的地方，就是参数标志符号为?，而不是@。例如：select * from TblUser where UserID=?UID。在MySQL的使用 MySQL 5.0.24a + ByteFX.Data 0.76中参数标志符号使用的就是@。
    因为想用MySQL写点东西，要在Enterprise Library的Data Access Application Block基础上加上MySQL支持，所以看了下MySQL以及MySql .Net Connector一些主要方面的用法。

    存储过程
    在test库里面建立测试表：

创建测试表TblUser
CREATE TABLE `test`.`TblUser` (
  `UserID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `UserCode` VARCHAR(12) NOT NULL,
  `UserName` VARCHAR(45),
  PRIMARY KEY(`UserID`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

    在MySQL Administrator的GUI工具里面，在User Administration里为当前操作的用户添加对test和系统库mysql的权限，让用户能创建存储过程。

    如果是在命令行或者是Query Browser创建存储过程，执行下面的语句：

创建存储过程SP_QueryUser
DELIMITER //
CREATE PROCEDURE test.SP_QueryUser(
  in UC varchar(12),
  in UN varchar(45))
BEGIN
     select * from TblUser
     where UserCode like UC and UserName like UN;
END//

    如果是用MySQL Administrator创建存储过程，用下面的语句：

创建存储过程SP_QueryUser
CREATE PROCEDURE test.SP_QueryUser(
  in UC varchar(12),
  in UN varchar(45))
BEGIN
     select * from TblUser
     where UserCode like UC and UserName like UN;
END

    注意：
    1. DELIMITER //语句。MySQL的多个SQL语句/命令之间，默认使用;隔开，存储过程的body里面也要使用;将多个语句分隔开，这样，如果MySQL将创建存储过程的SQL语句使用;分隔，然后一个一个执行，就会有语法错误，无法创建存储过程。因此我们先使用DELIMITER关键字，将默认的分隔符修改为//（也可以是其它你认为合适的字符，例如?等），这样MySQL才会将存储过程的创建语句当作一个完整的SQL语句执行。
    2. 参数。参数名是不需要使用参数标志符号的，例如上面的例子，UC、UN两个参数名前面并没有加上参数标志符号?，如果加上参数标志符号会报语法错误。因为参数名不需要使用参数标志符号，因此注意参数名字别跟表的字段名字重名。
    3. 详细的语法，参考MySQL官方文档。一些摘选的SQL语法如下：

MySQL语法摘选
#general statement#
declare uid, id int;
declare ucode varchar(12);
declare uname varchar(45);
set id=10;
select UserID into uid from TblUser where UserCode='admin';

#if statement#
if  then
  
elseif  then
  
end if;

#case statement#
case 
  when  then
    
  when  then
    
  else
    
end case;

#while statement#
while  do
  
end while;

#cursor#
declare rowCount int;
declare my_cur cursor for select UserID, UserCode, UserName from TblUser order by UserCode;
open my_cur;
  #you need control the loop count by yourself#
  select count(*) into rowCount from TblUser;
  while rowCount>0 do
    fetch my_cur into uid, ucode, uname;
    #finish your tasks#
    
    set rowCount = rowCount - 1
  end while;
close my_cur;

    MySQL里面执行存储过程的语法为call StoredProcedureName ('参数1','参数2')，例如调用上面的存储过程为：

Call SP_QueryUser('%','%')

    用MySql.Data.MySqlClient调用存储过程，方式跟SQL Server一样：

MySql.Data.MySqlClient调用存储过程
MySqlConnection connection = new MySqlConnection("Data source=localhost;User Id=root;Password=123;Database=test;");
connection.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_QueryUser";
command.Parameters.Add(new MySqlParameter("?UC", this.TextBox1.Text.Trim() + "%"));
command.Parameters.Add(new MySqlParameter("?UN", this.TextBox2.Text.Trim() + "%"));
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
connection.Close();

this.GridView1.DataSource = ds;
this.GridView1.DataBind();

    在Enterprise Library Data Access Application Block里面，提供这样的方式调用存储过程：

public virtual DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)

    你不必提供存储过程参数名列表，只需要提供object[]的参数值，在第一次调用存储过程时，DAAB会自动从数据库读取存储过程参数列表，并生成DbParameter对象缓存起来，后续调用这个存储过程就从缓存中读取参数列表，设置参数值，然后向数据库发送执行。象SQL Server的SqlDatabase类，通过调用SQL Server系统存储过程sp_procedure_params_rowset（SQL 2005中通过调用sp_procedure_params_managed）实现。其实SQL Server的存储过程完全可以使用StoredProcedureName '参数1','参数2'的方式执行，但这种方式下需要将CommandType设成CommandType.Text，估计这样可能会导致数据库驱动并不是采用存储过程的方式执行命令，可能会造成存储过程的一些优化方面失效（直接把参数值拼到SQL里面，估计会以ad hoc方式处理；使用存储过程名带参数的方式，估计以RPC方式执行，但数据库驱动以及数据库服务器是以普通的RPC还是存储过程的方式对待不得而知）。当然，这个结论只是个人猜测，并没有去验证。
    不管怎样，按照MS的推荐方式使用是一种好的选择，因此在DAAB实现MySqlDatabase时也保持这样一种机制，这样就需要在MySQL中获取存储过程的参数列表。这点跟Oracle、Sql Server的ADO.Net驱动完全一样，使用MySqlCommandBuilder.DeriveParameters(MySqlCommand command)方法实现。
    下面的方法是我自己写的一段取存储过程参数名称列表的实现方法，只是一种尝试。MySqlCommandBuilder的DeriveParameters方法是通过调用MySQL接口完成的，并且会填充参数的Direction、Size、Scale等属性。
    MySQL里面存储过程信息保存在mysql数据库的proc表里面，param_list字段为参数列表：

SELECT param_list FROM mysql.proc where proc.name='StoredProcedureName' and proc.db='DBName'

    param_list是一个BLOB类型，并且是存储过程创建语句的参数括号里面的原字符串，因此需要从这个字符串里面解析出参数名称。下面简单的用.Net代码示例解析参数名（不解析参数类型）。

取MySQL存储过程参数列表C#代码
using System;
using MySql.Data.MySqlClient;

public class MySqlUtil
{
    public MySqlUtil()
    {
    }

    /**//**//**//// <summary>
    /// 解析MySQL存储过程参数名列表
    /// </summary>
    /// <param name="procParam">mysql数据库proc表字段param_list的值</param>
    /// <param name="paramList">返回参数列表</param>
    /// <returns>如果有参数，返回true，否则返回false</returns>
    public static bool ProcParamList(object procParam, ref MySqlParameter[] paramList)
    {
        bool result = false;

        if (!object.ReferenceEquals(procParam, null) && (procParam is byte[]))
        {
            //将param_list值转换成字符串
            byte[] byteParam = (byte[])procParam;
            global::System.Text.ASCIIEncoding encoding = new global::System.Text.ASCIIEncoding();
            string strParam = encoding.GetString(byteParam);
            //移除回车换行TAB等特殊控制字符
            strParam = strParam.Replace("\r", "").Replace("\n", "").Replace("\t", "").Trim();
            //参数列表
            string[] arrayParams = strParam.Split(',');
            if (!object.ReferenceEquals(arrayParams, null) && arrayParams.Length > 0)
            {
                //用于过滤掉参数的输入、输出类型
                string tokens = "[IN][OUT][INOUT]";
                global::System.Collections.ArrayList arrayList = new global::System.Collections.ArrayList(arrayParams.Length);
                MySqlParameter mysqlParam;
                string[] oneParam;
                string param = "";
                for (int paramCount = 0; paramCount < arrayParams.Length; paramCount++)
                {
                    //param的格式类似于：IN/OUT/INOUT paramName Varchar(30)/INT等
                    param = arrayParams[paramCount];
                    if (!string.IsNullOrEmpty(param) && param.Trim().Length > 0)
                    {
                        oneParam = param.Split(' ');
                        for (int i = 0; i < param.Length; i++)
                        {
                            //是否是空，或者是参数的Direction关键字（IN/OUT/INOUT）?
                            if (string.IsNullOrEmpty(oneParam[i]) || oneParam[i].Trim() == "" || tokens.IndexOf("[" + oneParam[i].Trim().ToUpper() + "]") > 0)
                                continue;
                            //得到参数名
                            mysqlParam = new MySqlParameter();
                            mysqlParam.ParameterName = "?" + oneParam[i].Trim();
                            arrayList.Add(mysqlParam);
                            result = true;
                            break;
                        }
                    }
                }
                paramList=(MySqlParameter[])arrayList.ToArray(typeof(MySqlParameter));
            }
        }

        return result;
    }
}

//调用上面方法的示例代码
//取存储过程SP_QueryUser的参数
MySqlConnection connection = new MySqlConnection("Data source=localhost;User Id=root;Password=123;Database=mysql;");
connection.Open();
MySqlCommand command = new MySqlCommand("select param_list from mysql.proc where proc.name=?SPName and proc.DB=?DBName", connection);
command.Parameters.Add(new MySqlParameter("?SPName", "SP_QueryUser"));
command.Parameters.Add(new MySqlParameter("?DBName", "test"));
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
connection.Close();

//解析参数列表
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
    MySqlParameter[] paramList = null;
    if (MySqlUtil.ProcParamList(ds.Tables[0].Rows[0]["param_list"], ref paramList))
    {
        string s = "";
        foreach (MySqlParameter param in paramList)
        {
            s = s + param.ParameterName + " ";
        }
        this.TextBox1.Text = s;
    }
}

    DataSet Update
    上面建的表TblUser中有一个自增类型的字段，为了简化DataSet Update的测试，建立了另外一个测试表TblItem：

创建测试表TblItem
CREATE TABLE `test`.`TblItem` (
  `ItemID` VARCHAR(36) NOT NULL,
  `ItemNo` VARCHAR(20) NOT NULL,
  `ItemName` VARCHAR(60),
  PRIMARY KEY(`ItemID`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

    DataSet Update的测试代码如下，整个代码跟Sql Server数据库写法上没有差别。测试过程中一开始在调用adapter.Update()方法后稍微停滞一段时间，然后出现一个"Server shutdown in progress"异常，把MySql的一些内存Buffer适当调大一些，重起MySql服务再进行测试就OK了。

DataSet Update简单测试
//取DataSet
MySqlConnection connection = new MySqlConnection("Data source=localhost;User Id=root;Password=123;Database=test;");
connection.Open();
string sql = "select * from tblitem";
MySqlCommand command = new MySqlCommand(sql, connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);

//修改数据
DataTable table = ds.Tables[0];
//修改一行
if (table.Rows.Count >= 1)
    table.Rows[0]["ItemName"] = table.Rows[0]["ItemName"].ToString() + "_o";
//删除一行
if (table.Rows.Count >= 2)
    table.Rows[1].Delete();
//插入两行
DataRow row;
row = table.NewRow();
row["ItemID"] = Guid.NewGuid().ToString().ToUpper();
row["ItemNo"] = "Item-" + DateTime.Now.AddDays(-2).ToString("yyMMddHHmmss");
row["ItemName"] = "Item-" + DateTime.Now.AddDays(-2).ToString("yyMMddHHmmss");
table.Rows.Add(row);
row = table.NewRow();
row["ItemID"] = Guid.NewGuid().ToString().ToUpper();
row["ItemNo"] = "Item-" + DateTime.Now.AddDays(-1).ToString("yyMMddHHmmss");
row["ItemName"] = "Item-" + DateTime.Now.AddDays(-1).ToString("yyMMddHHmmss");
table.Rows.Add(row);

//更新数据
//IsolationLevel.ReadUncommitted
MySqlTransaction tran = connection.BeginTransaction();
try
{
    MySqlParameter param;
    MySqlCommand update = new MySqlCommand("update test.tblitem set tblitem.ItemNo=?ItemNo, tblitem.ItemName=?ItemName where tblitem.ItemID=?ItemID", connection);
    param = new MySqlParameter("?ItemNo", MySqlDbType.VarChar, 18, ParameterDirection.Input, false, 0, 0, "ItemNo", DataRowVersion.Current, null);
    update.Parameters.Add(param);
    param = new MySqlParameter("?ItemName", MySqlDbType.VarChar, 60, ParameterDirection.Input, true, 0, 0, "ItemName", DataRowVersion.Current, null);
    update.Parameters.Add(param);
    param = new MySqlParameter("?ItemID", MySqlDbType.VarChar, 36, ParameterDirection.Input, false, 0, 0, "ItemID", DataRowVersion.Original, null);
    update.Parameters.Add(param);
    update.Transaction = tran;

    MySqlCommand insert = new MySqlCommand("insert into test.tblitem(ItemID, ItemNo, ItemName) values(?ItemID, ?ItemNo, ?ItemName)", connection);
    param = new MySqlParameter("?ItemID", MySqlDbType.VarChar, 36, ParameterDirection.Input, false, 0, 0, "ItemID", DataRowVersion.Current, null);
    insert.Parameters.Add(param);
    param = new MySqlParameter("?ItemNo", MySqlDbType.VarChar, 18, ParameterDirection.Input, false, 0, 0, "ItemNo", DataRowVersion.Current, null);
    insert.Parameters.Add(param);
    param = new MySqlParameter("?ItemName", MySqlDbType.VarChar, 60, ParameterDirection.Input, true, 0, 0, "ItemName", DataRowVersion.Current, null);
    insert.Parameters.Add(param);
    insert.Transaction = tran;

    MySqlCommand delete = new MySqlCommand("delete from test.tblitem where tblitem.ItemID=?ItemID", connection);
    param = new MySqlParameter("?ItemID", MySqlDbType.VarChar, 36, ParameterDirection.Input, false, 0, 0, "ItemID", DataRowVersion.Original, null);
    delete.Parameters.Add(param);
    delete.Transaction = tran;

    adapter.UpdateCommand = update;
    adapter.InsertCommand = insert;
    adapter.DeleteCommand = delete;

    adapter.Update(table);
    tran.Commit();
}
catch
{
    tran.Rollback();
}

//显示更新后的数据
ds.Tables.Clear();
adapter.Fill(ds);
connection.Close();

this.GridView1.DataSource = ds;
this.GridView1.DataBind();

    有了上面这些了解，就可以从Enterprise Library Data Access Application Block的Database类继承，实现一个MySqlDatabase类了，并且非常简单。