C#基于Dapper实现读写仓储继承复用及CQRS适配方案咨询

核心需求回顾 #

• 拆分两类仓储：只读仓储（ICompanyReadRepository）负责查询操作，读写仓储（ICompanyRepository）负责CRUD操作
• 读写仓储需复用只读仓储的所有查询方法，避免代码冗余
• CQRS场景区分：查询请求使用CompanyReadRepository（绑定只读数据库连接），命令请求使用CompanyRepository（绑定读写数据库连接）

现有代码的潜在问题 #

1. CompanyReadRepository构造函数中同时持有只读连接和读写连接，违背了只读仓储的单一职责原则
2. 读写仓储直接继承CompanyReadRepository时，基类的连接是只读的，会导致写操作无法使用正确的读写连接
3. 接口层级不明确，未体现ICompanyRepository对ICompanyReadRepository的继承关系

优化实现方案 #

第一步：明确接口层级

先定义清晰的接口继承关系，确保读写仓储接口天然包含所有只读方法：

// 只读仓储接口：仅包含查询方法
public interface ICompanyReadRepository
{
    Task<Company> GetByIdAsync(Guid id);
    Task<IEnumerable<Company>> GetAllAsync();
    // 其他查询方法...
}

// 读写仓储接口：继承只读接口，新增写操作方法
public interface ICompanyRepository : ICompanyReadRepository
{
    Task AddAsync(Company company);
    Task UpdateAsync(Company company);
    Task DeleteAsync(Guid id);
    // 其他写操作方法...
}

第二步：重构只读仓储

让CompanyReadRepository专注于只读操作，仅依赖只读数据库连接：

public abstract class RepositoryBase
{
    protected readonly string ConnectionString;

    protected RepositoryBase(string connectionString)
    {
        ConnectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
    }

    // 通用查询执行方法（供子类调用）
    protected async Task<T> ExecuteQueryAsync<T>(string sql, object parameters = null)
    {
        using var conn = new SqlConnection(ConnectionString);
        await conn.OpenAsync();
        return await conn.QueryFirstOrDefaultAsync<T>(sql, parameters);
    }
}

public class CompanyReadRepository : RepositoryBase, ICompanyReadRepository
{
    public CompanyReadRepository(IOptions<RepositoryOptions> options)
        : base(options.Value.ReadOnlySqlConnectionString)
    {
        // 只读仓储仅初始化只读连接
    }

    public async Task<Company> GetByIdAsync(Guid id)
    {
        return await ExecuteQueryAsync<Company>(
            "SELECT Id, Name FROM Companies WHERE Id = @Id", 
            new { Id = id }
        );
    }

    public async Task<IEnumerable<Company>> GetAllAsync()
    {
        return await ExecuteQueryAsync<IEnumerable<Company>>(
            "SELECT Id, Name FROM Companies"
        );
    }
}

第三步：实现读写仓储（两种可选方案）

方案一：组合模式（推荐，符合SOLID原则）

通过注入只读仓储的方式复用查询逻辑，读写仓储专注于写操作：

public class CompanyRepository : RepositoryBase, ICompanyRepository
{
    private readonly ICompanyReadRepository _readRepository;

    public CompanyRepository(
        IOptions<RepositoryOptions> options, 
        ICompanyReadRepository readRepository)
        : base(options.Value.SqlConnectionString)
    {
        _readRepository = readRepository ?? throw new ArgumentNullException(nameof(readRepository));
    }

    // 直接复用只读仓储的查询方法
    public Task<Company> GetByIdAsync(Guid id) => _readRepository.GetByIdAsync(id);
    public Task<IEnumerable<Company>> GetAllAsync() => _readRepository.GetAllAsync();

    // 实现写操作，使用读写连接
    public async Task AddAsync(Company company)
    {
        await ExecuteNonQueryAsync(
            "INSERT INTO Companies (Id, Name) VALUES (@Id, @Name)", 
            company
        );
    }

