实体框架无法删除数据库，数据库正在使用中 [英] Entity Framework unable to delete database, database in use
问题描述
我（像许多其他人）遇到问题，我无法获得我的NUnit测试，以删除我的测试夹具的 [SetUp] 中的数据库。
我想要实现
我想编写测试代码的集成测试，并验证预期结果存储在数据库（Assert CRUD方法）中。那个和我想能够在SqlServer中实际显示表，看看数据库中的结果如何。最后一部分似乎是难以实现的...
发生什么问题
我可以运行我的测试，连续多次。数据库每次都在 [SetUp] 中重新创建，测试通过断言。一旦我想在SqlServer中的实际数据库中检查我的结果，它就会排除在外。一旦我从SqlServer打开了一个连接， [SetUp] 方法不允许删除数据库，因为它有开放的连接。
我尝试了什么
- 数据库初始化程序
- ALTER数据库SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Pooling = false added to connection string
我有什么
[SetUp] 方法：
[SetUp]
public void SetUp（）
{
// TenantSeedInitializer扩展
// 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（）;
}
一个非常简单的测试：
[测试]
public void AddTenant（）
{
//向数据库添加一个新租户，并验证
//表中只有一位租客
}
当我说，连续运行这个测试几次就像一个魅力，直到我尝试在SqlServer中打开表。
两个不同（对我来说没有解决）由于visual studio的连接仍然是开放的，我不允许查看表格。 $ /
数据库TestTenantDatabase已经打开，一次只能有一个用户。
数据库TestTenantDatabase已经打开，一次只能有一个用户。
添加 SqlConnection.ClearAllPools（）; 似乎没有解决这个问题。
2）或者我是在SqlServer中查看表，然后我不再允许从我的 [SetUp] fixture删除数据库。
无法删除数据库TestTenantDatabase，因为它目前正在使用。
关闭SqlServer是我知道解决这个问题的唯一方法。但是，我发现自己在白天重新启动SqlServer 很多 ...（关闭与数据库的连接的选项也将有所帮助，但我找不到）。
有人可以指导我吗？
此消息：
数据库TestTenantDatabase已经打开，一次只能有一个
用户。
正在发生，因为您已将数据库设置为单个用户。通常你会这样做会杀死与数据库的其他连接，并使连接成为唯一可以访问它的连接。但是，如果您更改数据库上下文，则可以连接其他应用程序，如Visual Studio，并成为单个用户，有效地锁定您。
在设置中尝试此操作夹具：
使用TestTenantDatabase;
alter database TestTenantDatabase将single_user设置为rollback immediate;
使用主;
drop database TestTenantDatabase;
如果您在设置单个用户时在数据库中，则将成为单个用户。然后，如果您更改为master，然后在同一批次中删除数据库，则应该击败任何尝试连接到该数据库的用户。
另一个选项是设置脱机的单用户，但是，当您删除数据库时，它不会删除数据库.mdf& .ldf（和任何.ndf）文件，因此您可能会重新创建另一组测试的数据库。
如果在运行single_user时遇到与死锁相关的错误，将您的僵局优先级设置为高。如果要重新使用连接，您可以将其恢复正常。
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.
What I want to achieve
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...
What is going wrong
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.
What have I tried
- Database initializer
- ALTER database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Pooling=false added to connection string
I took those ideas from this and this SO post.
What do I have
The [SetUp] method:
[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");
}
The [TearDown] method:
[TearDown]
public void TearDown()
{
SqlConnection.ClearAllPools();
}
And a pretty straight forward test:
[Test]
public void AddTenant()
{
// add a new tenant to the database and verify that there
// there is only one tenant present in the table
}
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.
Two different (for me unsolved) error scenario's
1) Either I am not allowed to view the table because the connection from visual studio is still open.
Database 'TestTenantDatabase' is already open and can only have one user at a time.
Adding the SqlConnection.ClearAllPools(); does not seem to solve this.
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.
Cannot drop database "TestTenantDatabase" because it is currently in use.
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).
Can anybody guide me through this?
This message:
Database 'TestTenantDatabase' is already open and can only have one user at a time.
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.
Try this in your setup 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.
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.
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屋！
