公布一个企业级DbHelperSQL，欢迎大家发言。。

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

namespace Maticsoft.DBUtility
{
    /// <summary>
    /// 数据库操作通用类
    /// </summary>
    public static class DbHelperSQL
    {
        //读取配置字符串

        #region 从web.confng中读取数据库连接字符串
        /// <summary>
        /// 从web.confng中读取数据库连接字符串
        /// </summary>
        private static readonly string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
        #endregion

        //查询返回结果集

        #region 查询返回结果集
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(string sqlText)
        {
            return Query(sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="Parameter">参数数组</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(string sqlText, SqlParameter[] Parameter)
        {
            return Query(sqlText, CommandType.Text, Parameter);
        }
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">查询语句类型，是SQL文本还是存储过程</param>
        /// <param name="Parameter">参数数组</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(string sqlText, CommandType commandType, SqlParameter[] Parameter)
        {
            DataSet Table = new DataSet();//实例化表格，用于装载数据
            using (SqlConnection Connection = new SqlConnection(ConnectionString))//实例化sqlConection
            {
                using (SqlCommand Command = new SqlCommand())
                {
                    PrepareCommand(Command, Connection, null, commandType, sqlText, Parameter);
                    SqlDataAdapter Adapter = new SqlDataAdapter(Command);//借助Adapter做传值给Table
                    Adapter.Fill(Table);//填充Table
                }
            }
            return Table.Tables[0];//返回数据集
        }
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(SqlConnection connection, string sqlText)
        {
            return Query(connection, sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="Parameter">参数数组</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(SqlConnection connection, string sqlText, SqlParameter[] Parameter)
        {
            return Query(connection, sqlText, CommandType.Text, Parameter);
        }
        /// <summary>
        /// 查询返回结果集
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">查询语句类型，是SQL文本还是存储过程</param>
        /// <param name="Parameter">参数数组</param>
        /// <returns>查询返回结果集</returns>
        public static DataTable Query(SqlConnection connection, string sqlText, CommandType commandType, SqlParameter[] Parameter)
        {
            DataSet Table = new DataSet();//实例化表格，用于装载数据
            using (SqlCommand Command = new SqlCommand())
            {
                PrepareCommand(Command, connection, null, commandType, sqlText, Parameter);
                SqlDataAdapter Adapter = new SqlDataAdapter(Command);//借助Adapter做传值给Table
                Adapter.Fill(Table);//填充Table
            }
            return Table.Tables[0];//返回数据集
        }
        #endregion

        //查询返回sqlDataReader

        #region 返回SqlDataReader高速输出
        /// <summary>
        /// 返回SqlDataReader高速输出
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sqlText)
        {
            return ExecuteReader(sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 返回SqlDataReader高速输出
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="Paramter">参数</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sqlText, SqlParameter[] Paramter)
        {
            return ExecuteReader(sqlText, CommandType.Text, Paramter);
        }
        /// <summary>
        /// 返回SqlDataReader高速输出
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">查询语句类型类型，是文本还是存储</param>
        /// <param name="Paramter">参数</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sqlText, CommandType commandType, SqlParameter[] Paramter)
        {
            SqlConnection Connection = new SqlConnection(ConnectionString);//实例化Connection
            SqlCommand Command = new SqlCommand();//实例化command
            PrepareCommand(Command, Connection, null, commandType, sqlText, Paramter);
            SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
            Command.Parameters.Clear();
            return reader;
        }
        #endregion

        //执行查询返回第一行第一列

        #region 执行查询返回第一行第一列
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(string sqlText)
        {
            return GetSingle(sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(string sqlText, SqlParameter[] parameter)
        {
            return GetSingle(sqlText, CommandType.Text, parameter);
        }
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">语句类型，是文本还是存储过程</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(string sqlText, CommandType commandType, SqlParameter[] parameter)
        {
            Object obj = null;//建立对象，接收返回值
            using (SqlConnection Connection = new SqlConnection(ConnectionString))//实例化Connection
            {
                using (SqlCommand Command = new SqlCommand())//实例化Command
                {
                    PrepareCommand(Command, Connection, null, commandType, sqlText, parameter);
                    obj = Command.ExecuteScalar();//执行操作，返回结果
                }
            }
            return obj;//返回对象
        }
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="connection">一个存在的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(SqlConnection connection, string sqlText)
        {
            return GetSingle(connection, sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="connection">一个存在的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(SqlConnection connection, string sqlText, SqlParameter[] parameter)
        {
            return GetSingle(connection, sqlText, CommandType.Text, parameter);
        }
        /// <summary>
        /// 执行查询返回第一行第一列
        /// </summary>
        /// <param name="connection">一个存在的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">语句类型，是文本还是存储过程</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回第一行第一列</returns>
        public static Object GetSingle(SqlConnection connection, string sqlText, CommandType commandType, SqlParameter[] parameter)
        {
            Object obj = null;//建立对象，接收返回值
            using (SqlCommand Command = new SqlCommand())//实例化Command
            {
                PrepareCommand(Command, connection, null, commandType, sqlText, parameter);
                obj = Command.ExecuteScalar();//执行操作，返回结果
            }
            return obj;//返回对象
        }
        #endregion

        //增删改系列

        #region 对数据库进行增删改返回受影响行数
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(string sqlText)
        {
            return ExecuteSql(sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(string sqlText, SqlParameter[] parameter)
        {
            return ExecuteSql(sqlText, CommandType.Text, parameter);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">语句类型，是文本还是存储过程</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(string sqlText, CommandType commandType, SqlParameter[] parameter)
        {
            int count = 0;//建立INT的对象，初始化值为0
            using (SqlConnection connection = new SqlConnection(ConnectionString))//实例化connection
            {
                using (SqlCommand command = new SqlCommand())//实例化command
                {
                    PrepareCommand(command, connection, null, commandType, sqlText, parameter);
                    count = command.ExecuteNonQuery();//执行操作，返回受影响行数
                }
            }
            return count;//返回受影响
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlConnection connection, string sqlText)
        {
            return ExecuteSql(connection, sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlConnection connection, string sqlText, SqlParameter[] parameter)
        {
            return ExecuteSql(connection, sqlText, CommandType.Text, parameter);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">语句类型，是文本还是存储过程</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlConnection connection, string sqlText, CommandType commandType, SqlParameter[] parameter)
        {
            int count = 0;//建立INT的对象，初始化值为0
            using (SqlCommand command = new SqlCommand())//实例化command
            {
                PrepareCommand(command, connection, null, commandType, sqlText, parameter);
                count = command.ExecuteNonQuery();//执行操作，返回受影响行数
            }
            return count;//返回受影响
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlTransaction trans, string sqlText)
        {
            return ExecuteSql(trans, sqlText, CommandType.Text, null);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlTransaction trans, string sqlText, SqlParameter[] parameter)
        {
            return ExecuteSql(trans, sqlText, CommandType.Text, parameter);
        }
        /// <summary>
        /// 对数据库进行增删改返回受影响行数
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="sqlText">查询语句</param>
        /// <param name="commandType">语句类型，是文本还是存储过程</param>
        /// <param name="parameter">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteSql(SqlTransaction trans, string sqlText, CommandType commandType, SqlParameter[] parameter)
        {
            int count = 0;//建立INT的对象，初始化值为0
            using (SqlCommand command = new SqlCommand())//实例化command
            {
                PrepareCommand(command, trans.Connection, trans, commandType, sqlText, parameter);
                count = command.ExecuteNonQuery();//执行操作，返回受影响行数
            }
            return count;//返回受影响
        }
        #endregion

        //分页查询系列

        #region 带主健（ID）使用 Top NOT IN 分页查询语句
        /// <summary>
        /// 带主健（ID）使用 Top NOT IN 分页查询语句
        /// </summary>
        /// <param name="Column">需要查询的列名</param>
        /// <param name="data">被查询的表名</param>
        /// <param name="numPerPage">每页显示数量</param>
        /// <param name="currentPage">分页页数</param>
        /// <param name="QueryConditions">Where后面的查询语句(不包括Where)</param>
        /// <param name="Parameters">参数</param>
        /// <param name="Sort">where条件后的排序语句</param>
        /// <param name="IDColumn">关健列（用于not in 前）</param>
        /// <returns>返回数据列表</returns>
        public static DataTable QueryPageList_NotIn(string Column, string data, int numPerPage, int currentPage, string QueryConditions, SqlParameter[] Parameter, string Sort, string IDColumn)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("select top ");
            SQL.Append(numPerPage + " ");
            SQL.Append(Column);
            SQL.Append(" from ");
            SQL.Append(data);
            SQL.Append(" where ");
            SQL.Append(IDColumn);
            SQL.Append(" not in(select top ");
            SQL.Append(numPerPage * currentPage);
            SQL.Append(" " + IDColumn);
            SQL.Append(" from ");
            SQL.Append(data);
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" where ");
                SQL.Append(QueryConditions);
            }
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            SQL.Append(")");
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" and " + QueryConditions);
            }
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            return Query(SQL.ToString(), CommandType.Text, Parameter);
        }
        #endregion

        #region 带主健（ID）使用 Top 关健列大于多少 分页查询语句
        /// <summary>
        /// 带主健（ID）使用 Top 关健列大于多少 分页查询语句
        /// </summary>
        /// <param name="Column">需要查询的列名</param>
        /// <param name="data">被查询的表名</param>
        /// <param name="numPerPage">每页显示数量</param>
        /// <param name="currentPage">分页页数</param>
        /// <param name="QueryConditions">Where后面的查询语句(不包括Where)</param>
        /// <param name="Parameters">参数</param>
        /// <param name="Sort">where条件后的排序语句,默认己有(order by [Column] asc)</param>
        /// <param name="IDColumn">关健列（用于分页）</param>
        /// <returns>返回数据列表</returns>
        public static DataTable QueryPageList_MaxID_ASC(string Column, string data, int numPerPage, int currentPage, string QueryConditions, SqlParameter[] Parameter, string Sort, string IDColumn)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("select top ");
            SQL.Append(numPerPage + " ");
            SQL.Append(Column);
            SQL.Append(" from ");
            SQL.Append(data);
            SQL.Append(" where ");
            SQL.Append(IDColumn);
            SQL.Append(" >(select isnull(min(id),0) from (select top ");
            SQL.Append(numPerPage * currentPage);
            SQL.Append(" " + IDColumn);
            SQL.Append(" from ");
            SQL.Append(data);
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" where ");
                SQL.Append(QueryConditions);
            }
            SQL.Append(" order by " + IDColumn + " asc ");
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            SQL.Append(")dt)");
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" and " + QueryConditions);
            }
            SQL.Append(" order by " + IDColumn + " asc ");
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            return Query(SQL.ToString(), CommandType.Text, Parameter);
        }
        #endregion

        #region 带主健（ID）使用 Top 关健列小于多少 分页查询语句
        /// <summary>
        /// 带主健（ID）使用 Top 关健列小于多少 分页查询语句
        /// </summary>
        /// <param name="Column">需要查询的列名</param>
        /// <param name="data">被查询的表名</param>
        /// <param name="numPerPage">每页显示数量</param>
        /// <param name="currentPage">分页页数</param>
        /// <param name="QueryConditions">Where后面的查询语句(不包括Where)</param>
        /// <param name="Parameters">参数</param>
        /// <param name="Sort">where条件后的排序语句,默认己有(order by [Column] desc)</param>
        /// <param name="IDColumn">关健列（用于分页）</param>
        /// <returns>返回数据列表</returns>
        public static DataTable QueryPageList_MaxID_DESC(string Column, string data, int numPerPage, int currentPage, string QueryConditions, SqlParameter[] Parameter, string Sort, string IDColumn)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("select top ");
            SQL.Append(numPerPage + " ");
            SQL.Append(Column);
            SQL.Append(" from ");
            SQL.Append(data);
            SQL.Append(" where ");
            SQL.Append(IDColumn);
            SQL.Append(" <(select isnull(min(id),0) from (select top ");
            SQL.Append(numPerPage * currentPage);
            SQL.Append(" " + IDColumn);
            SQL.Append(" from ");
            SQL.Append(data);
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" where ");
                SQL.Append(QueryConditions);
            }
            SQL.Append(" order by " + IDColumn + " desc ");
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            SQL.Append(")dt)");
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                SQL.Append(" and " + QueryConditions);
            }
            SQL.Append(" order by " + IDColumn + " desc ");
            if (Sort != null && Sort.Trim() != "")
            {
                SQL.Append(" ");
                SQL.Append(Sort);
            }
            return Query(SQL.ToString(), CommandType.Text, Parameter);
        }
        #endregion

