ABP框架+Dapper执行原生sql
之前发表一个ABP框架+EF执行原生sql，后来自己想了想安装Dapper，用Dapper执行原生sql也可以，并且效率会比EF优一点。
一、首先安装Dapper
有多种方案安装，nuget包安装，或者执行命令
dotnet add package Dapper二、项目中使用
注意在当前文件引用Dapper
using Dapper;注入你要操作表的仓储
private readonly IRepository<WorkOrder, Guid> _repository; public WorkOrderExtensionService( IRepository<WorkOrder, Guid> repository){ _repository = repository; }写好自己要的sql业务，然后通过Dapper执行
var parameters = new { TenantId = _currentTenant.Id, SkipCount = input.SkipCount, PageSize = input.MaxResultCount, OrderId = orderId, OrderNo = input.OrderNo, }; var sql = @" select w.SeriesNo,w.Seq, CASE WHEN r.SnCode is null THEN 0 ELSE 1 END `State` from JG_WorkOrderTasks w left join ( select SnCode from JG_WorkOrderDistributionRecords where WorkOrderNumber = @OrderNo and DispatchStatus = 1 group by SnCode ) r on w.SeriesNo = r.SnCode where w.OrderId = @OrderId and IsDeleted = 0 and TenantId = @TenantId order by `State`, Seq limit @PageSize offset @SkipCount"; var countSql = @" select count(0) from JG_WorkOrderTasks where OrderId = @OrderId and IsDeleted = 0 and TenantId = @TenantId"; var dbContext = await _repository.GetDbContextAsync(); var connection = dbContext.Database.GetDbConnection(); if (connection.State != ConnectionState.Open) await connection.OpenAsync(); var list = await connection.QueryAsync(sql, parameters); var snCount = await connection.ExecuteScalarAsync<int>(countSql, parameters); List<HitList> resData = list.Select(c => new HitList { SnCode = c.SeriesNo, Seq = c.Seq, HitState = c.State == 0 ? "未打标" : "已打标" }).ToList();三、解析一下上面代码
数据库上下文和连接：
var dbContext = await _repository.GetDbContextAsync(); var connection = dbContext.Database.GetDbConnection(); if (connection.State != ConnectionState.Open) await connection.OpenAsync();- 通过
_repository获取数据库上下文并获取数据库连接。 - 检查连接状态，如果未打开，则异步打开连接。
执行查询：
var list = await connection.QueryAsync(sql, parameters); var snCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);- 使用 Dapper 的
QueryAsync方法执行主查询，返回一个结果列表。 - 使用
ExecuteScalarAsync<int>执行计数查询，返回总记录数。