如何用XUnit为使用SqlConnection的方法编写单元测试？

核心思路 #

直接Mock密封的SqlConnection/SqlCommand类难度极高，所以先抽象依赖实现解耦：

• 把存储过程的创建/删除逻辑封装到ISqlProceduresService接口
• 把ADO.NET的命令执行逻辑封装到IDbCommandExecutor接口
  通过Mock这两个接口，就能脱离真实数据库，专注验证业务逻辑的正确性。

测试用例与代码示例 #

前置准备：抽象依赖接口与调整业务类 #

先对原业务代码做最小化改造，注入抽象依赖：

// 存储过程操作接口
public interface ISqlProceduresService
{
    void CreateProcedure(string connectionString, string procedureSqlFile);
    void DropProcedure(string connectionString, string procedureName);
}

// 数据库命令执行接口
public interface IDbCommandExecutor
{
    Task<object?> ExecuteStoredProcedureScalarAsync(string connectionString, string procedureName, IDictionary<string, object> parameters);
}

// 业务类
public class VersionChecker
{
    private readonly ISqlProceduresService _sqlProceduresService;
    private readonly ILogger<VersionChecker> _logger;
    private readonly IDbCommandExecutor _dbCommandExecutor;

    public VersionChecker(ISqlProceduresService sqlProceduresService, ILogger<VersionChecker> logger, IDbCommandExecutor dbCommandExecutor)
    {
        _sqlProceduresService = sqlProceduresService;
        _logger = logger;
        _dbCommandExecutor = dbCommandExecutor;
    }

    public async Task<bool> CheckSoftwareVersion(SoftVersion softVersion, string connectionString)
    {
        _sqlProceduresService.CreateProcedure(connectionString, "someProcedure.sql");

        try
        {
            var scalarResult = await _dbCommandExecutor.ExecuteStoredProcedureScalarAsync(
                connectionString, 
                "someProcedure", 
                new Dictionary<string, object> { { "SoftVersion", (int)softVersion } });

            if (scalarResult is null)
            {
                _logger.LogWarning("存储过程执行返回空结果");
                return false;
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "执行存储过程时出错");
            return false;
        }
        finally
        {
            _sqlProceduresService.DropProcedure(connectionString, "[dbo].[someProcedure]");
        }

        return true;
    }
}

public enum SoftVersion
{
    V1,
    V2
}

1. 正常执行成功场景 #

验证：存储过程创建→执行返回非空结果→存储过程删除→返回true

public class VersionCheckerTests
{
    private readonly Mock<ISqlProceduresService> _mockSqlProcService;
    private readonly Mock<ILogger<VersionChecker>> _mockLogger;
    private readonly Mock<IDbCommandExecutor> _mockDbExecutor;
    private readonly VersionChecker _versionChecker;

    public VersionCheckerTests()
    {
        _mockSqlProcService = new Mock<ISqlProceduresService>();
        _mockLogger = new Mock<ILogger<VersionChecker>>();
        _mockDbExecutor = new Mock<IDbCommandExecutor>();
        _versionChecker = new VersionChecker(_mockSqlProcService.Object, _mockLogger.Object, _mockDbExecutor.Object);
    }

    [Fact]
    public async Task CheckSoftwareVersion_ExecutionSucceeds_ReturnsTrue()
    {
        // 准备测试数据
        var testVersion = SoftVersion.V2;
        var testConnString = "Server=test;Database=test;Trusted_Connection=True;";
        
        // 模拟命令执行返回非空结果
        _mockDbExecutor.Setup(x => x.ExecuteStoredProcedureScalarAsync(
            testConnString, 
            "someProcedure", 
            It.IsAny<IDictionary<string, object>>()))
            .ReturnsAsync(1);

        // 执行测试方法
        var result = await _versionChecker.CheckSoftwareVersion(testVersion, testConnString);

        // 验证结果与交互
        Assert.True(result);
        _mockSqlProcService.Verify(x => x.CreateProcedure(testConnString, "someProcedure.sql"), Times.Once);
        _mockSqlProcService.Verify(x => x.DropProcedure(testConnString, "[dbo].[someProcedure]"), Times.Once);
        _mockDbExecutor.Verify(x => x.ExecuteStoredProcedureScalarAsync(
            testConnString, 
            "someProcedure", 
            It.Is<IDictionary<string, object>>(dict => 
                dict.ContainsKey("SoftVersion") && (int)dict["SoftVersion"] == (int)testVersion)), 
            Times.Once);
        _mockLogger.VerifyNoOtherCalls();
    }
}

