How to close reader properly?

Question

So, I want to check if the table exists in database and if it exists it has to throw a MessageBox. My problem is, when I execute the Reader I'm unable to close it and it gives the following error:

There is already an open DataReader associated with this Connection which must be closed first.

string query2 = $"SHOW TABLES WHERE Tables_in_appdb LIKE '%{tableNamee}%'";

var conn = new MySqlConnection(dbConnectionString);
conn.Open();

var cmd2 = new MySqlCommand(query2, conn);

var reader = cmd2.ExecuteReader();
if (reader.Read())
   {
     ia.flag = "stop";
     cmd2.Cancel();
     reader.Close();                                       
   }
else
   {
     cmd.ExecuteNonQuery();
     ia.flag = "continue";
     cmd2.Cancel();
     reader.Close();
   }         

conn.Close();
return true;

I'm using the reader.Close() but it seems like I have to do something else. Any ideas?

p.s.: the "ia.flag" is just a string I want to pass to other class if the table exists or not.

EDIT This works:

string query2 = $"SHOW TABLES WHERE Tables_in_appdb LIKE '%{tableNamee}%'";


using (var conn = new MySqlConnection(dbConnectionString))
{
    conn.Open();

    var cmd = new MySqlCommand(query, conn);
    //using(var cmd1 ) ..)
    using (var cmd2 = new MySqlCommand(query2, conn))
    {
        using (var reader = cmd2.ExecuteReader())
        {
            if (reader.Read())
            {
                reader.Close();
                MessageBox.Show("Ime ankete vec postoji, odaberite drugo!");
                flag = false;
                conn.Close();
                return false;
            }
            else
            {
                reader.Close();
                flag = true;
                cmd.ExecuteNonQuery();

                conn.Close();
                return true;

            }
        }
    }
}

Possible duplicate of [Using MySQLConnection in C# does not close properly](https://stackoverflow.com/questions/5567097/using-mysqlconnection-in-c-sharp-does-not-close-properly) — Tim, Aug 27 '19 at 12:27
Please provide a [mcve]. It is crucial, for example, that we see how `cmd` is declared and instantiated. — mjwills, Aug 27 '19 at 13:19

score 4 · Accepted Answer · edited Aug 27 '19 at 14:23

4

You should always dispose any object that implements IDisposable as soon as you are done using it (this includes commands and connections as well):

string query2 = $"SHOW TABLES WHERE Tables_in_appdb LIKE '%{tableNamee}%'";
using (var conn = new MySqlConnection(dbConnectionString))
{
    conn.Open();
    //using(var cmd1 ) ..)
    using (var cmd2 = new MySqlCommand(query2, conn))
    {
        using (var reader = cmd2.ExecuteReader())
        {
            if (reader.Read())
            {
                ia.flag = "stop";
                return false;
            }
            else
            {
                reader.Close();
                cmd.ExecuteNonQuery();
                ia.flag = "continue";
                return true;
            }
        }
    }
}

Note that we have used reader.Close(); to ensure that there is no open DataReader prior to cmd executing.

edited Aug 27 '19 at 14:23

mjwills

23,389
6
40
63

answered Aug 27 '19 at 13:09

mm8

163,881
10
57
88

@mjwills: What does the `reader` has to do with the execution of `cmd`...? – mm8 Aug 27 '19 at 14:10
You should probably let them be. Obviously I haven't tested this myself and I don't tend to execute two different commands like this myself. Your point is probably correct. – mm8 Aug 27 '19 at 14:17
1

@mjwills: I edited my answer slightly by adding a call to `reader.Close();` before `cmd` is executed. – mm8 Aug 27 '19 at 14:22

score 0 · Answer 2 · answered Aug 27 '19 at 13:27

Short template for connection:

using(MySqlConnection connect = new MySqlConnection(dbConnectionString))
using(MySqlCommand cmd = new MySqlCommand())
{
    cmd.CommandText = query2;

    cmd.Connection = connect;
    cmd.Connection.Open();

    using(MySqlDataReader msdr = cmd.ExecuteReader())
    {
         // do stuff
    }
}

How to close reader properly?

2 Answers2