如何Mock SqlConnection、SqlCommand？怎样重构DomainEventsMigrator支持单元测试

1. SqlConnection、SqlCommand 直接Mock的可行性 #

System.Data.SqlClient 和 Microsoft.Data.SqlClient 下的 SqlConnection、SqlCommand、SqlBulkCopy 都是密封类，没有对外暴露可用于Mock的公共接口或抽象基类，常规Mock框架（Moq、NSubstitute等）无法直接对其打桩。
如果你硬要在不重构的前提下做单元测试，只能用 Microsoft Fakes 这类可以直接修改IL做运行时打桩的工具，但是配置复杂、执行速度慢，性价比极低，完全不推荐，最优解是对现有类做简单重构解耦。

2. 现有类重构方案（兼顾可测性与架构合理性） #

核心思路是拆分职责：把「具体Sql读写逻辑」和「迁移流程编排、日志、错误处理逻辑」拆开，让迁移类只依赖抽象接口，不耦合具体的Sql实现。

第一步：定义读写抽象接口 #

// 源数据读取抽象
public interface IDomainEventSourceReader
{
    Task<IDataReader> GetEventBatchAsync(long fromId, long toId, CancellationToken cancellationToken = default);
}

// 目标数据写入抽象
public interface IDomainEventDestinationWriter
{
    Task WriteEventBatchAsync(IDataReader eventDataReader, CancellationToken cancellationToken = default);
}

第二步：实现Sql版的读写类（这部分单独做集成测试即可） #

public class SqlDomainEventSourceReader : IDomainEventSourceReader
{
    private readonly string _connectionString;
    public SqlDomainEventSourceReader(string connectionString) => _connectionString = connectionString;

    public async Task<IDataReader> GetEventBatchAsync(long fromId, long toId, CancellationToken cancellationToken = default)
    {
        using var sourceConnection = new SqlConnection(_connectionString);
        await sourceConnection.OpenAsync(cancellationToken);
        var query = @"SELECT StreamId, MessageId, EventDate, EventDataType, EventPayloadJson, IsActive
                      FROM dbo.tblExecutionPathDomainEvents WITH (NOLOCK)
                      WHERE Id >= @FromId AND Id < @ToId
                      ORDER BY Id";
        using var command = new SqlCommand(query, sourceConnection);
        command.Parameters.Add("@FromId", SqlDbType.BigInt).Value = fromId;
        command.Parameters.Add("@ToId", SqlDbType.BigInt).Value = toId;
        // 注意这里用CommandBehavior.CloseConnection，reader关闭时自动释放连接
        return await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, cancellationToken);
    }
}

public class SqlDomainEventDestinationWriter : IDomainEventDestinationWriter
{
    private readonly string _connectionString;
    public SqlDomainEventDestinationWriter(string connectionString) => _connectionString = connectionString;

    public async Task WriteEventBatchAsync(IDataReader eventDataReader, CancellationToken cancellationToken = default)
    {
        using var destinationConnection = new SqlConnection(_connectionString);
        await destinationConnection.OpenAsync(cancellationToken);
        using var bulkCopy = new SqlBulkCopy(destinationConnection)
        {
            DestinationTableName = "dbo.tblExecutionPathDomainEvents",
            BulkCopyTimeout = 3600,
            BatchSize = 1000
        };
        await bulkCopy.WriteToServerAsync(eventDataReader, cancellationToken);
    }
}

第三步：重构DomainEventsMigrator，只依赖抽象接口 #

public class DomainEventsMigrator : IDomainEventsMigrator
{
    private readonly IDomainEventSourceReader _sourceReader;
    private readonly IDomainEventDestinationWriter _destinationWriter;
    private readonly ILogger<DomainEventsMigrator> _logger;

    public DomainEventsMigrator(
        IDomainEventSourceReader sourceReader,
        IDomainEventDestinationWriter destinationWriter,
        ILogger<DomainEventsMigrator> logger)
    {
        _sourceReader = sourceReader;
        _destinationWriter = destinationWriter;
        _logger = logger;
    }

    public async Task MoveBatchAsync(MigrationBatch batch)
    {
        var stopWatch = Stopwatch.StartNew();
        IDataReader? reader = null;
        try
        {
            reader = await _sourceReader.GetEventBatchAsync(batch.FromId, batch.ToId);
            stopWatch.Stop();
            _logger.LogInformation("Read attempt successful in {Elapsed}, FromId = {FromId}, ToId = {ToId}", 
                stopWatch.Elapsed, batch.FromId, batch.ToId);

            stopWatch = Stopwatch.StartNew();
            await _destinationWriter.WriteEventBatchAsync(reader);
            stopWatch.Stop();
            _logger.LogInformation("Write attempt successful in {Elapsed}, FromId = {FromId}, ToId = {ToId}",
                stopWatch.Elapsed, batch.FromId, batch.ToId);
        }
        catch (Exception ex)
        {
            stopWatch.Stop();
            if (reader == null)
            {
                _logger.LogError(ex, "Read attempt failed in {Elapsed}, FromId = {FromId}, ToId = {ToId}",
                    stopWatch.Elapsed, batch.FromId, batch.ToId);
            }
            else
            {
                _logger.LogError(ex, "Write attempt failed in {Elapsed}, FromId = {FromId}, ToId = {ToId}",
                    stopWatch.Elapsed, batch.FromId, batch.ToId);
            }
        }
        finally
        {
            reader?.Dispose();
        }
    }
}

*额外优化：原来的日志字符串拼接改成了结构化日志参数，避免字符串分配同时方便日志检索；把AddWithValue改成显式指定SqlDbType，避免类型推导错误。

3. 重构后的单元测试方案 #

现在你只需要Mock两个抽象接口就可以完成所有迁移流程逻辑的单元测试，用任意主流Mock框架都可以快速实现，需要覆盖的核心场景：

• 正常读写成功场景：验证读写接口被调用了一次，入参和batch的FromId、ToId一致，两条Info日志正常输出
• 源数据读取失败场景：模拟SourceReader抛出异常，验证读取失败的Error日志正常输出，DestinationWriter的方法没有被调用
• 源读取成功、写入失败场景：模拟DestinationWriter抛出异常，验证写入失败的Error日志正常输出

至于SqlDomainEventSourceReader和SqlDomainEventDestinationWriter这两个具体实现类，不需要做单元测试，直接用SQL Server LocalDB或者Docker临时实例做集成测试，验证Sql语句、参数、BulkCopy配置的正确性即可，性价比远高于硬Mock。

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