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

Removable

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.

Selfinterest

that's why I ran into missing dependency error because my sut was using System.Data.SqlClient – Removable

@Removable yep. tight coupling. That is why I prefer abstracting away implementation concerns. Simplifies things tremendously. – Selfinterest

oh yes, worked like a charm, bundle of thanks! It's my first implementation of mocking in C#, will continue the journey and pay post another question if I run into any problem. Thanks again. – Removable

@Removable reference https://mcmap.net/q/1165734/-how-to-mock-to-execute-oracle-query-to-the-in-memory-data – Selfinterest

Here is also a few more where I have made use of this approach stackoverflow.com/… – Selfinterest

Answer 2

The problem with this is that IDbCommand doesnt have the method ExecuteNonQueryAsync, or any of the other Async methods.

Why should I remove async functionality just to test

Suzisuzie

If you use Dapper you get this async functionality back. The only thing I've seen that isn't async is there is no option for OpenAsync on IDbConnection. But if you look at what SqlConnection is doing it's not really async anyway. – Requital