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> 执行计数查询，返回总记录数。

使用 Dapper 作为 ORM，使得 SQL 查询更为简洁高效，同时通过参数化查询提高了安全性（防止 SQL 注入）。整体结构清晰，符合常见的 .NET 开发模式。