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?
Here is how you do it using Entity Framework version 6
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.
Create sqlconnection object for different database other than you want to delete.
Just don't use DB name in connection string.
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))Try this:
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 ausing
block eliminates the need to explicitly close or dispose the connection. All you really need to do is: