C# DBHelper类

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

namespace AdoNetDBHelper
{
    public static class SqlDBHelper
    {
        /// <summary>  
        /// 连接字符串  
        /// </summary>  
        public static readonly string connectionString = ConfigurationManager.ConnectionStrings["CoreDb"]?.ConnectionString;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connStr">默认为null</param>
        /// <returns>SqlConnection</returns>
        public static SqlConnection CreateSqlConnection(string connStr = null)
        {
            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = connStr == null ? connectionString : connStr;
            conn.Open();
            return conn;
        }

        #region Execute命令

        /// <summary>
        /// 执行带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="safeSql">增删改T-Sql语句或存储过程</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">指定如何解释命令字符串</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>是否成功</returns>
        public static bool ExecuteNonQuery(string safeSql, SqlParameter[] paras, CommandType ct, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                cmd.CommandType = ct;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                return Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;
            }
        }

        /// <summary>
        /// 执行带参数的增删改SQL语句
        /// </summary>
        /// <param name="safeSql">增删改T-Sql语句</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>是否成功</returns>
        public static bool ExecuteNonQuery(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;
            }
        }

        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集T类型</returns>  
        public static object ExecuteScalar(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return ExecuteScalar(safeSql, cmd);
            }
        }

        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集T类型</returns>  
        public static object ExecuteScalar(string safeSql, SqlCommand command)
        {
            SqlCommand cmd = new SqlCommand(safeSql, command.Connection);
            return cmd.ExecuteScalar();
        }

        /// <summary>  
        /// 执行查询T-SQL语句
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集DataTable</returns>  
        public static DataTable ExecuteDataTable(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return ExecuteDataTable(safeSql, cmd);
            }
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="Connection">SqlConnection</param>
        /// <returns>结果集DataTable</returns>
        private static DataTable ExecuteDataTable(string safeSql, SqlCommand command)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command);
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {

            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="command">command</param>
        /// <returns>返回List</returns>
        public static List<T> ExecuteToList<T>(string safeSql, SqlCommand command)
        {
            var data = ExecuteDataTable(safeSql, command);
            return ConvertTo<T>(data);
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <returns>返回List</returns>
        public static List<T> ExecuteToList<T>(string safeSql, string connStr = null)
        {
            var data = ExecuteDataTable(safeSql, connStr);
            return ConvertTo<T>(data);
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>T类型</returns>
        public static T ExecuteFirstOrDefault<T>(string safeSql, string connStr = null)
        {
            var table = ExecuteDataTable(safeSql, connStr);
            T obj = default(T);
            foreach (DataRow item in table.Rows)
            {
                obj = CreateItem<T>(item);
            }
            return obj;
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="conn">SqlConnection</param>
        /// <returns>T类型</returns>
        public static T ExecuteFirstOrDefault<T>(string sql, SqlCommand command)
        {
            var table = ExecuteDataTable(sql, command);
            T obj = default(T);
            foreach (DataRow item in table.Rows)
            {
                obj = CreateItem<T>(item);
            }
            return obj;
        }

        /// <summary>
        /// SQL 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="order">排序字段</param>
        /// <param name="page">当前页数</param>
        /// <param name="size">每页总记录数</param>
        /// <param name="desc">排序方式</param>
        /// <returns></returns>
        public static PagedResultInOut<T> SqlPage<T>(string sql, string order, int page, int size, string desc = "DESC")
        {
            try
            {
                string pageSql = $@"SELECT * FROM(
                                    SELECT ROW_NUMBER()OVER(ORDER BY {order} {desc}) NUMBER,* 
                                    FROM({sql})AS [PAGE]) AS NUMBERTABLE WHERE NUMBER>=({size}*{page}-{size}) AND NUMBER<={size}*{page}";
                string countSql = $@"SELECT COUNT(0) FROM ({sql}) AS [COUNT]";
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand page_cmd = new SqlCommand(pageSql, Connection);
                    SqlDataAdapter da = new SqlDataAdapter(page_cmd);
                    SqlCommand count_cmd = new SqlCommand(countSql, Connection);
                    int count = Convert.ToInt32(count_cmd.ExecuteScalar());
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
                    }
                    var list = ConvertTo<T>(ds.Tables[0]);
                    return new PagedResultInOut<T>() { Msg = "查询成功", Total = count, Rows = list };
                }
            }
            catch (Exception ex)
            {
                return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
            }
        }

        #endregion

        #region 常用类
        public class PagedResultInOut<T>
        {
            /// <summary>
            /// 总条数
            /// </summary>
            public int Total { get; set; }
            /// <summary>
            /// 数据
            /// </summary>
            public List<T> Rows { get; set; }
            /// <summary>
            /// 提示
            /// </summary>
            public string Msg { get; set; }
        }
        /// <summary>
        /// DataTable转Json
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string DataTableToJson(DataTable table)
        {
            var JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }
        #endregion

        #region 将DataTable转List
        public static List<T> ConvertTo<T>(DataTable table)
        {
            if (table == null)
            {
                return null;
            }
            List<DataRow> rows = new List<DataRow>();
            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }
            return ConvertTo<T>(rows);
        }
        public static List<T> ConvertTo<T>(List<DataRow> rows)
        {
            List<T> list = null;
            if (rows != null)
            {
                list = new List<T>();
                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }
            return list;
        }
        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();
                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value = row[column.ColumnName];
                        prop.SetValue(obj, value, null);
                    }
                    catch
                    { //You can log something here
                      //throw;
                    }
                }
            }
            return obj;
        }
        #endregion

    }
}