CODEWITHSUNDEEP
c#.netsql-server-2008
Anshuman Jasrotia
Anshuman Jasrotia

Reputation: 3185

Dropping SQL Server database through C#

I am using this code to delete a database through C#

Int32 result = 0;

try
{
        String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000);

        SqlConnection con = new SqlConnection();
        con.ConnectionString = Connectionstring;

        String sqlCommandText = "DROP DATABASE [" + DbName + "]";
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
            SqlConnection.ClearPool(con);
            con.ChangeDatabase("master");
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
            sqlCommand.ExecuteNonQuery();
        }
        else
        {
            con.ChangeDatabase("master");
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
            sqlCommand.ExecuteNonQuery();
        }



        con.Close();
        con.Dispose();
        result = 1;
    }
    catch (Exception ex)
    {
        result = 0;
    }
    return result;

But I get an error

Database currently in use

Can anyone help?

Upvotes: 16

Views: 25639

Answers (8)

Jokri
Jokri

Reputation: 1

I was having the same troubles as Anshuman... By my testing of the code in question of Anshuman there have been very simple error: there have to be SqlConnection.ClearAllPools(); instead of SqlConnection.ClearPool(con);

Like this trouble of

"cannot drop database because is in use..."

disappears.

Upvotes: 0

WhiteSnake
WhiteSnake

Reputation: 11

Just don't use DB name in connection string.

"Data Source=.\SQLEXPRESS;Integrated Security=True;"

Upvotes: 1

Bassem
Bassem

Reputation: 3026

Here is how you do it using Entity Framework version 6

System.Data.Entity.Database.Delete(connectionString);

Upvotes: 27

Dave Markle
Dave Markle

Reputation: 97811

Try this:

String sqlCommandText = @"
ALTER DATABASE " + DbName + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [" + DbName + "]";

Also make sure that your connection string defaults you to the master database, or any other database other than the one you're dropping!

As an aside, you really don't need all of that stuff around your queries. The ConnectionState will always start off Closed, so you don't need to check for that. Likewise, wrapping your connection in a using block eliminates the need to explicitly close or dispose the connection. All you really need to do is:

String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000);

using(SqlConnection con = new SqlConnection(Connectionstring)) {
    con.Open();
    String sqlCommandText = @"
        ALTER DATABASE " + DbName + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [" + DbName + "]";
    SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
    sqlCommand.ExecuteNonQuery();
}
result = 1;

Upvotes: 32

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Connection pooling at a guess, use sql server's activity monitor to make sure though.

Pooling keeps connections to the database alive in a cache, then when you create a new one, if there's one in the cache it hands it back instead of instantiating a new one. They hang around for a default time, (2 minutes I think) if they don't get re-used in that time, then they killed off.

So as a first go connect straight to master, instead of using change database, as I suspect change database will simply swap connections in the pool.

Add a check routine for database in use (use a connection to master to do it!). You can force the database to be dropped anyway by first executing

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

again from the connection to master!

However everybody else using the db, will no longer like you at all...

Upvotes: 2

Leonardo
Leonardo

Reputation: 11401

In this case i would recommend that you take the database offline first... that will close all connections and etc... heres an article on how to do it: http://blog.sqlauthority.com/2010/04/24/sql-server-t-sql-script-to-take-database-offline-take-database-online/

Microsoft clearly states that A database can be dropped regardless of its state: offline, read-only, suspect, and so on. on this MSDN article (DROP DATABASE (Transact-SQL))

Upvotes: 2

Mayank Pathak
Mayank Pathak

Reputation: 3681

You should take a look at SMO. These allow you to manage all aspects of SQL Server from code, including deleting of databases.

The database object has a Drop method to delete database.

Upvotes: 4

sreejithsdev
sreejithsdev

Reputation: 1210

Create sqlconnection object for different database other than you want to delete.

sqlCommandText = "DROP DATABASE [DBNAME]";
sqlCommand = new SqlCommand(sqlCommandText , sqlconnection);
sqlCommand.ExecuteNonQuery();

Upvotes: 2

Related Questions