为什么你的 Dapper 项目越维护越崩溃？

云中小生

发布于 2026-07-21 13:58:02

240

前两章把 Dapper 的十八般武艺都讲了。但你如果直接把 using IDbConnection散落在几十个 Service 和 Controller 里，项目跑几个月之后就会变成一坨很难维护的东西。

我做过一件现在想起来还后悔的事：一个对接多个外部系统的网关项目，Dapper 代码到处写，连接字符串硬编码在七八个地方。后来数据库迁移换连接方式，改了整整两天，中间还漏了两个地方，上线后半夜告警。

从那之后我所有 Dapper 项目都套仓储模式。这章就把这套模板完整给你。

模板分五步

第一步：通用接口

所有实体共用的能力，抽到泛型接口里。就这几个方法：

using System.Data;

public interface IRepository<T> where T : class
{
    Task<List<T>> QueryListAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null);
    Task<T?> QueryFirstAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null);
    Task<int> ExecuteAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null);
    IDbTransaction BeginTransaction();
    Task<IDbTransaction> BeginTransactionAsync();
}

第二步：通用实现

核心逻辑就一个判断：传了事务就复用现有连接，没传事务就新建并用 using释放。连接字符串从 IConfiguration读，只写一次。

using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

public class DapperRepository<T> : IRepository<T> where T : class
{
    private readonly string _connectionString;

    public DapperRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("TestDB")
            ?? throw new InvalidOperationException("未配置 TestDB 连接字符串");
    }

    // protected：派生类做联表映射时也能用
    protected IDbConnection GetDbConnection() => new SqlConnection(_connectionString);

    public async Task<List<T>> QueryListAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null)
    {
        if (transaction != null)
        {
            var data = await transaction.Connection.QueryAsync<T>(sql, param, transaction);
            return data.ToList();
        }
        using IDbConnection db = GetDbConnection();
        await db.OpenAsync();
        var result = await db.QueryAsync<T>(sql, param);
        return result.ToList();
    }

    public async Task<T?> QueryFirstAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null)
    {
        if (transaction != null)
            return await transaction.Connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction);
        using IDbConnection db = GetDbConnection();
        await db.OpenAsync();
        return await db.QueryFirstOrDefaultAsync<T>(sql, param);
    }

    public async Task<int> ExecuteAsync(string sql, object? param = null, 
        IDbTransaction? transaction = null)
    {
        if (transaction != null)
            return await transaction.Connection.ExecuteAsync(sql, param, transaction);
        using IDbConnection db = GetDbConnection();
        await db.OpenAsync();
        return await db.ExecuteAsync(sql, param);
    }

    public IDbTransaction BeginTransaction()
    {
        IDbConnection db = GetDbConnection();
        db.Open();
        return db.BeginTransaction();
    }

    public async Task<IDbTransaction> BeginTransactionAsync()
    {
        IDbConnection db = GetDbConnection();
        await db.OpenAsync();
        return await db.BeginTransactionAsync();
    }
}

GetDbConnection()用 protected而不是 private——这个细节很重要。派生类做联表映射时需要自己开连接，private就没法调。我之前犯过这个错，UserRepository里的联表方法编译报错才反应过来。

第三步：业务仓储

通用仓储只提供底层能力，业务相关的 SQL 收敛到自己专属的仓储里。

接口：

public interface IUserRepository : IRepository<User>
{
    Task<User?> GetByIdAsync(int id);
    Task<List<User>> GetUserByMinAgeAsync(int minAge);
    Task<int> AddUserAsync(User user);
    Task<int> UpdateUserNameAsync(int userId, string newName);
    Task<int> DeleteUserAsync(int userId);
    Task<List<User>> GetUserWithAddressListAsync();
}

实现——继承 DapperRepository<User>，只写业务 SQL：

public class UserRepository : DapperRepository<User>, IUserRepository
{
    public UserRepository(IConfiguration config) : base(config) { }

    public async Task<User?> GetByIdAsync(int id)
        => await QueryFirstAsync("SELECT * FROM Users WHERE Id = @Id", new { Id = id });

    public async Task<int> AddUserAsync(User user)
        => await ExecuteAsync("INSERT INTO Users (Name, Email, Age) VALUES (@Name, @Email, @Age)", user);

    public async Task<List<User>> GetUserByMinAgeAsync(int minAge)
        => await QueryListAsync("SELECT * FROM Users WHERE Age > @Age", new { Age = minAge });

    public async Task<int> UpdateUserNameAsync(int userId, string newName)
        => await ExecuteAsync("UPDATE Users SET Name = @Name WHERE Id = @Id", 
            new { Name = newName, Id = userId });

    public async Task<int> DeleteUserAsync(int userId)
        => await ExecuteAsync("DELETE FROM Users WHERE Id = @Id", new { Id = userId });

    // 联表查询：通过 protected 方法直接操作连接
    public async Task<List<User>> GetUserWithAddressListAsync()
    {
        string sql = @"SELECT u.Id, u.Name, a.City, a.Street 
                       FROM Users u JOIN Addresses a ON u.Id = a.UserId";
        using IDbConnection db = GetDbConnection();
        await db.OpenAsync();
        var data = await db.QueryAsync<User, Address, User>(sql,
            (user, addr) => { user.Address = addr; return user; },
            splitOn: "City");
        return data.Distinct().ToList();
    }
}

第四步：DI 注册

Program.cs：

builder.Services.AddScoped(typeof(IRepository<>), typeof(DapperRepository<>));
builder.Services.AddScoped<IUserRepository, UserRepository>();

第五步：Controller 直接用

[ApiController]
[Route("api/user")]
public class UserController : ControllerBase
{
    private readonly IUserRepository _repo;

    public UserController(IUserRepository repo) => _repo = repo;

    [HttpGet("{id}")]
    public async Task<IActionResult> Get(int id)
    {
        var user = await _repo.GetByIdAsync(id);
        return user == null ? NotFound() : Ok(user);
    }

    [HttpPost("batch")]
    public async Task<IActionResult> Batch()
    {
        var tran = await _repo.BeginTransactionAsync();
        try
        {
            await _repo.ExecuteAsync("INSERT INTO Users(Name) VALUES (@Name)",
                new { Name = "批量用户" }, tran);
            await tran.CommitAsync();
            return Ok("提交成功");
        }
        catch (Exception ex)
        {
            await tran.RollbackAsync();
            return BadRequest($"已回滚：{ex.Message}");
        }
    }
}

连接字符串

{
  "ConnectionStrings": {
    "TestDB": "Server=.;Database=TestDB;Integrated Security=True;TrustServerCertificate=True"
  }
}

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-20，如有侵权请联系 cloudcommunity@tencent.com 删除

连接

本文分享自 .NET修仙日记微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度