使用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.

我的插入方法当前如下所示:

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，请检查其快速入门

To better understand how to use Moq check their Quickstart

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