1

Following is a statement from SqlDataReader Class

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it.

I have code that has one open connection used by two different commands. There are actually two methods. The second method is getting called in the while(reader.Read()) loop.

I get intermittent errors saying – There is already an open DataReader associated with this Command which must be closed first.

But why isn’t it always throwing error?

REFERENCES

  1. Default Result Set Processing and Multiple Active Result Sets
  2. SQL Server Connection Basics
  3. Regarding a small confusion about DataReader

enter image description here

Community
  • 1
  • 1
LCJ
  • 22,196
  • 67
  • 260
  • 418
  • 3
    Show these methods and the relevant code. I assume it's a kind of race condition. But why do you use the same connection? Use the `using`-statement in both methods. – Tim Schmelter Dec 12 '13 at 14:55
  • ResultRead region would be nice as well :) – Ondrej Svejdar Dec 12 '13 at 15:22
  • @Lijo: Don't post images but text. It's not clear what `ResultRead` means, if it's the method show it with all parameters. You're also showing a class `DataAccessLayerBase` which is not used in the code above. – Tim Schmelter Dec 12 '13 at 15:23
  • 1
    I'm fairly sure that you should better scrap your `DataAccessLayerBase`-class. Are you [poaching on the Connection-Pool's territory](http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060)? – Tim Schmelter Dec 12 '13 at 15:28
  • 2
    Trying to execute a command on a connection that is still returning data is asking for trouble, even if it were allowed. Just don't use the same connection. Repeatedly opening and closing a connection costs nothing - the connection pool takes care to reuse previously closed connections. Actually, using the pool is much faster, because fewer connections means fewer locked resources – Panagiotis Kanavos Dec 12 '13 at 15:28

1 Answers1

3

now that you know that nested datareaders over the same connection is a bad idea, why are you even doing that? do refactor that code.

now to answer your question,

Network layer does not work in PULL mode. You open a connection, issue some command and then start consuming the results. Note you do NOT pull but consume. there is a difference. in your code, while(reader.Read) loop does not steadily pull data. the data starts arriving the moment the command is executed. it gets buffered at your pc. The buffer has a limit. if total data is less than the buffer size then the data-transfer happens really fast. if total data is more than the buffer size then of course the reader.read() makes room for next batch.

This means that two DataReaders can work with same connection as long as the first datareader fetches very little amount of data. so by the time the second datareader attempts to use the connection the connection has already gone idle.

there is a race condition between two threads. first thread is the one which collects the data sent in response to the first command. the second thread is the one on which your program is running. both race to complete first. if the collector thread is able to download all data before your main thread hits second datareader then the accident is averted. but if the data is large or network is slow, or by sheer luck it didnt get enough cpu, then the main thread hits second datareader while the connection is still working.

inquisitive
  • 3,549
  • 2
  • 21
  • 47
  • 1
    I don't know why you think there are two threads involved here. – Joe Dec 13 '13 at 08:30
  • 1
    @Joe: there is one thread: the program main thread. there are other threads, the framework threads which do the network activities. they are hidden, all you can see is their names and IDs. – inquisitive Dec 13 '13 at 09:37