您现在的位置是：网站首页> 编程资料编程资料

.Net Core下使用Dapper的方法_自学过程_

2023-05-24 375人已围观

简介 .Net Core下使用Dapper的方法_自学过程_

一、前言

关于什么是Dapper（详细入口），在此不做赘述；本文仅对Dapper在.Net Core中的使用作扼要说明，所陈代码以示例讲解为主，乃抛砖引玉，开发者可根据自身需要进行扩展和调整；其中如有疏漏之处，望不吝斧正。

不了解Dapper的朋友可以看这篇文章：ORM框架之Dapper简介和性能测试

二、Dapper环境搭建

当前以.Net Core WebAPI或MVC项目为例，框架版本为.NET 5.0，相关NuGet包引用如下：

Install-Package Dapper

Install-Package Dapper.Contrib

Install-Package Dapper.SqlBuilder

Install-Package System.Data.SqlClient

其中Dapper.Contrib和Dapper.SqlBuilder为Dapper的扩展，当然，Dapper的扩展还有如Dapper.Rainbow等其他包，根据自身需要引用，对相关引用作下说明：

Dapper：不言而喻；
Dapper.Contrib：可使用对象进行数据表的增删改查，免却SQL语句的编写；
Dapper.SqlBuilder：可以方便动态构建SQL语句，如Join、SELECT、Where、OrderBy等等；
System.Data.SqlClient：由于示例数据库为Sql Server，如MySql则引用MySql.Data；

对于Dapper.Contrib实体配置选项，以Product类为例，作扼要说明如下：

 [Table("Product")] public class Product { [Key] public int Id { get; set; } public string Name{ get; set; } public string Description { get; set; } public decimal Price { get; set; } public DateTime CreateTime { get; set; } }

对于实体配置项，有如下几个主要项：

Table：指定数据库表名，可忽略；
Key：指定为自动增长主键；
ExplicitKey：指定非自动增长主键，如guid；
Computed：计算列属性，Insert、Update操作将忽略此列；
Write：是否可写入，true/false，如[Write(false)]，false时Insert、Update操作将忽略此列，比如可扩展局部类作数据表额外查询字段使用；

对于数据表对象实体，可结合T4模板生成即可。

三、Dapper封装

　　关于Dapper数据访问，这里参考Github上的某示例（入口：https://github.com/EloreTec/UnitOfWorkWithDapper），作修改调整封装如下：

