CODEWITHSUNDEEP
c#disposesqlconnection
Claudi
Claudi

Reputation:

How to force SQLconnection to release a database?

I have written a small application, that can restore a database (C# and SQL2005), but after I have accessed the database, I can't drop it - it says that it is in use.. I guess it has to do with the SQLconnection-pooling, but can I force it to relase the database??

Upvotes: 0

Views: 1599

Answers (5)

user2489905
user2489905

Reputation: 1

"Call "USE SomeOtherDB" (e.g Master) to close your own connection, or one of" 

//on master ... CREATE
using (var cnn = new SqlConnection(MASTER))
{
    cnn.Open();
    var createDbCmd = new SqlCommand(string.Format("create database [{0}]", db), cnn).ExecuteNonQuery();
    cnn.Close();
}
using (var cnn = new SqlConnection(tempDB))
{
    cnn.Open();
    var createTbl = new SqlCommand(string.Format("create table t (t int )"), cnn).ExecuteNonQuery();
    var dropTbl = new SqlCommand(string.Format("drop table t"), cnn).ExecuteNonQuery();
    //Do additional stuf
    var userMaster = new SqlCommand(string.Format("use master"), cnn).ExecuteNonQuery();
    cnn.Close();

}
//on master ... CREATE
using (var cnn = new SqlConnection(MASTER))
{
    cnn.Open();
    var dropDbCmd = new SqlCommand(string.Format("drop database [{0}]", db), cnn).ExecuteNonQuery();
    cnn.Close();

}

Upvotes: 0

Fabian Vilers
Fabian Vilers

Reputation: 2992

Dispose your SqlConnection object.

Upvotes: 1

stuartd
stuartd

Reputation: 73303

Call "USE SomeOtherDB" (e.g Master) to close your own connection, or one of

ALTER DATABASE SET SINGLE_USER

or

ALTER DATABASE SET SINGLE_USER WITH ROLLBACK_IMMEDIATE

to close other connections. The first waits for connections to conclude, the second is immediate.

Upvotes: 0

Dean
Dean

Reputation: 5946

If you utlise 

Using SQLConnection
    '' do your stuff here
End Using

I think this then forces the freeing of resources after it exits

Upvotes: 0

leppie
leppie

Reputation: 117360

Specify 'Pooling=False' in the connection string.

Upvotes: 2

Related Questions