Search code examples
.netado.netdbdatareader

ADO.Net - Having multiple DBDataReaders running at the same time?

When I have multiple DBDataReaders reading data at the same time I get the following error:

There is already an open DataReader associated with this Connection which must be
closed first

I have ConnectionPooling enabled in my config so I don't understand why I am getting this error. Doesn't it suppose to create a new connection since my current connection is already in use?

I know that setting MultipleActiveResultSets to true would fix the problem, but I'm still trying to understand why the problem exist

Solution

  • Connection pooling does not do what you think it does.

    If you do something like this

    var connection = new SqlConnection(connectionString);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = // some query
var reader = command.ExecuteReader();
var anotherCommand = connection.CreateCommand();
anotherCommand.CommandText = // another query
var anotherReader = anotherCommand.ExecuteReader();

    then all of this will happen on one connection, whether or not you have connection pooling.

    Connection pooling just keeps a cache of connections that you can draw from every time that you create a new connection (new SqlConnection) and open it (SqlConnectinon.Open). When you close a connection, it returns to the pool to be reused. But one open SqlConnection object corresponds to one connection from the pool. Period.