MySql工具类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using MySql.Data.MySqlClient;
namespace skylark.SqlTool
{
    public class MySqlDBHelper 
    {
        public static string connectionString = ConnectConfig.DBConnectionString;
        public MySqlDBHelper()
        {            
        }

        private static void init(DataTable dtStru, MySql.Data.MySqlClient.MySqlDataAdapter adapter, MySqlConnection connection)
        {
            string strTableName = dtStru.TableName;
            DataColumn[] colPkName = dtStru.PrimaryKey;
            string strInsertField = "";
            string strInsertValue = "";
            string strUpdate = "";
            //插入语句
            string strInsertSql = "";
            //修改语句
            string strUpdateSql = "";
            //删除语句
            string strDeleteSql = "";
            string strWhereSql = "";
            foreach (DataColumn dc in colPkName)
            {
                if (strWhereSql == "")
                    strWhereSql = " where " + dc.ColumnName + @" = ?Original_" + dc.ColumnName;
                else
                    strWhereSql += " and " + dc.ColumnName + @" = ?Original_" + dc.ColumnName;
            }
            MySql.Data.MySqlClient.MySqlCommand sqlInsertComm = new MySql.Data.MySqlClient.MySqlCommand();
            MySql.Data.MySqlClient.MySqlCommand sqlUpdateComm = new MySql.Data.MySqlClient.MySqlCommand();
            MySql.Data.MySqlClient.MySqlCommand sqlDeleteComm = new MySql.Data.MySqlClient.MySqlCommand();

            //动态生成映射列
            System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName);

