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

2024-01-05 07:12
文章标签 sql mysql 系列 core asp net server 帮助 database dbhelper

本文主要是介绍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;}}
}

这篇关于Asp .Net Core系列：基于MySQL的DBHelper帮助类和SQL Server的DBHelper帮助类的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/572076。

相关文章

MySQL的JDBC编程详解

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》：本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq
阅读更多...
java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》：本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏
阅读更多...
Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
C#使用Spire.Doc for .NET实现HTML转Word的高效方案

C#使用Spire.Doc for .NET实现HTML转Word的高效方案

《C#使用Spire.Docfor.NET实现HTML转Word的高效方案》在Web开发中,HTML内容的生成与处理是高频需求,然而,当用户需要将HTML页面或动态生成的HTML字符串转换为Wor... 目录引言一、html转Word的典型场景与挑战二、用 Spire.Doc 实现 HTML 转 Word1
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
Python 基于http.server模块实现简单http服务的代码举例

Python 基于http.server模块实现简单http服务的代码举例

《Python基于http.server模块实现简单http服务的代码举例》Pythonhttp.server模块通过继承BaseHTTPRequestHandler处理HTTP请求,使用Threa... 目录测试环境代码实现相关介绍模块简介类及相关函数简介参考链接测试环境win11专业版python
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2