SqlHelper.cs

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
}
}