使用 Moq 模拟对 MySQL 数据库的插入查询 [英] Mocking insert query to a MySQL Database using Moq

问题描述

我目前正在尝试使用 Moq 学习 Mocking，并且我想在我拥有的现有数据库上尝试它，但是我不确定如何正确处理这个问题.

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.

我的 Insert 方法目前如下所示:

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

我将如何使用 Mocks 执行此操作?我是否为演员实体创建一个类?我应该为我的 DbConnection 类创建一个接口吗?抱歉所有问题，但我真的很难解决如何解决这个问题.

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.

推荐答案

目前被测试的方法与实现问题的耦合过于紧密，无法轻松地单独进行单元测试.尝试将这些实现问题抽象出来，以便可以轻松地模拟它们以进行隔离测试.

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

上述连接工厂抽象可用于访问 MySql 数据存储的其他必要的 System.Data 抽象.

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

工厂的生产实现会返回一个实际的MySqlConnection

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

最后，建议您在命令文本中使用命令参数，因为手动构造查询字符串会打开代码以进行 SQL 注入攻击.

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.

为了更好地了解如何使用 Moq，请查看他们的 Quickstart

To better understand how to use Moq check their Quickstart

这篇关于使用 Moq 模拟对 MySQL 数据库的插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！