使用MsTest在C#中模拟SqlConnection，SqlCommand和SqlReader [英] Mocking SqlConnection, SqlCommand and SqlReader in C# using MsTest

问题描述

我遇到了这个 answer ，我对使用Fake实现第二个答案很感兴趣.这是另一个一个.

I came across this answer and I'm interested in implementing the second answer using Fake. Here's another one.

我并没有真正理解那里的所有概念，而且仍在阅读和理解

I'm not really understanding all the concepts there and I'm still reading and understanding documentation, can someone help using my code, where I'm trying to access list of customers on how to use Fake/Shim/Stub/Mock here?

您也可以重写 FindAll 方法，以防万一要重构它以接受依赖关系.

You may rewrite FindAll method too just in case if it's to be refactored to accept dependencies.

讨论后编辑

public class Data
{
    private Func<IDbConnection> Factory { get; }

    public Data(Func<IDbConnection> factory)
    {
        Factory = factory;
    }

    public IList<Customer> FindAll()
    {
        using (var connection = Factory.Invoke())
        {
            const string sql = "SELECT Id, Name FROM Customer";
            using (var command = new SqlCommand(sql, (SqlConnection) connection))
            {
                command.Connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    IList<Customer> rows = new List<Customer>();
                    while (reader.Read())
                    {
                        rows.Add(new Customer
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name = reader.GetString(reader.GetOrdinal("Name"))
                        });
                    }
                    return rows;
                }
            }
        }
    }
}

客户

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

UnitTest

[TestMethod]
public void TestDB()
{
    var commandMock = new Mock<IDbCommand>();

    var readerMock = new Mock<IDataReader>();
    commandMock.Setup(m => m.ExecuteReader()).Returns(readerMock.Object).Verifiable();

    var parameterMock = new Mock<IDbDataParameter>();
    commandMock.Setup(m => m.CreateParameter()).Returns(parameterMock.Object);
    commandMock.Setup(m => m.Parameters.Add(It.IsAny<IDbDataParameter>())).Verifiable();

    var connectionMock = new Mock<IDbConnection>();
    connectionMock.Setup(m => m.CreateCommand()).Returns(commandMock.Object);

    var data = new Data(() => connectionMock.Object);
    var result = data.FindAll();
    Console.WriteLine(result);
}

错误

打了个依赖，添加了 System.Data.SqlClient ，随后出现另一个错误.

Had a hiccup with a dependency, added System.Data.SqlClient, another error follows.

System.InvalidCastException:无法转换类型的对象输入"Castle.Proxies.IDbConnectionProxy"'System.Data.SqlClient.SqlConnection'.

System.InvalidCastException: Unable to cast object of type 'Castle.Proxies.IDbConnectionProxy' to type 'System.Data.SqlClient.SqlConnection'.

指向此行

使用(var命令=新的SqlCommand(sql，(SqlConnection)连接))

推荐答案

被测试的目标方法应重构为依赖抽象而不是实现方面的问题.

Target method under test should be refactored to depend on abstractions and not implementation concerns.

例如

public class Data {
    private Func<IDbConnection> Factory { get; }

    public Data(Func<IDbConnection> factory) {
        Factory = factory;
    }

    public IList<Customer> FindAll() {
        using (IDbConnection connection = Factory.Invoke()) {
            const string sql = "SELECT Id, Name FROM Customer";
            using (IDbCommand command = connection.CreateCommand()) {                    
                command.CommandText = sql;

                connection.Open();
                using (IDataReader reader = command.ExecuteReader()) {
                    IList<Customer> rows = new List<Customer>();
                    while (reader.Read()) {
                        rows.Add(new Customer {
                            Id = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name = reader.GetString(reader.GetOrdinal("Name"))
                        });
                    }
                    return rows;
                }
            }
        }
    }
}

从那里可以对抽象进行模拟，使其在进行单元测试隔离时达到预期的效果.

From there the abstractions can be mocked to behave as expected when unit testing isolation.

[TestClass]
public class DataTests{
    [TestMethod]
    public void Should_Return_Customer() {
        //Arrange
        var readerMock = new Mock<IDataReader>();

        readerMock.SetupSequence(_ => _.Read())
            .Returns(true)
            .Returns(false);

        readerMock.Setup(reader => reader.GetOrdinal("Id")).Returns(0);
        readerMock.Setup(reader => reader.GetOrdinal("Name")).Returns(1);

        readerMock.Setup(reader => reader.GetInt32(It.IsAny<int>())).Returns(1);
        readerMock.Setup(reader => reader.GetString(It.IsAny<int>())).Returns("Hello World");

        var commandMock = new Mock<IDbCommand>();            
        commandMock.Setup(m => m.ExecuteReader()).Returns(readerMock.Object).Verifiable();

        var connectionMock = new Mock<IDbConnection>();
        connectionMock.Setup(m => m.CreateCommand()).Returns(commandMock.Object);

        var data = new Data(() => connectionMock.Object);

        //Act
        var result = data.FindAll();

        //Assert - FluentAssertions
        result.Should().HaveCount(1);
        commandMock.Verify(); //since it was marked verifiable.
    }
}

对于集成测试，可以使用与数据库的实际连接来验证功能

For integration tests an actual connection to a database can be used to verify functionality

var data = new Data(() => new SqlConnection("live connection string here"));

在生产中将使用相同的方法来连接到服务器.

The same approach would be used in production to connect to the server.

这篇关于使用MsTest在C#中模拟SqlConnection，SqlCommand和SqlReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！