How to mock to execute oracle query to the in memory data

Question

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?

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();
}

Answer 1

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();
}

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
            }
        }
    }
}

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();
    }
}

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.