Asp .Net Core系列：基于MySQL的DBHelper帮助类和SQL Server的DBHelper帮助类

文章目录

- MySQLDBHelper
- MSSQLDBHelper

MySQLDBHelper

app.config中添加配置

	<connectionStrings>
		<add name="MySqlConn" connectionString="server=localhost;port=3306;user=root;password=123456;database=db1;SslMode=none"/>
	</connectionStrings>

MySQLDBHelper

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class MySQLDBHelper
    {
        public readonly static string MySqlConn = ConfigurationManager.ConnectionStrings["MySqlConn"].ConnectionString.ToString();

        /// <summary>
        /// 执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, MySqlParameter[] pms)
        {
            using (MySqlConnection conn = new MySqlConnection(MySqlConn))
            {
                conn.Open();
                using (MySqlTransaction transaction = conn.BeginTransaction())
                {
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (pms != null && pms.Length > 0)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        int rows = cmd.ExecuteNonQuery();
                        transaction.Commit();
                        return rows > 0;
                    }
                }
            }
        }

        /// <summary>
        ///  执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, Dictionary<string, object> pms)
        {
            MySqlParameter[] parameters = null;

            if (pms != null && pms.Count > 0)
            {
                parameters = DictionaryToMySqlParameters(pms).ToArray();
            }

            return ExecuteNonQuery(sql, parameters);
        }

        /// <summary>
        ///  执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, new MySqlParameter[] { });
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql, MySqlParameter[] pms) where T : new()
        {
            using (var connection = new MySqlConnection(MySqlConn))
            {
                connection.Open();
                using (var command = new MySqlCommand(sql, connection))
                {
                    if (pms != null && pms.Length > 0)
                    {
                        command.Parameters.AddRange(pms);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        List<T> tList = new List<T>();

                        while (reader.Read()) // 遍历结果集中的每一行数据  
                        {
                            var t = ConvertToModel<T>(reader);
                            tList.Add(t);
                        }
                        return tList;
                    }
                }
            }
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql, Dictionary<string, object> pms) where T : new()
        {
            MySqlParameter[] parameters = null;

            if (pms != null)
            {
                parameters = DictionaryToMySqlParameters(pms).ToArray();
            }

            return ExecuteQuery<T>(sql, parameters);
        }


        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql) where T : new()
        {
            return ExecuteQuery<T>(sql, new MySqlParameter[] { });
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql, MySqlParameter[] pms) where T : new()
        {
            using (var connection = new MySqlConnection(MySqlConn))
            {
                connection.Open();
                using (var command = new MySqlCommand(sql, connection))
                {
                    if (pms != null && pms.Length > 0)
                    {
                        command.Parameters.AddRange(pms);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 遍历结果集中的每一行数据  
                        {

                            var t = ConvertToModel<T>(reader);

                            return t;
                        }

                        return default(T);
                    }
                }
            }
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql, Dictionary<string, object> pms) where T : new()
        {
            MySqlParameter[] parameters = null;

            if (pms != null && parameters.Length > 0)
            {
                parameters = DictionaryToMySqlParameters(pms).ToArray();
            }

            return ExecuteQueryOne<T>(sql, parameters);
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql) where T : new()
        {
            return ExecuteQueryOne<T>(sql, new MySqlParameter[] { });
        }


        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql, MySqlParameter[] pms = null)
        {
            DataTable dt = new DataTable();

            using (MySqlDataAdapter adapter = new MySqlDataAdapter(sql, MySqlConn))
            {
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                adapter.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql, Dictionary<string, object> pms)
        {
            MySqlParameter[] parameters = null;

            if (pms != null)
            {
                parameters = DictionaryToMySqlParameters(pms).ToArray();
            }

            return ExecuteQueryDataTable(sql, parameters);
        }

        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql)
        {
            MySqlParameter[] parameters = null;

            return ExecuteQueryDataTable(sql, parameters);
        }

        /// <summary>
        /// 字典转MySqlParameters
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<MySqlParameter> DictionaryToMySqlParameters(Dictionary<string, object> parameters)
        {
            List<MySqlParameter> MySqlParameters = new List<MySqlParameter>();

            foreach (var kvp in parameters)
            {
                string parameterName = kvp.Key;
                object parameterValue = kvp.Value;

                // 创建 MySqlParameter 对象  
                MySqlParameter MySqlParameter = new MySqlParameter(parameterName, parameterValue);
                MySqlParameters.Add(MySqlParameter);
            }
            return MySqlParameters;
        }

        /// <summary>
        /// 按列名转换(单条使用比较方便)
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static T ConvertToModel<T>(MySqlDataReader reader) where T : new()
        {
            T t = new T();

            PropertyInfo[] propertys = t.GetType().GetProperties();

            List<string> drColumnNames = new List<string>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                drColumnNames.Add(reader.GetName(i));
            }

            foreach (PropertyInfo pi in propertys)
            {
                if (drColumnNames.Contains(pi.Name))
                {
                    if (!pi.CanWrite)
                    {
                        continue;
                    }
                    var value = reader[pi.Name];
                    if (value != DBNull.Value)
                    {
                        pi.SetValue(t, value, null);
                    }
                }
            }
            return t;
        }
    }
}

