469,336 Members | 5,787 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

Drop SQL Database

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

Aug 16 '06 #1
2 15497
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;

<bo**********@hotmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
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

Aug 16 '06 #2
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.

<bo**********@hotmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
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

Aug 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jim Jawn | last post: by
1 post views Thread by MailSurfer | last post: by
1 post views Thread by tgru | last post: by
7 posts views Thread by aixunix | last post: by
5 posts views Thread by Robert Fitzpatrick | last post: by
reply views Thread by Jeff Beem | last post: by
4 posts views Thread by Michael C | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.