        #region 查询分页总行数
        /// <summary>
        /// 查询分页总行数
        /// </summary>
        /// <param name="data">被查询的表</param>
        /// <param name="QueryConditions">Where 后面的条件(不带Where)</param>
        /// <param name="Parameter">参数</param>
        /// <param name="Sort">where条件后的排序语句</param>
        /// <returns></returns>
        public static int QueryCount(string data, string QueryConditions, SqlParameter[] Parameter)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count (*) from ");
            sql.Append(data);
            if (QueryConditions != null && QueryConditions.Trim() != "")
            {
                sql.Append(" where ");
                sql.Append(QueryConditions);
            }
            return (int)GetSingle(sql.ToString(), CommandType.Text, Parameter);
        }
        #endregion

        //批量添加/复制系列

        #region DataReader批量添加相比之下，效率高(有事务)
        /// <summary>
        /// SqlDataReader批量添加(有事务)
        /// </summary>
        /// <param name="Reader">数据源</param>
        /// <param name="Mapping">定义数据源和目标源列的关系集合</param>
        /// <param name="DestinationTableName">目标表</param>
        public static bool MySqlBulkCopy(SqlDataReader Reader, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName)
        {
            bool Bool = true;
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (SqlTransaction Tran = con.BeginTransaction())//指定事务
                {
                    using (SqlBulkCopy copy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, Tran))
                    {
                        copy.DestinationTableName = DestinationTableName;//设置要添加的表名
                        if (Mapping != null)
                        {
                            //如果有匹配
                            foreach (SqlBulkCopyColumnMapping Mapp in Mapping)
                            {
                                copy.ColumnMappings.Add(Mapp);
                            }
                        }
                        try
                        {
                            copy.WriteToServer(Reader);//批量添加
                            Tran.Commit();//提交事务
                        }
                        catch
                        {
                            Tran.Rollback();//回滚事务
                            Bool = false;
                        }
                        finally
                        {
                            Reader.Close();//关闭
                        }
                    }
                }
            }
            return Bool;//返回结果
        }
        #endregion

        #region DataTable批量添加相比之下,灵活度高(有事务)
        /// <summary>
        /// DataTable批量添加(有事务)
        /// </summary>
        /// <param name="Table">数据源</param>
        /// <param name="Mapping">定义数据源和目标源列的关系集合</param>
        /// <param name="DestinationTableName">目标表</param>
        public static bool MySqlBulkCopy(DataTable Table, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName)
        {
            bool Bool = true;
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (SqlTransaction Tran = con.BeginTransaction())
                {
                    using (SqlBulkCopy Copy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, Tran))
                    {
                        Copy.DestinationTableName = DestinationTableName;//指定目标表
                        if (Mapping != null)
                        {
                            //如果有数据
                            foreach (SqlBulkCopyColumnMapping Map in Mapping)
                            {
                                Copy.ColumnMappings.Add(Map);
                            }
                        }
                        try
                        {
                            Copy.WriteToServer(Table);//批量添加
                            Tran.Commit();//提交事务
                        }
                        catch (Exception ex)
                        {
                            Tran.Rollback();//回滚事务
                            Bool = false;
                        }
                    }
                }
            }
            return Bool;
        }
        #endregion

        //公共方法，提取Command

        #region 执行一个命令
        /// <summary>
        /// 执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">Sql连接</param>
        /// <param name="trans">Sql事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="sqlText">sql命令文本,例如：Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string sqlText, SqlParameter[] cmdParms)
        {

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

            cmd.Connection = conn;
            cmd.CommandText = sqlText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    parm.Value = parm.Value ?? DBNull.Value;
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion

    }
}