C# 单元测试：测试的数据库设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/786580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unit-Testing: Database set-up for tests
提问by Charles
I'm writing unit-tests for an app that uses a database, and I'd like to be able to run the app against some sample/test data - but I'm not sure of the best way to setup the initial test data for the tests.
我正在为使用数据库的应用程序编写单元测试，我希望能够针对某些示例/测试数据运行该应用程序 - 但我不确定设置初始测试数据的最佳方用于测试。
What I'm looking for is a means to run the code-under-test against the same database (or schematically identical) that I currently use while debugging - and before each test, I'd like to ensure that the database is reset to a clean slate prior to inserting the test data.
我正在寻找的是一种针对当前在调试时使用的同一数据库（或示意性相同）运行被测代码的方 - 在每次测试之前，我想确保将数据库重置为在插入测试数据之前有一个干净的石板。
I realize that using an IRepository pattern would allow me to remove the complexity of testing against an actual database, but I'm not sure that will be possible in my case.
我意识到使用 IRepository 模式可以消除针对实际数据库进行测试的复杂性，但我不确定这在我的情况下是否可行。
Any suggestions or articles that could point me in the right direction?
任何可以为我指明正确方向的建议或文章？
Thanks!
谢谢！
--EDIT--
- 编辑 -
Thanks everyone, those are some great suggestions! I'll probably go the route of mocking my data access layer, combined with some simple set-up classes to generate exactly the data I need per test.
谢谢大家，这些都是很好的建议！我可能会走模拟我的数据访问层的路线，结合一些简单的设置类来生成我每次测试所需的数据。
采纳答案by Don Branson
Here's the general approach I try to use. I conceive of tests at about three or four levels:: unit-tests, interaction tests, integration tests, acceptance tests.
这是我尝试使用的一般方。我将测试设想为大约三到四个级别：单元测试、交互测试、集成测试、验收测试。
At the unit test level, it's just code. Any database interaction is mocked out, either manually or using one of the popular frameworks, so loading data is not an issue. They run quick, and make sure the objects work as expected. This allows for very quick write-test/write code/run test cycles. The mock objects serve up the data that is needed by each test.
在单元测试级别，它只是代码。任何数据库交互都被模拟出来，无论是手动还是使用流行的框架之一，因此加载数据不是问题。它们运行得很快，并确保对象按预期工作。这允许非常快速的写测试/写代码/运行测试周期。模拟对象提供每个测试所需的数据。
Interaction tests test the interactions of non-trivial class interactions. Again, no database required, it's mocked out.
交互测试测试非平凡类交互的交互。同样，不需要数据库，它被嘲笑了。
Now at the integration level, I'm testing integration of components, and that's where real databases, queues, services, yada yada, get thrown in. If I can, I'll use one of the popular in-memory databases, so initialization is not an issue. It always starts off empty, and I use utility classes to scrub the database and load exactly the data I want before each test, so that there's no coupling between the tests.
现在在集成级别，我正在测试组件的集成，这就是真正的数据库、队列、服务、yada yada 被抛出的地方。如果可以，我将使用流行的内存数据库之一，因此初始化不是问题。它总是从空开始，我使用实用程序类来清理数据库并在每次测试之前准确加载我想要的数据，以便测试之间没有耦合。
The problem I've hit using in-memory databases is that they often don't support all the features I need. For example, perhaps I require an outer join, and the in-memory DB doesn't support that. In that case, I'll typically test against a local conventional database such as MySQL, again, scrubbing it before each test. Since the app is deployed to production in a separate environment, that data is untouched by the testing cycle.
我在使用内存数据库时遇到的问题是它们通常不支持我需要的所有功能。例如，也许我需要一个外连接，而内存数据库不支持。在这种情况下，我通常会针对本地传统数据库（例如 MySQL）进行测试，再次在每次测试之前对其进行清理。由于应用程序是在单独的环境中部署到生产环境中的，因此该数据不会受到测试周期的影响。
回答by J.W.
I don't think there is an easy way to finish this. You just have to create those Pre-Test sql setup scripts and post-test Tear-down scripts. Then you need trigger those scripts for each run. A lot of people suggest SQLLite for unit test setup.
我不认为有一个简单的方来完成这个。您只需要创建那些 Pre-Test sql setup 脚本和 post-test Tear-down 脚本。然后您需要为每次运行触发这些脚本。很多人建议使用 SQLLite 进行单元测试设置。
回答by Mork0075
I know you're using C# but in the Java World there's the Spring framework. It allows you to run database minipulations in a transaction and after this transaction, you roll this one back. This means that you operate against a real database without touching the state after the test finishes. Perhaps this could be a hint to further investigation in C#.
我知道您使用的是 C#，但在 Java 世界中有 Spring 框架。它允许您在事务中运行数据库最小化，并且在此事务之后，您回滚此事务。这意味着您对真实数据库进行操作，而不会在测试完成后触及状态。也许这可能是在 C# 中进一步调查的提示。
回答by James Black
I found it best to have my tests go to a different db so I could wipe it clean and put in the data I wanted for the test.
我发现最好将我的测试转到不同的数据库，这样我就可以将它擦干净并放入我想要的测试数据。
You may want to have the database be something that can be set within the program, then your test can tell the classes to change the database.
您可能希望数据库可以在程序中设置，然后您的测试可以告诉类更改数据库。
回答by Eric Petroelje
The best way I've found to handle this is to use a static test database with known data, and use transactions to ensure that your tests don't change anything.
我发现处理此问题的最佳方是使用具有已知数据的静态测试数据库，并使用事务来确保您的测试不会改变任何内容。
In your test setup you would start a transaction, and in your test cleanup, you would roll the transaction back. This lets you modify data in your tests but also makes sure everything gets restored to its original state when the test completes.
在您的测试设置中，您将启动一个事务，而在您的测试清理中，您将回滚该事务。这使您可以修改测试中的数据，同时确保测试完成后所有内容都恢复到原始状态。
回答by Lu55
This code clears all data from all user's tables in MS SQL Server:
此代码清除 MS SQL Server 中所有用户表中的所有数据：
private DateTime _timeout;
public void ClearDatabase(SqlConnection connection)
{
_timeout = DateTime.Now + TimeSpan.FromSeconds(30);
do
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "exec sp_MSforeachtable 'DELETE FROM ?'";
try
{
command.ExecuteNonQuery();
return;
}
catch (SqlException)
{
}
} while (!TimeOut());
if (TimeOut())
Assert.Fail("Fail to clear DB");
}
private bool TimeOut()
{
return DateTime.Now > _timeout;
}
回答by StumpDK
Mocking is of cause the best way to unit test your code.
模拟是对代码进行单元测试的最佳方式。
As far as integration tests go, I have had some issues using in-memory databases like SQLite, mainly because of small differences in behaviour and/or syntax.
就集成测试而言，我在使用 SQLite 等内存数据库时遇到了一些问题，主要是因为行为和/或语的细微差异。
I have been using a local instance of MySql for integration tests in several projects. A returning problem is the server setup and creation of test data. I have created a small Nuget package called Mysql.Server (see more at https://github.com/stumpdk/MySql.Server), that simply sets up a local instance of MySql every time you run your tests.
我一直在使用 MySql 的本地实例在几个项目中进行集成测试。返回的问题是服务器设置和测试数据的创建。我创建了一个名为 Mysql.Server 的小型 Nuget 包（在https://github.com/stampdk/MySql.Server 上查看更多信息），它在每次运行测试时只设置一个本地的 MySql 实例。
With this instance running you can easily set up table structures and sample data for your tests without being concerned of either your production environment or local server setup.
运行此实例后，您可以轻松地为您的测试设置表结构和示例数据，而无需担心您的生产环境或本地服务器设置。
