SqlHelper个人完善版本介绍

 虽然现在有层出不穷的各种ORM框架来与数据库交互，但是其底层也无外乎调用ADO.NET来处理，所以ADO.NET是根本，而且对于传统的老三层项目，大家想都不用想就能说出数据库操作有各种完善的SqlHelper。

别人的自己用了总是有不爽的地方，所以花时间把现有的SqlHelper全部改写了一下，充分利用泛型的以及委托的特性。

先来个代码截图

主要代码如下：

1.批量SQL语句处理类创建

/// <summary>
    ///     批量SQL定义
    /// </summary>
    public sealed class BatchCmdEntity
    {
        //参数列表
        private readonly List<SqlParameter> _mParas;

        public BatchCmdEntity()
        {
            _mParas = new List<SqlParameter>();
        }

        /// <summary>
        ///     SQL语句
        /// </summary>
        public string Sql { get; set; }

        /// <summary>
        ///     参数数组属性
        /// </summary>
        public SqlParameter[] Parameter => _mParas.ToArray();

        /// <summary>
        ///     添加参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        public void AddParameter(string name, object value)
        {
            _mParas.Add(new SqlParameter(name, value));
        }

        /// <summary>
        ///     添加参数数组
        /// </summary>
        /// <param name="paras">参数数组</param>
        public void AddParameter(SqlParameter[] paras)
        {
            _mParas.AddRange(paras);
        }
    }

有些场景比如ExecuteNonQuery需要返回多条语句叠加的数量结果是可以采用此类来封装

2.处理数据库空值方法

#region 【处理数据库空值方法】

        /// <summary>
        /// 处理数据库空值方法
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="value">读取出来的值</param>
        /// <param name="defaultValue">泛型类型的默认值</param>
        /// <returns></returns>
        private static T ConvertData<T>(object value, T defaultValue)
        {
            if (Convert.IsDBNull(value) || value == null)
            {
                return defaultValue;
            }
            if (value is T)
            {
                return (T)value;
            }
            return (T)Convert.ChangeType(value, typeof(T));
        }

        #endregion

有些场景比如ExecuteScalar需要返回首行首列的值，该值可能为空，如果不为空则为object类型，需要返回我们需要的类型如int,string,double等，可以调用此方法

3.生成分页SQL语句，采用row_number函数

  #region【BuildPagingSql】

        /// <summary>
        ///     生成分页语句
        /// </summary>
        /// <param name="orderByField">分页字段</param>
        /// <param name="querySql">查询SQL语句</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">页索引</param>
        /// <returns></returns>
        public static string BuildPagingSql(string orderByField, string querySql, int pageSize, int pageIndex)
        {
            //开始记录数
            int beginRowNum = (pageIndex - 1) * pageSize + 1;
            //结束记录数
            int endRowNum = pageIndex * pageSize;

            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT * ");
            sb.AppendFormat($" FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY {orderByField} ) ROWNUMBER, *");
            sb.AppendFormat($" FROM ({querySql}) a) b ");
            sb.AppendFormat($" WHERE ROWNUMBER BETWEEN {beginRowNum} AND {endRowNum}");

            return sb.ToString();
        }

现在的系统基本都不大使用存储过程，存储过程的弊端大家都知道，不好迁移，不利于做分库，所以采用row_number函数生成SQL语句来处理分页

4.其他代码大家应该理解起来没多大难度，完整代码如下，如有错误，欢迎告知，感谢

#region

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

#endregion

namespace OAO2O.BusinessService.DAL
{
    /// <summary>
    ///     DbHelper 的摘要说明
    /// </summary>
    public sealed class SqlHelper
    {
        //连接字符串从配置文件中读取
        public static readonly string ConnString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

        #region【ExecuteNonQuery】

        /// <summary>
        /// 生成使用默认连接的ExecuteNonQuery
        /// </summary>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, CommandType cmdType, params SqlParameter[] paras)
        {
            return ExecuteNonQuery(ConnString, commandText, cmdType, paras);
        }

