Mocking SqlConnection, SqlCommand and SqlReader in C# using MsTest

Question

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?

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

Editing after discussion

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

Customer

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

Error

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

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

pointing to this line

using (var command = new SqlCommand(sql, (SqlConnection) connection))

Answer 1

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

For example

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.