Why can’t I use a SqlConnection instance until the SqlDataReader is closed?

Question

Hi,

From MSDN:

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. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

a) Why couldn’t you use SqlConnection for anything else? After all, when ExecuteQuery() returns SqlDataReader object, the data from DB has already been retrieved and populated SqlDatareader object. Thus I don’t see how or why SqlConnection should still be serving SqlDataReader object?!

b) More importantly, for what reason would it be bad idea to retrieve output parameters before you call Close() on SqlDataReader?

c) When above quote mentions that no other operations can be performed on SqlConnection, what operations does it have in mind? Just those that would require to connect to remote sql server, or we can’t use any members of SqlConnection instance?

Thank you.

Answer 1

a) When ExecuteReader returns, the data has not all been retrieved and populated in the reader, it may still be streaming back from the database. That's the whole point of the SqlDataReader because it's more efficient to do this than to load it all up front.

b) You can't retrieve output parameters until after the reader has finished because of the way the Tabular Data Stream (TDS) protocol is structured. The output parameters are not physically sent down the stream until after the result set data.

c) It means none of the operations except Close are documented as being guaranteed to work. Whether they actually do work or not is irrelevant because that is an implementation detail rather than a contract, and programming against implementation details is a recipe for disaster.

Why do you want to re-use the connection anyway? The connections that SqlConnection uses are pooled behind the scenes, so when you dispose one it doesn't really get rid of all the resources, it just returns the connection to the pool. This means you can use the connection for the shortest time possible, ideally within a using block, and not have to worry about this type of thing. Just create a new connection whenever you need one, and don't bother trying to re-use them as it's already happening behind the scenes.

Answer 2

Uhm... you can in SQL Server 2005. Please read this page:

http://msdn.microsoft.com/en-us/library/ms345109.aspx

Answer 3

hi

a)

When ExecuteReader returns, the data has not all been retrieved and populated in the reader, it may still be streaming back from the database. That's the whole point of the SqlDataReader because it's more efficient to do this than to load it all up front. So even when ExecuteReader() returns, data may still be streaming directly to SQlDatareader?!

a) So even when ExecuteReader() returns, data may still be streaming directly to SQlDatareader?!

b)Is there a way to force data to be transferred immediately ( in case there is possibility that connection may go down at any moment and you want to make sure all the data is already loaded into data reader )?

c)I assume if you call SqlDatareader.Close(), SqlDataReader instance looses all of the received data?

thanx mate

Answer 4

SqlDataReader doesn't return the entire table in one fetch, much like a StreamReader wouldn't return the entire file. Obviously in reality it will for small tables, but only one connection is being served. That's using blocking for network access though - just open a second connection if you need to do something else asynchronously.