C#单元测试，模拟存储过程 [英] c# unit testing, mocking stored procedure

问题描述

过去，当我执行单元测试时，我一直在努力为数据访问层设置体面的"单元测试，因为它们经常将数据库作为外部依赖项.在理想情况下，我将模拟存储过程调用，以便删除外部依赖项.

In the past when I have been implementing unit test I have struggled to set up 'decent' unit tests for data access layers because they often have a database as an external dependency. In an ideal world I would mock the stored procedure calls so that the external dependency is removed.

但是，我一直无法弄清楚如何使用MOQ模拟框架来做到这一点，或者找不到任何其他支持该框架的框架.取而代之的是，我转而使用已知数据创建脚本化的测试数据库(这样我就可以始终获得期望的输出)，但这与模拟该层略有不同.

However I have been unable to work out how to do this with the MOQ mocking framework, or find any other framework which supports this. Instead I have reverted to creating a scripted test database with known data in (so that I can always get outputs I expect) but this is slightly different from Mocking that layer.

任何人都可以建议如何模拟数据访问层的这一部分[对于Entity Framework，我知道 https://effort .codeplex.com/存在]?

Can anyone suggest how to Mock this part of the data access layer [I know for Entity Framework that https://effort.codeplex.com/ exists]?

---

详细信息 例如，如果我有以下方法

---

Detail For instance if I have the following method

public object RunStoredProc()
{
    //Some Setup

    using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING))
    {
        using (SqlCommand comm = new SqlCommand("storedProcName", conn))
        {
            conn.Open();
            comm.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader reader = comm.ExecuteReader())
            {
                while (reader.Read())
                {
                    //Logic
                }
            }
        }
    }

    //Return object based on logic
}

然后我该如何模拟存储过程的输出，以便SQLDataReader包含指定的数据.在较高的级别上，我可以模拟RunStoredProc()方法-但这无助于我测试该方法中的逻辑是否正确.或者，我可以将SQLReader剥离为另一种方法

then how do I mock the stored procedure output so that SQLDataReader contains specified data. At a higher level I could mock the RunStoredProc() method - but that won't help me test whether the logic in that method is correct. Alternatively I could strip the SQLReader out into another method

public object RunStoredProc()
{
    //Some Setup

    List<object> data = GetData();
    //Logic

    //Return object based on logic
}

private List<object> GetData()
{
    using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING))
    {
        using (SqlCommand comm = new SqlCommand("storedProcName", conn))
        {
            conn.Open();
            comm.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader reader = comm.ExecuteReader())
            {
                while (reader.Read())
                {
                    //place into return object
                }
            }
        }
    }
}

但是由于"GetData"方法应该是私有的(不是已发布接口的一部分)，所以我将无法对此进行模拟，因此问题仍然存在.

but as the 'GetData' method should be private (not part of the published interface) I then wouldn't be able to mock that and so the issue remains.

推荐答案

我认为我们拥有所有接口(IDbConnection，IDbTransaction，IDbCommand，IDataReader)，并从EF()提取所需的所有内容，以便可以对它们进行模拟并与Dependency Injection一起使用.我认为SqlConnection和其他内容更多地是实现细节，可以抽象化.

I think we have all the interfaces (IDbConnection, IDbTransaction, IDbCommand, IDataReader) and borrowing an idea from EF (IDbConnectionFactory) to abstract everything needed so that they can be mocked and used with Dependency Injection. I think SqlConnection and the rest are more of an implementation detail and can be abstracted.

根据Entity Framework的想法，您可以创建连接工厂

Following an idea from Entity Framework you can create a connection factory

public interface IDbConnectionFactory {
    /// <summary>
    ///  Creates a connection based on the given database name or connection string.
    IDbConnection CreateConnection(string nameOrConnectionString);
}

然后您可以重构示例方法以仅使用抽象.

And you can then refactor your example method to use only the abstractions.

public class MyDataAccessClass {
    private IDbConnectionFactory dbConnectionFactory;
    private string CONNNECTION_STRING = "Connection string here";

    public MyDataAccessClass(IDbConnectionFactory dbConnectionFactory) {
        this.dbConnectionFactory = dbConnectionFactory;
    }

    public object RunStoredProc() {
        //Some Setup
        List<object> result = new List<object>();

        using (IDbConnection conn = dbConnectionFactory.CreateConnection(CONNNECTION_STRING)) {
            using (IDbCommand comm = conn.CreateCommand()) {
                comm.CommandText = "storedProcName";
                conn.Open();
                comm.CommandType = CommandType.StoredProcedure;
                using (IDataReader reader = comm.ExecuteReader()) {
                    while (reader.Read()) {
                        //...Logic to populate result
                    }
                }
            }
        }

        //Return object based on logic
        return result;
    }
}

从那里，您可以使用所选的模拟框架来模拟接口，或者创建自己的伪造品以注入和测试您的方法.

From there you mock the interfaces using your mocking framework of choice or create your own fakes to inject and test your method.

[TestClass]
public class StoredProcedureUnitTest {
    [TestMethod]
    public void TestRunStoredProc() {
        //Arrange
        var connectionFactory = new Mock<IDbConnectionFactory>();
        //..Setup...

        var sut = new MyDataAccessClass(connectionFactory.Object);

        //Act
        var actual = sut.RunStoredProc();

        //Assert
        //...
    }
}

这篇关于C#单元测试，模拟存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！