C# sql Helper

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Threading;
namespace Common
{
    public enum AutoRollback
    {
        /// <summary>
        /// 手动回滚
        /// </summary>
        None,

        /// <summary>
        /// 除查询语句以外回滚
        /// </summary>
        ExceptQuery,

        /// <summary>
        /// 任何情况下回滚
        /// </summary>
        Always,
    }

    public class SqlDbContext : IDisposable
    {
        public AutoRollback AutoRollback { get; private set; }
        private string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public string ConnectionString { get { return connectionString; } }

        public SqlConnection Connection { get; private set; }
        public SqlTransaction Transaction { get; private set; }

        public SqlDbContext(AutoRollback auto = AutoRollback.ExceptQuery) :
            this(ConfigurationManager.ConnectionStrings["conn"].ConnectionString, auto)
        {
        }

        public SqlDbContext(string connectionString, AutoRollback auto)
        {
            this.Connection = new SqlConnection(connectionString);
            this.AutoRollback = auto;
        }

        public void Dispose()
        {
            this.EndTrans();
            this.Close();

            if (this.Connection != null)
                this.Connection.Dispose();

            this.Connection = null;
            this.Transaction = null;
        }

        #region Transaction
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTrans()
        {
            if (this.Transaction != null)
                this.Transaction.Dispose();
            this.Open();
            this.Transaction = this.Connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTrans()
        {
            if (this.Transaction != null)
                this.Transaction.Commit();
        }

        /// <summary>
        /// 回滚
        /// </summary>
        public void RollbackTrans()
        {
            if (this.Transaction != null)
                this.Transaction.Rollback();
        }

        /// <summary>
        /// 结束事务，释放资源
        /// </summary>
        public void EndTrans()
        {
            if (this.Transaction != null)
                this.Transaction.Dispose();
            this.Transaction = null;
        }
        #endregion

        #region Exec Command

        #region 执行sql脚本块
        /// <summary>
        /// 执行Sql脚本块
        /// </summary>
        /// <param name="dbType">0为access,1为sqlserver</param>
        /// <param name="connectionString">数据库连接</param>
        /// <param name="pathToScriptFile">脚本路径，物理路径</param>
        /// <returns></returns>
        public bool Go(string strSql, CommandType commandType = CommandType.Text)
        {


            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = strSql,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    foreach (string Sql in SqlList(strSql))
                    {
                        cmd.CommandText = Sql;
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                if (this.AutoRollback != AutoRollback.None)
                    this.RollbackTrans();
                throw;
            }
        }
        private static string[] SqlList(string StrSql)
        {
            string[] _strList = StrSql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
            return _strList;
        }
        #endregion
        /// <summary>
        /// 执行SQL语句， 此方法用于插入、更新操作
        /// 返回受影响的行数
        /// </summary>
        /// <param name="text">SQL执行语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>返回受影响的行数</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public int Execute(
            string text,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = text,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    return cmd.ExecuteNonQuery();
                }

            }
            catch
            {
                if (this.AutoRollback != AutoRollback.None)
                    this.RollbackTrans();
                throw;
            }
        }


        /// <summary>
        /// SqlBulkCopy 大批量数据插入
        /// </summary>
        /// <param name="table">内存表 Datatable</param>
        /// <param name="destinationTableName">服务器上表的名称</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public void ExecuteBulkCopy(DataTable table,
            string destinationTableName
        )
        {
            this.BeginTrans();
            try
            {
                using (SqlBulkCopy copy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, this.Transaction))
                {
                    copy.DestinationTableName = destinationTableName;
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        copy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
                    }
                    copy.WriteToServer(table);
                    this.CommitTrans();
                }
            }
            catch
            {
                if (this.AutoRollback != AutoRollback.None)
                    this.RollbackTrans();
                throw;
            }
        }


        /// <summary>
        /// 执行SQL语句，并返回查询结果的第一行第一列的值要返回什么样的值，就T 里面写入什么类型
        /// </summary>
        /// <typeparam name="T">返回结果类型</typeparam>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>返回查询结果的第一行第一列的值</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public T ExecuteScalar<T>(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    object obj = cmd.ExecuteScalar();
                    if (obj == null || obj == DBNull.Value)
                        return default(T);
                    return (T)obj;
                }
            }
            catch
            {
                if (this.AutoRollback != AutoRollback.None)
                    this.RollbackTrans();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句，并返回查询结果的第一行第一列的值
        /// </summary>
        /// <typeparam name="T">返回结果类型</typeparam>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>返回查询结果的第一行第一列的值</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public int ExecuteScalar(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    object obj = cmd.ExecuteScalar();
                    return Convert.ToInt32(obj);
                }
            }
            catch
            {
                if (this.AutoRollback != AutoRollback.None)
                    this.RollbackTrans();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句，并返回查询结果的实体类集合
        /// 实体类的属性需包含查询结果的表头
        /// </summary>
        /// <typeparam name="T">查询结果的实体类</typeparam>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>返回结果的实体类集合</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public List<T> Query<T>(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
            where T : new()
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<T> result = new List<T>();
                        var columns = GetColumns<T>(reader);
                        while (reader.Read())
                        {
                            T obj = CreateObject<T>(reader, columns);
                            result.Add(obj);
                        }
                        return result;
                    }
                }
            }
            catch
            {
                if (AutoRollback == AutoRollback.Always)
                    this.RollbackTrans();
                throw;
            }
        }


        /// <summary>
        /// 执行SQL语句，返回 SqlDataReader
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>SqlDataReader</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public SqlDataReader GetDataReader(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    return cmd.ExecuteReader();
                }
            }
            catch
            {
                if (AutoRollback == AutoRollback.Always)
                    this.RollbackTrans();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句，以DataTable对象作为结果返回查询结果
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>DataTable对象</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public DataTable QueryDT(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
        {
            this.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);
                    DataTable result = new DataTable();
                    using (SqlDataAdapter ad = new SqlDataAdapter())
                    {
                        ad.SelectCommand = cmd;
                        ad.Fill(result);
                        return result;
                    }
                }
            }
            catch
            {
                if (AutoRollback == AutoRollback.Always)
                    this.RollbackTrans();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句，并返回查询结果的第一个对象, 如果没有查询结果则为NULL
        /// 实体类的属性需包含查询结果的表头
        /// </summary>
        /// <typeparam name="T">查询结果的实体类</typeparam>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">语句类型</param>
        /// <param name="args">语句参数</param>
        /// <returns>查询结果的第一个对象，如果没有查询结果则为NULL</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public T FirstOrDefault<T>(
            string query,
            CommandType commandType = CommandType.Text,
            params SqlParameter[] args)
            where T : new()
        {
            this.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    CommandText = query,
                    CommandType = commandType,
                    Connection = this.Connection,
                })
                {
                    if (this.Transaction != null)
                        cmd.Transaction = this.Transaction;

                    AddParameterToCommand(cmd, args);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            var columns = GetColumns<T>(reader);
                            reader.Read();
                            return CreateObject<T>(reader, columns);
                        }

                        return default(T);
                    }
                }
            }
            catch
            {
                if (AutoRollback == AutoRollback.Always)
                    this.RollbackTrans();
                throw;
            }
        }
        #endregion

        #region HelperMethods
        public void Open()
        {
            if (this.Connection != null &&
                this.Connection.State != ConnectionState.Open)
                this.Connection.Open();
        }

        public void Close()
        {
            if (this.Connection != null)
                this.Connection.Close();
        }

        public SqlCommand CreateCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.Connection;
            if (this.Transaction != null)
                cmd.Transaction = this.Transaction;

            return cmd;
        }

        public static void AddParameterToCommand(SqlCommand cmd, SqlParameter[] args)
        {
            if (args != null && args.Length > 0)
            {
                foreach (var arg in args)
                {
                    if (arg != null)
                    {
                        if (arg.IsNullable && arg.Value == null)
                        {
                            arg.Value = DBNull.Value;
                        }

                        cmd.Parameters.Add(arg);
                    }
                }
            }
        }

        private static PropertyInfo[] GetColumns<T>(SqlDataReader reader)
        {
            List<T> result = new List<T>();
            Type type = typeof(T);
            var columns = new List<PropertyInfo>(reader.FieldCount);
            var props = type.GetProperties();
            string name;

            for (int i = 0; i < reader.FieldCount; i++)
            {
                name = reader.GetName(i);
                for (int j = 0; j < props.Length; j++)
                {
                    if (props[j].Name.ToLower() == name.ToLower())
                    {
                        columns.Add(props[j]);
                        break;
                    }
                }
            }

            return columns.ToArray();
        }

        private static T CreateObject<T>(SqlDataReader reader, PropertyInfo[] columns) where T : new()
        {
            T result = Activator.CreateInstance<T>();
            for (int i = 0; i < columns.Length; i++)
            {
                columns[i].SetValue(result, reader[columns[i].Name] == DBNull.Value ? null : reader[columns[i].Name], null);
            }

            return result;
        }
        #endregion

    }
}