Sql Server Drop Database failure

Question

I have a SQL Server database and an old backup of that database. Sometimes I have to verify some data in the backup copy using a C# WinForms application. I restore the backup file using this post: How to restore to a different database in sql server?.

My restore function looks like this:

SqlConnection myConn = new SqlConnection("Server=.\\sqlexpress;Database=master;Trusted_Connection=True;");
try
{
    if (!Databases.CheckDatabaseExists(myConn, fileName))
    {
        myConn.Open();
        SqlCommand cmd = new SqlCommand("RESTORE FILELISTONLY FROM DISK='" + fileName + ".bak'", myConn);
        SqlDataReader reader = cmd.ExecuteReader();
        cmd.CommandText = "restore database " + Path.GetFileName(fileName) + " from disk = '" + fileName + ".bak' with move'";
        int i = 0;
        while (reader.Read())
        {
            if (i == 0)
            {
                cmd.CommandText += reader[0].ToString() + "' to '" + filePath + "\\" + Path.GetFileName(fileName) + ".mdf', move ";
                i++;
            }
            else
            {
                cmd.CommandText += "'" + reader[0].ToString() + "' to '" + filePath + "\\" + Path.GetFileName(fileName) + ".mdf.ldf'";
            }
        }
        reader.Close();
        cmd.ExecuteNonQuery();
        myConn.Close();
        database.ReadDataBaseIstoric(dataGridView1, Path.GetFileName(fileName));
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        if (myConn.State == ConnectionState.Open) { myConn.Close(); }
    }

and the

database.ReadDataBaseIstoric(dataGridview1,Path.GetFileName(filename)); 

reads the data from the restored database and looks like this:

public void ReadDataBaseIstoric(DataGridView dataGridView1, string dataBaseName)
    {
        dataGridView1.Rows.Clear();
        SqlConnection conn = new SqlConnection("Server=.\\sqlexpress;Trusted_Connection=true;database=" + dataBaseName + ";");
        SqlDataReader reader = null;

        try
        {
            conn.Open();

            SqlCommand  cmd = new SqlCommand("select * from istoric", conn);
            conn.Close();
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string[] str = new string[5] { reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString() };
                dataGridView1.Rows.Add(str);
            }
            reader.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            if (reader != null && !reader.IsClosed) { reader.Close(); }
            if (conn.State == ConnectionState.Open) { conn.Close(); }
        }
    }

Everything works fine for now. The problem is that when I try to delete the restored database it returns en error which says that database couldn't be deleted because it's still in use. This is how I want to delete the database:

private void Arhiva_FormClosing(object sender, FormClosingEventArgs e)
{
        bool closed = false;
        if (!closing) { e.Cancel = true; closing = false; closed = true; }
        SqlConnection myConn = new SqlConnection("Server=.\\sqlexpress;Database=master;Trusted_Connection=True;");

        try
        {
            if (Databases.CheckDatabaseExists(myConn, Path.GetFileName(fileName)))
            {
                myConn.Open();
                SqlCommand cmd = new SqlCommand("DROP DATABASE "+Path.GetFileName(fileName), myConn);
                cmd.ExecuteNonQuery();
                myConn.Close();
                label1.Visible = false;
            }
            else
            {
                MessageBox.Show("Exista deja o baza de date cu numele '" + fileName + "'.", "VivaFEED", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            if (myConn.State == ConnectionState.Open) { myConn.Close(); }
        }
        if (closed) { this.Close(); }
  }

I have tried to delete the restored database before reading the data from it and it worked great, so I think the problem should be in the database.ReadDataBaseIstoric().

P.S. I am using the database.ReadDataBaseIstoric() function also for reading data from the current database (not the restored backup) an there it works great, without any errors or exceptions.

Answer 1

You've three issues here. First you aren't disposing as suggested by @jyparask.

The other is by default you are using the connection pool. This means even if you close the connection, it stays alive in the pool for a default time (2 minutes I think). So if I was doing this, I'd be adding pooling = false to that connection string as well. Because of that I might against all recommendation, instance a connection and then pass it about and dispose of it during the drop operation

Last but not least you are connecting to the database you are trying to drop aren't you? Best bet would be to create a new connection to the Master database in order to drop the one you want.

Answer 2

Try changing your function to:

public void ReadDataBaseIstoric(DataGridView dataGridView1, string dataBaseName)
{
    dataGridView1.Rows.Clear();

    using(SqlConnection conn = new SqlConnection("Server=.\\sqlexpress;Trusted_Connection=true;database=" + dataBaseName + ";"))
    using(SqlCommand  cmd = new SqlCommand("select * from istoric", conn))
    {
        SqlDataReader reader = null;
        try
        {

            conn.Open();
            using(SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string[] str = new string[5] { reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString() };
                    dataGridView1.Rows.Add(str);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

using will close and dispose the objects.