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