Invalid attempt to Read when reader is closed

Question

I have a common database class for my application and in that class i have a function

public MySqlDataReader getRecord(string query)
        {
            MySqlDataReader reader;
            using (var connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                using (var cmd = new MySqlCommand(query, connection))
                {

                    reader = cmd.ExecuteReader();
                    return reader;

                }
            }

            return null;
        }

and on my code behind pages I use

String sql = "SELECT * FROM `table`";
MySqlDataReader dr = objDB.getRecord(sql);
if (dr.Read())
{
   // some code goes hear
} 

and I am having error as Invalid attempt to Read when reader is closed.

I know access the reader after the database connection is closed is not possible bot I am looking for a work around in which I need not to change in the codebehind

EDIT: I WILL LIKE THE SOLUTION IN WHICH THE READER IS ASSIGNED TO OTHER OBJECT (SIMILAR TO READER ) AND THEN RETURN THAT OBJECT so i need not to change in all the application pages

Answer 1

You can load the results of your query to memory, then close the connection and still return an IDataReader that works as expected. Note that this costs memory.

public IDataReader getRecord(string query)
    {
        MySqlDataReader reader;
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            using (var cmd = new MySqlCommand(query, connection))
            {

                reader = cmd.ExecuteReader();
                var dt = new DataTable();
                dt.Load( reader );
                return dt.CreateDataReader();
            }
        }

        return null;
    }

In the callers:

String sql = "SELECT * FROM `table`";
var dr = objDB.getRecord(sql); // or DataTableReader dr = ...
if (dr.Read())
{
    // some code goes here
} 

Answer 2

When the scope of your call to using (var connection = new MySqlConnection(connectionString)) ends, the connection will be closed.

However, you are still returning a reader based on that connection. Once you try to use it in your caller method, you will get the error as closed connection can't be used.

Besides, your method is called GetRecord but it returns a reader.

One of the options is to do this:

public void processQuery(string query, Action<MySqlDataReader> fn)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();
        using (var cmd = new MySqlCommand(query, connection))
        {
            using (var reader = cmd.ExecuteReader())
            {
               fn(reader);
            }
        }
    }
}


// caller
String sql = "SELECT * FROM `table`";
objDB.procesQuery(sql, dr => {
    if (dr.Read())
    {
       // some code goes here
    } 
});

Your idea of creating an object 'similar to reader', so you don't have to change the caller, would not work: the returned object would need to contain both reader and an open connection, so that you can use the reader. This means you would have to close the connection in the caller. In best case, the caller would need to be modified as follows:

String sql = "SELECT * FROM `table`";
using (MyWrapper wr = objDB.getRecord(sql))
{
   if (wr.Reader.Read())
   {
      // some code goes here
   } 
}

You will not save that much work, but one missing using statement in the caller will result in your app not working after some time due to a connection leak.