Dropping SQL Server database through C#

2020-02-25 22:34发布

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?

7条回答
别忘想泡老子
2楼-- · 2020-02-25 23:07

Here is how you do it using Entity Framework version 6

System.Data.Entity.Database.Delete(connectionString);
查看更多
狗以群分
3楼-- · 2020-02-25 23:08

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.

查看更多
\"骚年 ilove
4楼-- · 2020-02-25 23:09

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

sqlCommandText = "DROP DATABASE [DBNAME]";
sqlCommand = new SqlCommand(sqlCommandText , sqlconnection);
sqlCommand.ExecuteNonQuery();
查看更多
▲ chillily
5楼-- · 2020-02-25 23:09

Just don't use DB name in connection string.

"Data Source=.\SQLEXPRESS;Integrated Security=True;"
查看更多
等我变得足够好
6楼-- · 2020-02-25 23:25

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))

查看更多
孤傲高冷的网名
7楼-- · 2020-02-25 23:29

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;
查看更多
登录 后发表回答