CODEWITHSUNDEEP
c#sql-server
J.P Masangcay
J.P Masangcay

Reputation: 769

C# How to drop connection when detaching database

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;
            }
        }

Upvotes: 5

Views: 1405

Answers (4)

sujith karivelil
sujith karivelil

Reputation: 29016

Make use of using statement.

"using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.

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

Updates

In your case the problem is that You cannot detach or reattach the database when it is being used. Here is an example from MSDN, This will be helpful for you to solve the issue

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer, or if you want to move the database.

Upvotes: 2

TheDanMan
TheDanMan

Reputation: 1836

Try the following

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 mentioned by Aaron Bertrand on our sister site on this question

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

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

Upvotes: 2

marc_s
marc_s

Reputation: 754468

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.

Upvotes: 4

Related Questions