C# how to check if database is not busy?

Question

My class has a couple of methods going on. The first one is creating a database, that's done. Then, creates stored procedures that is being read from a sql file. then detach that DB. Now it seems that my store procedure query is taking a while to finish and my method to detach is being invoked while the database is busy. So how do I tell if the database is idle. The exception goes "cannot detach the database because it is currently in use"

Methods:

void CreateStoredProcedures(string type)
        {
            string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
            var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd"));            

            try
            {
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(spLocation);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

bool DetachBackup(string type)
        {
            var conn = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand("", conn);
            command.CommandText = @"sys.sp_detach_db '" + type + DateTime.Now.ToString("yyyyMMdd") + "'";

            try
            {
                conn.Open();
                command.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
            finally
            {
                if ((conn.State == ConnectionState.Open))
                {
                    conn.Close();
                }
            }
            return true;
        }

Click event:

private void btnFullBackup_Click(object sender, EventArgs e)
        {
            lblStatus.Text = "Starting full backup...";
            Execute("FULL");
            progressBar.Value = 20;                       
            lblStatus.Text = "Copying tables...";
            progressBar.Value = 60;            
            CopyTables("FULL");
            progressBar.Value = 70;
            lblStatus.Text = "Creating stored procedures...";
            CreateStoredProcedures("FULL");

            progressBar.Value = 80;
            CheckDBSize(newBackupLocation, "FULL");

            progressBar.Value = 100;
            MessageBox.Show("Backup was created successfully", "",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);            

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

            if (DetachBackup("FULL") == false)
            {
                DetachBackup("FULL");
            }
        }

Answer 1

Chances are it's getting hung on its own connection. sp_detach_db's MSDN https://msdn.microsoft.com/en-CA/library/ms188031.aspx has the following suggestion under the section Obtain Exclusive Access:

USE master; 
ALTER DATABASE [DBName] SET SINGLE_USER;

You're DetachBackup method will have connect to master, run the ALTER and the sp_detach_db procedure.

Answer 2

You aren't closing the connection in your CreateStoredProcedures method. Put using statements in like I've shown here and it should fix the problem. (Brief using statement explanation from Microsoft.)

Try this code for your methods:

    void CreateStoredProcedures(string type)
    {
        string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
        using (var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd")))
        {
            try
            {
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(spLocation);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        } // End of using, connection will always close when you reach this point.
    }

    bool DetachBackup(string type)
    {
        using (var conn = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(@"sys.sp_detach_db '" + type + DateTime.Now.ToString("yyyyMMdd") + "'", conn);

            try
            {
                conn.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        } // End of using, connection will always close when you reach this point.
        return true;
    }

Answer 3

You shouldn't think of it as the database being "busy", but the error message uses good verbage: in use. To find out if the database is currently in use, the most accurate way would be to find out if any sessions have any lock in the particular database, by querying sys.dm_tran_locks. Here is a helper function to return a bool whether or not the database is in use:

    bool IsDatabaseInUse(string databaseName)
    {
        using (SqlConnection sqlConnection = new SqlConnection("... your connection string ..."))
        using (SqlCommand sqlCmd = new SqlCommand())
        {
            sqlCmd.Connection = sqlConnection;
            sqlCmd.CommandText =
                @"select count(*)
                from sys.dm_tran_locks
                where resource_database_id = db_id(@database_name);";
            sqlCmd.Parameters.Add(new SqlParameter("@database_name", SqlDbType.NVarChar, 128)
            {
                Value = databaseName
            });

            sqlConnection.Open();
            int sessionCount = Convert.ToInt32(sqlCmd.ExecuteScalar());

            if (sessionCount > 0)
                return true;
            else
                return false;
        }
    }

Note: Make sure your initial catalog in your connection string isn't the database you're trying to make "not in use", as that'll put your current session in the context of the database, not allowing that operation to complete