        /// <summary>
        /// 生成使用自定义连接的ExecuteNonQuery
        /// </summary>
        /// <param name="connectionString">自定义连接字符串</param>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType cmdType,
            params SqlParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    PrepareCommand(command, connection, null, cmdType, commandText, parms);
                    int retval = command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    return retval;
                }
            }
        }

        /// <summary>
        /// 生成使用默认连接的ExecuteNonQuery
        /// </summary>
        /// <param name="cmdList">批量SQL语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(List<BatchCmdEntity> cmdList)
        {
            return ExecuteNonQuery(ConnString, cmdList);
        }

        /// <summary>
        /// 生成使用自定义连接的ExecuteNonQuery
        /// </summary>
        /// <param name="connectionString">自定义连接字符串</param>
        /// <param name="cmdList">批量SQL语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, List<BatchCmdEntity> cmdList)
        {
            return ExecuteNonQuery(connectionString, command =>
            {
                int retval = 0;
                foreach (BatchCmdEntity cmd in cmdList)
                {
                    //因为是批量语句，所以进来之前先清空
                    command.CommandText = "";
                    command.Parameters.Clear();
                    //重新赋值
                    command.CommandText = cmd.Sql;
                    command.Parameters.AddRange(cmd.Parameter);
                    retval += command.ExecuteNonQuery();
                }

                return retval;
            });
        }

        /// <summary>
        /// 生成使用默认连接的ExecuteNonQuery
        /// </summary>
        /// <param name="fun">泛型委托方法</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(Func<SqlCommand, int> fun)
        {
            return ExecuteNonQuery(ConnString, fun);
        }

        /// <summary>
        /// 生成使用自定义连接的ExecuteNonQuery
        /// </summary>
        /// <param name="connectionString">自定义连接字符串</param>
        /// <param name="fun">泛型委托方法</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, Func<SqlCommand, int> fun)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open) connection.Open();

                SqlTransaction trans = connection.BeginTransaction();
                using (SqlCommand command = new SqlCommand())
                {
                    PrepareCommand(command, connection, trans);
                    int retval = 0;
                    try
                    {
                        retval = fun(command);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();

                    }
                    return retval;
                }
            }
        }

        #endregion

        #region 【ExecuteScalar】

        /// <summary>
        /// 生成使用默认连接的ExecuteScalar
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(string commandText, CommandType cmdType, params SqlParameter[] parms)
        {
            return ExecuteScalar<T>(ConnString, commandText, cmdType, parms);
        }

        /// <summary>
        /// 生成使用自定义连接的ExecuteScalar
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="connectionString">自定义连接字符串</param>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(string connectionString, string commandText, CommandType cmdType,
            params SqlParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    PrepareCommand(command, connection, null, cmdType, commandText, parms);
                    object retval = command.ExecuteScalar();
                    command.Parameters.Clear();
                    return ConvertData(retval, default(T));
                }
            }
        }

        #endregion

        #region 【ExecuteDataRow】

        public static DataRow ExecuteDataRow(string commandText, CommandType cmdType, params SqlParameter[] parms)
        {
            return ExecuteDataRow(ConnString, commandText, cmdType, parms);
        }

        /// <summary>
        /// 生成数据行DataRow
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdType"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataRow ExecuteDataRow(string connectionString, string commandText, CommandType cmdType,
            params SqlParameter[] parms)
        {
            DataTable data = ExecuteDataTable(connectionString, commandText, cmdType, parms);
            return data?.Rows[0];
        }

        #endregion

        #region 【ExecuteDataTable】

        public static DataTable ExecuteDataTable(string commandText, CommandType cmdType, params SqlParameter[] paras)
        {
            return ExecuteDataTable(ConnString, commandText, cmdType, paras);
        }

        /// <summary>
        /// 生成数据表DataTable
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string connectionString, string commandText, CommandType cmdType,
            params SqlParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    PrepareCommand(command, connection, null, cmdType, commandText, parms);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    command.Parameters.Clear();

                    return data;
                }
            }
        }

        #endregion

        #region【ExecuteDataSet】

        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] parms)
        {
            return ExecuteDataSet(ConnString, cmdText, cmdType, parms);
        }

        /// <summary>
        /// 生成数据集合DataSet
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">语句</param>
        /// <param name="cmdType">类型</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType cmdType,
            params SqlParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    PrepareCommand(command, connection, null, cmdType, commandText, parms);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                    DataSet data = new DataSet();
                    adapter.Fill(data);
                    command.Parameters.Clear();

                    return data;
                }
            }
        }

        #endregion

        #region 【PrepareCommand】

        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction)
        {
            PrepareCommand(command, connection, transaction, CommandType.Text, string.Empty, null);
        }

        /// <summary>
        /// 生成cmd
        /// </summary>
        /// <param name="command">cmd对象</param>
        /// <param name="connection">连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">类型</param>
        /// <param name="commandText">语句</param>
        /// <param name="parms">参数数组</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction,
            CommandType commandType, string commandText, params SqlParameter[] parms)
        {
            if (connection.State != ConnectionState.Open) connection.Open();

            command.Connection = connection;
            command.CommandTimeout = 600;
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (transaction != null) command.Transaction = transaction;

            if (parms == null || parms.Length == 0) return;
            //循环设置参数值为null的参数，设置其值为数据库制定类型DBNull.Value
            foreach (SqlParameter parameter in parms.Where(parameter => (parameter.Direction == ParameterDirection.InputOutput ||
                                                                parameter.Direction == ParameterDirection.Input) &&
                                                               (parameter.Value == null)))
            {
                parameter.Value = DBNull.Value;
            }
            //添加到参数数组中
            command.Parameters.AddRange(parms);
        }

        #endregion

        #region 【BuildPara】
        /// <summary>
        /// 生成普通参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, object value)
        {
            return new SqlParameter(name, value);
        }

        /// <summary>
        /// 生成普通参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, SqlDbType dbType, object value)
        {
            return new SqlParameter(name, dbType)
            {
                Value = value
            };
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="direction">输出方向</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, ParameterDirection direction, object value)
        {
            return new SqlParameter
            {
                ParameterName = name,
                Direction = direction,
                Value = value
            };
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="direction">输出方向</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, SqlDbType dbType, ParameterDirection direction)
        {
            return new SqlParameter(name, dbType)
            {
                Direction = direction
            };
        }

        /// <summary>
        /// 生成带长度的输出参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">长度</param>
        /// <param name="direction">输出方向</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, SqlDbType dbType, int size, ParameterDirection direction)
        {
            return new SqlParameter(name, dbType, size)
            {
                Direction = direction
            };
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="direction">输出方向</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static SqlParameter BuildPara(string name, SqlDbType dbType, ParameterDirection direction, object value)
        {
            return new SqlParameter(name, dbType)
            {
                Direction = direction,
                Value = value
            };
        }

        #endregion

        #region【BuildPagingSql】

        /// <summary>
        ///     生成分页语句
        /// </summary>
        /// <param name="orderByField">分页字段</param>
        /// <param name="querySql">查询SQL语句</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">页索引</param>
        /// <returns></returns>
        public static string BuildPagingSql(string orderByField, string querySql, int pageSize, int pageIndex)
        {
            //开始记录数
            int beginRowNum = (pageIndex - 1) * pageSize + 1;
            //结束记录数
            int endRowNum = pageIndex * pageSize;

            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT * ");
            sb.AppendFormat($" FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY {orderByField} ) ROWNUMBER, *");
            sb.AppendFormat($" FROM ({querySql}) a) b ");
            sb.AppendFormat($" WHERE ROWNUMBER BETWEEN {beginRowNum} AND {endRowNum}");

            return sb.ToString();
        }

        #endregion

        #region 【处理数据库空值方法】

        /// <summary>
        /// 处理数据库空值方法
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="value">读取出来的值</param>
        /// <param name="defaultValue">泛型类型的默认值</param>
        /// <returns></returns>
        private static T ConvertData<T>(object value, T defaultValue)
        {
            if (Convert.IsDBNull(value) || value == null)
            {
                return defaultValue;
            }
            if (value is T)
            {
                return (T)value;
            }
            return (T)Convert.ChangeType(value, typeof(T));
        }

        #endregion
    }


    /// <summary>
    ///     批量SQL定义
    /// </summary>
    public sealed class BatchCmdEntity
    {
        //参数列表
        private readonly List<SqlParameter> _mParas;

        public BatchCmdEntity()
        {
            _mParas = new List<SqlParameter>();
        }

        /// <summary>
        ///     SQL语句
        /// </summary>
        public string Sql { get; set; }

        /// <summary>
        ///     参数数组属性
        /// </summary>
        public SqlParameter[] Parameter => _mParas.ToArray();

        /// <summary>
        ///     添加参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        public void AddParameter(string name, object value)
        {
            _mParas.Add(new SqlParameter(name, value));
        }

        /// <summary>
        ///     添加参数数组
        /// </summary>
        /// <param name="paras">参数数组</param>
        public void AddParameter(SqlParameter[] paras)
        {
            _mParas.AddRange(paras);
        }
    }
}