c# unit testing, mocking stored procedure

Question

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.

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.

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
}

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

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.

Answer 1

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.

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