Can I avoid the open DataReader exception when using nested commands?

Question

Hi!

Can I avoid the open DataReader exception ("There is already an open DataReader associated with this Command which must be closed first.") when using constructs like this one?

public void FirstMethod()
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "...";
        using (var reader = command.ExecuteReader())
        {
            // do something with the data
            SecondMethod();
        }
    }
}

public void SecondMethod()
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "...";
        using (var reader = command.ExecuteReader()) // Exception
        {
        }
    }
}

Best Regards

Answer 1

You could use local connections (i.e. local to the method); with connection pooling, the advantages of keeping a connection as a field are minimal, and regularly cause this type of... fun. Of course, you can then get into issues of blocking yourself if you are editing data - perhaps mitigated using things like TransactionScope. Example at the bottom...

Alternatively, you could enable MARS (multiple active result sets) on the connection; that should do it. Just include "MultipleActiveResultSets=True" as a pair in the connection-string (SQL Server 2005 and above).

public void FirstMethod() {
    using (var connection = CreateAndOpenConnection())
    using (var command = connection.CreateCommand()) {
        command.CommandText = "...";
        using (var reader = command.ExecuteReader()) {
            // do something with the data
            SecondMethod();
        }
    }
}
public void SecondMethod() {
    using (var connection = CreateAndOpenConnection())
    using (var command = connection.CreateCommand()) {
        command.CommandText = "...";
        using (var reader = command.ExecuteReader()) // Exception
        { }
    }
}
private SqlConnection CreateAndOpenConnection() {
    var conn = new SqlConnection(connectionString);
    conn.Open(); // perhaps dispose if this fails...
    return conn;
}

Answer 2

Why don't you just change your method signatures?

public void FirstMethod()
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "...";
        using (var reader = command.ExecuteReader())
        {
            // do something with the data
            SecondMethod(reader);
        }
    }
}

public void SecondMethod(var reader)
{
    // do stuff with reader...
}

Not only does this avoid the problem, but it also incurs less overhead as you're only creating 1 command/reader as opposed to 2. (This assumes your method calls are synchronous.)

Also, consider using strongly-typed variables instead of the var keyword; var was introduced to support anonymous types and should be used only if necessary.