c# DbHelper的封装

2024-03-13 06:04
文章标签 c# 封装 .net netcore dbhelper

本文主要是介绍c# DbHelper的封装，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

c# DbHelper的封装

基于ADO.NET框架，封装了适用于多个关系型数据库的DbHelper。通过简洁明了的代码，实现了对各种数据库的高效操作。

public class DbHelper{private readonly DataBase _dataBase;public DbHelper(DataBase dataBase){_dataBase = dataBase;}public DataBase GetDataBase(){return _dataBase;}public DbConnection GetDbConnection(){var conn = _dataBase.CreationConnection();if (conn.State == ConnectionState.Closed){conn.Open();}return conn;}/// <summary>/// 执行语句/// </summary>/// <param name="sql">sql语句</param>/// <param name="cmdParms">参数</param>/// <returns></returns>public int Execute(string sql, params DbParameter[] cmdParms){using (DbConnection connection = GetDbConnection()){using (DbCommand cmd = connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (DbException e){throw e;}}}}/// <summary>/// 批量查询/// </summary>/// <param name="sql">sql语句</param>/// <param name="cmdParms">参数</param>/// <returns></returns>public DataSet Query(string sql, params DbParameter[] cmdParms){using (DbConnection connection = GetDbConnection()){DataSet ds = new DataSet();try{DbProviderFactory factory = DbProviderFactories.GetFactory(connection);DbCommand command = factory.CreateCommand();PrepareCommand(command, connection, null, sql, cmdParms);DbDataAdapter adapter = factory.CreateDataAdapter();adapter.SelectCommand = command;adapter.Fill(ds, "ds");adapter.Dispose();command.Dispose();}catch (DbException ex){throw ex;}return ds;}}/// <summary>/// 批量查询/// </summary>/// <typeparam name="T"></typeparam>/// <param name="sql">sql语句</param>/// <param name="reader">数据读取器</param>/// <param name="cmdParms">参数</param>/// <returns></returns>/// <exception cref="Exception"></exception>public List<T> Query<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms){if (reader == null)throw new Exception("数据读取器是空的!");List<T> list = new List<T>();using (DbConnection connection = GetDbConnection()){using (DbCommand cmd = connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);DbDataReader myReader = cmd.ExecuteReader();cmd.Parameters.Clear();while (myReader.Read()){list.Add(reader(myReader));}myReader.Close();}catch (DbException e){throw e;}}}return list;}/// <summary>/// 单个查询/// </summary>/// <typeparam name="T"></typeparam>/// <param name="sql">sql语句</param>/// <param name="reader">数据读取器</param>/// <param name="cmdParms">参数</param>/// <returns></returns>/// <exception cref="Exception"></exception>public T QueryFirstOrDefault<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms){if (reader == null){throw new Exception("数据读取器是空的!");}var model = default(T);using (DbConnection connection = GetDbConnection()){using (DbCommand cmd = connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);DbDataReader myReader = cmd.ExecuteReader();cmd.Parameters.Clear();if (myReader.Read())model = reader(myReader);myReader.Close();}catch (DbException e){throw e;}}}return model;}/// <summary>/// 执行存储过程/// </summary>/// <param name="storedProcName">存储过程名</param>/// <param name="parameters">存储过程参数</param>/// <returns></returns>public DataSet RunProcedure(string storedProcName, DbParameter[] parameters){using (DbConnection connection = GetDbConnection()){DataSet dataSet = new DataSet();connection.Open();DbDataAdapter sqlDA = DbProviderFactories.GetFactory(connection).CreateDataAdapter();sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);sqlDA.Fill(dataSet, "ds");sqlDA.SelectCommand.Dispose();sqlDA.Dispose();return dataSet;}}/// <summary>/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )/// </summary>/// <param name="storedProcName">存储过程名</param>/// <param name="parameters">存储过程参数</param>/// <returns>SqlDataReader</returns>public DbDataReader RunProcedureToReader(string storedProcName, DbParameter[] parameters){using (DbConnection connection = GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);command.CommandType = CommandType.StoredProcedure;returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();return returnReader;}}/// <summary>/// 执行存储过程/// </summary>/// <param name="storedProcName">存储过程名</param>/// <param name="parameters">存储过程参数</param>/// <returns>SqlDataReader</returns>public T RunProcedure<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters){if (reader == null){throw new Exception("数据读取器是空的!");}T t = default(T);using (DbConnection connection = GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);command.CommandType = CommandType.StoredProcedure;returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();if (returnReader.Read())t = reader(returnReader);returnReader.Close();}return t;}/// <summary>/// 执行存储过程/// </summary>/// <param name="storedProcName">存储过程名</param>/// <param name="parameters">存储过程参数</param>/// <returns>SqlDataReader</returns>public List<T> RunProcedureToList<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters){if (reader == null){throw new Exception("数据读取器是空的!");}List<T> list = new List<T>();using (DbConnection connection = GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);command.CommandType = CommandType.StoredProcedure;returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();while (returnReader.Read())list.Add(reader(returnReader));returnReader.Close();}return list;}/// <summary>/// 返回首行首列/// </summary>/// <param name="sql">sql语句</param>/// <param name="cmdParms">参数</param>/// <returns></returns>public object ExecuteScalar(string sql, params DbParameter[] cmdParms){object result = null;using (DbConnection connection = GetDbConnection()){using (DbCommand cmd = connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);result = cmd.ExecuteScalar();}catch (DbException e){throw e;}}}return result;}/// <summary>/// 分页列表/// </summary>/// <typeparam name="T"></typeparam>/// <param name="tablename">表名(可以自定)</param>/// <param name="page">分页信息</param>/// <param name="reader">读取器</param>/// <param name="where">条件</param>/// <param name="field">字段</param>/// <param name="order">排序</param>public List<T> QueryWithPage<T>(string tablename, PageInfo page, Func<IDataReader, T> reader, string where = "", string field = "*", string order = "", params DbParameter[] cmdParms){long offset = page.Index * page.PageSize;string sql = "SELECT " + field + " FROM " + tablename;sql = ListPageSql(sql, where, order);sql = sql + " " + Limit(offset, page.PageSize);string sql2 = "SELECT COUNT(0) FROM " + tablename;sql2 = ListPageSql(sql2, where, "");string sql3 = sql + ";" + sql2;List<T> list = new List<T>();using (DbConnection conn = GetDbConnection()){using (DbCommand cmd = conn.CreateCommand()){try{PrepareCommand(cmd, conn, null, sql3, cmdParms);DbDataReader myReader = cmd.ExecuteReader();cmd.Parameters.Clear();while (myReader.Read()){list.Add(reader(myReader));}if (myReader.NextResult() && myReader.Read())page.Count = myReader.GetInt64Ex(0);myReader.Close();}catch (MySqlException e){throw new Exception(e.Message);}}}return list;}/// <summary>/// 组装分页sql/// </summary>/// <param name="sql">基础sql</param>/// <param name="where">条件</param>/// <param name="order">排序</param>/// <returns></returns>private string ListPageSql(string sql, string where, string order){if (!string.IsNullOrEmpty(where)){sql = sql + " WHERE " + where;}if (!string.IsNullOrEmpty(order)){sql = sql + " " + order;}return sql;}/// <summary>/// 分页/// </summary>/// <param name="offset">偏移</param>/// <param name="size">每页显示数据尺寸</param>/// <returns></returns>/// <exception cref="Exception"></exception>public string Limit(long offset, long size){if (offset == -1){if (_dataBase.DbType != DbBaseType.SqlServer){return "LIMIT " + size;}}else{if (_dataBase.DbType == DbBaseType.MySql){return string.Format("LIMIT {0},{1}", offset, size);}if (_dataBase.DbType == DbBaseType.PostgreSql || _dataBase.DbType == DbBaseType.Sqlite){return string.Format(" LIMIT {0} OFFSET {1}", size, offset);}}throw new Exception("暂时不支持其它分页语法");}public DbParameter CreateDbParameter(string parameterName, DbType dbType, object value){using(DbConnection connection = GetDbConnection()){DbParameter dbParameter = DbProviderFactories.GetFactory(connection).CreateParameter();dbParameter.ParameterName = parameterName;dbParameter.DbType = dbType;dbParameter.Value = value;return dbParameter;}}protected void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms){cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null)cmd.Transaction = trans;cmd.CommandType = CommandType.Text;SetParameters(cmd, cmdParms);}private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters){DbCommand command = connection.CreateCommand();command.CommandText = storedProcName;command.CommandType = CommandType.StoredProcedure;SetParameters(command, parameters);return command;}private void SetParameters(DbCommand command, DbParameter[] cmdParms){if (cmdParms != null){foreach (var parameter in cmdParms){if ((parameter.Direction == ParameterDirection.InputOutput||parameter.Direction == ParameterDirection.Input)&&(parameter.Value == null)){parameter.Value = DBNull.Value;}command.Parameters.Add(parameter);}}}}

这篇关于c# DbHelper的封装的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


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

相关文章

C#使用SendMessage实现进程间通信的示例代码

C#使用SendMessage实现进程间通信的示例代码

《C#使用SendMessage实现进程间通信的示例代码》在软件开发中,进程间通信（IPC）是关键技术之一,C#通过调用WindowsAPI的SendMessage函数实现这一功能,本文将通过实例介绍... 目录第一章：SendMessage的底层原理揭秘第二章：构建跨进程通信桥梁2.1 定义通信协议2.2
阅读更多...
C#实现千万数据秒级导入的代码

C#实现千万数据秒级导入的代码

《C#实现千万数据秒级导入的代码》在实际开发中excel导入很常见,现代社会中很容易遇到大数据处理业务,所以本文我就给大家分享一下千万数据秒级导入怎么实现,文中有详细的代码示例供大家参考,需要的朋友可... 目录前言一、数据存储二、处理逻辑优化前代码处理逻辑优化后的代码总结前言在实际开发中excel导入很
阅读更多...
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
阅读更多...
C#实现一键批量合并PDF文档

C#实现一键批量合并PDF文档

《C#实现一键批量合并PDF文档》这篇文章主要为大家详细介绍了如何使用C#实现一键批量合并PDF文档功能,文中的示例代码简洁易懂,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言效果展示功能实现1、添加文件2、文件分组（书签）3、定义页码范围4、自定义显示5、定义页面尺寸6、PDF批量合并7、其他方法
阅读更多...
C#下Newtonsoft.Json的具体使用

C#下Newtonsoft.Json的具体使用

《C#下Newtonsoft.Json的具体使用》Newtonsoft.Json是一个非常流行的C#JSON序列化和反序列化库,它可以方便地将C#对象转换为JSON格式,或者将JSON数据解析为C#对... 目录安装 Newtonsoft.json基本用法1. 序列化 C# 对象为 JSON2. 反序列化
阅读更多...
C#文件复制异常："未能找到文件"的解决方案与预防措施

C#文件复制异常："未能找到文件"的解决方案与预防措施

《C#文件复制异常：未能找到文件的解决方案与预防措施》在C#开发中,文件操作是基础中的基础,但有时最基础的File.Copy()方法也会抛出令人困惑的异常,当targetFilePath设置为D:2... 目录一个看似简单的文件操作问题问题重现与错误分析错误代码示例错误信息根本原因分析全面解决方案1. 确保
阅读更多...
基于C#实现PDF转图片的详细教程

基于C#实现PDF转图片的详细教程

《基于C#实现PDF转图片的详细教程》在数字化办公场景中,PDF文件的可视化处理需求日益增长,本文将围绕Spire.PDFfor.NET这一工具,详解如何通过C#将PDF转换为JPG、PNG等主流图片... 目录引言一、组件部署二、快速入门：PDF 转图片的核心 C# 代码三、分辨率设置 - 清晰度的决定因
阅读更多...
C# LiteDB处理时间序列数据的高性能解决方案

C# LiteDB处理时间序列数据的高性能解决方案

《C#LiteDB处理时间序列数据的高性能解决方案》LiteDB作为.NET生态下的轻量级嵌入式NoSQL数据库,一直是时间序列处理的优选方案,本文将为大家大家简单介绍一下LiteDB处理时间序列数... 目录为什么选择LiteDB处理时间序列数据第一章：LiteDB时间序列数据模型设计1.1 核心设计原则
阅读更多...
C#高效实现Word文档内容查找与替换的6种方法

C#高效实现Word文档内容查找与替换的6种方法

《C#高效实现Word文档内容查找与替换的6种方法》在日常文档处理工作中,尤其是面对大型Word文档时,手动查找、替换文本往往既耗时又容易出错,本文整理了C#查找与替换Word内容的6种方法,大家可以... 目录环境准备方法一：查找文本并替换为新文本方法二：使用正则表达式查找并替换文本方法三：将文本替换为图
阅读更多...
C#使用Spire.XLS快速生成多表格Excel文件

C#使用Spire.XLS快速生成多表格Excel文件

《C#使用Spire.XLS快速生成多表格Excel文件》在日常开发中,我们经常需要将业务数据导出为结构清晰的Excel文件,本文将手把手教你使用Spire.XLS这个强大的.NET组件,只需几行C#... 目录一、Spire.XLS核心优势清单1.1 性能碾压：从3秒到0.5秒的质变1.2 批量操作的优雅
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2