技术求助：调用已关闭SqlDataReader的Read方法出现Invalid attempt to call Read when reader is closed错误

Hey there, let's break down why you're hitting this error and fix it up.

What's causing the issue? #

Your ReadQuery method uses a using block to wrap the SqlConnection—and that using block automatically closes and disposes the connection as soon as the block ends. The problem is that SqlDataReader is tightly coupled to its parent connection by default: when the connection gets closed, the reader becomes unusable. That's exactly why calling dr.Read() outside the method throws the "reader is closed" error.

Fix 1: Use CommandBehavior.CloseConnection (Recommended) #

When you call ExecuteReader, pass in the CommandBehavior.CloseConnection parameter. This tells the reader to close the connection automatically when you close the reader later. This way, you don't lose the safety of connection cleanup, and the reader works outside the method.

Here's how to adjust your code:

public class DB{
    public SqlDataReader ReadQuery(string query) {
        var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        var connection = new SqlConnection(connectionString);
        connection.Open(); // Don't forget to open the connection first!
        
        var command = new SqlCommand(query, connection);
        // Pass CommandBehavior.CloseConnection to link reader and connection lifecycle
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }
}

Important note: When you're done using the reader in your calling code, make sure to call dr.Close()—this will trigger the connection to close and dispose properly.

Fix 2: Return a List of Entities (Safer Approach) #

Honestly, returning a SqlDataReader isn't the best practice because it forces you to manage connection state outside the data access method. A better approach is to read the reader's data inside the method, convert it into a list of your custom entities (or a DataTable), then return that. This way, all connection/reader resources get cleaned up inside the using blocks automatically.

Example code:

// First, define your entity class (adjust fields to match your query)
public class YourEntity {
    public int Id { get; set; }
    public string Name { get; set; }
    // Add other properties as needed
}

public class DB{
    public List<YourEntity> ReadQuery(string query) {
        var results = new List<YourEntity>();
        var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        
        using (var connection = new SqlConnection(connectionString)) {
            connection.Open();
            
            using (var command = new SqlCommand(query, connection)) {
                using (var dr = command.ExecuteReader()) {
                    while (dr.Read()) {
                        // Map reader data to your entity
                        var entity = new YourEntity {
                            Id = dr.GetInt32(dr.GetOrdinal("Id")), // Using column names is safer than indexes
                            Name = dr.GetString(dr.GetOrdinal("Name"))
                        };
                        results.Add(entity);
                    }
                }
            }
        }
        
        return results;
    }
}

This method keeps all resource management contained, so you don't have to worry about forgotten connections or closed readers in your calling code.

To recap #

Your original code fails because the using block closes the connection before you can use the reader. Either use CommandBehavior.CloseConnection to link the reader and connection lifecycle, or (preferably) convert the reader data to a list inside the method to avoid managing external resources.

内容的提问来源于stack exchange，提问作者user9470241