2023年6月21日发(作者：)

⾃⽤类库整理之SqlHelper和MySqlHelper⾃⽤的SQLHelper和MySqlHelper，除⼀些通⽤⽅法外，封装了⼀些很实⽤的批量操作⽅法，简单介绍下SqlHelperExecuteEmptyDataTable获取空表结构，主要⽤于需要构造DataTable进⾏⼤批量数据插⼊时使⽤BulkInsert⼤批量数据插⼊,使⽤SqlBulkCopy进⾏⼤批量数据导⼊,仅提供了DataTable⽅式,需要DataReader的请⾃⾏重载BatchUpdate批量数据更新、插⼊,使⽤SqlDataAdapter实现，需要进⾏较⼤数据量更新、插⼊时可以使⽤此⽅法，性能还不错BatchUpdate,BatchDelete分批次批量删除、更新数据,此⽅法应⽤场景较少,⽤于需要删除、更新⼤量数据的同时不造成数据库阻塞且不影响数据库订阅、复制延时等ExecutePageDataTable通⽤的分页⽅法，提供了CTE表达式的⽀持(ExecuteObject、ExecuteObjects)提供了泛型获取对象⽅法⽀持,依赖依赖实现，⾮常在意性能的慎⽤erver获取数据库、表、索引信息的封装，可以⽤来辅助写⾃⼰的代码⽣成器，查看数据库索引之类的MySqlHelperExecuteEmptyDataTable获取空表结构，主要⽤于需要构造DataTable进⾏⼤批量数据插⼊时使⽤BulkInsert使⽤MySqlBulkLoader进⾏⼤批量数据导⼊,仅提供了DataTable⽅式,需要DataReader的请⾃⾏重载BatchUpdate批量数据更新、插⼊,使⽤MySqlDataAdapter实现，需要进⾏较⼤数据量更新、插⼊时可以使⽤此⽅法，性能还不错uteObject、ExecuteObjects提供了泛型获取对象⽅法⽀持,依赖依赖实现，⾮常在意性能的慎⽤

using System;using c;using ;using ;using ent;using ;using rExpressions;namespace Helpers{ ///

/// SqlHelper操作类 /// public sealed partial class SqlHelper { /// /// 批量操作每批次记录数 /// public static int BatchSize = 2000; /// /// 超时时间 /// public static int CommandTimeOut = 600; /// ///初始化SqlHelper实例 /// /// 数据库连接字符串 public SqlHelper(string connectionString) { tionString = connectionString; } /// /// 数据库连接字符串 /// public string ConnectionString { get; set; } #region 实例⽅法 #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, commandType, commandText, parms); } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public T ExecuteScalar(string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandType, commandText, parms); } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// SQL语句 /// 查询参数 /// 返回只读数据集 private SqlDataReader ExecuteDataReader(string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private SqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, commandType, commandText, parms); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(string commandText, params SqlParameter[] parms) { return ExecuteDataTable(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 排序SQL,如"ORDER BY ID DESC" /// 每页记录数 /// 页索引 /// 查询参数 /// 查询SQL

/// public DataTable ExecutePageDataTable(string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { return ExecutePageDataTable(sql, order, pageSize, pageIndex, parms, query, cte); } #endregion ExecuteDataTable #region ExecuteDataSet ///

/// 执⾏SQL语句,返回结果集 /// /// SQL语句 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataSet #region 批量操作 /// /// ⼤批量数据插⼊ /// /// 数据表 public void BulkInsert(DataTable table) { BulkInsert(ConnectionString, table); } /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据表 public void BatchUpdate(DataTable table) { BatchUpdate(ConnectionString, table); } /// /// 分批次批量删除数据 /// /// SQL语句 /// 每批次删除记录⾏数 /// 批次执⾏间隔(秒) public void BatchDelete(string sql, int batchSize = 1000, int interval = 1) { BatchDelete(ConnectionString, sql, batchSize, interval); } /// /// 分批次批量更新数据 /// /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public void BatchUpdate(string sql, int batchSize = 1000, int interval = 1) { BatchUpdate(ConnectionString, sql, batchSize, interval); } #endregion 批量操作 #endregion 实例⽅法 #region 静态⽅法 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms) { if ( != ) (); tion = connection; dTimeout = CommandTimeOut; // 设置命令⽂本(存储过程名或SQL语句) dText = commandText; // 分配事务 if (transaction != null) { ction = transaction; } // 设置命令类型. dType = commandType; if (parms != null && > 0) { //预处理SqlParameter参数数组，将为NULL的参数赋值为; foreach (SqlParameter parameter in parms) { if ((ion == utput || ion == ) && ( == null)) { = ; } } ge(parms); } } #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 private static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); int retval = eNonQuery(); (); return retval; } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static T ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parms) { object result = ExecuteScalar(connectionString, commandText, parms); if (result != null) { return (T)Type(result, typeof(T)); ; } return default(T); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 private static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); object retval = eScalar(); (); return retval; } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// SQL语句 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(string connectionString, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); return eReader(onnection); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, , commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms); return > 0 ? [0] : null; } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0]; } /// /// 获取空表结构 /// /// 数据库连接字符串 /// 数据表名称 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName) { return ExecuteDataSet(connectionString, , ("select * from {0} where 1=-1", tableName)).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 排序SQL,如"ORDER BY ID DESC" /// 每页记录数 /// 页索引 /// 查询参数

/// 查询SQL /// CTE表达式 /// public static DataTable ExecutePageDataTable(string connectionString, string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { string psql = (@" {3} SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {1}) RowNumber,* FROM ( {0} ) t WHERE 1 = 1 {2} ) t WHERE RowNumber BETWEEN @RowNumber_Begin AND @RowNumber_End", sql, order, query, cte); List paramlist = new List() { new SqlParameter("@RowNumber_Begin", ){ Value = (pageIndex - 1) * pageSize + 1 }, new SqlParameter("@RowNumber_End", ){ Value = pageIndex * pageSize } }; if (parms != null) ge(parms); return ExecuteDataTable(connectionString, psql, y()); } #endregion ExecuteDataTable #region ExecuteDataSet ///

/// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteDataSet(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 private static DataSet ExecuteDataSet(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); (ds); if (f("@") > 0) { commandText = r(); int index = f("where "); if (index < 0) { index = f("nwhere"); } if (index > 0) { ("SQL", ing(0, index - 1)); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } foreach (DataTable dt in ) { ("SQL", edProperties["SQL"]); } (); return ds; } #endregion ExecuteDataSet #region 批量操作 /// /// ⼤批量数据插⼊ /// /// 数据库连接字符串 /// 数据表 public static void BulkInsert(string connectionString, DataTable table) { if (OrEmpty(ame)) throw new Exception("ame属性不能为空"); using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString)) { ize = BatchSize; pyTimeout = CommandTimeOut; ationTableName = ame; foreach (DataColumn col in s) { (Name, Name); } oServer(table); (); } } /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据库连接字符串 /// 数据表 public static void BatchUpdate(string connectionString, DataTable table) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = Command(); dTimeout = CommandTimeOut; dType = ; SqlDataAdapter adapter = new SqlDataAdapter(command); SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter); ctOption = iteChanges; SqlTransaction transaction = null; try { (); transaction = ransaction(); //设置批量更新的每次处理条数 BatchSize = BatchSize; //设置事物 ction = transaction; if (edProperties["SQL"] != null) { dText = edProperties["SQL"].ToString(); } (table); ();/////提交事务 } catch (SqlException ex) { if (transaction != null) ck(); throw ex; } finally { (); e(); } } /// /// 分批次批量删除数据 /// /// 数据库连接字符串 /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public static void BatchDelete(string connectionString, string sql, int batchSize = 1000, int interval = 1) { sql = r(); if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; while (ExecuteScalar(connectionString, e("delete", "select top 1 1")) != null) { ExecuteNonQuery(connectionString, , e("delete", ("delete top ({0})", batchSize))); (interval * 1000); } } /// /// 分批次批量更新数据 /// /// 数据库连接字符串 /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public static void BatchUpdate(string connectionString, string sql, int batchSize = 1000, int interval = 1) { if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; string existsSql = e(sql, @"[ws.=,']*from", "select top 1 1 from", Case); existsSql = e(existsSql, @"set[ws.=,']* where", "where", Case); existsSql = e(existsSql, @"update", "select top 1 1 from", Case); while (ExecuteScalar(connectionString, existsSql) != 0) { ExecuteNonQuery(connectionString, , e(sql, "update", ("update top ({0})", batchSize), Case)); (interval * 1000); } } #endregion 批量操作 #endregion 静态⽅法 }}g System;using uration;using ;using ent;using ;using c;using ing;using rExpressions;namespace Helpers{ /// /// SqlHelper扩展(依赖) /// public sealed partial class SqlHelper { #region 实例⽅法 public T ExecuteObject(string commandText, params SqlParameter[] parms) { return ExecuteObject(tionString, commandText, parms); } public List ExecuteObjects(string commandText, params SqlParameter[] parms) { return ExecuteObjects(tionString, commandText, parms); } #endregion #region 静态⽅法 public static T ExecuteObject(string connectionString, string commandText, params SqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()).FirstOrDefault(); using (SqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader).FirstOrDefault(); } } public static List ExecuteObjects(string connectionString, string commandText, params SqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()); using (SqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader); } } #endregion }}g System;using c;using ;using ;using ;using ent;namespace Helpers{ public sealed partial class SqlHelper { #region Schema 数据库结构 /// /// 执⾏指定数据库连接字符串的命令,返回服务器数据库名称数组 /// /// 数据库连接字符串 /// public string[] GetDatabases(string connectionString) { string sql = "select name from ses where name not in ('master','model','msdb','tempdb')"; DataTable dt = ExecuteDataTable(connectionString, sql); return ().Select(row => row["name"].ToString()).ToArray(); } /// /// 执⾏指定数据库连接字符串的命令,返回指定数据库的表信息 /// /// 数据库连接字符串 /// /// public List GetDbTables(string connectionString, string database) { #region SQL string sql = (@"SELECT tablename, schemname, , CAST ( CASE

WHEN (SELECT COUNT(1) FROM s WHERE object_id= _ID AND is_primary_key=1) >=1 THEN 1 ELSE 0 END

AS BIT) HasPrimaryKey

