Invalid read attempt when reader is closed

Question

I have a common database class for my application, and in this 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 in my code behind the pages I use

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

and I am mistaken as an Incorrect reading attempt when the reader is closed.

I know that access to the reader after closing the database connection is impossible bot I'm looking for a job around in which I do not need to change the code

EDIT: I WILL BE IN THE DECISION IN WHICH THE READER ASSIGNED ANOTHER OBJECT (SIMILAR TO THE READER) AND THEN RETURN THAT THE OBJECT , therefore I do not need to change in all application pages

Answer 1

You can load the results of your query into memory, then close the connection and still return an IDataReader that works as expected. Please note that this is worth the 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 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 volume of your call before using (var connection = new MySqlConnection(connectionString)) completed, the connection will be closed.

However, you are still returning the reader based on this connection. As soon as you try to use it in your caller method, you will receive an error message because a closed connection cannot be used.

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

One option is as follows:

 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 is “like a reader,” so you don’t need to change the caller, it won’t work: the returned object must contain both a reader and an open connection so that you can use the reader. This means that you need to close the connection in the caller. In the best case, the caller should be changed as follows:

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

You won’t save so much work, but one missing using statement in the caller will cause your application to not work after some time due to a connection leak.

Answer 3

What you want is possible, but this is not a good solution, because you have to wrap all the functions of the MySqlDataReader class and redirect it to the real MySqlDataReader . See the ConnectedMySqlDataReader class (hint: it does not implement all the functions of MySqlDataReader, if you really want to use it, you need to do it yourself) and how it will correspond to 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 is sealed in the context of C # , and yes, MySqlDataReader is sealed .