How to Detach a database so that it could be copied/moved?

Question

I used Database first EF method to create a Model in a wpf application using C#. After adding relevant information, changing column's names, table's names, I want to copy it to a new location. But I get the error message that the database is in use.

using (var ctx = new TableGraphDBEntities())
{
    for (int l = 0, m = 1; l < TableNames.Count; l++, m++)
    {
        string columnxQ = "sp_rename 'Table1.x','" + XNames[l] + "', 'COLUMN'";
        string columnyQ = "sp_rename 'Table1.y','" + YNames[l] + "', 'COLUMN'";
        string talbeQ = "sp_rename Table" + m.ToString() + "," + TableNames[l];
        string detachQ = "sp_detach_db @dbname = N'TableGraphDB'";
        //string setOffline = "
        //string detachQ = "USE master; GO EXEC sp_detach_db @dbname = N'TableGraphDB'; GO";
        ctx.Database.ExecuteSqlCommand(columnxQ);
        ctx.Database.ExecuteSqlCommand(columnyQ);
        ctx.Database.ExecuteSqlCommand(talbeQ);
        ctx.Database.ExecuteSqlCommand(detachQ);


    }     
}
string oldFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "TableGraphDB.mdf";
string FilePath = Properties.Settings.Default.ExtensionFileDir + @"\Saved DataBases\" + fnw.FileName + ".mdf";
if (File.Exists(FilePath))
{
    MessageBox.Show("The database aleady exist, please type a different name");
    return;
}
//FileInfo fileInfo = new FileInfo(oldFilePath);
//while (IsFileLocked(fileInfo))
//{

//}
File.Copy(oldFilePath, FilePath);
MessageBox.Show("Database is saved!");

Here is the link to slq command http://msdn.microsoft.com/en-gb/library/ms187858.aspx

The database is located in the Bin folder. It is copied as a new copy from the project each time I run the application. If I close the application and reopen it, it can be copied with all the data.

I tried using ExecuteSqlCommand(), but it didn't help. So how could I detach the database so that I could move/copy it?

Unfortunately StackOverFlow doesn't let me to answer my own question, so I have to write it down in here.

Answer: First of all you have to check the actual name of your Database, it's not necessarily the name of the database file. In order to do that you need to:

1. Open the server explorer in Visual Studio or SQL Management Studio.
2. Open new query.
3. Execute the following query: SELECT name, create_date FROM sys.databases
4. Check the name of the database (in my case it was the full path of the database file, it can be only the name of the database or anything else.)

Then you can change the name of the columns and tables by executing the following code:

using (var ctx = new TableGraphDBEntities())
{
    for (int l = 0, m = 1; l < TableNames.Count; l++, m++)
    {
        string columnxQ = "sp_rename 'Table1.x','" + XNames[l] + "', 'COLUMN'";
        string columnyQ = "sp_rename 'Table1.y','" + YNames[l] + "', 'COLUMN'";
        string talbeQ = "sp_rename Table" + m.ToString() + "," + TableNames[l];
        ctx.Database.ExecuteSqlCommand(columnxQ);
        ctx.Database.ExecuteSqlCommand(columnyQ);
        ctx.Database.ExecuteSqlCommand(talbeQ);
        ctx.Dispose();
    }
}

And finally use the following code to detach and move it to a new location:

string oldFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "TableGraphDB.mdf";
string conString = @"Data Source=(LocalDB)\v11.0;Integrated Security=True;";
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
{
    string query = @"USE [master] 
        ALTER DATABASE ["+oldFilePath+@"] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
        USE [master] 
        EXEC master.dbo.sp_detach_db @dbname = N'"+oldFilePath+"'";
    SqlCommand cmd = new SqlCommand(query, con);
    con.Open();
    cmd.ExecuteNonQuery();
    cmd.Dispose();
    con.Dispose();
}

string FilePath = Properties.Settings.Default.ExtensionFileDir + @"\Saved DataBases\" + fnw.FileName + ".mdf";
if (File.Exists(FilePath))
{
    MessageBox.Show("The database aleady exist, please type a different name");
    return;
}
File.Copy(oldFilePath, FilePath);

Note: If you try implement ctx.Database.ExecuteSqlCommand(query), you'll get the following error:

ALTER DATABASE statement not allowed within multi-statement transaction. The procedure 'sys.sp_detach_db' cannot be executed within a transaction. Changed database context to 'master'.

Answer 1

If you're using EF6 you may try this code:

string command = "EXEC sp_detach_db 'TableGraphDB', 'true'";
dbContext.ExecuteStoreCommand(command);

Second parameter in sp_detach_db procedure will drop all connections to this database

Answer 2

In SSMS this is the query that is used to detach a database. Can you try this?

You should just be able to wrap the whole thing up as a string and execute it. I'm not sure if it will be happy with the 'GO', so you might have to remove those.

string detachQ = "" + 
"USE [master]\n" +
"GO\n" +
"ALTER DATABASE [TableGraphDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE\n" +
"GO\n" +
"USE [master]\n" +
"GO\n" +
"EXEC master.dbo.sp_detach_db @dbname = N'TableGraphDB'\n" +
"GO\n";

ctx.Database.ExecuteSqlCommand(detachQ);