unit testing - how do I mock sqlconnection or should I refactor the code?

Question

I have the code below, I have read Moq and SqlConnection? and How can I stub IDBconnection, but I still have no idea how to mock the following sqlconnection.

public class SqlBulkWriter : ISqlBulkWriter
{
    private readonly string _dbConnectionString;;

    public SqlBulkWriter(string dbConnectionString)
    {
        this._dbConnectionString = dbConnectionString;
    }

    public void EmptyTable(string schema, string tableName)
    {
        using (var connection = new SqlConnection(this._dbConnectionString))
        {
            try
            {
                connection.Open();
                using (var truncate = new SqlCommand($"TRUNCATE TABLE [{schema}].[{tableName}] ", connection))
                {
                    truncate.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

I'm going to do the unit test for EmptyTable, I think I should mock the sqlconnection firstly? or how do I do the unit test for the EmptyTempTable?

Thank you! Many appreciate!

Answer 1

SqlBulkWriter class is tightly coupled to implementation concerns which make it difficult to test the class in isolation.

Knowing the connection string is not really a concern of that class and can be delegated out to another service.

Something like

public interface IDbConnectionFactory {
   IDbConnection CreateConnection();
}

and its implementation for your class would look something like this

public class SqlConnectionFactory : IDbConnectionFactory {
    private readonly string dbConnectionString;

    public SqlConnectionFactory(string dbConnectionString) {
        this.dbConnectionString = dbConnectionString;
    }

    public IDbConnection CreateConnection() {
        return new SqlConnection(this.dbConnectionString);
    }
}

SqlBulkWriter class can now be refactored to depend on the abstraction instead of the concretion.

public class SqlBulkWriter : ISqlBulkWriter {
    private readonly IDbConnectionFactory connectionFactory;

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

    public void EmptyTable(string schema, string tableName) {
        using (var connection = connectionFactory.CreateConnection()) {
            try {
                connection.Open();
                using (var command = connection.CreateCommand()) {
                    command.CommandText = $"TRUNCATE TABLE [{schema}].[{tableName}] ";
                    command.CommandType = CommandType.Text;
                    int rowsAffected = command.ExecuteNonQuery();
                }
            } catch (Exception ex) {
                throw ex;
            } finally {
                connection.Close();
            }
        }
    }
}

This allows the class to easier to be tested in isolation as those abstractions can be mocked and injected into the class.

[TestClass]
public class SqlBulkWriter_Should {
    [TestMethod]
    public void EmptyTable() {
        //Arrange
        var mock = new MockRepository(MockBehavior.Default);
        var command = mock.OneOf<IDbCommand>();
        var connection = mock.OneOf<IDbConnection>(_ => _.CreateCommand() == command);
        var factory = mock.OneOf<IDbConnectionFactory>(_ => _.CreateConnection() == connection);

        var subject = new SqlBulkWriter(factory);
        var schema = "dbo";
        var tableName = "TestTable";

        //Act
        subject.EmptyTable(schema, tableName);

        //Assert
        Mock.Get(command).Verify(_ => _.ExecuteNonQuery(), Times.Once());
    }
}

Reference Moq Quickstart to get a better understanding of how to use the framework.