how do I mock sqlconnection or should I refactor the code?

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

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

public class SqlBulkWriter_Should {
    public void EmptyTable() {
        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";

        subject.EmptyTable(schema, tableName);

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

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

Leave a Comment