title: C# how to check if database is not busy?
body: <p>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"</p>
  
  <p>Methods:</p>
  
  <pre><code>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;
          }
  </code></pre>
  
  <p>Click event:</p>
  
  <pre><code>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");
              }
          }
  </code></pre>