MSSQLDBHelper

app.config中添加配置

	<connectionStrings>
		<add name="SqlConn" connectionString="Server=127.0.0.1;Database=db1;UserId=sa;Password=123456;"/>
	</connectionStrings>

MSSQLDBHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class MSSQLDBHelper
    {
        public readonly static string SqlConn = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString.ToString();

        /// <summary>
        /// 执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(SqlConn))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (pms != null && pms.Length > 0)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        int rows = cmd.ExecuteNonQuery();
                        transaction.Commit();
                        return rows > 0;
                    }
                }
            }
        }

        /// <summary>
        ///  执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, Dictionary<string, object> pms)
        {
            SqlParameter[] parameters = null;

            if (pms != null && pms.Count > 0)
            {
                parameters = DictionaryToSqlParameters(pms).ToArray();
            }

            return ExecuteNonQuery(sql, parameters);
        }

        /// <summary>
        ///  执行增、删、改的方法：ExecuteNonQuery,返回true,false
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, new SqlParameter[] { });
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql, SqlParameter[] pms) where T : new()
        {
            using (var connection = new SqlConnection(SqlConn))
            {
                connection.Open();
                using (var command = new SqlCommand(sql, connection))
                {
                    if (pms != null && pms.Length > 0)
                    {
                        command.Parameters.AddRange(pms);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        List<T> tList = new List<T>();

                        while (reader.Read()) // 遍历结果集中的每一行数据  
                        {
                            var t = ConvertToModel<T>(reader);
                            tList.Add(t);
                        }
                        return tList;
                    }
                }
            }
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql, Dictionary<string, object> pms) where T : new()
        {
            SqlParameter[] parameters = null;

            if (pms != null)
            {
                parameters = DictionaryToSqlParameters(pms).ToArray();
            }

            return ExecuteQuery<T>(sql, parameters);
        }


        /// <summary>
        /// 将查出的数据装到实体里面，返回一个List
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(string sql) where T : new()
        {
            return ExecuteQuery<T>(sql, new SqlParameter[] { });
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql, SqlParameter[] pms) where T : new()
        {
            using (var connection = new SqlConnection(SqlConn))
            {
                connection.Open();
                using (var command = new SqlCommand(sql, connection))
                {
                    if (pms != null && pms.Length > 0)
                    {
                        command.Parameters.AddRange(pms);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 遍历结果集中的每一行数据  
                        {

                            var t = ConvertToModel<T>(reader);

                            return t;
                        }

                        return default(T);
                    }
                }
            }
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql, Dictionary<string, object> pms) where T : new()
        {
            SqlParameter[] parameters = null;

            if (pms != null && parameters.Length > 0)
            {
                parameters = DictionaryToSqlParameters(pms).ToArray();
            }

            return ExecuteQueryOne<T>(sql, parameters);
        }

        /// <summary>
        /// 将查出的数据装到实体里面，返回一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T ExecuteQueryOne<T>(string sql) where T : new()
        {
            return ExecuteQueryOne<T>(sql, new SqlParameter[] { });
        }


        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql, SqlParameter[] pms = null)
        {
            DataTable dt = new DataTable();

            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, SqlConn))
            {
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                adapter.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql, Dictionary<string, object> pms)
        {
            SqlParameter[] parameters = null;

            if (pms != null)
            {
                parameters = DictionaryToSqlParameters(pms).ToArray();
            }

            return ExecuteQueryDataTable(sql, parameters);
        }

        /// <summary>
        /// 将查出的数据装到table里，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDataTable(string sql)
        {
            SqlParameter[] parameters = null;

            return ExecuteQueryDataTable(sql, parameters);
        }

        /// <summary>
        /// 字典转SqlParameters
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<SqlParameter> DictionaryToSqlParameters(Dictionary<string, object> parameters)
        {
            List<SqlParameter> SqlParameters = new List<SqlParameter>();

            foreach (var kvp in parameters)
            {
                string parameterName = kvp.Key;
                object parameterValue = kvp.Value;

                // 创建 SqlParameter 对象  
                SqlParameter SqlParameter = new SqlParameter(parameterName, parameterValue);
                SqlParameters.Add(SqlParameter);
            }
            return SqlParameters;
        }

        /// <summary>
        /// 按列名转换(单条使用比较方便)
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static T ConvertToModel<T>(SqlDataReader reader) where T : new()
        {
            T t = new T();

            PropertyInfo[] propertys = t.GetType().GetProperties();

            List<string> drColumnNames = new List<string>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                drColumnNames.Add(reader.GetName(i));
            }

            foreach (PropertyInfo pi in propertys)
            {
                if (drColumnNames.Contains(pi.Name))
                {
                    if (!pi.CanWrite)
                    {
                        continue;
                    }
                    var value = reader[pi.Name];
                    if (value != DBNull.Value)
                    {
                        pi.SetValue(t, value, null);
                    }
                }
            }
            return t;
        }
    }
}