如何模拟执行对内存中数据的oracle查询 [英] How to mock to execute oracle query to the in memory data

问题描述

我正在尝试编写单元测试以执行oracle查询以过滤对象的内存列表.

I am trying to write unit test to execute an oracle query to filter the in memory list of object.

我该如何模拟，以便将过滤条件应用于对象的内存列表中而不是实际的数据库中?

How do i mock so that the filter condition will be applied to my in memory list of object rather than the actual database?

我可以使用Entity Framework实现此目的，在这里我可以模拟上下文并返回内存数据，但不知道如何使用OracleCommand.ExecuteReader实现相同的功能.

I could achieve this with the Entity Framework where I can mock the context and return in memory data but don't know how to achieve the same using OracleCommand.ExecuteReader.

using (var connection = new OracleConnection(connectionString))
{
    connection.Open();

    var cmd = new OracleCommand
        {
            //TODO add Reg_Date in Where clause
            Connection = connection,
            CommandText =
                "SELECT mi.* from fromTable mi where 1=1 " 
                + (string.IsNullOrEmpty(nuf) ? "" : " and mi.NUF != '"+ nuf +"'")
                + " and mi.Category<>'TES' and mi.Category<>'CVD'"
            CommandType = CommandType.Text
        };

    Debug.WriteLine(cmd.CommandText);

    var dr = cmd.ExecuteReader();
}

推荐答案

目前，被测试的方法与实现方面的考虑过于紧密，以致于无法轻松地对其进行单独的单元测试.尝试抽象出这些实现问题，以便可以轻松模拟它们以进行独立的测试.

Currently the method under test it too tightly coupled to implementation concerns to make it easily unit testable in isolation. Try abstracting those implementation concerns out so that they can be mocked easily for isolated tests.

public interface IDbConnectionFactory {
    IDbConnection CreateConnection();
}

上述连接工厂抽象可用于访问Oracle数据存储的其他必要的System.Data抽象.

The above connection factory abstraction can be used to access the other necessary System.Data abstractions of your Oracle data store.

public class MyDataAccessClass {
    private IDbConnectionFactory connectionFactory;

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

    public object GetData(string nuf) {
        using (var connection = connectionFactory.CreateConnection()) {
            connection.Open();
            var query = "SELECT mi.* from fromTable mi where 1=1 " 
                        + (string.IsNullOrEmpty(nuf) ? "" : " and mi.NUF != @nuf")
                        + " and mi.Category<>'TES' and mi.Category<>'CVD'"
            using(var command = connection.CreateCommand()){
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                if(!string.IsNullOrEmpty(nuf)) {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = "@nuf";
                    parameter.Value = nuf;

                    command.Parameters.Add(parameter);
                }

                Debug.WriteLine(command.CommandText);

                var dr = command.ExecuteReader();

                //...other code removed for brevity
            }
        }
    }
}

工厂的生产实现将返回实际的OracleConnection

The production implementation of the factory will return an actual OracleConnection

public class OracleConnectionFactory: IDbConnectionFactory {
    public IDbConnection CreateConnection() {
        return new OracleConnection("connection string");
    }
}

可以通过依赖项注入传递给依赖类.

which can be passed into the dependent class via dependency injection.

对于测试，您可以使用所选的模拟框架对接口进行模拟，也可以创建自己的伪造品以注入和测试您的方法.

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

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

        var commandMock = new Mock<IDbCommand>();
        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 connectionFactoryMock = new Mock<IDbConnectionFactory>();
        connectionFactoryMock
            .Setup(m => m.CreateConnection())
            .Returns(connectionMock.Object);

        var sut = new MyDataAccessClass(connectionFactoryMock.Object);
        var input = "some value";

        //Act
        var data = sut.GetData(input);

        //Assert
        commandMock.Verify();
    }
}

最后，建议您在命令文本中使用命令参数，因为使用外部值手动构造查询字符串会使代码受到SQL注入攻击.

Finally it is advisable that you use command parameters in the command text as constructing the query string manually with external values opens the code up to SQL injection attacks.

这篇关于如何模拟执行对内存中数据的oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！