Category : .Net | Author : Chtiwi Malek | First posted : 3/18/2012 | Updated : 4/27/2012
Tags : sql, t-sql, asp.net, c#, data, database, drop, ssms
Removing a Database using SQLDMO.SQLServer

Removing a Database using SQLDMO.SQLServer

In a project I'm working on now, I was trying to drop a database programmatically, here is the code I wrote:

// Connection to SQL
SQLDMO.SQLServer fServ = new SQLDMO.SQLServer();
fServ.LoginSecure = true;
fServ.Connect(ConnStr, "", "");

// Removing the database
fServ.Databases.Item("DbName").Remove();
But, then when executing this code against some databases I got this error:

Cannot drop database "DbName" because it is currently in use. (Microsoft SQL Server, Error: 3702)

In fact theses databases where in use at that time and SQLDMO.SQLServer was enable to remove them, the same thing happens when trying to remove a database in Microsoft SQL Server Management Studio without checking the “close existing connections” box, so we need to Close existing connections first, I added a T-SQL  command to do that :

// Connection to SQL
SQLDMO.SQLServer fServ = new SQLDMO.SQLServer();
fServ.LoginSecure = true;
fServ.Connect(ConnStr, "", "");

// Close all current database's connections
SQLDMO.Database oDb = (SQLDMO.Database)fServ.Databases.Item(dbname);
oDb.ExecuteImmediate("ALTER DATABASE " + dbname + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");

// Finally remove the database
fServ.Databases.Item(dbname).Remove();
Leave a Comment:
Name :
Email : * will not be shown
Title :
Comment :