C# Unable to detach database

Question

I have a class that creates a database that saves the mdf file in a specified location. Then it copies tables from an existing database. Then creates stored procedures from an sql file. Then detaches the database created from the start once the process is done. My problem is that my detach method won't work throwing an exception saying that the database is in use. I have disposed my connections properly.

This is in-line with my previous question.

Here is my class:

Event
    private void btnFullBackup_Click(object sender, EventArgs e)
            {
                progressBar.Value = 0;   
                lblStatus.Text = "Starting full backup...";
                CreateDB("FULL");
                progressBar.Value = 20;      

                lblStatus.Text = "Copying tables...";
                CopyTables("FULL");
                progressBar.Value = 60;

                lblStatus.Text = "Creating stored procedures...";
                CreateStoredProcedures("FULL");
                progressBar.Value = 70;

                progressBar.Value = 80;

                DetachBackup("FULL");

                lblStatus.Text = "Done";
                progressBar.Value = 100;

                MessageBox.Show("Backup was created successfully", "",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);  
            }

Methods used:

void CreateDB(string type)
        {
            //define and browse location to save mdf
            lblStatus.Text = "Creating pysical database...";
            FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();            
            folderBrowserDialog.ShowDialog();
            lblStatus.Text = "Checking folder permission...";
            string selectedFolder = folderBrowserDialog.SelectedPath + "\\";
            newBackupLocation = selectedFolder;
            //check permission
            if (WriteAccessToFolder(selectedFolder) == false)
            {
                MessageBox.Show("The folder you have chosen does not have write permission", "Monytron",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                folderBrowserDialog.ShowDialog();
                return;
            }

            //create DB
            lblStatus.Text = "Creating database...";
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var query = GetDbCreationQuery(selectedFolder, type);
            using (var conn = new SqlConnection(connectionString))
            using (var command = new SqlCommand(query, conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    folderBrowserDialog.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    if ((conn.State == ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }
            }
        }
void CopyTables(string backupDBName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var query = CopyQuery(backupDBName + DateTime.Now.ToString("yyyyMMdd"));
            using (var conn = new SqlConnection(connectionString))
            using (var command = new SqlCommand(query, conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    if ((conn.State == ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }
            }            
        }

void CreateStoredProcedures(string type)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd")))
            {
                string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
                Server server = new Server(new ServerConnection(conn));
                try
                {
                    server.ConnectionContext.ExecuteNonQuery(spLocation);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

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 should first kill connections to the database if you want to keep connection pooling. You can do it setting the database in single user access with rollback_immediate clause before calling the detach method.

Have a look here to use C#: Is there a way to set the DB as Single User Mode in C#?

Or here to run T-SQL script: https://serverfault.com/questions/76432/how-can-i-detach-a-database-that-is-in-use

Answer 2

The Connection to the database is in most cases placed in a pool after using. This way you can re-connect quickly using the same connection string, but on the other hand, I suspect this pool of connections is blocking you from detaching a database.

You can probably do something like this:

• Put use master as the last statement in each query against database before you close the connection, or
• Modify connection string so it doesn't use pooling (uid=...; pwd=...; pooling=false;)

Hope it helps.