c# - Mocking insert query to a MySQL Database using Moq

Question

I am currently trying to learn Mocking with Moq, and I wanted to try it on an existing database that I have, however I am unsure how is the correct way to approach this.

In my data layer I have a class that handles connecting to the DB and has the various methods for inserting, selecting etc. I want to test whether an actor was correctly inserted into the database.

My Insert method currently looks like this:

public void Insert(string firstname, string lastname)
{
    string query = $"INSERT INTO `sakila`.`actor`(`first_name`,`last_name`) VALUES('" + firstname + "','" + lastname + "')";
    Console.WriteLine(query);
    //open connection
    if (this.OpenConnection() == true)
    {
        Console.WriteLine("Established connection");
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, connection);

        //Execute command
        cmd.ExecuteNonQuery();
        Console.WriteLine("Insert query succesfully executed.");

        //close connection
        this.CloseConnection();
    }
}

How would I go about doing this using Mocks? Do I create a class for the actor entity? Should I create an interface for my DbConnection class? Sorry for all the questions, but I'm really stumped on how to approach this problem.

Answer 1

Currently the method under test it too tightly coupled to implementation concerns to make it easily unit testable in isolation. Try abstracting those implementation concerns out so that they can be mocked easily for isolated tests.

public interface IDbConnectionFactory {
    IDbConnection CreateConnection();
}

The above connection factory abstraction can be used to access the other necessary System.Data abstractions of your MySql data store.

public class MyDataAccessClass {
    private IDbConnectionFactory connectionFactory;

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

    public void Insert(string firstname, string lastname) {
        var query = $"INSERT INTO `sakila`.`actor`(`first_name`,`last_name`) VALUES('" + firstname + "','" + lastname + "')";
        Console.WriteLine(query);
        using(var connection = connectionFactory.CreateConnection() {
            //Creates and returns a MySqlCommand object associated with the MySqlConnection. 
            using(var command = connection.CreateCommand()) {
                command.CommandText = query;
                Console.WriteLine("Established connection");
                connection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Insert query succesfully executed.");
                connection.Close();//is not actually necessary as the using statement will make sure to close the connection.
            }
        }
    }
}

The production implementation of the factory will return an actual MySqlConnection

public class MySqlConnectionFactory: IDbConnectionFactory {
    public IDbConnection CreateConnection() {
        return new MySqlConnection("connection string");
    }
}

which can be passed into the data layer via dependency injection

For testing you mock the interfaces using your mocking framework of choice or create your own fakes to inject and test your method.

[TestClass]
public class DataAccessLayerUnitTest {
    [TestMethod]
    public void TestInsert() {
        //Arrange
        var commandMock = new Mock<IDbCommand>();
        commandMock
            .Setup(m => m.ExecuteNonQuery())
            .Verifiable();

        var connectionMock = new Mock<IDbConnection>();
        connectionMock
            .Setup(m => m.CreateCommand())
            .Returns(commandMock.Object);

        var connectionFactoryMock = new Mock<IDbConnectionFactory>();
        connectionFactoryMock
            .Setup(m => m.CreateConnection())
            .Returns(connectionMock.Object);

        var sut = new MyDataAccessClass(connectionFactoryMock.Object);
        var firstName = "John";
        var lastName = "Doe";

        //Act
        sut.Insert(firstName, lastName);

        //Assert
        commandMock.Verify();
    }
}

Finally it is advisable that you use command parameters in the command text as constructing the query string manually opens the code up to SQL injection attacks.

To better understand how to use Moq check their Quickstart