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.

Apart from my solution, i can see that you close the connection before you execute the reader, so there might be an issue there. — Giannis Paraskevopoulos, Sep 15 '13 at 11:37

score 2 · Accepted Answer · answered Sep 15 '13 at 11:30

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.

answered Sep 15 '13 at 11:30

Giannis Paraskevopoulos

18,261
1
49
69

It would be a good idea to check for conn != null in the catch block and call conn.dispose() as well. To be really right, same for cmd and reader. Or put inner catch blocks around the using statements and call throw in the inner ones. – Tony Hopkinson Sep 15 '13 at 12:00
@TonyHopkinson So you say i should have left the finally part? – Giannis Paraskevopoulos Sep 15 '13 at 12:03
No using is okay, just need to make sure if an exception gets thrown all the finallys implicit or explicit still get executed. Looks like they might in this case after a closer look. – Tony Hopkinson Sep 15 '13 at 13:17
Your solution and adding pooling=false; in the connection string did the job. Thank you – Jaws Sep 15 '13 at 21:09

score 2 · Answer 2 · answered Sep 15 '13 at 11:54

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.

I am connecting to the master database when I am trying to drop the database. See the private void Arhiva_FormClosing(object sender, FormClosingEventArgs e) event. The other 2 issues are existent though. — Jaws, Sep 15 '13 at 12:00

Sql Server Drop Database failure

2 Answers2