asp.net my sqlHelper

  private static readonly string connString;
        static SqlHelper()
        {
            var conn = ConfigurationManager.ConnectionStrings["connStr"];
            if (conn != null)
                connString = conn.ConnectionString;
        }

        #region MyRegion 查询方法 + GetTable(string sql, CommandType type, params SqlParameter[] pars)
        /// <summary>
        /// 查询方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">类型：存储过程 OR 字符串?</param>
        /// <param name="pars">参数列表</param>
        /// <returns></returns>
        public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlDataAdapter apter = new SqlDataAdapter(sql, conn))
                {
                    apter.SelectCommand.CommandType = type;
                    if (pars != null)
                    {
                        apter.SelectCommand.Parameters.AddRange(pars);
                    }
                    DataTable da = new DataTable();
                    apter.Fill(da);
                    return da;
                }
            }
        }

        #endregion

        #region 查询方法，默认命令类型：字符串 +static DataTable GetTable(string sql, params SqlParameter[] pars)
        /// <summary>
        /// 查询方法，默认类型：字符串
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns></returns>
        public static DataTable GetTable(string sql, params SqlParameter[] pars)
        {
            return GetTable(sql, CommandType.Text, pars);
        }
        #endregion

        #region 查询方法 + static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] pars)
        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象
        /// </summary>
        /// <param name="sql">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="pars">参数列表</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pars != null)
                    {
                        cmd.Parameters.AddRange(pars);
                    }
                    cmd.CommandType = type;
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    return reader;

                }
            }
        }
        #endregion


        #region  查询方法 默认命令类型：字符串 +  static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) 
        /// <summary>
        /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象
        /// </summary>
        /// <param name="sql">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, parameters);
        }
        #endregion

        #region 修改、添加、删除方法 +ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars)
        /// <summary>
        /// 修改、添加、删除方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (pars != null)
                        cmd.Parameters.AddRange(pars);

                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    return cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion

        #region 修改、添加、删除方法，默认字符串 + static int ExecuteNonquery(string sql, params SqlParameter[] pars)
        /// <summary>
        /// 修改、添加、删除方法，默认字符串
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static int ExecuteNonquery(string sql, params SqlParameter[] pars)
        {
            return ExecuteNonquery(sql, CommandType.Text, pars);
        }
        #endregion

        #region ExecuteScalar +static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)
        /// <summary>
        /// 执行一个查询的T-SQL语句，返回第一行第一列的结果
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>返回第一行第一列的数据</returns>
        public static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    if (parameters != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.CommandType = type;
                    try
                    {
                        conn.Open();
                        object res = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return res;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteScalar +static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
        /// <summary>
        /// 执行一个查询的T-SQL语句，返回第一行第一列的结果
        /// </summary>
        /// <param name="cmdText">要执行的T-SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <exception cref="链接数据库异常"></exception>
        /// <returns>返回第一行第一列的数据</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteScalar(cmdText, CommandType.Text, parameters);
        }
        #endregion

        #region 公共方法
        #region  将一个SqlDataReader转换为实体类对象 +  static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class, new()
        /// <summary>
        /// 将一个SqlDataReader转换为实体类对象
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <param name="reader">当前指向的reader</param>
        /// <returns>实体对象</returns>
        public static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class, new()
        {
            try
            {
                var props = typeof(TEntity).GetProperties();
                var entity = new TEntity();
                foreach (var prop in props)
                {
                    if (prop.CanWrite)
                    {
                        try
                        {
                            var index = reader.GetOrdinal(prop.Name);
                            var data = reader.GetValue(index);
                            if (data != DBNull.Value)
                            {
                                prop.SetValue(entity, Convert.ChangeType(data, prop.PropertyType), null);
                            }
                        }
                        catch (IndexOutOfRangeException)
                        {
                            continue;
                        }
                    }
                }
                return entity;
            }
            catch
            {
                return null;
            }
        }
        #endregion


        #region 将dataTable转换为实体 + static List<TEntity> MapEntity<TEntity>(DataTable table) where TEntity : class, new()
        /// <summary>
        /// 将dataTable转换为实体
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<TEntity> MapEntity<TEntity>(DataTable table) where TEntity : class, new()
        {
            try
            {
                List<TEntity> Entitys = new List<TEntity>();
                if (table.Rows.Count == 0)
                    return Entitys;

                var props = typeof(TEntity).GetProperties();
                foreach (DataRow dr in table.Rows)
                {
                    var entity = new TEntity();
                    foreach (var prop in props)
                    {
                        if (prop.CanWrite && table.Columns.Contains(prop.Name))
                        {
                            var value = dr[prop.Name];
                            if (value != DBNull.Value)
                            {
                                prop.SetValue(entity, Convert.ChangeType(value, prop.PropertyType), null);
                            }
                        }
                    }
                    Entitys.Add(entity);
                }
                return Entitys;
            }
            catch
            {
                return null;
            }

        }
        #endregion
        #endregion

        #region 判断某张表的某个字段是否存在某个值 +  static bool ColumnExistsValue(string table, string column, string value)
        /// <summary>
        /// 判断某张表的某个字段是否存在某个值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="column">列名称</param>
        /// <param name="value">要判断的值</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExistsValue(string table, string column, string value)
        {
            string sql = "SELECT count(1) FROM [" + table + "] WHERE [" + column + "] =@Value;";
            object res = ExecuteScalar(sql, new SqlParameter("@Value", value));
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        #endregion

        #region 构建分页查询
        /// <summary>
        /// 构建分页的T-SQL语句
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string CreateQuerySql(int? page, int? rows, StringBuilder sbSql)
        {
            int? iBeginIndex = 1;
            int? iEndIndex = 1;
            string strPageWhere = string.Empty;
            StringBuilder sbSqlPager = null;
            try
            {
                sbSqlPager = new StringBuilder();
                page = page == null ? 1 : page;
                rows = rows == null ? 20 : rows;
                iBeginIndex = (page - 1) * rows + 1;
                iEndIndex = (page) * rows;
                strPageWhere = "where t.SN between " + iBeginIndex + " and " + iEndIndex;
                sbSqlPager.AppendLine("select * from (");
                sbSqlPager.AppendLine(sbSql.ToString());
                sbSqlPager.AppendLine(")t " + strPageWhere);
            }
            catch
            {
                sbSqlPager = sbSql;
            }
            return sbSqlPager.ToString();

        } 
        #endregion