c# - 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
}