    public async Task UpdateAsync(Company company)
    {
        await ExecuteNonQueryAsync(
            "UPDATE Companies SET Name = @Name WHERE Id = @Id", 
            company
        );
    }

    public async Task DeleteAsync(Guid id)
    {
        await ExecuteNonQueryAsync(
            "DELETE FROM Companies WHERE Id = @Id", 
            new { Id = id }
        );
    }

    // 通用非查询执行方法（供写操作调用）
    private async Task<int> ExecuteNonQueryAsync(string sql, object parameters = null)
    {
        using var conn = new SqlConnection(ConnectionString);
        await conn.OpenAsync();
        return await conn.ExecuteAsync(sql, parameters);
    }
}

方案二：继承模式（适合需要大量复用查询逻辑的场景）

调整只读仓储的连接管理，让子类可以替换连接字符串：

// 修改CompanyReadRepository，开放连接字符串的重写权限
public class CompanyReadRepository : RepositoryBase, ICompanyReadRepository
{
    protected virtual string ActiveConnectionString => ConnectionString;

    public CompanyReadRepository(IOptions<RepositoryOptions> options)
        : base(options.Value.ReadOnlySqlConnectionString)
    {
    }

    // 查询方法使用ActiveConnectionString
    public async Task<Company> GetByIdAsync(Guid id)
    {
        return await ExecuteQueryAsync<Company>(
            "SELECT Id, Name FROM Companies WHERE Id = @Id", 
            new { Id = id },
            ActiveConnectionString
        );
    }

    // 调整RepositoryBase的ExecuteQueryAsync，支持传入连接字符串
    protected async Task<T> ExecuteQueryAsync<T>(string sql, object parameters = null, string connectionString = null)
    {
        string connString = connectionString ?? ConnectionString;
        using var conn = new SqlConnection(connString);
        await conn.OpenAsync();
        return await conn.QueryFirstOrDefaultAsync<T>(sql, parameters);
    }
}

// 读写仓储继承并替换连接字符串
public class CompanyRepository : CompanyReadRepository, ICompanyRepository
{
    private readonly string _writeConnectionString;

    public CompanyRepository(IOptions<RepositoryOptions> options)
        : base(options)
    {
        _writeConnectionString = options.Value.SqlConnectionString;
    }

    // 重写ActiveConnectionString，确保写操作使用读写连接
    protected override string ActiveConnectionString => _writeConnectionString;

    // 实现写操作
    public async Task AddAsync(Company company)
    {
        await ExecuteNonQueryAsync(
            "INSERT INTO Companies (Id, Name) VALUES (@Id, @Name)", 
            company,
            _writeConnectionString
        );
    }

    // 其他写操作同理，复用基类的连接逻辑
    public async Task UpdateAsync(Company company)
    {
        await ExecuteNonQueryAsync(
            "UPDATE Companies SET Name = @Name WHERE Id = @Id", 
            company,
            _writeConnectionString
        );
    }

    public async Task DeleteAsync(Guid id)
    {
        await ExecuteNonQueryAsync(
            "DELETE FROM Companies WHERE Id = @Id", 
            new { Id = id },
            _writeConnectionString
        );
    }

    // 新增通用非查询方法
    private async Task<int> ExecuteNonQueryAsync(string sql, object parameters = null, string connectionString = null)
    {
        string connString = connectionString ?? ConnectionString;
        using var conn = new SqlConnection(connString);
        await conn.OpenAsync();
        return await conn.ExecuteAsync(sql, parameters);
    }
}

方案选择建议 #

• 组合模式：职责划分清晰，只读仓储和读写仓储各自专注单一功能，易于测试和维护，优先推荐
• 继承模式：适合查询逻辑复杂、需要大量复用的场景，但需注意连接字符串的管理，避免混淆只读/读写连接

内容的提问来源于stack exchange，提问作者Marcos Varela