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 开发模式。