DbHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Asmkt.Database
{
    /// <summary>
    /// SQL数据库帮助
    /// </summary>
    public sealed class SqlDatabaseHelper
    {
        #region 属性
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        { get; set; }
        #endregion 属性

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="configurationName">配置名称</param>
        public SqlDatabaseHelper(string configurationName = null)
        {
            ConnectionStringSettings config = ConfigurationManager.ConnectionStrings[0];
            if (!string.IsNullOrWhiteSpace(configurationName))
                config = ConfigurationManager.ConnectionStrings[configurationName];
            ConnectionString = config.ConnectionString;
        }
        #endregion 构造函数

        #region 静态成员方法
        /// <summary>
        /// 通过指定的数据库连接获得数据库处理事务
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="transactionName">事务名称</param>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <returns>数据库处理事务</returns>
        public static SqlTransaction GetTransaction(
            SqlConnection conn,
            string transactionName=null,
            IsolationLevel isolationLevel= IsolationLevel.Chaos)
        {
            if (conn == null)
                return null;
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();
            if (string.IsNullOrWhiteSpace(transactionName))
                return conn.BeginTransaction();
            else
                return conn.BeginTransaction(isolationLevel, transactionName);
        }
        /// <summary>
        /// 对连接执行Trans-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="conn">指定的数据库连接</param>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(
            SqlConnection conn,
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            int rslt = -1;
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = connectionTimeout;
                cmd.Parameters.Clear();
                if (parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                rslt = cmd.ExecuteNonQuery(); 
            }
            return rslt;

        }
        /// <summary>
        /// 执行查询，并返回查询返回结果集中的第一行的第一列，并忽略其他行或行
        /// </summary>
        /// <param name="conn">指定的数据库连接</param>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>查询结果集中的第一行的第一列</returns>
        public static object ExcuteScalar(
            SqlConnection conn,
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            object rslt = null;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = connectionTimeout;
                if (parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                rslt = cmd.ExecuteScalar();
            }
            return rslt;
        }
        /// <summary>
        /// 执行查询，并将单一结果集存储在DataTable中并返回
        /// </summary>
        /// <param name="conn">指定的数据库连接</param>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>查询结果集</returns>
        public static DataTable ExecuteDataTable(
            SqlConnection conn,
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            DataTable rslt = null;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = connectionTimeout;
                if (parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                using(SqlDataReader reader = cmd.ExecuteReader())
                {
                    rslt = new DataTable();
                    rslt.Load(reader);
                }
            }
            return rslt;
        }
        /// <summary>
        /// 执行查询，并将结果集填充到指定的表中
        /// </summary>
        /// <param name="conn">指定的数据库连接</param>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="datatable">要填充的表</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="loadOption">指示已存在于datatable的行如何与共享主键的传入行合并</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        public static void FillDataTable(
            SqlConnection conn,
            string sqlText,
            ref DataTable datatable,
            CommandType cmdType = CommandType.Text,
            LoadOption loadOption = LoadOption.PreserveChanges,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = connectionTimeout;
                if (parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                    datatable.Load(reader, loadOption);
            }
        }

        /// <summary>
        /// 将指定表中的数据存储到数据库中
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="dataTable">等存储的数据</param>
        /// <param name="destinationTableName">存储的目标表</param>
        /// <param name="batchSize">每一批次的行数，在每一批次结束时，将该批次的行发送到服务器</param>
        /// <param name="ConnectionTimeout">超时之前操作完成所允许的秒数</param>
        /// <param name="rowState">只在匹配状态的行才会复制到目标表中</param>
        /// <param name="columnMapping">列映射关系</param>
        public static void SaveData(
            SqlConnection conn,
            ref DataTable dataTable,
            string destinationTableName = null,
            int batchSize = 500,
            int ConnectionTimeout = 30,
            DataRowState rowState = DataRowState.Added,
            params SqlBulkCopyColumnMapping[] columnMapping)
        {
            using(SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))
            {
                sqlBulkCopy.BulkCopyTimeout = ConnectionTimeout;
                sqlBulkCopy.BatchSize = batchSize;
                if (string.IsNullOrWhiteSpace(destinationTableName))
                    sqlBulkCopy.DestinationTableName = dataTable.TableName;
                else
                    sqlBulkCopy.DestinationTableName = destinationTableName;
                foreach (SqlBulkCopyColumnMapping item in columnMapping)
                    sqlBulkCopy.ColumnMappings.Add(item);
                sqlBulkCopy.WriteToServer(dataTable,rowState);
            }
        }
        #endregion 表态成员方法

        #region 成员方法
        /// <summary>
        /// 获得数据库连接
        /// </summary>
        /// <returns>返回数据库连接</returns>
        public SqlConnection GetConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 对连接执行Trans-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = GetConnection())
                return ExecuteNonQuery(conn, sqlText, cmdType, connectionTimeout, parameters);
        }

        /// <summary>
        /// 执行查询，并返回查询返回结果集中的第一行的第一列，并忽略其他行或行
        /// </summary>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>查询结果集中的第一行的第一列</returns>
        public object ExcuteScalar(
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = GetConnection())
                return ExcuteScalar(conn, sqlText, cmdType, connectionTimeout, parameters);
        }
        /// <summary>
        /// 执行查询，并将单一结果集存储在DataTable中并返回
        /// </summary>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        /// <returns>查询结果集</returns>
        public DataTable ExecuteDataTable(
            string sqlText,
            CommandType cmdType = CommandType.Text,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = GetConnection())
                return ExecuteDataTable(conn, sqlText, cmdType, connectionTimeout, parameters);
        }
        /// <summary>
        /// 执行查询，并将结果集填充到指定的表中
        /// </summary>
        /// <param name="sqlText">执行的语句、存储过程或表名</param>
        /// <param name="datatable">要填充的表</param>
        /// <param name="cmdType">如何解释sqlText的属性</param>
        /// <param name="loadOption">指示已存在于datatable的行如何与共享主键的传入行合并</param>
        /// <param name="connectionTimeout">终止执行命令的尝试生成错误之前等待的时间</param>
        /// <param name="parameters">使用的参数集合</param>
        public void FillDataTable(
            string sqlText,
            ref DataTable datatable,
            CommandType cmdType = CommandType.Text,
            LoadOption loadOption = LoadOption.PreserveChanges,
            int connectionTimeout = 30,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = GetConnection())
                FillDataTable(conn, sqlText, ref datatable, cmdType, loadOption, connectionTimeout, parameters);
        }

        /// <summary>
        /// 将指定表中的数据存储到数据库中
        /// </summary>
        /// <param name="dataTable">等存储的数据</param>
        /// <param name="destinationTableName">存储的目标表</param>
        /// <param name="batchSize">每一批次的行数，在每一批次结束时，将该批次的行发送到服务器</param>
        /// <param name="ConnectionTimeout">超时之前操作完成所允许的秒数</param>
        /// <param name="rowState">只在匹配状态的行才会复制到目标表中</param>
        /// <param name="columnMapping">列映射关系</param>
        public void SaveData(
            ref DataTable dataTable,
            string destinationTableName = null,
            int batchSize = 500,
            int ConnectionTimeout = 30,
            DataRowState rowState = DataRowState.Added,
            params SqlBulkCopyColumnMapping[] columnMapping)
        {
            using (SqlConnection conn = GetConnection())
                SaveData(conn, ref dataTable,destinationTableName, batchSize, ConnectionTimeout, rowState, columnMapping);
        }
        #endregion 成员方法
    }
}