MySqlHelper 帮助类

原创于 2021-07-02 09:45:46 发布 · 281 阅读

CC 4.0 BY-SA版权

文章标签：

这个代码示例展示了一个名为MySqlHelper的类，用于执行MySQL数据库的各种SQL操作，包括批量操作和数据读写。类中定义了执行非查询（ExecuteNonQuery）、获取单个值（ExecuteScalar）、数据读取（ExecuteReader）、获取数据行（ExecuteDataRow）和数据表（ExecuteDataTable）的方法。此外，还提供了批量更新（BatchUpdate）和大量数据插入（BulkInsert）的批量操作。示例中还包含了数据库连接字符串和配置信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

     /// <summary>
    /// MySqlHelper操作类
    /// </summary>
    public sealed partial class MySqlHelper
    {
        /// <summary>
        /// 批量操作每批次记录数
        /// </summary>
        public static int BatchSize = 2000;

    /// <summary>
    /// 超时时间
    /// </summary>
    public static int CommandTimeOut = 600;

    /// <summary>
    ///初始化MySqlHelper实例
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    public MySqlHelper()
    {
        this.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString(); ; 
    }

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    public string ConnectionString { get; set; }

    #region 实例方法

    #region ExecuteNonQuery

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public int ExecuteNonQuery(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteNonQuery(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteNonQuery(ConnectionString, commandType, commandText, parms);
    }

    #endregion ExecuteNonQuery

    #region ExecuteScalar

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <typeparam name="T">返回对象类型</typeparam>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public T ExecuteScalar<T>(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteScalar<T>(ConnectionString, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public object ExecuteScalar(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteScalar(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public object ExecuteScalar(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteScalar(ConnectionString, commandType, commandText, parms);
    }

    #endregion ExecuteScalar

    #region ExecuteDataReader

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private MySqlDataReader ExecuteDataReader(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataReader(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private MySqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataReader(ConnectionString, commandType, commandText, parms);
    }
    #endregion

    #region ExecuteDataRow

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行</returns>
    public DataRow ExecuteDataRow(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataRow(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行</returns>
    public DataRow ExecuteDataRow(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataRow(ConnectionString, commandType, commandText, parms);
    }

    #endregion ExecuteDataRow

    #region ExecuteDataTable

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataTable(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public DataTable ExecuteDataTable(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0];
    }

    #endregion ExecuteDataTable

    #region ExecuteDataSet

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(ConnectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public DataSet ExecuteDataSet(CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(ConnectionString, commandType, commandText, parms);
    }

    #endregion ExecuteDataSet

    #region 批量操作

    /// <summary>
    /// 使用MySqlDataAdapter批量更新数据
    /// </summary>
    /// <param name="table">数据表</param>
    public void BatchUpdate(DataTable table)
    {
        BatchUpdate(ConnectionString, table);
    }

    /// <summary>
    ///大批量数据插入,返回成功插入行数
    /// </summary>
    /// <param name="table">数据表</param>
    /// <returns>返回成功插入行数</returns>
    public int BulkInsert(DataTable table)
    {
        return BulkInsert(ConnectionString, table);
    }

    #endregion 批量操作

    #endregion 实例方法

    #region 静态方法

    private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] parms)
    {
        if (connection.State != ConnectionState.Open) connection.Open();

        command.Connection = connection;
        command.CommandTimeout = CommandTimeOut;
        // 设置命令文本(存储过程名或SQL语句)
        command.CommandText = commandText;
        // 分配事务
        if (transaction != null)
        {
            command.Transaction = transaction;
        }
        // 设置命令类型.
        command.CommandType = commandType;
        if (parms != null && parms.Length > 0)
        {
            //预处理MySqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
            foreach (MySqlParameter parameter in parms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
            }
            command.Parameters.AddRange(parms);
        }
    }

    #region ExecuteNonQuery

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public static int ExecuteNonQuery(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            return ExecuteNonQuery(connection, CommandType.Text, commandText, parms);
        }
    }

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            return ExecuteNonQuery(connection, commandType, commandText, parms);
        }
    }

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteNonQuery(connection, null, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteNonQuery(transaction.Connection, transaction, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回影响的行数
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回影响的行数</returns>
    private static int ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        MySqlCommand command = new MySqlCommand();
        PrepareCommand(command, connection, transaction, commandType, commandText, parms);
        int retval = command.ExecuteNonQuery();
        command.Parameters.Clear();
        return retval;
    }

    #endregion ExecuteNonQuery

    #region ExecuteScalar

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <typeparam name="T">返回对象类型</typeparam>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public static T ExecuteScalar<T>(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        object result = ExecuteScalar(connectionString, commandText, parms);
        if (result != null)
        {
            return (T)Convert.ChangeType(result, typeof(T)); ;
        }
        return default(T);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            return ExecuteScalar(connection, CommandType.Text, commandText, parms);
        }
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            return ExecuteScalar(connection, commandType, commandText, parms);
        }
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteScalar(connection, null, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteScalar(transaction.Connection, transaction, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行第一列
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一行第一列</returns>
    private static object ExecuteScalar(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        MySqlCommand command = new MySqlCommand();
        PrepareCommand(command, connection, transaction, commandType, commandText, parms);
        object retval = command.ExecuteScalar();
        command.Parameters.Clear();
        return retval;
    }

    #endregion ExecuteScalar

    #region ExecuteDataReader

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private static MySqlDataReader ExecuteDataReader(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        MySqlConnection connection = new MySqlConnection(connectionString);
        return ExecuteDataReader(connection, null, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private static MySqlDataReader ExecuteDataReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        MySqlConnection connection = new MySqlConnection(connectionString);
        return ExecuteDataReader(connection, null, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataReader(connection, null, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private static MySqlDataReader ExecuteDataReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataReader(transaction.Connection, transaction, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回只读数据集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回只读数据集</returns>
    private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        MySqlCommand command = new MySqlCommand();
        PrepareCommand(command, connection, transaction, commandType, commandText, parms);
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }

    #endregion

    #region ExecuteDataRow

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>,返回结果集中的第一行</returns>
    public static DataRow ExecuteDataRow(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        DataTable dt = ExecuteDataTable(connectionString, CommandType.Text, commandText, parms);
        return dt.Rows.Count > 0 ? dt.Rows[0] : null;
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>,返回结果集中的第一行</returns>
    public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms);
        return dt.Rows.Count > 0 ? dt.Rows[0] : null;
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>,返回结果集中的第一行</returns>
    public static DataRow ExecuteDataRow(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms);
        return dt.Rows.Count > 0 ? dt.Rows[0] : null;
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一行
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>,返回结果集中的第一行</returns>
    public static DataRow ExecuteDataRow(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms);
        return dt.Rows.Count > 0 ? dt.Rows[0] : null;
    }

    #endregion ExecuteDataRow

    #region ExecuteDataTable

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public static DataTable ExecuteDataTable(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms).Tables[0];
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0];
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0];
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0];
    }

    /// <summary>
    /// 执行SQL语句,返回结果集中的第一个数据表
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="tableName">数据表名称</param>
    /// <returns>返回结果集中的第一个数据表</returns>
    public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName)
    {
        return ExecuteDataSet(connectionString, CommandType.Text, string.Format("select * from {0} where 1=-1", tableName)).Tables[0];
    }

    #endregion ExecuteDataTable

    #region ExecuteDataSet

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public static DataSet ExecuteDataSet(string connectionString, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            return ExecuteDataSet(connection, commandType, commandText, parms);
        }
    }

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public static DataSet ExecuteDataSet(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(connection, null, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    public static DataSet ExecuteDataSet(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        return ExecuteDataSet(transaction.Connection, transaction, commandType, commandText, parms);
    }

    /// <summary>
    /// 执行SQL语句,返回结果集
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">SQL语句或存储过程名称</param>
    /// <param name="parms">查询参数</param>
    /// <returns>返回结果集</returns>
    private static DataSet ExecuteDataSet(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
    {
        MySqlCommand command = new MySqlCommand();

        PrepareCommand(command, connection, transaction, commandType, commandText, parms);
        MySqlDataAdapter adapter = new MySqlDataAdapter(command);

        DataSet ds = new DataSet();
        adapter.Fill(ds);
        if (commandText.IndexOf("@") > 0)
        {
            commandText = commandText.ToLower();
            int index = commandText.IndexOf("where ");
            if (index < 0)
            {
                index = commandText.IndexOf("\nwhere");
            }
            if (index > 0)
            {
                ds.ExtendedProperties.Add("SQL", commandText.Substring(0, index - 1));  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
            }
            else
            {
                ds.ExtendedProperties.Add("SQL", commandText);  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
            }
        }
        else
        {
            ds.ExtendedProperties.Add("SQL", commandText);  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
        }

        foreach (DataTable dt in ds.Tables)
        {
            dt.ExtendedProperties.Add("SQL", ds.ExtendedProperties["SQL"]);
        }

        command.Parameters.Clear();
        return ds;
    }

    #endregion ExecuteDataSet

    #region 批量操作

    /// <summary>
    ///使用MySqlDataAdapter批量更新数据
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="table">数据表</param>
    public static int BatchUpdate(string connectionString, DataTable table)
    {
        MySqlConnection connection = new MySqlConnection(connectionString);
        if (table.Rows.Count == 0) return 0;
        int insertCount = 0;
        MySqlCommand command = connection.CreateCommand();
        command.CommandTimeout = CommandTimeOut;
        command.CommandType = CommandType.Text;
        MySqlDataAdapter adapter = new MySqlDataAdapter(command);
        MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter);
        commandBulider.ConflictOption = ConflictOption.OverwriteChanges;

        MySqlTransaction transaction = null;
        try
        {
            connection.Open();
            transaction = connection.BeginTransaction();
            //设置批量更新的每次处理条数
            adapter.UpdateBatchSize = BatchSize;
            //设置事物
            adapter.SelectCommand.Transaction = transaction;

            if (table.ExtendedProperties["SQL"] != null)
            {
                adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
            }
            insertCount = adapter.Update(table);
            transaction.Commit();/////提交事务
        }
        catch (MySqlException ex)
        {
            if (transaction != null) transaction.Rollback();
            connection.Close();
            connection.Dispose();
            throw ex;
        }
        finally
        {
            connection.Close();
            connection.Dispose();
        }
        return insertCount;
    }

    /// <summary>
    ///大批量数据插入,返回成功插入行数
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="table">数据表</param>
    /// <returns>返回成功插入行数</returns>
    public static int BulkInsert(string connectionString, DataTable table)
    {
        if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
        if (table.Rows.Count == 0) return 0;
        int insertCount = 0;
        string tmpPath = Path.GetTempFileName();
        string csv = DataTableToCsv(table);
        File.WriteAllText(tmpPath, csv);
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            MySqlTransaction tran = null;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = tmpPath,
                    NumberOfLinesToSkip = 0,
                    TableName = table.TableName,
                };
                bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                insertCount = bulk.Load();
                tran.Commit();
            }
            catch (MySqlException ex)
            {
                if (tran != null) tran.Rollback();
                throw ex;
            }
        }
        File.Delete(tmpPath);
        return insertCount;
    }

    /// <summary>
    ///将DataTable转换为标准的CSV
    /// </summary>
    /// <param name="table">数据表</param>
    /// <returns>返回标准的CSV</returns>
    private static string DataTableToCsv(DataTable table)
    {
        //以半角逗号（即,）作分隔符，列为空也要表达其存在。
        //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
        //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
        StringBuilder sb = new StringBuilder();
        DataColumn colum;
        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                colum = table.Columns[i];
                if (i != 0) sb.Append(",");
                if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                {
                    sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                }
                else sb.Append(row[colum].ToString());
            }
            sb.AppendLine();
        }

        return sb.ToString();
    }

    #endregion 批量操作

    #endregion 静态方法
}

配置

 <add name="DefaultConnection" connectionString="Data Source=47.74.171.108;port=9906;Initial Catalog=thai_gf_citdb_ai;user id=admin;password=stander7955178;Character Set=utf8;Allow Zero Datetime=True;Convert Zero Datetime=True" providerName="MySql.Data.MySqlClient" />

需要引用的DLL
https://download.youkuaiyun.com/download/xiongliuyun/20320294

##调用

	     var OrderIds = TaskDetailList.Select(a => a.OrderId).Distinct().ToList();
                        MySqlHelper dbhelper = new MySqlHelper();
                        dbhelper.ConnectionString = ConfigurationManager.ConnectionStrings["CrmConnection"].ConnectionString.ToString();
                        string strsqlupdate = @" update t_bank_order set FlowState=3,OrderStateType=2,OrderStatusId=27,ChangedStatus=4,TrackStatus=0 where {0}";
                        string strsqlupdate_where = " Id in('" + string.Join("','", OrderIds) + "')";
                        string strSQLUpdate = string.Format(strsqlupdate, strsqlupdate_where);
                        dbhelper.ExecuteNonQuery(strSQLUpdate);