如何为使用SqlConnection的.NET Core数据访问层编写可Mock单元测试

问题根源 #

你的数据访问层（DAL）直接实例化SqlConnection和SqlCommand这类具体实现类，导致无法通过Mock隔离真实数据库；而测试代码中使用的IDbConnection/IDbCommand基础接口本身不包含异步方法（OpenAsync/ExecuteReaderAsync是SqlClient专属的扩展方法，不属于基础IDb接口定义），因此出现编译错误。

两种可行解决方案 #

方案1：封装数据库操作抽象（最佳实践） #

通过自定义抽象接口封装数据库连接、命令的创建与执行，让DAL依赖抽象而非具体实现，彻底解耦SqlClient。

步骤1：定义抽象接口

// 连接工厂接口，负责创建并打开数据库连接
public interface IDbConnectionFactory
{
    Task<IDbConnection> CreateAndOpenConnectionAsync(string connectionString);
}

// 命令包装接口，封装命令的配置与执行
public interface IDbCommandWrapper : IDisposable
{
    CommandType CommandType { get; set; }
    string CommandText { get; set; }
    void AddParameters(SqlParameter[] parameters);
    Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior);
}

步骤2：实现SqlClient版本的封装类

public class SqlConnectionFactory : IDbConnectionFactory
{
    public async Task<IDbConnection> CreateAndOpenConnectionAsync(string connectionString)
    {
        var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        return connection;
    }
}

public class SqlCommandWrapper : IDbCommandWrapper
{
    private readonly SqlCommand _command;

    public SqlCommandWrapper(SqlCommand command)
    {
        _command = command;
    }

    public CommandType CommandType
    {
        get => _command.CommandType;
        set => _command.CommandType = value;
    }

    public string CommandText
    {
        get => _command.CommandText;
        set => _command.CommandText = value;
    }

    public void AddParameters(SqlParameter[] parameters)
    {
        if (parameters != null)
        {
            _command.Parameters.AddRange(parameters);
        }
    }

    public async Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior)
    {
        return await _command.ExecuteReaderAsync(behavior);
    }

    public void Dispose()
    {
        _command.Dispose();
    }
}

步骤3：修改DAL依赖抽象

public class DataAccessLayer : IDataAccessLayer
{
    private readonly string _connectionString;
    private readonly ILogger<DataAccessLayer> _logger;
    private readonly Settings _settings;
    private readonly IDbConnectionFactory _connectionFactory;

    TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;

    // 注入连接工厂
    public DataAccessLayer(string connectionString, ILogger<DataAccessLayer> logger, 
                          IOptions<Settings> emailAlertSettings, IDbConnectionFactory connectionFactory)
    {
        _connectionString = connectionString;
        _logger = logger;
        _settings = emailAlertSettings.Value;
        _connectionFactory = connectionFactory;
    }

    public async Task<DataTable> ExecuteNonQueryWithResultAsync(string storedProcedureName, SqlParameter[] parameters = null)
    {
        DataTable dataTable = new DataTable();

        try
        {
            using (var connection = await _connectionFactory.CreateAndOpenConnectionAsync(_connectionString))
            {
                using (var command = new SqlCommandWrapper(new SqlCommand(storedProcedureName, (SqlConnection)connection)))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.AddParameters(parameters);

                    using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleResult))
                    {
                        dataTable.Load(reader);
                    }
                }
            }
        }
        catch (SqlException ex)
        {
            _logger.LogError(ex, "DataAccessLayer: Got exception when executing ExecuteNonQueryWithResultAsync method");
            throw;
        }

        return dataTable;
    }
}

步骤4：编写Mock测试

现在可以轻松Mock自定义的抽象接口：

public class DataAccessLayerTests : DataAccessLayerFixture
{
    [Fact]
    public async Task ExecuteNonQueryWithResultAsync_ReturnsEmptyDataTable_Test()
    {
        // Arrange
        var mockConnectionFactory = new Mock<IDbConnectionFactory>();
        var mockConnection = new Mock<IDbConnection>();
        var mockCommand = new Mock<IDbCommandWrapper>();
        var mockReader = new Mock<DbDataReader>();

        mockConnectionFactory.Setup(f => f.CreateAndOpenConnectionAsync(It.IsAny<string>()))
                             .Returns(Task.FromResult(mockConnection.Object));
        // 这里需要结合你的命令创建逻辑调整，比如可以在工厂中同时封装命令创建
        // 简化示例中假设DAL内部创建命令时可以被Mock覆盖，实际可扩展工厂接口包含命令创建方法

        mockCommand.Setup(c => c.ExecuteReaderAsync(CommandBehavior.SingleResult))
                   .Returns(Task.FromResult(mockReader.Object));
        mockReader.Setup(r => r.HasRows).Returns(false);
        mockReader.SetupSequence(r => r.Read()).Returns(false);

        var dataAccessLayer = new DataAccessLayer(_connectionString, _mockLogger.Object, _mockOptions, mockConnectionFactory.Object);

        // Act
        var dataTable = await dataAccessLayer.ExecuteNonQueryWithResultAsync("MyStoredProcedure");

        // Assert
        Assert.NotNull(dataTable);
        Assert.Empty(dataTable.Columns);
        Assert.Empty(dataTable.Rows);
    }
}

