Drop SQL Database

[bob_jeffcoat]

Hi,

I'm trying to:
1) create an SQL database in C#
2) open a connection to it
3) close the connection
4) drop the database

If I don't do parts 2) and 3) it works, I can create and drop a
database. If I do parts 2) and 3) the drop fails as the database is in
use. Why? Can anyone help?

Here's my test:

[Test]
public void DeleteTest()
{
SqlCommand cmd;
string sql;

string databaseName = "TEST";

SqlConnection sqlConnection = new SqlConnection(
"Server=Server1;Integrated Security=True;User
Instance=False");

sqlConnection.Open();


sql = "CREATE DATABASE " + databaseName;
cmd = new SqlCommand(sql, sqlConnection);
cmd.ExecuteNonQuery();

SqlConnection databaseConnection = new SqlConnection(
"Server=Server1;Initial Catalog=" + databaseName +
";Integrated Security=True;User Instance=False");
databaseConnection.Open();
databaseConnection.Close();

System.Threading.Thread.Sleep(10000);


sql = "DROP DATABASE " + databaseName;
cmd = new SqlCommand(sql, sqlConnection);
cmd.ExecuteNonQuery();
}

Any help would be greatly appriciated.

Thanks,

Bob

 

[Siva M]

That's because ADO.NET keeps connections open internally for connection
pooling.

If you are using .NET 2.0, call SqlConnection.ClearPool() or ClearAllPools()
after closing the connection so that all pooled connections are closed.
Alternatively, set Pooling=false in the connection string when opening the
connection.

Hi,

I'm trying to:
1) create an SQL database in C#
2) open a connection to it
3) close the connection
4) drop the database

If I don't do parts 2) and 3) it works, I can create and drop a
database. If I do parts 2) and 3) the drop fails as the database is in
use. Why? Can anyone help?

Here's my test:

[Test]
public void DeleteTest()
{
SqlCommand cmd;
string sql;

string databaseName = "TEST";

SqlConnection sqlConnection = new SqlConnection(
"Server=Server1;Integrated Security=True;User
Instance=False");

sqlConnection.Open();


sql = "CREATE DATABASE " + databaseName;
cmd = new SqlCommand(sql, sqlConnection);
cmd.ExecuteNonQuery();

SqlConnection databaseConnection = new SqlConnection(
"Server=Server1;Initial Catalog=" + databaseName +
";Integrated Security=True;User Instance=False");
databaseConnection.Open();
databaseConnection.Close();

System.Threading.Thread.Sleep(10000);


sql = "DROP DATABASE " + databaseName;
cmd = new SqlCommand(sql, sqlConnection);
cmd.ExecuteNonQuery();
}

Any help would be greatly appriciated.

Thanks,

Bob

 

[Winista]

I think connection pooling is preventing it to happen. Once a connection is
opened, ADO.Net has pooled it. And when you tried to drop the table, it saw
there is an open connection.
Try turning off connection pooling. You can add one more entry in connection
string to try it.

Pooling=false;