SqlHelper.cs ゝ一纸荒年。 2022-04-11 06:26 203阅读 0赞 using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System; //using System.Transactions; namespace DotNet.SQLServer.DataAccess \{ public class SqlHelper \{ \#region execute ado.net command with transaction /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// public static object ExecuteScalar(string sqlString, string strConnection) \{ object result = null; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; result = cmd.ExecuteScalar(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static object ExecuteScalar(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ object result = null; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; PrepareCommand(cmd, sqlParams); result = cmd.ExecuteScalar(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// public static object ExecuteScalar(string sqlString, string strConnection, IDictionary dictParams) \{ object result = null; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; PrepareCommand(cmd, dictParams); result = cmd.ExecuteScalar(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 对连接执行SQL语句并返回受影响的行数 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// public static int ExecuteNonQuery(string sqlString, string strConnection) \{ int affectNum = -1; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; affectNum = cmd.ExecuteNonQuery(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static int ExecuteNonQuery(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ int affectNum = -1; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; PrepareCommand(cmd, sqlParams); affectNum = cmd.ExecuteNonQuery(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 默认带事务 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// public static int ExecuteNonQuery(string sqlString, string strConnection, IDictionary dictParams) \{ int affectNum = -1; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; PrepareCommand(cmd, dictParams); affectNum = cmd.ExecuteNonQuery(); trans.Commit(); \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} \#endregion \#region execute ado.net command with or without transaction /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// SQL语句 /// 数据库连接字符串 /// 是否启用事务 /// public static object ExecuteScalar(string sqlString, string strConnection, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteScalar(sqlString, strConnection); \} object result = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); result = cmd.ExecuteScalar(); \} \} catch (Exception ex) \{ HandleException(ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// 是否启用事务 /// public static object ExecuteScalar(string sqlString, string strConnection, SqlParameter\[\] sqlParams, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteScalar(sqlString, strConnection, sqlParams); \} object result = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, sqlParams); result = cmd.ExecuteScalar(); \} \} catch (Exception ex) \{ HandleException(ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// 是否启用事务 /// public static object ExecuteScalar(string sqlString, string strConnection, IDictionary dictParams, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteScalar(sqlString, strConnection, dictParams); \} object result = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, dictParams); result = cmd.ExecuteScalar(); \} \} catch (Exception ex) \{ HandleException(ex); \} return result; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// SQL语句 /// 数据库连接字符串 /// 是否启用事务 /// public static int ExecuteNonQuery(string sqlString, string strConnection, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteNonQuery(sqlString, strConnection); \} int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); affectNum = cmd.ExecuteNonQuery(); \} \} catch (Exception ex) \{ HandleException(ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// 是否启用事务 /// public static int ExecuteNonQuery(string sqlString, string strConnection, SqlParameter\[\] sqlParams, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteNonQuery(sqlString, strConnection, sqlParams); \} int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, sqlParams); affectNum = cmd.ExecuteNonQuery(); \} \} catch (Exception ex) \{ HandleException(ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// 是否启用事务 /// public static int ExecuteNonQuery(string sqlString, string strConnection, IDictionary dictParams, bool isUseTransction) \{ if (isUseTransction) \{ return ExecuteNonQuery(sqlString, strConnection, dictParams); \} int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, dictParams); affectNum = cmd.ExecuteNonQuery(); \} \} catch (Exception ex) \{ HandleException(ex); \} return affectNum; \} \#endregion \#region collections /// /// 生成一个DataReader读取器 (不确定读取器对应的连接什么时候关闭 慎用) /// /// SQL语句 /// 数据库连接字符串 /// public static IDataReader ExecuteReader(string sqlString, string strConnection) \{ IDataReader reader = null; SqlConnection conn = new SqlConnection(strConnection); SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); reader = cmd.ExecuteReader(); return reader; \} /// /// 生成一个DataReader读取器 (不确定读取器对应的连接什么时候关闭 慎用) /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static IDataReader ExecuteReader(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ IDataReader reader = null; SqlConnection conn = new SqlConnection(strConnection); SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, sqlParams); reader = cmd.ExecuteReader(); return reader; \} /// /// 生成一个DataReader读取器 (不确定读取器对应的连接什么时候关闭 慎用) /// /// SQL语句 /// 数据库连接字符串 /// /// public static IDataReader ExecuteReader(string sqlString, string strConnection, IDictionary dictParams) \{ IDataReader reader = null; SqlConnection conn = new SqlConnection(strConnection); SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); PrepareCommand(cmd, dictParams); reader = cmd.ExecuteReader(); return reader; \} /// /// 获取一个DataSet数据集 /// /// SQL语句 /// 数据库连接字符串 /// public static DataSet QueryForDataSet(string sqlString, string strConnection) \{ DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); \} return ds; \} /// /// 获取一个DataSet数据集 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static DataSet QueryForDataSet(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ if (sqlParams == null || sqlParams.Length == 0) \{ return QueryForDataSet(sqlString, strConnection); \} DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); PrepareCommand(cmd, sqlParams); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); \} return ds; \} /// /// 获取一个DataSet数据集 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// public static DataSet QueryForDataSet(string sqlString, string strConnection, IDictionary dictParams) \{ if (dictParams == null || dictParams.Count == 0) \{ return QueryForDataSet(sqlString, strConnection); \} DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(strConnection)) \{ SqlCommand cmd = new SqlCommand(sqlString, conn); PrepareCommand(cmd, Config.commandTimeout); PrepareCommand(cmd, dictParams); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); \} return ds; \} /// /// 获取一个DataTable对象 /// /// SQL语句 /// 数据库连接字符串 /// public static DataTable QueryForDataTable(string sqlString, string strConnection) \{ DataTable dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection); if (ds != null && ds.Tables.Count > 0) \{ dt = ds.Tables\[0\]; \} return dt; \} /// /// 获取一个DataTable对象 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static DataTable QueryForDataTable(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ if (sqlParams == null || sqlParams.Length == 0) \{ return QueryForDataTable(sqlString, strConnection); \} DataTable dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection, sqlParams); if (ds != null && ds.Tables.Count > 0) \{ dt = ds.Tables\[0\]; \} return dt; \} /// /// 获取一个DataTable对象 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// public static DataTable QueryForDataTable(string sqlString, string strConnection, IDictionary dictParams) \{ if (dictParams == null || dictParams.Count == 0) \{ return QueryForDataTable(sqlString, strConnection); \} DataTable dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection, dictParams); if (ds != null && ds.Tables.Count > 0) \{ dt = ds.Tables\[0\]; \} return dt; \} /// /// 获取DataTable对象集合 /// /// SQL语句 /// 数据库连接字符串 /// public static DataTable\[\] QueryForDataTables(string sqlString, string strConnection) \{ DataTable\[\] dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection); if (ds != null && ds.Tables.Count > 0) \{ dt = new DataTable\[ds.Tables.Count\]; for (int i = 0; i /// 获取DataTable对象集合 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数数组 /// public static DataTable\[\] QueryForDataTables(string sqlString, string strConnection, SqlParameter\[\] sqlParams) \{ if (sqlParams == null || sqlParams.Length == 0) \{ return QueryForDataTables(sqlString, strConnection); \} DataTable\[\] dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection, sqlParams); if (ds != null && ds.Tables.Count > 0) \{ dt = new DataTable\[ds.Tables.Count\]; for (int i = 0; i /// 获取DataTable对象集合 /// /// SQL语句 /// 数据库连接字符串 /// SQL参数字典 /// public static DataTable\[\] QueryForDataTables(string sqlString, string strConnection, IDictionary dictParams) \{ if (dictParams == null || dictParams.Count == 0) \{ return QueryForDataTables(sqlString, strConnection); \} DataTable\[\] dt = null; DataSet ds = QueryForDataSet(sqlString, strConnection, dictParams); if (ds != null && ds.Tables.Count > 0) \{ dt = new DataTable\[ds.Tables.Count\]; for (int i = 0; i /// 准备SqlParameter参数 /// /// 参数名数组 /// 参数值数组 /// public static SqlParameter\[\] PrepareParameters(string\[\] paraNames, object\[\] paraValues) \{ if (paraNames == null) \{ return null; \} SqlParameter\[\] sqlParas = new SqlParameter\[paraNames.Length\]; for (int i = 0; i /// 准备SqlParameter参数 /// /// 参数名 /// 参数值 /// public static SqlParameter PrepareParameter(string paraName, object paraValue) \{ SqlParameter para = new SqlParameter(paraName, paraValue); return para; \} /// /// 准备SqlParameter参数 /// /// 参数字典 不可以为空 /// public static SqlParameter\[\] PrepareParameters(IDictionary dictParams) \{ if (dictParams == null) \{ return null; \} SqlParameter\[\] sqlParas = new SqlParameter\[dictParams.Count\]; int counter = 0; foreach (KeyValuePair kv in dictParams) \{ sqlParas\[counter\] = new SqlParameter(kv.Key, kv.Value); counter++; \} return sqlParas; \} /// /// 为DbCommand对象设置参数 /// /// SqlCommand对象 /// SqlParameter对象 可以为null public static void PrepareCommand(SqlCommand cmd, SqlParameter parameter) \{ if (parameter != null) \{ cmd.Parameters.Add(parameter); \} \} /// /// 为DbCommand对象设置参数 /// /// SqlCommand对象 /// SqlParameter数组 可以为null public static void PrepareCommand(SqlCommand cmd, SqlParameter\[\] sqlParams) \{ if (sqlParams != null && sqlParams.Length > 0) \{ cmd.Parameters.AddRange(sqlParams); \} \} /// /// 为DbCommand对象设置参数 /// /// SqlCommand对象 /// 参数字典 可以为null public static void PrepareCommand(SqlCommand cmd, IDictionary dictParams) \{ if (dictParams == null || dictParams.Count == 0) \{ return; \} foreach (KeyValuePair kv in dictParams) \{ SqlParameter param = new SqlParameter(kv.Key, kv.Value); cmd.Parameters.Add(param); \} \} /// /// 为DbCommand对象设置参数 /// /// SqlCommand对象 /// /// 参数值数组 public static void PrepareCommand(SqlCommand cmd, string\[\] paraNames, object\[\] paraValues) \{ SqlParameter\[\] sqlParas = PrepareParameters(paraNames, paraValues); if (sqlParas == null) \{ return; \} cmd.Parameters.AddRange(sqlParas); \} /// /// 为DbCommand对象设置参数 /// /// SqlCommand对象 /// 参数名 /// 参数值 public static void PrepareCommand(SqlCommand cmd, string paraName, object paraValue) \{ SqlParameter sqlPara = PrepareParameter(paraName, paraValue); cmd.Parameters.Add(sqlPara); \} /// /// 设置在终止执行命令的尝试并生成错误之前的等待时间 /// /// SqlCommand对象 /// 在终止执行命令的尝试并生成错误之前的等待时间，通常写在配置文件中 public static void PrepareCommand(SqlCommand cmd, int commandTimeout) \{ cmd.CommandTimeout = commandTimeout; \} \#endregion \#region execute store procedure /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// 存储过程名 /// 数据库连接字符串 /// 是否启用事务 /// public static object SPExecuteScalar(string spName, string strConnection, bool isUseTransction) \{ object result = null; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; PrepareCommand(cmd, Config.commandTimeout); if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} result = cmd.ExecuteScalar(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// 存储过程名 /// 数据库连接字符串 /// SQL参数数组 /// 是否启用事务 /// public static object SPExecuteScalar(string spName, string strConnection, SqlParameter\[\] sqlParams, bool isUseTransction) \{ object result = null; IDbTransaction trans = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; PrepareCommand(cmd, Config.commandTimeout); if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} PrepareCommand(cmd, sqlParams); result = cmd.ExecuteScalar(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// /// 存储过程名 /// 数据库连接字符串 /// SQL参数字典 /// 是否启用事务 /// public static object SPExecuteScalar(string spName, string strConnection, IDictionary dictParams, bool isUseTransction) \{ IDbTransaction trans = null; object result = null; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} PrepareCommand(cmd, Config.commandTimeout); PrepareCommand(cmd, dictParams); result = cmd.ExecuteScalar(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return result; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// 存储过程名 /// 数据库连接字符串 /// 是否启用事务 /// public static int SPExecuteNonQuery(string spName, string strConnection, bool isUseTransction) \{ IDbTransaction trans = null; int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} PrepareCommand(cmd, Config.commandTimeout); affectNum = cmd.ExecuteNonQuery(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// 存储过程名 /// 数据库连接字符串 /// SQL参数数组 /// 是否启用事务 /// public static int SPExecuteNonQuery(string spName, string strConnection, SqlParameter\[\] sqlParams, bool isUseTransction) \{ IDbTransaction trans = null; int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} PrepareCommand(cmd, Config.commandTimeout); PrepareCommand(cmd, sqlParams); affectNum = cmd.ExecuteNonQuery(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} /// /// 对连接执行SQL语句并返回受影响的行数 /// /// 存储过程名 /// 数据库连接字符串 /// SQL参数字典 /// 是否启用事务 /// public static int SPExecuteNonQuery(string spName, string strConnection, IDictionary dictParams, bool isUseTransction) \{ IDbTransaction trans = null; int affectNum = -1; try \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); SqlCommand cmd = new SqlCommand(spName, conn); cmd.CommandType = CommandType.StoredProcedure; if (isUseTransction) \{ trans = conn.BeginTransaction(); cmd.Transaction = trans as SqlTransaction; \} PrepareCommand(cmd, Config.commandTimeout); PrepareCommand(cmd, dictParams); affectNum = cmd.ExecuteNonQuery(); if (isUseTransction) \{ trans.Commit(); \} \} \} catch (Exception ex) \{ HandleException(trans, ex); \} return affectNum; \} \#endregion \#region batch insert /// /// 执行批量插入 /// /// sql连接字符串 /// 表名称 /// 组装好的要批量导入的datatable /// public static bool BatchInsert(string strConnection, string tableName, DataTable dt) \{ bool flag = false; try \{ //using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); using (SqlBulkCopy sbc = new SqlBulkCopy(conn)) \{ //服务器上目标表的名称 sbc.DestinationTableName = tableName; sbc.BatchSize = 500000;//默认一次导入500000条记录 sbc.BulkCopyTimeout = 300; for (int i = 0; i /// 执行批量插入 /// /// sql连接字符串 /// 表名称 /// 一次导入记录数 /// 超时之前操作完成所允许的秒数 /// 组装好的要批量导入的datatable /// public static bool BatchInsert(string strConnection, string tableName, int batchSize, int timeout, DataTable dt) \{ bool flag = false; try \{ //using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); using (SqlBulkCopy sbc = new SqlBulkCopy(conn)) \{ //服务器上目标表的名称 sbc.DestinationTableName = tableName; sbc.BatchSize = batchSize;//默认一次导入num条记录 sbc.BulkCopyTimeout = timeout; for (int i = 0; i /// 执行批量插入 /// /// sql连接字符串 /// 表名称 /// 一次导入记录数 /// 超时之前操作完成所允许的秒数 /// datareader，必须保证是没有关闭的读取器 /// public static bool BatchInsert(string strConnection, string tableName, int batchSize, int timeout, IDataReader rdr) \{ bool flag = false; try \{ //using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) \{ using (SqlConnection conn = new SqlConnection(strConnection)) \{ conn.Open(); using (SqlBulkCopy sbc = new SqlBulkCopy(conn)) \{ //服务器上目标表的名称 sbc.DestinationTableName = tableName; sbc.BatchSize = batchSize;//默认一次导入num条记录 sbc.BulkCopyTimeout = timeout; for (int i = 0; i /// 异常处理 记录日志或者直接抛出 /// /// private static void HandleException(Exception ex) \{ throw ex; \} /// /// 异常处理 记录日志或者直接抛出 /// /// 事务 /// private static void HandleException(IDbTransaction trans, Exception ex) \{ if (trans != null) \{ trans.Rollback(); \} throw ex; \} /// /// 异常处理 记录日志或者直接抛出 /// /// 出现异常的sql语句 /// 数据库连接字符串 /// private static void HandleException(string sqlString, string sqlConn, Exception ex) \{ Exception innerEx = new Exception(string.Format(" 数据库连接字符串：\{0\}，执行SQL语句：\{1\} 时发生异常，异常信息：\{2\}", sqlConn, sqlString, ex.Message), ex); throw innerEx; \} /// /// 异常处理 记录日志或者直接抛出 /// /// 出现异常的sql语句 /// 数据库连接字符串 /// 事务 /// private static void HandleException(string sqlString, string sqlConn, IDbTransaction trans, Exception ex) \{ if (trans != null) \{ trans.Rollback(); \} Exception innerEx = new Exception(string.Format(" 数据库连接字符串：\{0\}，执行SQL语句：\{1\} 时发生异常，异常信息：\{2\}", sqlConn, sqlString, ex.Message), ex); throw innerEx; \} \#endregion \} \}
还没有评论，来说两句吧...