方案2：使用第三方Mock库（快速适配） #

如果不想重构现有DAL代码，可以使用Moq.SqlClient（NuGet可安装），它专门支持SqlClient类的Mock，无需修改原有DAL逻辑。

步骤1：安装NuGet包

Install-Package Moq.SqlClient

步骤2：编写测试代码

public class DataAccessLayerTests : DataAccessLayerFixture
{
    [Fact]
    public async Task ExecuteNonQueryWithResultAsync_ReturnsEmptyDataTable_Test()
    {
        // Arrange
        var mockConnection = new Mock<SqlConnection>();
        var mockCommand = new Mock<SqlCommand>();
        var mockReader = new Mock<SqlDataReader>();

        // Mock连接打开
        mockConnection.Setup(c => c.OpenAsync(It.IsAny<CancellationToken>()))
                      .Returns(Task.CompletedTask);
        // Mock创建命令
        mockConnection.Setup(c => c.CreateCommand())
                      .Returns(mockCommand.Object);

        // Mock命令执行
        mockCommand.Setup(c => c.ExecuteReaderAsync(CommandBehavior.SingleResult, It.IsAny<CancellationToken>()))
                   .Returns(Task.FromResult(mockReader.Object));

        // Mock空结果集
        mockReader.Setup(r => r.HasRows).Returns(false);
        mockReader.SetupSequence(r => r.Read()).Returns(false);

        var dataAccessLayer = new DataAccessLayer(_connectionString, _mockLogger.Object, _mockOptions);

        // Act
        var dataTable = await dataAccessLayer.ExecuteNonQueryWithResultAsync("MyStoredProcedure");

        // Assert
        Assert.NotNull(dataTable);
        Assert.Empty(dataTable.Columns);
        Assert.Empty(dataTable.Rows);
    }

    [Fact]
    public async Task ExecuteNonQueryWithResultAsync_ReturnsDataTable_Test()
    {
        // Arrange
        var mockConnection = new Mock<SqlConnection>();
        var mockCommand = new Mock<SqlCommand>();
        var mockReader = new Mock<SqlDataReader>();

        mockConnection.Setup(c => c.OpenAsync(It.IsAny<CancellationToken>()))
                      .Returns(Task.CompletedTask);
        mockConnection.Setup(c => c.CreateCommand())
                      .Returns(mockCommand.Object);

        mockCommand.Setup(c => c.ExecuteReaderAsync(CommandBehavior.SingleResult, It.IsAny<CancellationToken>()))
                   .Returns(Task.FromResult(mockReader.Object));

        // Mock带数据的结果集
        mockReader.Setup(r => r.HasRows).Returns(true);
        mockReader.Setup(r => r.FieldCount).Returns(1);
        mockReader.Setup(r => r.GetName(0)).Returns("ColumnName1");
        mockReader.SetupSequence(r => r.Read()).Returns(true).Returns(false);
        mockReader.Setup(r => r["ColumnName1"]).Returns("Value1");

        var dataAccessLayer = new DataAccessLayer(_connectionString, _mockLogger.Object, _mockOptions);

        // Act
        var dataTable = await dataAccessLayer.ExecuteNonQueryWithResultAsync("MyStoredProcedure");

        // Assert
        Assert.NotNull(dataTable);
        Assert.Single(dataTable.Columns);
        Assert.Equal("ColumnName1", dataTable.Columns[0].ColumnName);
        Assert.Single(dataTable.Rows);
        Assert.Equal("Value1", dataTable.Rows[0]["ColumnName1"]);
    }
}

方案对比 #

• 方案1是长期最优解：通过抽象解耦，不仅方便测试，还支持后续数据库切换，符合SOLID原则。
• 方案2是临时快速方案：适合现有代码量大、不想重构的场景，但依赖第三方库，Mock细节需严格匹配SqlClient的重载参数（比如异步方法的CancellationToken）。

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