- 浏览: 17729 次
- 性别:
- 来自: 成都
文章分类
最新评论
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
}
}
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
}
}
相关推荐
在IT行业中,数据库操作是核心任务之一,而`sqlHelper.cs`和`systemError.cs`是两个关键的C#源代码文件,它们在.NET开发中扮演着重要角色。`sqlHelper.cs`通常是一个自定义的类库,用于封装SQL查询和数据库交互,以...
### SqlHelper.cs 修改及使用详解 #### 一、概述 `SqlHelper.cs`是一个与数据库交互的通用类,它封装了一系列用于操作数据库的方法,旨在简化应用程序中的数据库操作流程。通过这个类,开发者可以轻松实现数据库...
SQLHELPER.CS类英文文档
SqlHelper.cs 微软官方版本 ===========================================================================
SqlHelper.cs 微软的SQLHelper类 微软的SQLHelper类(含完整中文注释) 非常好的SQLHelper
微软C# SqlHelper.cs 源码
sqlhelper.cs
具体使用不用实例化,直接引用: SqlDataReader mydr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
《SqlHelper.cs——数据库操作的高效助手》 在.NET开发中,数据库操作是不可或缺的一部分,尤其是在企业级应用中。为了简化数据库访问,提高代码的可读性和可维护性,开发者通常会封装一些辅助类,SqlHelper.cs就是...
《Asp.Net数据库帮助类SQLHelper.cs详解》 在Asp.Net开发中,数据库操作是不可或缺的一部分。为了简化代码,提高开发效率,开发者通常会创建一个数据库帮助类,如SQLHelper.cs,它封装了常见的数据库操作,使得在...
根据提供的文件信息,我们可以深入解析并提取出有关`SqlHelper.cs`的重要知识点: ### SqlHelper.cs概述 `SqlHelper.cs`是一个封装了对SQL Server数据库进行CRUD操作的类库,它通过提供一系列静态方法来简化数据库...
标题中的"SqlHelper.cs工厂类专用"表明这是一个关于SQL数据库操作的C#代码实现,具体是使用工厂模式设计的一个辅助类。在.NET开发中,SqlHelper通常被用来封装数据库访问逻辑,提供便捷、高效的SQL执行服务。这个...
我自己写的数据库操作类SQLHelper.cs 又需要可以下载因为之前我也在找微软的SQLHelper发现官方的太罗嗦了,希望对大家有用
[C#] DataSet ds = SqlHelper.ExecuteDataset( connectionString, CommandType.StoredProcedure, "getProductsByCategory", new SqlParameter("@CategoryID", categoryID)); 注意: Application Block for .NET...
《SQLHelper.CS.doc》是关于.NET框架中的一个数据访问工具类——SqlHelper的文档,主要涉及了如何高效、可扩展地使用SqlClient进行数据库操作。SqlHelper类封装了多种数据库操作的最佳实践,旨在简化数据库访问并...