如何对使用ExecuteSqlRawAsync调用存储过程的.NET Core 3.1中的函数进行单元测试? [英] How to unit test a function in .NET Core 3.1 that uses ExecuteSqlRawAsync to call a stored procedure?

问题描述

我正在ASP.NET Core 3.1中编写一个API，使用EF Core访问SQL Server数据库.我在API中有一个函数，需要使用几个输入参数和一个输出参数来调用存储过程.下面是此功能的简化版本.

I'm writing an API in ASP.NET Core 3.1, using EF Core to access a SQL Server database. I have a function in the API that needs to call a stored procedure with several input parameters and an output parameter. A simplified version of this function is below.

我正在将 DbContext 与 .UseInMemoryDatabase()一起用于其他测试，但是内存数据库不能与存储过程一起使用.

I am using a DbContext with .UseInMemoryDatabase() for other tests, but the in memory database cannot be used with stored procedures.

(此解决方案首先是数据库，而不是代码.如有必要，可以更改存储过程，但是如果不需要的话，它会更好.我可以更改C#函数来调用存储过程但如果有帮助的话，可以采取其他方法.)

(This solution is database first, not code first. It would be possible to change the stored procedure if necessary but it would be much better if I didn't have to. I could change my C# function to call the stored procedure a different way though if that helps.)

如何对该功能进行单元测试?

How do I unit test this function?

public class MyFoo : IFoo
{
    public ApplicationDbContext DbContext { get; }

    public MyFoo(ApplicationDbContext dbContext)
    {
        DbContext = dbContext;
    }

    public async Task<bool> GetMyStoredProcResult(string val1, string val2, string val3, string val4, string val5)
    {
        // input validation removed for brevity
        var p1 = new SqlParameter
        {
            ParameterName = "p1",
            DbType = System.Data.DbType.String,
            Direction = System.Data.ParameterDirection.Input,
            Value = val1
        };
        // p2 - p5 removed for brevity
        var resultParam = new SqlParameter
        {
            ParameterName = "Result",
            DbType = System.Data.DbType.Boolean,
            Direction = System.Data.ParameterDirection.Output
        };
        var sql = "EXEC sp_MyProcedure @p1, @p2, @p3, @p4, @p5, @Result OUTPUT";
        _ = await DbContext.Database.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);
        return (bool)resultParam.Value;
    }
}

推荐答案

我的最终解决方案基于Stas Petrov给出的答案.我使用一个接口包装了对 DbContext.Database.ExecuteSqlRawAsync()的调用，该接口带有在 Startup.ConfigureServices()中添加到DI的类.

My final solution is based on the answer given by Stas Petrov. I wrapped the call to DbContext.Database.ExecuteSqlRawAsync() using an interface with a class that is added to the DI in Startup.ConfigureServices().

我创建了以下接口和类:

I created the following interface and class:

public interface IStoredProcedureExecutor
{
    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters);
}

public class StoredProcedureExecutor : IStoredProcedureExecutor
{
    public ApplicationDbContext DbContext { get; }

    public StoredProcedureExecutor(ApplicationDbContext dbContext)
    {
        DbContext = dbContext;
    }

    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters)
    {
        return DbContext.Database.ExecuteSqlRawAsync(sql, parameters);
    }
}

在问题代码中，我替换了此调用:

In my code from the question, I replaced this call:

_ = await DbContext.Database.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);

与此:

_ = await StoredProcedureExecutor.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);

然后在测试代码中，我创建了一个实例化的类，设置了一个合适的 ReturnValue ，然后将其插入到我正在测试的类中，而不是 StoredProcedureExecutor 中:

Then in the test code, I created this class which I instantiate, set a suitable ReturnValue, and then insert into the class I'm testing instead of StoredProcedureExecutor:

class TestStoredProcedureExecutor : IStoredProcedureExecutor
{
    public bool ReturnValue { get; set; }

    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters)
    {
        foreach (var param in parameters)
        {
            var p = (SqlParameter)param;
            if (p.Direction == System.Data.ParameterDirection.Output) p.Value = ReturnValue;
        }
        return Task.FromResult(0);
    }
}

这篇关于如何对使用ExecuteSqlRawAsync调用存储过程的.NET Core 3.1中的函数进行单元测试?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！