2. 存储过程返回空结果场景 #

验证：返回false，记录警告日志，存储过程仍被删除

[Fact]
public async Task CheckSoftwareVersion_ScalarResultIsNull_ReturnsFalseAndLogsWarning()
{
    // 准备测试数据
    var testVersion = SoftVersion.V1;
    var testConnString = "Server=test;Database=test;Trusted_Connection=True;";
    
    // 模拟返回空结果
    _mockDbExecutor.Setup(x => x.ExecuteStoredProcedureScalarAsync(
        testConnString, 
        "someProcedure", 
        It.IsAny<IDictionary<string, object>>()))
        .ReturnsAsync((object?)null);

    // 执行测试方法
    var result = await _versionChecker.CheckSoftwareVersion(testVersion, testConnString);

    // 验证结果与交互
    Assert.False(result);
    _mockSqlProcService.Verify(x => x.DropProcedure(testConnString, "[dbo].[someProcedure]"), Times.Once);
    _mockLogger.Verify(logger => logger.Log(
        LogLevel.Warning,
        It.IsAny<EventId>(),
        It.Is<It.IsAnyType>((v, t) => v.ToString().Contains("存储过程执行返回空结果")),
        It.IsAny<Exception>(),
        It.IsAny<Func<It.IsAnyType, Exception?, string>>()), 
        Times.Once);
}

3. 执行过程抛出异常场景 #

验证：返回false，记录错误日志，存储过程仍被删除

[Fact]
public async Task CheckSoftwareVersion_ExecutionThrowsException_ReturnsFalseAndLogsError()
{
    // 准备测试数据
    var testVersion = SoftVersion.V2;
    var testConnString = "Server=test;Database=test;Trusted_Connection=True;";
    var testException = new InvalidOperationException("测试异常");
    
    // 模拟抛出异常
    _mockDbExecutor.Setup(x => x.ExecuteStoredProcedureScalarAsync(
        testConnString, 
        "someProcedure", 
        It.IsAny<IDictionary<string, object>>()))
        .ThrowsAsync(testException);

    // 执行测试方法
    var result = await _versionChecker.CheckSoftwareVersion(testVersion, testConnString);

    // 验证结果与交互
    Assert.False(result);
    _mockSqlProcService.Verify(x => x.DropProcedure(testConnString, "[dbo].[someProcedure]"), Times.Once);
    _mockLogger.Verify(logger => logger.Log(
        LogLevel.Error,
        It.IsAny<EventId>(),
        It.Is<It.IsAnyType>((v, t) => v.ToString().Contains("执行存储过程时出错")),
        testException,
        It.IsAny<Func<It.IsAnyType, Exception?, string>>()), 
        Times.Once);
}

4. 无效连接字符串场景 #

验证：连接失败时返回false，记录错误日志

[Fact]
public async Task CheckSoftwareVersion_InvalidConnectionString_ReturnsFalseAndLogsError()
{
    // 准备测试数据
    var testVersion = SoftVersion.V1;
    var invalidConnString = "Invalid Connection String";
    var sqlException = new SqlException("无法打开连接", (int)SqlErrorCodes.CannotOpenDatabase);
    
    // 模拟连接失败抛出异常
    _mockDbExecutor.Setup(x => x.ExecuteStoredProcedureScalarAsync(
        invalidConnString, 
        "someProcedure", 
        It.IsAny<IDictionary<string, object>>()))
        .ThrowsAsync(sqlException);

    // 执行测试方法
    var result = await _versionChecker.CheckSoftwareVersion(testVersion, invalidConnString);

    // 验证结果与交互
    Assert.False(result);
    _mockSqlProcService.Verify(x => x.DropProcedure(invalidConnString, "[dbo].[someProcedure]"), Times.Once);
    _mockLogger.Verify(logger => logger.Log(
        LogLevel.Error,
        It.IsAny<EventId>(),
        It.Is<It.IsAnyType>((v, t) => v.ToString().Contains("执行存储过程时出错")),
        sqlException,
        It.IsAny<Func<It.IsAnyType, Exception?, string>>()), 
        Times.Once);
}

关键注意事项 #

• 抽象ADO.NET操作时，要保证接口方法覆盖业务所需的核心逻辑（事务、参数、命令类型等）
• ISqlProceduresService的复杂逻辑可单独写单元测试，Mock底层文件读取或SQL执行逻辑
• 事务的提交/回滚属于数据访问层职责，应在IDbCommandExecutor的单元测试中验证，业务层只需确保调用流程正确

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