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

Question

Can I avoid the open DataReader exception ("There is an already open DataReader associated with this Command that must be closed first.") When using constructs such as this?

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 wishes

Answer 1

You can use local connections (i.e. local to the method); with a pool of connections, the benefits of keeping in touch as fields are minimal and regularly cause this type of ... fun. Of course, you may run into problems blocking yourself if you are editing data, possibly softening them using TransactionScope . Example below ...

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

 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 the signature of the methods?

 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... } 

This not only fixes the problem, but also incurs less overhead, since you only create 1 command / reader , not 2. (It is assumed that 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 only be used if necessary.