定义DapperDBContext类

 public abstract class DapperDBContext : IContext { private IDbConnection _connection; private IDbTransaction _transaction; private int? _commandTimeout = null; private readonly DapperDBContextOptions _options; public bool IsTransactionStarted { get; private set; } protected abstract IDbConnection CreateConnection(string connectionString); protected DapperDBContext(IOptions optionsAccessor) { _options = optionsAccessor.Value; _connection = CreateConnection(_options.Configuration); _connection.Open(); DebugPrint("Connection started."); } #region Transaction public void BeginTransaction() { if (IsTransactionStarted) throw new InvalidOperationException("Transaction is already started."); _transaction = _connection.BeginTransaction(); IsTransactionStarted = true; DebugPrint("Transaction started."); } public void Commit() { if (!IsTransactionStarted) throw new InvalidOperationException("No transaction started."); _transaction.Commit(); _transaction = null; IsTransactionStarted = false; DebugPrint("Transaction committed."); } public void Rollback() { if (!IsTransactionStarted) throw new InvalidOperationException("No transaction started."); _transaction.Rollback(); _transaction.Dispose(); _transaction = null; IsTransactionStarted = false; DebugPrint("Transaction rollbacked and disposed."); } #endregion Transaction #region Dapper.Contrib.Extensions public async Task GetAsync(int id) where T : class, new() { return await _connection.GetAsync(id, _transaction, _commandTimeout); } public async Task GetAsync(string id) where T : class, new() { return await _connection.GetAsync(id, _transaction, _commandTimeout); } public async Task> GetAllAsync() where T : class, new() { return await _connection.GetAllAsync(); } public long Insert(T model) where T : class, new() { return _connection.Insert(model, _transaction, _commandTimeout); } public async Task InsertAsync(T model) where T : class, new() { return await _connection.InsertAsync(model, _transaction, _commandTimeout); } public bool Update(T model) where T : class, new() { return _connection.Update(model, _transaction, _commandTimeout); } public async Task UpdateAsync(T model) where T : class, new() { return await _connection.UpdateAsync(model, _transaction, _commandTimeout); } public async Task> PageAsync(long pageIndex, long pageSize, string sql, object param = null) { DapperPage.BuildPageQueries((pageIndex - 1) * pageSize, pageSize, sql, out string sqlCount, out string sqlPage); var result = new Page { CurrentPage = pageIndex, ItemsPerPage = pageSize, TotalItems = await _connection.ExecuteScalarAsync(sqlCount, param) }; result.TotalPages = result.TotalItems / pageSize; if ((result.TotalItems % pageSize) != 0) result.TotalPages++; result.Items = await _connection.QueryAsync(sqlPage, param); return result; } #endregion #region Dapper Execute & Query public int ExecuteScalar(string sql, object param = null, CommandType commandType = CommandType.Text) { return _connection.ExecuteScalar(sql, param, _transaction, _commandTimeout, commandType); } public async Task ExecuteScalarAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.ExecuteScalarAsync(sql, param, _transaction, _commandTimeout, commandType); } public int Execute(string sql, object param = null, CommandType commandType = CommandType.Text) { return _connection.Execute(sql, param, _transaction, _commandTimeout, commandType); } public async Task ExecuteAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, commandType); } public IEnumerable Query(string sql, object param = null, CommandType commandType = CommandType.Text) { return _connection.Query(sql, param, _transaction, true, _commandTimeout, commandType); } public async Task> QueryAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.QueryAsync(sql, param, _transaction, _commandTimeout, commandType); } public T QueryFirstOrDefault(string sql, object param = null, CommandType commandType = CommandType.Text) { return _connection.QueryFirstOrDefault(sql, param, _transaction, _commandTimeout, commandType); } public async Task QueryFirstOrDefaultAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.QueryFirstOrDefaultAsync(sql, param, _transaction, _commandTimeout, commandType); } public IEnumerable Query(string sql, Func map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text) { return _connection.Query(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType); } public async Task> QueryAsync(string sql, Func map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text) { return await _connection.QueryAsync(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType); } public async Task QueryMultipleAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.QueryMultipleAsync(sql, param, _transaction, _commandTimeout, commandType); } #endregion Dapper Execute & Query public void Dispose() { if (IsTransactionStarted) Rollback(); _connection.Close(); _connection.Dispose(); _connection = null; DebugPrint("Connection closed and disposed."); } private void DebugPrint(string message) { #if DEBUG Debug.Print(">>> UnitOfWorkWithDapper - Thread {0}: {1}", Thread.CurrentThread.ManagedThreadId, message); #endif } }

以上代码涵盖了Dapper访问数据库的基本操作，分同步和异步，其中大部分不作赘述，着重说下分页部分；

异步分页构建（PageAsync）

分页这里为方便调用，只需传入要查询的Sql语句（如：SELECT * FROM Table，必须带Order BY）、页索引、页大小即可；

至于具体如何构建的，这里参照某小型ORM工具PetaPoco，抽取相关代码如下，有兴趣的同学也可以自行改造：

 public class Page { ///  /// The current page number contained in this page of result set /// 
 public long CurrentPage { get; set; } ///  /// The total number of pages in the full result set /// 
 public long TotalPages { get; set; } ///  /// The total number of records in the full result set /// 
 public long TotalItems { get; set; } ///  /// The number of items per page /// 
 public long ItemsPerPage { get; set; } ///  /// The actual records on this page /// 
 public IEnumerable Items { get; set; } //public List Items { get; set; } } public class DapperPage { public static void BuildPageQueries(long skip, long take, string sql, out string sqlCount, out string sqlPage) { // Split the SQL if (!PagingHelper.SplitSQL(sql, out PagingHelper.SQLParts parts)) throw new Exception("Unable to parse SQL statement for paged query"); sqlPage = BuildPageSql.BuildPageQuery(skip, take, parts); sqlCount = parts.sqlCount; } } static class BuildPageSql { public static string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts) { parts.sqlSelectRemo
                提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                
                
                                            上一篇：ORM框架之Dapper简介和性能测试_基础应用_
                                                                下一篇：.NET 6开发TodoList应用之实现查询分页_实用技巧_

您现在的位置是：网站首页> 编程资料编程资料

.Net Core下使用Dapper的方法_自学过程_

目录

一、前言

二、Dapper环境搭建

三、Dapper封装

定义DapperDBContext类

异步分页构建（PageAsync）

相关内容

点击排行

本栏推荐

猜你喜欢