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.

Answer 3

What you want is possible, but it is not a nice solution, because you have to wrap all the functions of the MySqlDataReader class and forward it to the real MySqlDataReader. See the ConnectedMySqlDataReader class (hint: it does not implement all functions of MySqlDataReader, if you really want to use it, you have to do it yourself) and how it would fit in your solution:

public ConnectedMySqlDataReader GetRecord(string query)
{
    return new ConnectedMySqlDataReader(connectionString, query);
}

// ...

var sql = "SELECT * FROM `table`";
using(var dr = objDB.GetRecord(sql))
{
    if (dr.Read())
    {
        // some code goes hear
    } 
 }

i have not tested this class, it is just for demonstration!

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace MySqlTest
{
    public class ConnectedMySqlDataReader : DbDataReader
    {
        private readonly MySqlConnection _connection;
        private readonly Lazy<MySqlDataReader> _reader;
        private MySqlCommand _command;


        public ConnectedMySqlDataReader(MySqlConnection connection, string query)
        {
            if(connection == null)
                throw new ArgumentNullException("connection");
            _connection = connection;
            _reader = new Lazy<MySqlDataReader>(() =>
            {                
                _connection.Open();
                _command = new MySqlCommand(query, _connection);
                return _command.ExecuteReader();
            });
        }

        public ConnectedMySqlDataReader(string connectionString, string query)
            : this(new MySqlConnection(connectionString), query) { }

        private MySqlDataReader Reader
        {
            get { return _reader.Value; }
        }

        public override void Close()
        {
            if (_reader.IsValueCreated)            
                _reader.Value.Close();
            if(_command != null)
                _command.Dispose();
            _connection.Dispose();
        }

        public override DataTable GetSchemaTable()
        {
            return this.Reader.GetSchemaTable();
        }

        public override bool NextResult()
        {
            return this.Reader.NextResult();
        }

        public override bool Read()
        {
            return this.Reader.Read();
        }

        public override int Depth
        {
            get { return this.Reader.Depth; }
        }

        public override bool IsClosed
        {
            get { return this.Reader.IsClosed; }
        }

        public override int RecordsAffected
        {
            get { return this.Reader.RecordsAffected; }
        }

        public override bool GetBoolean(int ordinal)
        {
            return this.Reader.GetBoolean(ordinal);
        }

        public override byte GetByte(int ordinal)
        {
            return this.Reader.GetByte(ordinal);
        }

        public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)
        {
            return this.Reader.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length);
        }

        public override char GetChar(int ordinal)
        {
            return this.Reader.GetChar(ordinal);
        }

        public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length)
        {
            return this.Reader.GetChars(ordinal, dataOffset, buffer, bufferOffset, length);
        }

        public override Guid GetGuid(int ordinal)
        {
            return this.Reader.GetGuid(ordinal);
        }

        public override short GetInt16(int ordinal)
        {
            return this.Reader.GetInt16(ordinal);
        }

        public override int GetInt32(int ordinal)
        {
            return this.Reader.GetInt32(ordinal);
        }

        public override long GetInt64(int ordinal)
        {
            return this.Reader.GetInt64(ordinal);
        }

        public override DateTime GetDateTime(int ordinal)
        {
            return this.Reader.GetDateTime(ordinal);
        }

        public override string GetString(int ordinal)
        {
            return this.Reader.GetString(ordinal);
        }

        public override object GetValue(int ordinal)
        {
            return this.Reader.GetValue(ordinal);
        }

        public override int GetValues(object[] values)
        {
            return this.Reader.GetValues(values);
        }

        public override bool IsDBNull(int ordinal)
        {
            return this.Reader.IsDBNull(ordinal);
        }

        public override int FieldCount
        {
            get { return this.Reader.FieldCount; }
        }

        public override object this[int ordinal]
        {
            get { return this.Reader[ordinal]; }
        }

        public override object this[string name]
        {
            get { return this.Reader[name]; }
        }

        public override bool HasRows
        {
            get { return this.Reader.HasRows; }
        }

        public override decimal GetDecimal(int ordinal)
        {
            return this.Reader.GetDecimal(ordinal);
        }

        public override double GetDouble(int ordinal)
        {
            return this.Reader.GetDouble(ordinal);
        }

        public override float GetFloat(int ordinal)
        {
            return this.Reader.GetFloat(ordinal);
        }

        public override string GetName(int ordinal)
        {
            return this.Reader.GetName(ordinal);
        }

        public override int GetOrdinal(string name)
        {
            return this.Reader.GetOrdinal(name);
        }

        public override string GetDataTypeName(int ordinal)
        {
            return this.Reader.GetDataTypeName(ordinal);
        }

        public override Type GetFieldType(int ordinal)
        {
            return this.Reader.GetFieldType(ordinal);
        }

        public override IEnumerator GetEnumerator()
        {
            return this.Reader.GetEnumerator();
        }
    }
}

PS: this is what sealed in c# context means, and yes, MySqlDataReader is sealed.