实体框架无法删除数据库，数据库在使用中 [英] Entity Framework unable to delete database, database in use

问题描述

我（像许多其他人）遇到的问题，我不能得到我的NUnit测试删除数据库中的 [SetUp] 我的测试夹具。

I am (like many others) running into the problem that I can't get my NUnit tests to delete the database in the [SetUp] of my test fixture.

我想编写测试我的代码的集成测试，结果存储在数据库中（Assert CRUD方法）。那，和我想能够实际显示SqlServer中的表，并查看数据库中的结果如何。最后一部分似乎很难实现...

I want to write integration tests that test my code and verify if the expected results are stored in the database (Assert CRUD methods). That, and I want to be able to actually show the tables in SqlServer and see how the results in the database look like. That last part seems a hard thing to achieve...

测试，连续多次。每次都在 [SetUp] 中重新创建数据库，测试通过断言。一旦我想在SqlServer的实际数据库中检查我的结果，它就会消失。一旦我从SqlServer打开一个连接， [SetUp] 方法不允许删除数据库，因为它有打开的连接。

I can run my tests, many times in a row. The database is recreated in the [SetUp] every time and the tests pass the asserts. It goes down the drain as soon as I want to check my result in the actual database in SqlServer. Once I opened a connection from SqlServer, the [SetUp] method is not allowed to delete the database, since it has open connections.

• 数据库初始化程序


• ALTER数据库SET SINGLE_USER WITH ROLLBACK IMMEDIATE


• 已将Pooling = false添加到连接字符串

=http://stackoverflow.com/questions/5494158/entity-framework-4-1-code-first-setinitializer-not-being-called-again-after-da>这和 this SO post。

I took those ideas from this and this SO post.

[SetUp] 方法：

    [SetUp]
    public void SetUp()
    {
        // TenantSeedInitializer extends the 
        // DropCreateDatabaseAlways<TenantApplicationTestContext> class

        Database.SetInitializer(new TenantSeedInitializer());
        _applicationContext = new TenantApplicationTestContext();
        _applicationContext.Database.ExecuteSqlCommand("ALTER DATABASE " + 
            TenantApplicationTestContext.DatabaseName + 
            " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
    }

[TearDown] 方法：

    [TearDown]
    public void TearDown()
    {
        SqlConnection.ClearAllPools();
    }

一个非常简单的测试：

    [Test]
    public void AddTenant()
    {
        // add a new tenant to the database and verify that there
        // there is only one tenant present in the table  
    }

说，在连续运行这个测试几次连续工作就像一个魅力，直到我尝试在SqlServer中打开表的点。

As I said, running this test several times in a row works like a charm, until the point where I try to open the table in SqlServer.

1）我不允许查看表，因为visual studio的连接仍然打开。

1) Either I am not allowed to view the table because the connection from visual studio is still open.

数据库'TestTenantDatabase'已经打开，每次只能有一个用户。

Database 'TestTenantDatabase' is already open and can only have one user at a time.

添加 SqlConnection.ClearAllPools（）; 似乎无法解决这个问题。

Adding the SqlConnection.ClearAllPools(); does not seem to solve this.

2）或者我可以查看SqlServer中的表，然后我不再允许从我的 [SetUp] fixture。

2) Or I am allowed to view the table in SqlServer, and then I am no longer allowed to delete the database from my [SetUp] fixture.

无法删除数据库TestTenantDatabase 因为它目前正在使用中。

Cannot drop database "TestTenantDatabase" because it is currently in use.

关闭SqlServer是我知道的唯一方法摆脱这一点。但是后来我发现自己在白天重新启动SqlServer 很多 ...（一个关闭数据库连接的选项也会有所帮助，但我找不到）。

Shutting down SqlServer is the only way I know to get rid of this. But then I find myself restarting SqlServer a lot during the day... (an option to close the connection to the database would also help, but I can't find it).

推荐答案

此讯息：

b
$ b

This message:

数据库'TestTenantDatabase'已经打开，每次只能有一个
用户。

Database 'TestTenantDatabase' is already open and can only have one user at a time.

正在发生，因为您已将数据库设置为单用户。通常你会杀死其他连接到数据库，并使你的连接只有一个可以访问它。但是，如果您更改数据库上下文，其他一些应用程序可以像Visual Studio一样连接，并成为单个用户，有效地锁定您。

is happening because you have set the database to single user. Usually you would do this kill other connections to the database, and make your connection the only one that can access it. However, if you change your database context some other application could connect in, like Visual Studio, and become the single user, effectively locking you out.

fixture：

use TestTenantDatabase;
alter database TestTenantDatabase set single_user with rollback immediate;
use master;
drop database TestTenantDatabase;

如果在数据库中设置单用户时，您将成为单用户。

If you are in the database when you set single user, you will become the single user. Then, if you change to master and then delete the database within the same batch, it should beat anyone trying to connect into it.

另一个选项是设置为离线，而不是设置为离线，而不是在同一批次中删除数据库。的single_user，但是，当你删除数据库时，它不会删除数据库.mdf& .ldf（和任何.ndf）文件，因此您可能会有问题为另一组测试重新创建数据库。

Another option is to set offline, instead of single_user, however, when you then delete the database, it will not delete the database .mdf & .ldf (and any .ndf) files, and so you could have issues recreating the database for another set of tests.

如果运行single_user时遇到与死锁相关的错误，将你的死锁优先级设​​置为高。如果您要重新使用连接，您可以在删除后将其重新设置为正常。

If you get errors relating to deadlocks when running single_user, set your deadlock priority to high. You can set it back to normal after the drop, if you are going to reuse the connection.

这篇关于实体框架无法删除数据库，数据库在使用中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！