ASP.NET Core调用MySQL存储过程的方法如何编写单元测试？

实现思路 #

• 依赖解耦：原方法直接依赖MySQL连接和自定义的GetRsp方法，首先需要将硬编码的数据库实例化逻辑抽象为接口（如IDbConnectionFactory），避免单元测试依赖真实数据库，也方便Mock返回结果。
• 可访问性处理：由于目标方法是protected virtual修饰，可通过继承原业务类的方式编写测试子类，将protected方法暴露为public供测试调用。
• 分支全覆盖：需要覆盖所有逻辑分支：scope为空的场景、scope不为空但存储过程返回null的场景、存储过程返回有效结果的场景，以及GetRsp返回字段缺失的边界异常场景。
• Mock替代外部依赖：使用Moq等Mock框架模拟GetRsp返回值、数据库连接、存储过程执行结果，不需要启动真实MySQL服务即可完成测试。

代码示例 #

1. 原方法依赖抽象（小改原有代码降低耦合） #

// 新增数据库连接工厂接口，替代原方法中直接new MySqlConnection的逻辑
public interface IDbConnectionFactory
{
    MySqlConnection CreateTransactionConnection(string connStr);
}

// 原业务类构造函数注入依赖
public class PermissionService
{
    private readonly IDbConnectionFactory _dbFactory;
    public PermissionService(IDbConnectionFactory dbFactory)
    {
        _dbFactory = dbFactory;
    }

    // 你的原有IsUserPermission方法，把new MySqlConnection替换为_dbFactory.CreateTransactionConnection(connStrTransactions)即可
    protected virtual bool IsUserPermission(Query query, string scope, string sessionID, string clientID, string connStrTransactions)
    {
        if (!string.IsNullOrEmpty(scope))
        {
            JObject res = GetRsp(query, sessionID, clientID);
            // 替换原有new MySqlConnection的代码
            using (MySqlConnection conn = _dbFactory.CreateTransactionConnection(connStrTransactions))
            {
                // 原有逻辑不变
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand("AuthUserPermission", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    MySqlParameter rv = new MySqlParameter("value", MySqlDbType.Text);
                    rv.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(rv);
                    cmd.Parameters.AddWithValue("pUserId", (res["UserId"]));
                    cmd.Parameters.AddWithValue("pModule", (res["Module"]));
                    cmd.Parameters.AddWithValue("pOperation", (res["Operation"]));
                    cmd.ExecuteNonQuery();

                    var result = cmd.Parameters["value"].Value;
                    return result != null;
                }
            }
        }
        return false;
    }

    // 原有GetRsp方法，保留virtual修饰方便Mock
    protected virtual JObject GetRsp(Query query, string sessionID, string clientID)
    {
        // 原有逻辑不变
        return default;
    }
}

2. 测试类与测试用例（使用xUnit + Moq框架） #

using Moq;
using Xunit;
using MySql.Data.MySqlClient;
using System.Data;
using Newtonsoft.Json.Linq;

// 测试子类，暴露protected的IsUserPermission方法
public class TestablePermissionService : PermissionService
{
    public TestablePermissionService(IDbConnectionFactory dbFactory) : base(dbFactory)
    {
    }

    public new bool IsUserPermission(Query query, string scope, string sessionID, string clientID, string connStrTransactions)
    {
        return base.IsUserPermission(query, scope, sessionID, clientID, connStrTransactions);
    }
}

public class PermissionServiceTests
{
    private readonly Mock<IDbConnectionFactory> _mockDbFactory;
    private readonly Mock<TestablePermissionService> _mockService;
    private readonly string _testConnStr = "server=mock;database=mock;uid=mock;pwd=mock;";

    public PermissionServiceTests()
    {
        _mockDbFactory = new Mock<IDbConnectionFactory>();
        // CallBase = true表示未Mock的方法走原有实现
        _mockService = new Mock<TestablePermissionService>(_mockDbFactory.Object) { CallBase = true };
    }

    [Fact(DisplayName = "scope为空时直接返回false")]
    public void IsUserPermission_ScopeEmpty_ReturnFalse()
    {
        var result = _mockService.Object.IsUserPermission(new Query(), string.Empty, "test-session", "test-client", _testConnStr);
        Assert.False(result);
    }

    [Fact(DisplayName = "存储过程返回有效值时返回true")]
    public void IsUserPermission_ProcedureHasReturn_ReturnTrue()
    {
        // 模拟GetRsp返回的用户权限参数
        var mockRsp = new JObject
        {
            ["UserId"] = 1001,
            ["Module"] = "User",
            ["Operation"] = "Add"
        };
        _mockService.Setup(s => s.GetRsp(It.IsAny<Query>(), It.IsAny<string>(), It.IsAny<string>()))
                    .Returns(mockRsp);

        // 模拟存储过程返回有效值
        var mockCmd = new Mock<MySqlCommand>();
        mockCmd.Setup(c => c.Parameters["value"].Value).Returns("auth_pass");
        mockCmd.Setup(c => c.ExecuteNonQuery()).Verifiable();

        // 模拟数据库连接
        var mockConn = new Mock<MySqlConnection>(_testConnStr);
        mockConn.Setup(c => c.Open()).Verifiable();
        mockConn.Setup(c => c.CreateCommand()).Returns(mockCmd.Object);
        _mockDbFactory.Setup(f => f.CreateTransactionConnection(_testConnStr)).Returns(mockConn.Object);

        var result = _mockService.Object.IsUserPermission(new Query(), "test-scope", "test-session", "test-client", _testConnStr);

        Assert.True(result);
        mockCmd.Verify(c => c.ExecuteNonQuery(), Times.Once);
        mockConn.Verify(c => c.Open(), Times.Once);
    }

    [Fact(DisplayName = "存储过程返回null时返回false")]
    public void IsUserPermission_ProcedureReturnNull_ReturnFalse()
    {
        var mockRsp = new JObject
        {
            ["UserId"] = 1001,
            ["Module"] = "User",
            ["Operation"] = "Add"
        };
        _mockService.Setup(s => s.GetRsp(It.IsAny<Query>(), It.IsAny<string>(), It.IsAny<string>()))
                    .Returns(mockRsp);

        // 模拟存储过程返回null
        var mockCmd = new Mock<MySqlCommand>();
        mockCmd.Setup(c => c.Parameters["value"].Value).Returns(null);
        mockCmd.Setup(c => c.ExecuteNonQuery()).Verifiable();

        var mockConn = new Mock<MySqlConnection>(_testConnStr);
        mockConn.Setup(c => c.Open()).Verifiable();
        mockConn.Setup(c => c.CreateCommand()).Returns(mockCmd.Object);
        _mockDbFactory.Setup(f => f.CreateTransactionConnection(_testConnStr)).Returns(mockConn.Object);

        var result = _mockService.Object.IsUserPermission(new Query(), "test-scope", "test-session", "test-client", _testConnStr);

        Assert.False(result);
    }
}

可选方案：集成测试 #

如果你不想修改原有代码，也可以编写集成测试：提前在测试MySQL实例中创建AuthUserPermission存储过程，造好对应的测试用户、权限数据，直接传入测试库连接字符串调用方法验证结果，不需要做任何Mock。

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