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