C# How to drop connection when detaching database

Question

I currently have the following Command

SqlCommand command = new SqlCommand(@"sys.sp_detach_db 'DBname'", conn);

to detach a database, but it throws an exception when I execute it. Saying that the database is in use. How can I drop the connection when or before I detach it?

Update: I am currently using SMO but it's still not working:

bool DetachBackup(string backupDBName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var builder = new SqlConnectionStringBuilder(connectionString);
            string serverName = builder.DataSource; 
            string dbName = builder.InitialCatalog;
            try
            {
                Server smoServer = new Server(serverName);
                smoServer.DetachDatabase(backupDBName + DateTime.Now.ToString("yyyyMMdd"), false);
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        }

Answer 1

You'll need to execute this SQL statement first, before detaching:

ALTER DATABASE YourDbNameHere
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

It sets the database into single user mode, and tosses out any active connection immediately.

Answer 2

You can use SMO

Detaches the specified database from the instance of SQL Server with the option to update statistics before the database is detached.

using Microsoft.SqlServer.Management.Smo;

 void DetachDatabase()
 {
      Server smoServer = new Server("MSSQLSERVER2008");
      smoServer.DetachDatabase("Yourdatabasename", False);
 }

To get server name from the app.config you can try like this:

string connectString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectString);  
string serverName = builder.DataSource;   //Server name
string dbName = builder.InitialCatalog; //Database name