C # How to remove a connection when detaching a database

Question

I currently have the following command

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

to separate the database, but when it is executed, it throws an exception. A statement that the database is being used. How can I remove a connection when or when I disconnect it?

Update: I am currently using SMO, but it still does not work:

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 can use smo

Disconnects the specified database from an instance of SQL Server by using the ability to update statistics before the database is disconnected.

using Microsoft.SqlServer.Management.Smo;

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

To get the server name from app.config, you can try the following:

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

Answer 2

SQL, :

ALTER DATABASE YourDbNameHere
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

.

Answer 3

using statement.

"" , , , .

using(SqlCommand command = new SqlCommand(@"sys.sp_detach_db 'DBname'", conn))
{
//Processing command
}//Here it detach the connection and dispose the command

, . MSDN. .

SQL Server. , SQL Server , .

Answer 4

Try to execute

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

As Aaron Bertrand mentioned on our partner site on question