            foreach (DataColumn dcField in dtStru.Columns)
            {
                dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName);
                //如果不是自动增长的字段
                if (!dcField.AutoIncrement)
                {
                    if (strInsertField == "")
                    {
                        strInsertField = dcField.ColumnName;
                        strInsertValue = "?" + dcField.ColumnName;
                    }
                    else
                    {
                        strInsertField += "," + dcField.ColumnName;
                        strInsertValue += ",?" + dcField.ColumnName;
                    }
                    if (!dcField.Unique)
                    {
                        if (strUpdate == "")
                        {
                            strUpdate = dcField.ColumnName + "=?" + dcField.ColumnName;
                        }
                        else
                        {
                            strUpdate += "," + dcField.ColumnName + "=?" + dcField.ColumnName;
                        }
                    }
                }
            }
            strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")";
            strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql;
            strDeleteSql = @"delete from " + strTableName + strWhereSql;
            adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap });
            // 
            // Adapter
            //
            adapter.DeleteCommand = sqlDeleteComm;
            adapter.InsertCommand = sqlInsertComm;
            adapter.UpdateCommand = sqlUpdateComm;
            // 
            // sqlInsertComm
            // 
            //动态生成插入语句
            sqlInsertComm.CommandText = strInsertSql;
            sqlInsertComm.Connection = connection;
            BuildPara(sqlInsertComm, dtStru);
            // 
            // sqlUpdateComm
            // 
            sqlUpdateComm.CommandText = strUpdateSql;
            sqlUpdateComm.Connection = connection;
            BuildPara(sqlUpdateComm, dtStru);
            BuildWherePara(sqlUpdateComm, dtStru);
            // 
            // sqlDeleteComm
            // 
            sqlDeleteComm.CommandText = strDeleteSql;
            sqlDeleteComm.Connection = connection;
            BuildWherePara(sqlDeleteComm, dtStru);

        }
        /// <summary>
        /// 验证表结构
        /// </summary>
        /// <param name="dtStru">DataTable</param>
        /// <param name="adapter"></param>
        /// <param name="arlCol">在SQlServer表中的列名称</param>
        private static void init(DataTable dtStru, MySqlDataAdapter adapter, MySqlConnection connection, ArrayList arlCol)
        {
            string strTableName = dtStru.TableName;
            DataColumn[] colPkName = dtStru.PrimaryKey;
            string strInsertField = "";
            string strInsertValue = "";
            string strUpdate = "";
            //插入语句
            string strInsertSql = "";
            //修改语句
            string strUpdateSql = "";
            //删除语句
            string strDeleteSql = "";
            string strWhereSql = "";
            foreach (DataColumn dc in colPkName)
            {
                if (strWhereSql == "")
                    strWhereSql = " where " + dc.ColumnName + @" = ?Original_" + dc.ColumnName;
                else
                    strWhereSql += " and " + dc.ColumnName + @" = ?Original_" + dc.ColumnName;
            }
            MySqlCommand sqlInsertComm = new MySqlCommand();
            MySqlCommand sqlUpdateComm = new MySqlCommand();
            MySqlCommand sqlDeleteComm = new MySqlCommand();
            //动态生成映射列
            System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName);
            foreach (DataColumn dcField in dtStru.Columns)
            {
                bool bnlIsExistCol = false;
                dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName);
                //检查在表结构中是否存在
                foreach (string aStr in arlCol)
                {
                    if (aStr.Equals(dcField.ColumnName))
                    {
                        bnlIsExistCol = true;
                    }
                }
                //如果不是自动增长的字段
                if (!dcField.AutoIncrement && bnlIsExistCol)
                {

                    if (strInsertField == "")
                    {
                        strInsertField = dcField.ColumnName;
                        strInsertValue = "?" + dcField.ColumnName;
                        strUpdate = dcField.ColumnName + "=?" + dcField.ColumnName;
                    }
                    else
                    {
                        strInsertField += "," + dcField.ColumnName;
                        strInsertValue += ",?" + dcField.ColumnName;
                        strUpdate += "," + dcField.ColumnName + "=?" + dcField.ColumnName;
                    }
                }
            }
            strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")";
            strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql;
            strDeleteSql = @"delete from " + strTableName + strWhereSql;
            adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap });
            // 
            // Adapter
            //
            adapter.DeleteCommand = sqlDeleteComm;
            adapter.InsertCommand = sqlInsertComm;
            adapter.UpdateCommand = sqlUpdateComm;
            // 
            // sqlInsertComm
            // 
            //动态生成插入语句
            sqlInsertComm.CommandText = strInsertSql;
            sqlInsertComm.Connection = connection;
            BuildPara(sqlInsertComm, dtStru);
            // 
            // sqlUpdateComm
            // 
            sqlUpdateComm.CommandText = strUpdateSql;
            sqlUpdateComm.Connection = connection;
            BuildPara(sqlUpdateComm, dtStru);
            BuildWherePara(sqlUpdateComm, dtStru);
            // 
            // sqlDeleteComm
            // 
            sqlDeleteComm.CommandText = strDeleteSql;
            sqlDeleteComm.Connection = connection;
            BuildWherePara(sqlDeleteComm, dtStru);
        }

        private static void BuildPara(MySql.Data.MySqlClient.MySqlCommand mSqlommand, DataTable dt)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                //如果不是自动增长的字段， 创建参数
                if (!dc.AutoIncrement)
                {
                    switch (dc.DataType.Name.ToString())
                    {
                        case "String":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarChar, 0, dc.ColumnName));
                            break;
                        case "Int16":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int16, 0, dc.ColumnName));
                            break;
                        case "Int32":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int32, 0, dc.ColumnName));
                            break;
                        case "Int64":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int64, 0, dc.ColumnName));
                            break;
                        case "Decimal":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Decimal, 0, dc.ColumnName));
                            break;
                        case "Boolean":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Bit, 0, dc.ColumnName));
                            break;
                        case "DateTime":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.DateTime, 0, dc.ColumnName));
                            break;
                        case "VarBinary":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarBinary, 2147483647, dc.ColumnName));
                            break;
                        case "Byte[]":
                            mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarBinary, 2147483647, dc.ColumnName));
                            break;

                    }
                }
            }

        }

        private static void BuildWherePara(MySql.Data.MySqlClient.MySqlCommand mSqlommand, DataTable dt)
        {
            foreach (DataColumn dc in dt.PrimaryKey)
            {
                switch (dc.DataType.Name.ToString())
                {
                    case "String":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.VarChar, 0, dc.ColumnName));
                        break;
                    case "Int16":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int16, 0, dc.ColumnName));
                        break;
                    case "Int32":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int32, 0, dc.ColumnName));
                        break;
                    case "Int64":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int64, 0, dc.ColumnName));
                        break;
                    case "Decimal":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Decimal, 0, dc.ColumnName));
                        break;
                    case "Boolean":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Bit, 0, dc.ColumnName));
                        break;
                    case "DateTime":
                        mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.DateTime, 0, dc.ColumnName));
                        break;
                }
            }

        }

        /// <summary>
        /// 根据表名返回所有列名称
        /// </summary>
        /// <param name="strTablename">表名称</param>
        /// <returns></returns>
        

        private static ArrayList GetColumn(string strTablename, MySqlTransaction myTrans)
        {
            try
            {
                MySqlParameter[] paramss = 
                {
                    new MySqlParameter{ParameterName="?table_name",Direction = ParameterDirection.Input,  Value = strTablename}
                };
                // 执行存储过程，返回指定的结果集行数，同时返回总行数
                DataSet ds = Query("select column_name from information_schema.COLUMNS where table_name= ?table_name",paramss);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ArrayList arlCol = new ArrayList();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        arlCol.Add(dr["column_name"].ToString());
                    }
                    return arlCol;
                };
                return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }
        
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        #region 查询
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        public static DataSet Query(string SQLString,string strTablename, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;  
                        if (strTablename != "")
                            da.Fill(ds, strTablename);
                        else
                            da.Fill(ds);
                        cmd.Parameters.Clear();
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// 根据SQLString 获取记录集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="strTabname"></param>
        /// <returns></returns>
        public static DataSet Query(string commandText, string strTabname)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, connection, null, commandText, null);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {                        
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;                        
                        if (strTabname != "")
                            da.Fill(ds, strTabname);
                        else
                            da.Fill(ds);
                    }
                    catch (System.Exception e)
                    {
                        throw e;
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决排序问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public static DataSet Query(ArrayList hstSQlTabel)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                using (MySqlDataAdapter da = new MySqlDataAdapter())
                {
                    DataSet data = new DataSet();
                    MySqlTransaction myTrans = connection.BeginTransaction();
                    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    try
                    {
                        for (int i = 0; i < hstSQlTabel.Count; i++)
                        {
                            Hashtable hst = (Hashtable)hstSQlTabel[i];
                            foreach (DictionaryEntry hstSql in hst)
                            {
                                DataSet ds = new DataSet();
                                MySqlCommand cmd = new MySqlCommand();
                                PrepareCommand(cmd, connection,myTrans, hstSql.Value.ToString(), null);
                                da.SelectCommand = cmd;
                                if (hstSql.Key.ToString() != "")
                                    da.Fill(ds, hstSql.Key.ToString());
                                else
                                    da.Fill(ds);
                                data.Merge(ds);
                            }
                        }
                        myTrans.Commit();
                    }
                    catch (System.Exception e)
                    {
                        myTrans.Rollback();
                        throw e;
                    }
                    return data;
                }
            }
        }
        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决多表问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public static DataSet Query(Hashtable hstSqlTable)
        {
            ArrayList arList = new ArrayList();
            arList.Add(hstSqlTable);
            return Query(arList);
        }
        #endregion

        #region 更新数据

        /// <summary>
        /// 根据记录集更新数据 
        /// </summary>
        /// <param name="data">用户进行增加删除修改操作的记录集</param>
        /// <returns></returns>
        public static string UpdateData(DataSet data)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string blnResult = "";
                int intTableNum = data.Tables.Count;
                MySqlDataAdapter[] adapter = new MySqlDataAdapter[intTableNum];
                MySqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        MySqlDataAdapter adapterTmp = new MySqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i], adapter[i],connection);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {
                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added));
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));
                    }
                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult = e.Message;
                    throw e;
                }                
                return blnResult;
            }
        }

        /// <summary>
        /// 根据记录集更新数据
        /// 参数记录集data， 与SQlServer表结构不一致时使用（--待更新表的字段关联了一些其他表的字段）
        /// ***
        /// </summary>
        /// <param name="data">用户进行增加删除修改操作的记录集</param>
        /// <returns></returns>
        public static string UpdateDataCheckField(DataSet data)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string blnResult = "";
                int intTableNum = data.Tables.Count;
                connection.Open();
                MySqlDataAdapter[] adapter = new MySqlDataAdapter[intTableNum];
                MySqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        ArrayList arlCol = GetColumn(data.Tables[i].TableName, myTrans);
                        MySqlDataAdapter adapterTmp = new MySqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i], adapter[i],connection, arlCol);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {
                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added));
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));
                    }
                    myTrans.Commit();

                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult = e.Message;
                    throw e;
                }                
                return blnResult;
            }
        }
        #endregion

        #region 获取主键，更新主键表
        /// <summary>
        /// 更新当前主键编号，获取主键编号
        /// </summary>
        /// <param name="strFieldKey">主键标识--两位</param>
        /// <returns>Return[0]：成功与否；Return[1]：返回主键</returns>
        public static string[] getOrderNO(string strFieldKey)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string[] blnResult = new string[2];
                blnResult[0] = "";
                blnResult[1] = "";
                connection.Open();
                string scommandtext = "SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'";
                 MySqlCommand cmd = new MySqlCommand();
                 MySqlTransaction myTrans = connection.BeginTransaction();
                 PrepareCommand(cmd, connection,myTrans, scommandtext, null);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    try
                    {
                        DataSet dsOrder = new DataSet();
                        da.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                        string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                        if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                        {
                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                        }
                        else
                        {
                            if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                            {
                                int intNum = int.Parse(strKeyNum.Substring(10, 6));
                                intNum = 1000001 + intNum;
                                strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);
                            }
                            else
                            {
                                strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                            }
                        }
                        //更新主键值
                        MySqlCommand updatecommand = new MySqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                        updatecommand.Transaction = myTrans;
                        updatecommand.ExecuteNonQuery();
                        blnResult[1] = strKeyNum;
                        myTrans.Commit();
                    }
                    catch (Exception e)
                    {
                        myTrans.Rollback();
                        blnResult[0] = e.Message;
                        throw e;
                    }
                    return blnResult;
                }
            }
        }

        public static string[] getOrderNO(string strFieldKey, int OrderNum)
        {
            string[] blnResult = new string[2];
            blnResult[0] = "";
            blnResult[1] = "";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                MySqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    string scommandtext = "SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'";
                    MySqlCommand cmd = new MySqlCommand();
                    PrepareCommand(cmd, connection, myTrans, scommandtext, null);
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        DataSet dsOrder = new DataSet();
                        da.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                        string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                        string strKeyNumB = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                        if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                        {
                            int intNum = 1000001 + OrderNum;
                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6);
                            strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                        }
                        else
                        {
                            if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                            {
                                int intNum = int.Parse(strKeyNum.Substring(10, 6));
                                intNum = 1000001 + intNum + OrderNum;
                                int intNumB = 1000001 + intNum;
                                strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);
                                strKeyNumB = strKeyNum.Substring(0, 10) + intNumB.ToString().Substring(1, 6);
                            }
                            else
                            {
                                int intNum = 1000001 + OrderNum;
                                strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6);
                                strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                            }
                        }
                        //更新主键值
                        MySqlCommand updatecommand = new MySqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                        updatecommand.Transaction = myTrans;
                        updatecommand.ExecuteNonQuery();
                        blnResult[1] = strKeyNumB;
                        myTrans.Commit();
                    }
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult[0] = e.Message;
                    throw e;
                }            
                return blnResult;
            }
        }
        #endregion

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 是否存在（基于MySqlParameter）
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                int result;
                connection.Open();
                MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static MySqlCommand BuildIntCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new MySqlParameter("ReturnValue",
                MySqlDbType.Int16, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            MySqlCommand command = new MySqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (MySqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

        private static DataSet RunProcedureNoConn(string storedProcName, IDataParameter[] parameters, MySqlTransaction myTrans)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                DataSet data = new DataSet();
                try
                {
                    adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    adapter.SelectCommand.Transaction = myTrans;
                    adapter.Fill(data, storedProcName);
                }
                catch (System.Exception e)
                {
                    throw e;
                }
                return data;
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                MySqlDataAdapter sqlDA = new MySqlDataAdapter();
                try
                {
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet, tableName);
                }
                catch (Exception)
                {
                    throw;
                }
                return dataSet;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }        
        
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                            foreach (MySqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (MySqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}