from {0}.s obj

inner join {0}.exes idx on _id= and <=1 INNER JOIN {0}.s schem ON _id=_id where type='U'

order by ", database); #endregion DataTable dt = ExecuteDataTable(e("master", database), sql); return ().Select(row => new DbTable { TableName = ("tablename"), SchemaName = ("schemname"), Rows = ("rows"), HasPrimaryKey = ("HasPrimaryKey") }).ToList(); } ///

/// 执⾏指定数据库连接字符串的命令,返回指定数据库、表的字段信息 /// /// 数据库连接字符串 /// /// /// /// public List GetDbColumns(string connectionString, string database, string tableName, string schema = "dbo") { #region SQL string sql = (@" WITH indexCTE AS ( SELECT

_id, _column_id, _id

FROM {0}.s idx INNER JOIN {0}._columns ic ON _id = _id AND _id = _id WHERE _id =OBJECT_ID(@tableName) AND _primary_key=1 ) select _id ColumnID, CAST(CASE WHEN _id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey, ColumnName, ColumnType, _identity IsIdentity, _nullable IsNullable, cast(_length as int) ByteLength, ( case

when ='nvarchar' and _length>0 then _length/2

when ='nchar' and _length>0 then _length/2 when ='ntext' and _length>0 then _length/2

else _length end ) CharLength, cast(ion as int) Precision, cast( as int) Scale, Remark from {0}.s colm inner join {0}. systype on _type_id=_type_id and _type_id=_type_id left join {0}.ed_properties prop on _id=_id and _id=_id LEFT JOIN indexCTE ON _id=_id AND _id=_id

where _id=OBJECT_ID(@tableName) order by _id", database); #endregion SqlParameter param = new SqlParameter("@tableName", ar, 100) { Value = ("{0}.{1}.{2}", database, schema, tableName) }; DataTable dt = ExecuteDataTable(connectionString, sql, param); return ().Select(row => new DbColumn() { ColumnID = ("ColumnID"), IsPrimaryKey = ("IsPrimaryKey"), ColumnName = ("ColumnName"), ColumnType = ("ColumnType"), IsIdentity = ("IsIdentity"), IsNullable = ("IsNullable"), ByteLength = ("ByteLength"), CharLength = ("CharLength"), Scale = ("Scale"), Remark = row["Remark"].ToString() }).ToList(); } ///

/// 执⾏指定数据库连接字符串的命令,返回指定数据库、表的索引信息 /// /// 数据库连接字符串 /// /// /// /// public List GetDbIndexs(string connectionString, string database, string tableName, string schema = "dbo") { #region SQL string sql = (@" select

IndexName ,_desc IndexType ,_primary_key IsPrimaryKey ,_unique IsUnique ,_unique_constraint IsUniqueConstraint ,STUFF( ( SELECT ','+ from {0}._columns ic inner join {0}.s c on _id=_id and _id=_id WHERE _included_column = 0 and _id=_id AND _id=_id ORDER BY _ordinal FOR XML PATH('') ),1,1,'') IndexColumns ,STUFF( ( SELECT ','+ from {0}._columns ic inner join {0}.s c on _id=_id and _id=_id WHERE _included_column = 1 and _id=_id AND _id=_id ORDER BY _ordinal FOR XML PATH('') ),1,1,'') IncludeColumns from {0}.s idx where object_id =OBJECT_ID(@tableName)", database); #endregion SqlParameter param = new SqlParameter("@tableName", ar, 100) { Value = ("{0}.{1}.{2}", database, schema, tableName) }; DataTable dt = ExecuteDataTable(connectionString, sql, param); return ().Select(row => new DbIndex() { IndexName = ("IndexName"), IndexType = ("IndexType"), IsPrimaryKey = ("IsPrimaryKey"), IsUnique = ("IsUnique"), IsUniqueConstraint = ("IsUniqueConstraint"), IndexColumns = ("IndexColumns"), IncludeColumns = ("IncludeColumns") }).ToList(); } #endregion } ///

/// 表索引结构 /// public sealed class DbIndex { /// /// 索引名称 /// public string IndexName { get; set; } /// /// 索引类型 /// public string IndexType { get; set; } /// /// 是否为主键 /// public bool IsPrimaryKey { get; set; } /// /// 是否唯⼀索引 /// public bool IsUnique { get; set; } /// /// 是否唯⼀约束 /// public bool IsUniqueConstraint { get; set; } /// /// 索引列 /// public string IndexColumns { get; set; } /// /// 覆盖索引列 /// public string IncludeColumns { get; set; } } /// /// 表结构 /// public sealed class DbTable { /// /// 表名称 /// public string TableName { get; set; } /// /// 表的架构 /// public string SchemaName { get; set; } /// /// 表的记录数 /// public int Rows { get; set; } /// /// 是否含有主键 /// public bool HasPrimaryKey { get; set; } } /// /// 表字段结构 /// public sealed class DbColumn { /// /// 字段ID /// public int ColumnID { get; set; } /// /// 是否主键 /// public bool IsPrimaryKey { get; set; } /// /// 字段名称 /// public string ColumnName { get; set; } /// /// 字段类型 /// public string ColumnType { get; set; } /// /// 数据库类型对应的C#类型 /// public string CSharpType { get { return arpType(ColumnType); } } /// ///

///

public Type CommonType { get { return monType(ColumnType); } } /// /// 字节长度 /// public int ByteLength { get; set; } /// /// 字符长度 /// public int CharLength { get; set; } /// /// ⼩数位 /// public int Scale { get; set; } /// /// 是否⾃增列 /// public bool IsIdentity { get; set; } /// /// 是否允许空 /// public bool IsNullable { get; set; } /// /// 描述 /// public string Remark { get; set; } } public class SqlMap { public static string MapCsharpType(string dbtype) { if (OrEmpty(dbtype)) return dbtype; dbtype = r(); string csharpType = "object"; switch (dbtype) { case "bigint": csharpType = "long"; break; case "binary": csharpType = "byte[]"; break; case "bit": csharpType = "bool"; break; case "char": csharpType = "string"; break; case "date": csharpType = "DateTime"; break; case "datetime": csharpType = "DateTime"; break; case "datetime2": csharpType = "DateTime"; break; case "datetimeoffset": csharpType = "DateTimeOffset"; break; case "decimal": csharpType = "decimal"; break; case "float": csharpType = "double"; break; case "image": csharpType = "byte[]"; break; case "int": csharpType = "int"; break; case "money": csharpType = "decimal"; break; case "nchar": csharpType = "string"; break; case "ntext": csharpType = "string"; break; case "numeric": csharpType = "decimal"; break; case "nvarchar": csharpType = "string"; break; case "real": csharpType = "Single"; break; case "smalldatetime": csharpType = "DateTime"; break; case "smallint": csharpType = "short"; break; case "smallmoney": csharpType = "decimal"; break; case "sql_variant": csharpType = "object"; break; case "sysname": csharpType = "object"; break; case "text": csharpType = "string"; break; case "time": csharpType = "TimeSpan"; break; case "timestamp": csharpType = "byte[]"; break; case "tinyint": csharpType = "byte"; break; case "uniqueidentifier": csharpType = "Guid"; break; case "varbinary": csharpType = "byte[]"; break; case "varchar": csharpType = "string"; break; case "xml": csharpType = "string"; break; default: csharpType = "object"; break; } return csharpType; } public static Type MapCommonType(string dbtype) { if (OrEmpty(dbtype)) return e(); dbtype = r(); Type commonType = typeof(object); switch (dbtype) { case "bigint": commonType = typeof(long); break; case "binary": commonType = typeof(byte[]); break; case "bit": commonType = typeof(bool); break; case "char": commonType = typeof(string); break; case "date": commonType = typeof(DateTime); break; case "datetime": commonType = typeof(DateTime); break; case "datetime2": commonType = typeof(DateTime); break; case "datetimeoffset": commonType = typeof(DateTimeOffset); break; case "decimal": commonType = typeof(decimal); break; case "float": commonType = typeof(double); break; case "image": commonType = typeof(byte[]); break; case "int": commonType = typeof(int); break; case "money": commonType = typeof(decimal); break; case "nchar": commonType = typeof(string); break; case "ntext": commonType = typeof(string); break; case "numeric": commonType = typeof(decimal); break; case "nvarchar": commonType = typeof(string); break; case "real": commonType = typeof(Single); break; case "smalldatetime": commonType = typeof(DateTime); break; case "smallint": commonType = typeof(short); break; case "smallmoney": commonType = typeof(decimal); break; case "sql_variant": commonType = typeof(object); break; case "sysname": commonType = typeof(object); break; case "text": commonType = typeof(string); break; case "time": commonType = typeof(TimeSpan); break; case "timestamp": commonType = typeof(byte[]); break; case "tinyint": commonType = typeof(byte); break; case "uniqueidentifier": commonType = typeof(Guid); break; case "varbinary": commonType = typeof(byte[]); break; case "varchar": commonType = typeof(string); break; case "xml": commonType = typeof(string); break; default: commonType = typeof(object); break; } return commonType; } }}g System;using ;using ;using ;using ;using lient;namespace Helpers{ /// /// MySqlHelper操作类 /// public sealed partial class MySqlHelper { /// /// 批量操作每批次记录数 /// public static int BatchSize = 2000; /// /// 超时时间 /// public static int CommandTimeOut = 600; /// ///初始化MySqlHelper实例 /// /// 数据库连接字符串 public MySqlHelper(string connectionString) { tionString = connectionString; } /// /// 数据库连接字符串 /// public string ConnectionString { get; set; } #region 实例⽅法 #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, commandType, commandText, parms); } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public T ExecuteScalar(string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandType, commandText, parms); } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// SQL语句 /// 查询参数 /// 返回只读数据集 private MySqlDataReader ExecuteDataReader(string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private MySqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(ConnectionString, commandType, commandText, parms); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(string commandText, params MySqlParameter[] parms) { return ExecuteDataRow(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataRow(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parms) { return ExecuteDataTable(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0]; } #endregion ExecuteDataTable #region ExecuteDataSet /// /// 执⾏SQL语句,返回结果集 /// /// SQL语句 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataSet #region 批量操作 /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据表 public void BatchUpdate(DataTable table) { BatchUpdate(ConnectionString, table); } /// ///⼤批量数据插⼊,返回成功插⼊⾏数 /// /// 数据表 /// 返回成功插⼊⾏数 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 ( != ) (); tion = connection; dTimeout = CommandTimeOut; // 设置命令⽂本(存储过程名或SQL语句) dText = commandText; // 分配事务 if (transaction != null) { ction = transaction; } // 设置命令类型. dType = commandType; if (parms != null && > 0) { //预处理MySqlParameter参数数组，将为NULL的参数赋值为; foreach (MySqlParameter parameter in parms) { if ((ion == utput || ion == ) && ( == null)) { = ; } } ge(parms); } } #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, string commandText, params MySqlParameter[] parms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { return ExecuteNonQuery(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 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); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 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 = eNonQuery(); (); return retval; } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static T ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms) { object result = ExecuteScalar(connectionString, commandText, parms); if (result != null) { return (T)Type(result, typeof(T)); ; } return default(T); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { return ExecuteScalar(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 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); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 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 = eScalar(); (); return retval; } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// SQL语句 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(string connectionString, string commandText, params MySqlParameter[] parms) { MySqlConnection connection = new MySqlConnection(connectionString); return ExecuteDataReader(connection, null, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 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); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 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 eReader(onnection); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, , commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms); return > 0 ? [0] : null; } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 数据表名称 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName) { return ExecuteDataSet(connectionString, , ("select * from {0} where 1=-1", tableName)).Tables[0]; } #endregion ExecuteDataTable #region ExecuteDataSet /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 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); } } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 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(); (ds); if (f("@") > 0) { commandText = r(); int index = f("where "); if (index < 0) { index = f("nwhere"); } if (index > 0) { ("SQL", ing(0, index - 1)); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } foreach (DataTable dt in ) { ("SQL", edProperties["SQL"]); } (); return ds; } #endregion ExecuteDataSet #region 批量操作 /// ///使⽤MySqlDataAdapter批量更新数据 /// /// 数据库连接字符串 /// 数据表 public static void BatchUpdate(string connectionString, DataTable table) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand command = Command(); dTimeout = CommandTimeOut; dType = ; MySqlDataAdapter adapter = new MySqlDataAdapter(command); MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter); ctOption = iteChanges; MySqlTransaction transaction = null; try { (); transaction = ransaction(); //设置批量更新的每次处理条数 BatchSize = BatchSize; //设置事物 ction = transaction; if (edProperties["SQL"] != null) { dText = edProperties["SQL"].ToString(); } (table); ();/////提交事务 } catch (MySqlException ex) { if (transaction != null) ck(); throw ex; } finally { (); e(); } } /// ///⼤批量数据插⼊,返回成功插⼊⾏数 /// /// 数据库连接字符串 /// 数据表 /// 返回成功插⼊⾏数 public static int BulkInsert(string connectionString, DataTable table) { if (OrEmpty(ame)) throw new Exception("请给DataTable的TableName属性附上表名称"); if ( == 0) return 0; int insertCount = 0; string tmpPath = pFileName(); string csv = DataTableToCsv(table); llText(tmpPath, csv); using (MySqlConnection conn = new MySqlConnection(connectionString)) { MySqlTransaction tran = null; try { (); tran = ransaction(); MySqlBulkLoader bulk = new MySqlBulkLoader(conn) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "rn", FileName = tmpPath, NumberOfLinesToSkip = 0, TableName = ame, }; ge(().Select(colum => Name).ToList()); insertCount = (); (); } catch (MySqlException ex) { if (tran != null) ck(); throw ex; } } (tmpPath); return insertCount; } /// ///将DataTable转换为标准的CSV /// /// 数据表 /// 返回标准的CSV private static string DataTableToCsv(DataTable table) { //以半⾓逗号（即,）作分隔符，列为空也要表达其存在。 //列内容如存在半⾓逗号（即,）则⽤半⾓引号（即""）将该字段值包含起来。 //列内容如存在半⾓引号（即"）则应替换成半⾓双引号（""）转义，并⽤半⾓引号（即""）将该字段值包含起来。 StringBuilder sb = new StringBuilder(); DataColumn colum; foreach (DataRow row in ) { for (int i = 0; i < ; i++) { colum = s[i]; if (i != 0) (","); if (pe == typeof(string) && row[colum].ToString().Contains(",")) { (""" + row[colum].ToString().Replace(""", """") + """); } else (row[colum].ToString()); } Line(); } return ng(); } #endregion 批量操作 #endregion 静态⽅法 }}g System;using c;using ;using ;using lient;using ;namespace Helpers{ /// /// MySqlHelper扩展(依赖) /// public sealed partial class MySqlHelper { #region 实例⽅法 public T ExecuteObject(string commandText, params MySqlParameter[] parms) { return ExecuteObject(tionString, commandText, parms); } public List ExecuteObjects(string commandText, params MySqlParameter[] parms) { return ExecuteObjects(tionString, commandText, parms); } #endregion #region 静态⽅法 public static T ExecuteObject(string connectionString, string commandText, params MySqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()).FirstOrDefault(); using (MySqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader).FirstOrDefault(); } } public static List ExecuteObjects(string connectionString, string commandText, params MySqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()); using (MySqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader); } } #endregion }}最后附上⽂件源码及相关DLL:

2023年6月21日发(作者：)

⾃⽤类库整理之SqlHelper和MySqlHelper⾃⽤的SQLHelper和MySqlHelper，除⼀些通⽤⽅法外，封装了⼀些很实⽤的批量操作⽅法，简单介绍下SqlHelperExecuteEmptyDataTable获取空表结构，主要⽤于需要构造DataTable进⾏⼤批量数据插⼊时使⽤BulkInsert⼤批量数据插⼊,使⽤SqlBulkCopy进⾏⼤批量数据导⼊,仅提供了DataTable⽅式,需要DataReader的请⾃⾏重载BatchUpdate批量数据更新、插⼊,使⽤SqlDataAdapter实现，需要进⾏较⼤数据量更新、插⼊时可以使⽤此⽅法，性能还不错BatchUpdate,BatchDelete分批次批量删除、更新数据,此⽅法应⽤场景较少,⽤于需要删除、更新⼤量数据的同时不造成数据库阻塞且不影响数据库订阅、复制延时等ExecutePageDataTable通⽤的分页⽅法，提供了CTE表达式的⽀持(ExecuteObject、ExecuteObjects)提供了泛型获取对象⽅法⽀持,依赖依赖实现，⾮常在意性能的慎⽤erver获取数据库、表、索引信息的封装，可以⽤来辅助写⾃⼰的代码⽣成器，查看数据库索引之类的MySqlHelperExecuteEmptyDataTable获取空表结构，主要⽤于需要构造DataTable进⾏⼤批量数据插⼊时使⽤BulkInsert使⽤MySqlBulkLoader进⾏⼤批量数据导⼊,仅提供了DataTable⽅式,需要DataReader的请⾃⾏重载BatchUpdate批量数据更新、插⼊,使⽤MySqlDataAdapter实现，需要进⾏较⼤数据量更新、插⼊时可以使⽤此⽅法，性能还不错uteObject、ExecuteObjects提供了泛型获取对象⽅法⽀持,依赖依赖实现，⾮常在意性能的慎⽤

using System;using c;using ;using ;using ent;using ;using rExpressions;namespace Helpers{ ///

/// SqlHelper操作类 /// public sealed partial class SqlHelper { /// /// 批量操作每批次记录数 /// public static int BatchSize = 2000; /// /// 超时时间 /// public static int CommandTimeOut = 600; /// ///初始化SqlHelper实例 /// /// 数据库连接字符串 public SqlHelper(string connectionString) { tionString = connectionString; } /// /// 数据库连接字符串 /// public string ConnectionString { get; set; } #region 实例⽅法 #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, commandType, commandText, parms); } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public T ExecuteScalar(string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandType, commandText, parms); } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// SQL语句 /// 查询参数 /// 返回只读数据集 private SqlDataReader ExecuteDataReader(string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private SqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, commandType, commandText, parms); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(string commandText, params SqlParameter[] parms) { return ExecuteDataTable(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 排序SQL,如"ORDER BY ID DESC" /// 每页记录数 /// 页索引 /// 查询参数 /// 查询SQL

/// public DataTable ExecutePageDataTable(string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { return ExecutePageDataTable(sql, order, pageSize, pageIndex, parms, query, cte); } #endregion ExecuteDataTable #region ExecuteDataSet ///

/// 执⾏SQL语句,返回结果集 /// /// SQL语句 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataSet #region 批量操作 /// /// ⼤批量数据插⼊ /// /// 数据表 public void BulkInsert(DataTable table) { BulkInsert(ConnectionString, table); } /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据表 public void BatchUpdate(DataTable table) { BatchUpdate(ConnectionString, table); } /// /// 分批次批量删除数据 /// /// SQL语句 /// 每批次删除记录⾏数 /// 批次执⾏间隔(秒) public void BatchDelete(string sql, int batchSize = 1000, int interval = 1) { BatchDelete(ConnectionString, sql, batchSize, interval); } /// /// 分批次批量更新数据 /// /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public void BatchUpdate(string sql, int batchSize = 1000, int interval = 1) { BatchUpdate(ConnectionString, sql, batchSize, interval); } #endregion 批量操作 #endregion 实例⽅法 #region 静态⽅法 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms) { if ( != ) (); tion = connection; dTimeout = CommandTimeOut; // 设置命令⽂本(存储过程名或SQL语句) dText = commandText; // 分配事务 if (transaction != null) { ction = transaction; } // 设置命令类型. dType = commandType; if (parms != null && > 0) { //预处理SqlParameter参数数组，将为NULL的参数赋值为; foreach (SqlParameter parameter in parms) { if ((ion == utput || ion == ) && ( == null)) { = ; } } ge(parms); } } #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 private static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); int retval = eNonQuery(); (); return retval; } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static T ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parms) { object result = ExecuteScalar(connectionString, commandText, parms); if (result != null) { return (T)Type(result, typeof(T)); ; } return default(T); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 private static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); object retval = eScalar(); (); return retval; } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// SQL语句 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(string connectionString, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static SqlDataReader ExecuteDataReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); return eReader(onnection); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, , commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms); return > 0 ? [0] : null; } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0]; } /// /// 获取空表结构 /// /// 数据库连接字符串 /// 数据表名称 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName) { return ExecuteDataSet(connectionString, , ("select * from {0} where 1=-1", tableName)).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 排序SQL,如"ORDER BY ID DESC" /// 每页记录数 /// 页索引 /// 查询参数

/// 查询SQL /// CTE表达式 /// public static DataTable ExecutePageDataTable(string connectionString, string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { string psql = (@" {3} SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {1}) RowNumber,* FROM ( {0} ) t WHERE 1 = 1 {2} ) t WHERE RowNumber BETWEEN @RowNumber_Begin AND @RowNumber_End", sql, order, query, cte); List paramlist = new List() { new SqlParameter("@RowNumber_Begin", ){ Value = (pageIndex - 1) * pageSize + 1 }, new SqlParameter("@RowNumber_End", ){ Value = pageIndex * pageSize } }; if (parms != null) ge(parms); return ExecuteDataTable(connectionString, psql, y()); } #endregion ExecuteDataTable #region ExecuteDataSet ///

/// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteDataSet(connection, commandType, commandText, parms); } } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 private static DataSet ExecuteDataSet(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); (ds); if (f("@") > 0) { commandText = r(); int index = f("where "); if (index < 0) { index = f("nwhere"); } if (index > 0) { ("SQL", ing(0, index - 1)); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } foreach (DataTable dt in ) { ("SQL", edProperties["SQL"]); } (); return ds; } #endregion ExecuteDataSet #region 批量操作 /// /// ⼤批量数据插⼊ /// /// 数据库连接字符串 /// 数据表 public static void BulkInsert(string connectionString, DataTable table) { if (OrEmpty(ame)) throw new Exception("ame属性不能为空"); using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString)) { ize = BatchSize; pyTimeout = CommandTimeOut; ationTableName = ame; foreach (DataColumn col in s) { (Name, Name); } oServer(table); (); } } /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据库连接字符串 /// 数据表 public static void BatchUpdate(string connectionString, DataTable table) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = Command(); dTimeout = CommandTimeOut; dType = ; SqlDataAdapter adapter = new SqlDataAdapter(command); SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter); ctOption = iteChanges; SqlTransaction transaction = null; try { (); transaction = ransaction(); //设置批量更新的每次处理条数 BatchSize = BatchSize; //设置事物 ction = transaction; if (edProperties["SQL"] != null) { dText = edProperties["SQL"].ToString(); } (table); ();/////提交事务 } catch (SqlException ex) { if (transaction != null) ck(); throw ex; } finally { (); e(); } } /// /// 分批次批量删除数据 /// /// 数据库连接字符串 /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public static void BatchDelete(string connectionString, string sql, int batchSize = 1000, int interval = 1) { sql = r(); if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; while (ExecuteScalar(connectionString, e("delete", "select top 1 1")) != null) { ExecuteNonQuery(connectionString, , e("delete", ("delete top ({0})", batchSize))); (interval * 1000); } } /// /// 分批次批量更新数据 /// /// 数据库连接字符串 /// SQL语句 /// 每批次更新记录⾏数 /// 批次执⾏间隔(秒) public static void BatchUpdate(string connectionString, string sql, int batchSize = 1000, int interval = 1) { if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; string existsSql = e(sql, @"[ws.=,']*from", "select top 1 1 from", Case); existsSql = e(existsSql, @"set[ws.=,']* where", "where", Case); existsSql = e(existsSql, @"update", "select top 1 1 from", Case); while (ExecuteScalar(connectionString, existsSql) != 0) { ExecuteNonQuery(connectionString, , e(sql, "update", ("update top ({0})", batchSize), Case)); (interval * 1000); } } #endregion 批量操作 #endregion 静态⽅法 }}g System;using uration;using ;using ent;using ;using c;using ing;using rExpressions;namespace Helpers{ /// /// SqlHelper扩展(依赖) /// public sealed partial class SqlHelper { #region 实例⽅法 public T ExecuteObject(string commandText, params SqlParameter[] parms) { return ExecuteObject(tionString, commandText, parms); } public List ExecuteObjects(string commandText, params SqlParameter[] parms) { return ExecuteObjects(tionString, commandText, parms); } #endregion #region 静态⽅法 public static T ExecuteObject(string connectionString, string commandText, params SqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()).FirstOrDefault(); using (SqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader).FirstOrDefault(); } } public static List ExecuteObjects(string connectionString, string commandText, params SqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()); using (SqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader); } } #endregion }}g System;using c;using ;using ;using ;using ent;namespace Helpers{ public sealed partial class SqlHelper { #region Schema 数据库结构 /// /// 执⾏指定数据库连接字符串的命令,返回服务器数据库名称数组 /// /// 数据库连接字符串 /// public string[] GetDatabases(string connectionString) { string sql = "select name from ses where name not in ('master','model','msdb','tempdb')"; DataTable dt = ExecuteDataTable(connectionString, sql); return ().Select(row => row["name"].ToString()).ToArray(); } /// /// 执⾏指定数据库连接字符串的命令,返回指定数据库的表信息 /// /// 数据库连接字符串 /// /// public List GetDbTables(string connectionString, string database) { #region SQL string sql = (@"SELECT tablename, schemname, , CAST ( CASE

WHEN (SELECT COUNT(1) FROM s WHERE object_id= _ID AND is_primary_key=1) >=1 THEN 1 ELSE 0 END

AS BIT) HasPrimaryKey

from {0}.s obj

inner join {0}.exes idx on _id= and <=1 INNER JOIN {0}.s schem ON _id=_id where type='U'

order by ", database); #endregion DataTable dt = ExecuteDataTable(e("master", database), sql); return ().Select(row => new DbTable { TableName = ("tablename"), SchemaName = ("schemname"), Rows = ("rows"), HasPrimaryKey = ("HasPrimaryKey") }).ToList(); } ///

/// 执⾏指定数据库连接字符串的命令,返回指定数据库、表的字段信息 /// /// 数据库连接字符串 /// /// /// /// public List GetDbColumns(string connectionString, string database, string tableName, string schema = "dbo") { #region SQL string sql = (@" WITH indexCTE AS ( SELECT

_id, _column_id, _id

FROM {0}.s idx INNER JOIN {0}._columns ic ON _id = _id AND _id = _id WHERE _id =OBJECT_ID(@tableName) AND _primary_key=1 ) select _id ColumnID, CAST(CASE WHEN _id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey, ColumnName, ColumnType, _identity IsIdentity, _nullable IsNullable, cast(_length as int) ByteLength, ( case

when ='nvarchar' and _length>0 then _length/2

when ='nchar' and _length>0 then _length/2 when ='ntext' and _length>0 then _length/2

else _length end ) CharLength, cast(ion as int) Precision, cast( as int) Scale, Remark from {0}.s colm inner join {0}. systype on _type_id=_type_id and _type_id=_type_id left join {0}.ed_properties prop on _id=_id and _id=_id LEFT JOIN indexCTE ON _id=_id AND _id=_id

where _id=OBJECT_ID(@tableName) order by _id", database); #endregion SqlParameter param = new SqlParameter("@tableName", ar, 100) { Value = ("{0}.{1}.{2}", database, schema, tableName) }; DataTable dt = ExecuteDataTable(connectionString, sql, param); return ().Select(row => new DbColumn() { ColumnID = ("ColumnID"), IsPrimaryKey = ("IsPrimaryKey"), ColumnName = ("ColumnName"), ColumnType = ("ColumnType"), IsIdentity = ("IsIdentity"), IsNullable = ("IsNullable"), ByteLength = ("ByteLength"), CharLength = ("CharLength"), Scale = ("Scale"), Remark = row["Remark"].ToString() }).ToList(); } ///

/// 执⾏指定数据库连接字符串的命令,返回指定数据库、表的索引信息 /// /// 数据库连接字符串 /// /// /// /// public List GetDbIndexs(string connectionString, string database, string tableName, string schema = "dbo") { #region SQL string sql = (@" select

IndexName ,_desc IndexType ,_primary_key IsPrimaryKey ,_unique IsUnique ,_unique_constraint IsUniqueConstraint ,STUFF( ( SELECT ','+ from {0}._columns ic inner join {0}.s c on _id=_id and _id=_id WHERE _included_column = 0 and _id=_id AND _id=_id ORDER BY _ordinal FOR XML PATH('') ),1,1,'') IndexColumns ,STUFF( ( SELECT ','+ from {0}._columns ic inner join {0}.s c on _id=_id and _id=_id WHERE _included_column = 1 and _id=_id AND _id=_id ORDER BY _ordinal FOR XML PATH('') ),1,1,'') IncludeColumns from {0}.s idx where object_id =OBJECT_ID(@tableName)", database); #endregion SqlParameter param = new SqlParameter("@tableName", ar, 100) { Value = ("{0}.{1}.{2}", database, schema, tableName) }; DataTable dt = ExecuteDataTable(connectionString, sql, param); return ().Select(row => new DbIndex() { IndexName = ("IndexName"), IndexType = ("IndexType"), IsPrimaryKey = ("IsPrimaryKey"), IsUnique = ("IsUnique"), IsUniqueConstraint = ("IsUniqueConstraint"), IndexColumns = ("IndexColumns"), IncludeColumns = ("IncludeColumns") }).ToList(); } #endregion } ///

/// 表索引结构 /// public sealed class DbIndex { /// /// 索引名称 /// public string IndexName { get; set; } /// /// 索引类型 /// public string IndexType { get; set; } /// /// 是否为主键 /// public bool IsPrimaryKey { get; set; } /// /// 是否唯⼀索引 /// public bool IsUnique { get; set; } /// /// 是否唯⼀约束 /// public bool IsUniqueConstraint { get; set; } /// /// 索引列 /// public string IndexColumns { get; set; } /// /// 覆盖索引列 /// public string IncludeColumns { get; set; } } /// /// 表结构 /// public sealed class DbTable { /// /// 表名称 /// public string TableName { get; set; } /// /// 表的架构 /// public string SchemaName { get; set; } /// /// 表的记录数 /// public int Rows { get; set; } /// /// 是否含有主键 /// public bool HasPrimaryKey { get; set; } } /// /// 表字段结构 /// public sealed class DbColumn { /// /// 字段ID /// public int ColumnID { get; set; } /// /// 是否主键 /// public bool IsPrimaryKey { get; set; } /// /// 字段名称 /// public string ColumnName { get; set; } /// /// 字段类型 /// public string ColumnType { get; set; } /// /// 数据库类型对应的C#类型 /// public string CSharpType { get { return arpType(ColumnType); } } /// ///

///

public Type CommonType { get { return monType(ColumnType); } } /// /// 字节长度 /// public int ByteLength { get; set; } /// /// 字符长度 /// public int CharLength { get; set; } /// /// ⼩数位 /// public int Scale { get; set; } /// /// 是否⾃增列 /// public bool IsIdentity { get; set; } /// /// 是否允许空 /// public bool IsNullable { get; set; } /// /// 描述 /// public string Remark { get; set; } } public class SqlMap { public static string MapCsharpType(string dbtype) { if (OrEmpty(dbtype)) return dbtype; dbtype = r(); string csharpType = "object"; switch (dbtype) { case "bigint": csharpType = "long"; break; case "binary": csharpType = "byte[]"; break; case "bit": csharpType = "bool"; break; case "char": csharpType = "string"; break; case "date": csharpType = "DateTime"; break; case "datetime": csharpType = "DateTime"; break; case "datetime2": csharpType = "DateTime"; break; case "datetimeoffset": csharpType = "DateTimeOffset"; break; case "decimal": csharpType = "decimal"; break; case "float": csharpType = "double"; break; case "image": csharpType = "byte[]"; break; case "int": csharpType = "int"; break; case "money": csharpType = "decimal"; break; case "nchar": csharpType = "string"; break; case "ntext": csharpType = "string"; break; case "numeric": csharpType = "decimal"; break; case "nvarchar": csharpType = "string"; break; case "real": csharpType = "Single"; break; case "smalldatetime": csharpType = "DateTime"; break; case "smallint": csharpType = "short"; break; case "smallmoney": csharpType = "decimal"; break; case "sql_variant": csharpType = "object"; break; case "sysname": csharpType = "object"; break; case "text": csharpType = "string"; break; case "time": csharpType = "TimeSpan"; break; case "timestamp": csharpType = "byte[]"; break; case "tinyint": csharpType = "byte"; break; case "uniqueidentifier": csharpType = "Guid"; break; case "varbinary": csharpType = "byte[]"; break; case "varchar": csharpType = "string"; break; case "xml": csharpType = "string"; break; default: csharpType = "object"; break; } return csharpType; } public static Type MapCommonType(string dbtype) { if (OrEmpty(dbtype)) return e(); dbtype = r(); Type commonType = typeof(object); switch (dbtype) { case "bigint": commonType = typeof(long); break; case "binary": commonType = typeof(byte[]); break; case "bit": commonType = typeof(bool); break; case "char": commonType = typeof(string); break; case "date": commonType = typeof(DateTime); break; case "datetime": commonType = typeof(DateTime); break; case "datetime2": commonType = typeof(DateTime); break; case "datetimeoffset": commonType = typeof(DateTimeOffset); break; case "decimal": commonType = typeof(decimal); break; case "float": commonType = typeof(double); break; case "image": commonType = typeof(byte[]); break; case "int": commonType = typeof(int); break; case "money": commonType = typeof(decimal); break; case "nchar": commonType = typeof(string); break; case "ntext": commonType = typeof(string); break; case "numeric": commonType = typeof(decimal); break; case "nvarchar": commonType = typeof(string); break; case "real": commonType = typeof(Single); break; case "smalldatetime": commonType = typeof(DateTime); break; case "smallint": commonType = typeof(short); break; case "smallmoney": commonType = typeof(decimal); break; case "sql_variant": commonType = typeof(object); break; case "sysname": commonType = typeof(object); break; case "text": commonType = typeof(string); break; case "time": commonType = typeof(TimeSpan); break; case "timestamp": commonType = typeof(byte[]); break; case "tinyint": commonType = typeof(byte); break; case "uniqueidentifier": commonType = typeof(Guid); break; case "varbinary": commonType = typeof(byte[]); break; case "varchar": commonType = typeof(string); break; case "xml": commonType = typeof(string); break; default: commonType = typeof(object); break; } return commonType; } }}g System;using ;using ;using ;using ;using lient;namespace Helpers{ /// /// MySqlHelper操作类 /// public sealed partial class MySqlHelper { /// /// 批量操作每批次记录数 /// public static int BatchSize = 2000; /// /// 超时时间 /// public static int CommandTimeOut = 600; /// ///初始化MySqlHelper实例 /// /// 数据库连接字符串 public MySqlHelper(string connectionString) { tionString = connectionString; } /// /// 数据库连接字符串 /// public string ConnectionString { get; set; } #region 实例⽅法 #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, commandType, commandText, parms); } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public T ExecuteScalar(string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public object ExecuteScalar(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandType, commandText, parms); } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// SQL语句 /// 查询参数 /// 返回只读数据集 private MySqlDataReader ExecuteDataReader(string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private MySqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(ConnectionString, commandType, commandText, parms); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(string commandText, params MySqlParameter[] parms) { return ExecuteDataRow(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏ public DataRow ExecuteDataRow(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataRow(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parms) { return ExecuteDataTable(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public DataTable ExecuteDataTable(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0]; } #endregion ExecuteDataTable #region ExecuteDataSet /// /// 执⾏SQL语句,返回结果集 /// /// SQL语句 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public DataSet ExecuteDataSet(CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataSet #region 批量操作 /// /// 使⽤MySqlDataAdapter批量更新数据 /// /// 数据表 public void BatchUpdate(DataTable table) { BatchUpdate(ConnectionString, table); } /// ///⼤批量数据插⼊,返回成功插⼊⾏数 /// /// 数据表 /// 返回成功插⼊⾏数 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 ( != ) (); tion = connection; dTimeout = CommandTimeOut; // 设置命令⽂本(存储过程名或SQL语句) dText = commandText; // 分配事务 if (transaction != null) { ction = transaction; } // 设置命令类型. dType = commandType; if (parms != null && > 0) { //预处理MySqlParameter参数数组，将为NULL的参数赋值为; foreach (MySqlParameter parameter in parms) { if ((ion == utput || ion == ) && ( == null)) { = ; } } ge(parms); } } #region ExecuteNonQuery /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(string connectionString, string commandText, params MySqlParameter[] parms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { return ExecuteNonQuery(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 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); } } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteNonQuery(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回影响的⾏数 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回影响的⾏数 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 = eNonQuery(); (); return retval; } #endregion ExecuteNonQuery #region ExecuteScalar /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 返回对象类型 /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static T ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms) { object result = ExecuteScalar(connectionString, commandText, parms); if (result != null) { return (T)Type(result, typeof(T)); ; } return default(T); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { return ExecuteScalar(connection, , commandText, parms); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 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); } } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteScalar(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏第⼀列 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀⾏第⼀列 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 = eScalar(); (); return retval; } #endregion ExecuteScalar #region ExecuteDataReader /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// SQL语句 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(string connectionString, string commandText, params MySqlParameter[] parms) { MySqlConnection connection = new MySqlConnection(connectionString); return ExecuteDataReader(connection, null, , commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 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); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 private static MySqlDataReader ExecuteDataReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataReader(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回只读数据集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回只读数据集 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 eReader(onnection); } #endregion #region ExecuteDataRow /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, , commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms); return > 0 ? [0] : null; } /// /// 执⾏SQL语句,返回结果集中的第⼀⾏ /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// ,返回结果集中的第⼀⾏ public static DataRow ExecuteDataRow(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms); return > 0 ? [0] : null; } #endregion ExecuteDataRow #region ExecuteDataTable /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0]; } /// /// 执⾏SQL语句,返回结果集中的第⼀个数据表 /// /// 数据库连接字符串 /// 数据表名称 /// 返回结果集中的第⼀个数据表 public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName) { return ExecuteDataSet(connectionString, , ("select * from {0} where 1=-1", tableName)).Tables[0]; } #endregion ExecuteDataTable #region ExecuteDataSet /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// SQL语句 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(string connectionString, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connectionString, , commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接字符串 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 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); } } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(connection, null, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 public static DataSet ExecuteDataSet(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms) { return ExecuteDataSet(tion, transaction, commandType, commandText, parms); } /// /// 执⾏SQL语句,返回结果集 /// /// 数据库连接 /// 事务 /// 命令类型(存储过程,命令⽂本, 其它.) /// SQL语句或存储过程名称 /// 查询参数 /// 返回结果集 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(); (ds); if (f("@") > 0) { commandText = r(); int index = f("where "); if (index < 0) { index = f("nwhere"); } if (index > 0) { ("SQL", ing(0, index - 1)); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } } else { ("SQL", commandText); //将获取的语句保存在表的⼀个附属数组⾥，⽅便更新时⽣成CommandBuilder } foreach (DataTable dt in ) { ("SQL", edProperties["SQL"]); } (); return ds; } #endregion ExecuteDataSet #region 批量操作 /// ///使⽤MySqlDataAdapter批量更新数据 /// /// 数据库连接字符串 /// 数据表 public static void BatchUpdate(string connectionString, DataTable table) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand command = Command(); dTimeout = CommandTimeOut; dType = ; MySqlDataAdapter adapter = new MySqlDataAdapter(command); MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter); ctOption = iteChanges; MySqlTransaction transaction = null; try { (); transaction = ransaction(); //设置批量更新的每次处理条数 BatchSize = BatchSize; //设置事物 ction = transaction; if (edProperties["SQL"] != null) { dText = edProperties["SQL"].ToString(); } (table); ();/////提交事务 } catch (MySqlException ex) { if (transaction != null) ck(); throw ex; } finally { (); e(); } } /// ///⼤批量数据插⼊,返回成功插⼊⾏数 /// /// 数据库连接字符串 /// 数据表 /// 返回成功插⼊⾏数 public static int BulkInsert(string connectionString, DataTable table) { if (OrEmpty(ame)) throw new Exception("请给DataTable的TableName属性附上表名称"); if ( == 0) return 0; int insertCount = 0; string tmpPath = pFileName(); string csv = DataTableToCsv(table); llText(tmpPath, csv); using (MySqlConnection conn = new MySqlConnection(connectionString)) { MySqlTransaction tran = null; try { (); tran = ransaction(); MySqlBulkLoader bulk = new MySqlBulkLoader(conn) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "rn", FileName = tmpPath, NumberOfLinesToSkip = 0, TableName = ame, }; ge(().Select(colum => Name).ToList()); insertCount = (); (); } catch (MySqlException ex) { if (tran != null) ck(); throw ex; } } (tmpPath); return insertCount; } /// ///将DataTable转换为标准的CSV /// /// 数据表 /// 返回标准的CSV private static string DataTableToCsv(DataTable table) { //以半⾓逗号（即,）作分隔符，列为空也要表达其存在。 //列内容如存在半⾓逗号（即,）则⽤半⾓引号（即""）将该字段值包含起来。 //列内容如存在半⾓引号（即"）则应替换成半⾓双引号（""）转义，并⽤半⾓引号（即""）将该字段值包含起来。 StringBuilder sb = new StringBuilder(); DataColumn colum; foreach (DataRow row in ) { for (int i = 0; i < ; i++) { colum = s[i]; if (i != 0) (","); if (pe == typeof(string) && row[colum].ToString().Contains(",")) { (""" + row[colum].ToString().Replace(""", """") + """); } else (row[colum].ToString()); } Line(); } return ng(); } #endregion 批量操作 #endregion 静态⽅法 }}g System;using c;using ;using ;using lient;using ;namespace Helpers{ /// /// MySqlHelper扩展(依赖) /// public sealed partial class MySqlHelper { #region 实例⽅法 public T ExecuteObject(string commandText, params MySqlParameter[] parms) { return ExecuteObject(tionString, commandText, parms); } public List ExecuteObjects(string commandText, params MySqlParameter[] parms) { return ExecuteObjects(tionString, commandText, parms); } #endregion #region 静态⽅法 public static T ExecuteObject(string connectionString, string commandText, params MySqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()).FirstOrDefault(); using (MySqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader).FirstOrDefault(); } } public static List ExecuteObjects(string connectionString, string commandText, params MySqlParameter[] parms) { //DataTable dt = ExecuteDataTable(connectionString, commandText, parms); //return cMap>(DataReader()); using (MySqlDataReader reader = ExecuteDataReader(connectionString, commandText, parms)) { return cMap>(reader); } } #endregion }}最后附上⽂件源码及相关DLL: