mock使用Moq向MySQL数据库插入查询

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.

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

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.

在我的数据层中，我有一个处理连接到DB的类，有各种插入、选择等的方法。我想测试参与者是否被正确地插入到数据库中。

My Insert method currently looks like this:

我的Insert方法目前看起来是这样的:

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.

我该怎么用模拟来做呢?我是否为actor实体创建一个类?我应该为我的DbConnection类创建一个接口吗?很抱歉所有的问题，但是我真的被如何处理这个问题难住了。

1 个解决方案

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

上面的连接工厂抽象可以用来访问其他必要的系统。MySql数据存储的数据抽象。

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

工厂的生产实现将返回一个实际的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.

对于测试，您可以使用所选的mock框架模拟接口，或者创建自己的假接口来注入和测试方法。

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

最后，建议您在命令文本中使用命令参数来构造查询字符串，以手动打开SQL注入攻击的代码。

To better understand how to use Moq check their Quickstart

为了更好地理解如何使用Moq检查他们的快速启动。

#1