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