`

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

分享到:
评论

相关推荐

    sqlHelper.cs和systemError.cs

    在IT行业中,数据库操作是核心任务之一,而`sqlHelper.cs`和`systemError.cs`是两个关键的C#源代码文件,它们在.NET开发中扮演着重要角色。`sqlHelper.cs`通常是一个自定义的类库,用于封装SQL查询和数据库交互,以...

    SqlHelper.cs修改及使用

    ### SqlHelper.cs 修改及使用详解 #### 一、概述 `SqlHelper.cs`是一个与数据库交互的通用类,它封装了一系列用于操作数据库的方法,旨在简化应用程序中的数据库操作流程。通过这个类,开发者可以轻松实现数据库...

    SQLHELPER.CS类英文文档

    SQLHELPER.CS类英文文档

    SqlHelper.cs(微软官方)

    SqlHelper.cs 微软官方版本 ===========================================================================

    SqlHelper.cs 微软的SQLHelper类(含完整中文注释)

    SqlHelper.cs 微软的SQLHelper类 微软的SQLHelper类(含完整中文注释) 非常好的SQLHelper

    微软C# SqlHelper.cs 源码

    微软C# SqlHelper.cs 源码

    微软SQLHelper.cs类 中文版

    微软SQLHelper.cs类 中文版 sql数据库工具类

    sqlhelper.cs

    sqlhelper.cs

    c# 数据库操作类SqlHelper.cs

    具体使用不用实例化,直接引用: SqlDataReader mydr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);

    SqlHelper.cs网上的借用

    《SqlHelper.cs——数据库操作的高效助手》 在.NET开发中,数据库操作是不可或缺的一部分,尤其是在企业级应用中。为了简化数据库访问,提高代码的可读性和可维护性,开发者通常会封装一些辅助类,SqlHelper.cs就是...

    Asp.Net数据库帮助类 SQLHelper.cs

    《Asp.Net数据库帮助类SQLHelper.cs详解》 在Asp.Net开发中,数据库操作是不可或缺的一部分。为了简化代码,提高开发效率,开发者通常会创建一个数据库帮助类,如SQLHelper.cs,它封装了常见的数据库操作,使得在...

    官网SqlHelper.cs

    根据提供的文件信息,我们可以深入解析并提取出有关`SqlHelper.cs`的重要知识点: ### SqlHelper.cs概述 `SqlHelper.cs`是一个封装了对SQL Server数据库进行CRUD操作的类库,它通过提供一系列静态方法来简化数据库...

    SqlHelper.cs工厂类专用

    标题中的"SqlHelper.cs工厂类专用"表明这是一个关于SQL数据库操作的C#代码实现,具体是使用工厂模式设计的一个辅助类。在.NET开发中,SqlHelper通常被用来封装数据库访问逻辑,提供便捷、高效的SQL执行服务。这个...

    SQLHelper.CS

    我自己写的数据库操作类SQLHelper.cs 又需要可以下载因为之前我也在找微软的SQLHelper发现官方的太罗嗦了,希望对大家有用

    SQLHelper.cs

    [C#] DataSet ds = SqlHelper.ExecuteDataset( connectionString, CommandType.StoredProcedure, "getProductsByCategory", new SqlParameter("@CategoryID", categoryID)); 注意: Application Block for .NET...

Global site tag (gtag.js) - Google Analytics