C# 阅读器关闭时尝试读取无效

Invalid attempt to Read when reader is closed

提问by Champ

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 aroundin 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 OBJECTso i need not to change in all the application pages

编辑：我会喜欢将阅读器分配给其他对象（类似于阅读器）然后返回该对象的解决方案，因此我不需要在所有应用程序页面中进行更改

采纳答案by Alex

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

您可以将查询结果加载到内存中，然后关闭连接并仍然返回IDataReader按预期工作的 。请注意，这会消耗内存。

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
} 

回答by Zdeslav Vojkovic

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

当您的调用范围using (var connection = new MySqlConnection(connectionString)) 结束时，连接将关闭。

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 GetRecordbut it returns a reader.

此外，您的方法被调用GetRecord但它返回一个阅读器。

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 usingstatement in the caller will result in your app not working after some time due to a connection leak.

您不会节省那么多工作，但是using调用者中缺少一个语句将导致您的应用程序在一段时间后由于连接泄漏而无法运行。

回答by esskar

What you want is possible, but it is not a nice solution, because you have to wrap all the functions of the MySqlDataReaderclass and forward it to the real MySqlDataReader. See the ConnectedMySqlDataReaderclass (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:

你想要的是可能的，但这不是一个很好的解决方案，因为你必须包装类的所有功能MySqlDataReader并将其转发到真正的MySqlDataReader. 查看ConnectedMySqlDataReader类（提示：它没有实现 MySqlDataReader 的所有功能，如果你真的想使用它，你必须自己做）以及它如何适合你的解决方案：

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# contextmeans, and yes, MySqlDataReaderis sealed.

PS：这就是在 c# 上下文中密封的意思，是的，MySqlDataReader是sealed.