ADO.NET 数据库帮助类SQLHelper

SQLHelper：

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

namespace  Libs {
    public class SQLHelper {
        #region 通用方法  

        private readonly string _connectionString;

        public SQLHelper() : this(InternalGetDbConnection("ConnectionString")) {
            
        }

        public SQLHelper(string key)
        {
            if (string.IsNullOrEmpty(key)) throw new Exception("数据库连接未配置");

            _connectionString = InternalGetDbConnection(key);
        }
        private static string InternalGetDbConnection(string key) {
            var value = ConfigurationManager.AppSettings[key];
            if (value == null) {
                value = ConfigurationManager.ConnectionStrings[key]?.ConnectionString;
                if (value == null) {
                    return null;
                }
            }

            // 如果在链接字符串内能找到Data Source或者Initial Catalog字眼就认为是正常的链接字符串，否则将调用服务以DbKey的方式获取到真正的链接字符串。
            if (value.IndexOf("Data Source", StringComparison.InvariantCultureIgnoreCase) > -1 || value.IndexOf("Initial Catalog", StringComparison.InvariantCultureIgnoreCase) > -1) {
                return value;
            }
            else {
                return null;
            }
        }
       
        #endregion

        #region 对数据库执行增删改操作  
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <returns></returns>  
        public int ExecuteNonQuerySql(string sql) {
            return ExecuteNonQuery(sql, null, CommandType.Text);
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <returns></returns>  
        public  int ExecuteNonQuerySql(string sql, SqlParameter[] parameters) {
            return ExecuteNonQuery(sql, parameters, CommandType.Text);
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的存储过程</param>   
        /// <returns></returns>  
        public  int ExecuteNonQueryProc(string sql) {
            return ExecuteNonQuery(sql, null, CommandType.StoredProcedure);
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的存储过程</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <returns></returns>  
        public  int ExecuteNonQueryProc(string sql, SqlParameter[] parameters) {
            return ExecuteNonQuery(sql, parameters, CommandType.StoredProcedure);
        }
        #endregion

        #region 查询

        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <returns></returns>
        public  DataTable ExecuteDataTableSql(string sql) {
            return ExecuteDataTableSql(sql, null);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public  DataTable ExecuteDataTableSql(string sql, SqlParameter[] parameters) {
            return ExecuteDataTable(sql, parameters, CommandType.Text, null);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的存储过程</param>   
        /// <returns></returns>
        public  DataTable ExecuteDataTableProc(string sql) {
            return ExecuteDataTableProc(sql, null);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的存储过程</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public  DataTable ExecuteDataTableProc(string sql, SqlParameter[] parameters) {
            return ExecuteDataTable(sql, parameters, CommandType.StoredProcedure, null);
        }

        /// <summary>   
        /// 执行一个查询语句，返回表列名和类型
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <returns></returns>
        public  DataTable ExecuteDataTableSchema(string sql) {
            return ExecuteDataTableSchema(sql, null);
        }
        /// <summary>   
        /// 执行一个查询语句，返回表列名和类型
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public  DataTable ExecuteDataTableSchema(string sql, SqlParameter[] parameters) {
            return ExecuteDataTable(sql, parameters, CommandType.Text, SchemaType.Mapped);
        }
        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool IsTabExists(string tableName) {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + tableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            object obj = ExecuteScalar(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                cmdresult = 0;
            }
            else {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0) {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public  bool IsColumnExists(string tableName, string columnName) {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = ExecuteScalar(sql);
            if (res == null) {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 校验某个字段值是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public  bool IsValueExists(string strSql) {
            object obj = ExecuteScalar(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                cmdresult = 0;
            }
            else {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0) {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 获取字段最大值
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public  int GetMaxId(string fieldName, string tableName) {
            string sql = "select max(" + fieldName + ")+1 from " + tableName;
            object obj = ExecuteScalar(sql);
            if (obj == null) {
                return 1;
            }
            return int.Parse(obj.ToString());
        }

        #endregion


        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public  int ExecuteNonQuery(string strSql, SqlParameter[] param, CommandType commandType) {
            int affectedRows = 0;
            //执行数据库操作
            using (var command = new SqlCommand()) {
                //数据库连接
                command.Connection = new SqlConnection(_connectionString);
                try {
                    //打开连接
                    command.Connection.Open();
                    //执行类型
                    command.CommandType = commandType;
                    //数据库语句
                    command.CommandText = strSql;
                    //执行参数
                    if (param != null) command.Parameters.AddRange(param);
                    //数据库执行
                    affectedRows = command.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    throw new Exception("数据库执行:" + ex);
                }
                finally {
                    //关闭连接
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Parameters.Clear();
                }
            }
            return affectedRows;
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <param name="schemaType">是否映射表结构</param>
        /// <returns></returns>
        public  DataTable ExecuteDataTable(string sql, SqlParameter[] parameters, CommandType commandType, SchemaType? schemaType) {
            //返回数据
            DataTable data = new DataTable();

            //判断是否有SQL语句
            if (string.IsNullOrEmpty(sql)) throw new Exception("SQL语句为空");

            //执行数据库操作
            using (var command = new SqlCommand()) {
                //数据库连接
                command.Connection = new SqlConnection(_connectionString);
                try {
                    //打开连接
                    command.Connection.Open();
                    //执行类型
                    command.CommandType = commandType;
                    //数据库语句
                    command.CommandText = sql;
                    //执行参数
                    if (parameters != null) command.Parameters.AddRange(parameters);
                    using (DbDataAdapter adapter = new SqlDataAdapter(command)) {
                        //是否映射表结构
                        if (schemaType.HasValue) {
                            adapter.FillSchema(data, schemaType.Value);
                        }
                        else {
                            adapter.Fill(data);
                        }
                        data.AcceptChanges();
                    }
                }
                catch (Exception ex) {
                    throw new Exception("数据库执行:" + ex);
                }
                finally {
                    //关闭连接
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Parameters.Clear();
                }
            }

            return data;
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStrList">多条SQL语句</param>        
        public  void ExecuteSqlTran(List<string> sqlStrList) {
            using (SqlConnection conn = new SqlConnection(_connectionString)) {
                conn.Open();
                SqlCommand cmd = new SqlCommand {Connection = conn};
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try {
                    for (int n = 0; n < sqlStrList.Count; n++) {
                        string strsql = sqlStrList[n];
                        if (strsql.Trim().Length > 1) {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (SqlException E) {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally {
                    cmd.Dispose();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行分页存储过程
        ///         DataTable dt = Helper.HelperExecuteSql.GetListRunProcedure(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, "caseInfo", "ID", "Up_CommonPage").Tables[0];
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="tableName"></param>
        /// <param name="keyName"></param>
        /// <param name="procedureName"></param>
        /// <returns></returns>
        public  DataSet GetListRunProcedure(int pageSize, int pageIndex, string strWhere, string tableName, string keyName, string procedureName) {
            SqlParameter[] parameters = {
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@CurrentIndex",SqlDbType.Int),
                new SqlParameter("@StrWhere",SqlDbType.NVarChar,300),
                new SqlParameter("@TableName",SqlDbType.NVarChar,50),
                new SqlParameter("@KeyName",SqlDbType.NVarChar,50)      //主键
            };
            parameters[0].Value = pageSize;
            parameters[1].Value = pageIndex;
            parameters[2].Value = strWhere;
            parameters[3].Value = tableName;
            parameters[4].Value = keyName;
            return RunProcedure(procedureName, parameters, tableName);
        }

        #region Up_CommonPage存储过程
        /*        
CREATE PROCEDURE [dbo].[Up_CommonPage] 
(
@PageSize INT,
@CurrentIndex INT,
@StrWhere NVARCHAR(300),   --有条件,直接写条件,没有条件要写1=1,有order by 也写在里面
@TableName NVARCHAR(50),
@KeyName NVARCHAR(50)
)
AS
 DECLARE @StrSql NVARCHAR(800)
 IF(@CurrentIndex=1)  --如果是第一页
    BEGIN
         SET @StrSql=N'select top '+CONVERT(NVARCHAR(10),@PageSize)+' * from '+@TableName+' where '+@StrWhere
    END
  ELSE
     BEGIN
        SET @StrSql=N'select top '+CONVERT(NVARCHAR(10),@PageSize)+' * from '+@TableName+' where '+@KeyName+' not in (select top '+CONVERT(NVARCHAR(10),@PageSize*(@CurrentIndex-1))+' '+@KeyName+' from '+@TableName+' where '+@StrWhere+') and '+@StrWhere
     END      
  EXECUTE (@StrSql)
GO
      */

        #endregion


        public  DataSet RunProcedure(string storedProcName, SqlParameter[] parameters, string tableName) {
            return ExecuteDataSet(storedProcName, parameters, CommandType.StoredProcedure, tableName);
        }

        /// <summary>
        /// 获取dataset DataTable dt = ExecuteDataSet(sql).Tables[0];
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public  DataSet ExecuteDataSet(string strSql, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(strSql, parameters, CommandType.Text, "ds");
        }

        /// <summary>
        /// 查询sql结果或存储dataset
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <param name="commandType"></param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns></returns>
        public  DataSet ExecuteDataSet(string strSql, SqlParameter[] parameters, CommandType commandType, string tableName) {
            //执行数据库操作
            using (var command = new SqlCommand()) {
                //数据库连接
                command.Connection = new SqlConnection(_connectionString);
                try {
                    //打开连接
                    command.Connection.Open();
                    //执行类型
                    command.CommandType = commandType;
                    //数据库语句
                    command.CommandText = strSql;
                    //执行参数
                    if (parameters != null) command.Parameters.AddRange(parameters);
                    DbDataAdapter adapter = new SqlDataAdapter(command);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, tableName);
                    return dataSet;
                }
                catch (Exception ex) {
                    throw new Exception("数据库执行:" + ex);
                }
                finally {
                    //关闭连接
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Parameters.Clear();
                }
            }
        }



        /// <summary>
        /// 执行一条计算查询结果语句，
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回查询结果,如查询条数</returns>
        public  object ExecuteScalar(string sql) {
            using (SqlConnection conn = new SqlConnection(_connectionString)) {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    try {
                        //打开连接
                        if (conn.State == System.Data.ConnectionState.Closed) {
                            conn.Open();
                        }
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                            return null;
                        }
                        else {
                            return obj;
                        }
                    }
                    catch (Exception ex) {
                        throw new Exception("数据库执行:" + ex);
                    }
                    finally {
                        //关闭连接
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 返回单个值的泛型版本
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public  T ExcuteScalar<T>(string sql, params SqlParameter[] parameters) {
            using (SqlConnection conn = new SqlConnection(_connectionString)) {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    try {
                        //打开连接
                        if (conn.State == System.Data.ConnectionState.Closed) {
                            conn.Open();
                        }
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                            return default(T);
                        }
                        else {
                            return (T)obj;
                        }
                    }
                    catch (Exception ex) {
                        throw new Exception("数据库执行:" + ex);
                    }
                    finally {
                        //关闭连接
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// datatable批量写入
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="strTableName"></param>
        /// <param name="batchSize"></param>
        public  void SqlBulkCopy2Db(DataTable dataTable, string strTableName) {
            using (IDbConnection dbConnection = new SqlConnection(_connectionString)) {
                if (dbConnection.State != ConnectionState.Open) {
                    dbConnection.Open();
                }
                using (var transaction = dbConnection.BeginTransaction()) {

                    try {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)dbConnection,
                            SqlBulkCopyOptions.Default,
                            (SqlTransaction)transaction)) {
                            bulkCopy.DestinationTableName = strTableName;
                            //bulkCopy.BatchSize = batchSize;
                            //for (int i = 0; i < dataTable.Columns.Count; i++) {
                            //    bulkCopy.ColumnMappings.Add(i, i);
                            //}
                            bulkCopy.WriteToServer(dataTable);
                        }
                        transaction.Commit();
                    }
                    catch (Exception exception) {
                        transaction.Rollback();

                        throw new Exception($"{strTableName}持久化异常", exception);
                    }
                }
            }
        }


        /// <summary>
        /// SqlDataReader转换成DataTable
        /// </summary>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        public  DataTable GetNewDataTable(SqlDataReader dataReader) {
            DataTable datatable = new DataTable();
            DataTable schemaTable = dataReader.GetSchemaTable();

            //动态添加列
            try {
                foreach (DataRow myRow in schemaTable.Rows) {
                    DataColumn myDataColumn = new DataColumn();
                    myDataColumn.DataType = myRow.GetType();
                    myDataColumn.ColumnName = myRow[0].ToString();
                    datatable.Columns.Add(myDataColumn);
                }
                //添加数据
                while (dataReader.Read()) {
                    DataRow myDataRow = datatable.NewRow();
                    for (int i = 0; i < schemaTable.Rows.Count; i++) {
                        myDataRow[i] = dataReader[i].ToString();
                    }
                    datatable.Rows.Add(myDataRow);
                }
                dataReader.Close();
                return datatable;
            }
            catch (Exception ex) {
                throw new Exception("转换出错出错!", ex);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType">SqlDbType.Int etc</param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static SqlParameter MakeInParam(string paramName, SqlDbType dbType, int size, object value) {
            return MakeParam(paramName, dbType, size, ParameterDirection.Input, value);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public static SqlParameter MakeOutParam(string paramName, SqlDbType dbType, int size) {
            return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);
        }

        public static SqlParameter MakeParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object value) {
            SqlParameter parameter;
            if ((size > 0) && (size != 0x10)) {
                parameter = new SqlParameter(paramName, dbType, size);
            }
            else {
                parameter = new SqlParameter(paramName, dbType);
            }
            parameter.Direction = direction;
            if ((direction != ParameterDirection.Output) || (value != null)) {
                parameter.Value = value;
            }
            return parameter;
        }
    }
}