Data Reader connection state

Question

I am using C# SQL Server 2008.

In DataReader(ADO.Net), the records can be fetched one at time and the connection remains open, until all record are fetched.

Confusion is, from the stored procedure, we send the complete table.

So, why is the connection still open when sql server have sent all records. I mean what's the mechanism behind this.

the connection being open does not affect the records being returned or fetched, what the connection being open would be more or less for decreasing round trips or having to create a new connection instance everytime and open() it has performance issues when you do that.. perhaps you could post an example of what you have to better gain an understading of what your questions is.. also DataReader is Forward reading but if you want you could also create a RecordSet, fill the object, and iterate // I personally prefer datareader — MethodMan, Dec 15 '11 at 17:24
Of course the connection being open affects the records being fetched. Try closing the connection half-way through and see where calling `.Next()` will get you! It's not the only consideration when it comes to how long to leave them open, but it is a consideration, and that which the querent is asking about. — Jon Hanna, Dec 15 '11 at 18:46

score 3 · Answer 1 · answered Dec 15 '11 at 17:23

3

SQL Server is not just "sending" the records... then client has to read them, one by one. It does not matter that it's a sproc. A result set is returned, and is being read through until the client is done.

answered Dec 15 '11 at 17:23

Andrew Barber

39,603
20
94
123

score 1 · Answer 2 · answered Dec 15 '11 at 17:24

You must call the close method on the data reader and I would recommend disposing it too if you are done with it. I often do this when closing a connection as I know that if I no longer need the connection then I will no longer need the reader.

For example:

 m_oleDatabaseDataReader.Close();
 m_oleDatabaseDataReader.Dispose();

score 1 · Accepted Answer · edited May 23 '17 at 12:27

The database will send the results through some sort of stream. The exact details of this are abstracted so that the code one layer higher up doesn't need to care whether it's a network stream, a named pipe, or something else, but it's some sort of stream.

Moving things along a stream takes time. A lot more time to move a massive recordset of 2million rows along a network stream from a computer on the other side of the world than a recordset of one row from an in-memory stream on the same computer, but time either way.

Rather than wait for all of the data to come across, .Next() will read enough bytes to build the next row of data. Possibly this data is already sitting in a buffer in memory because it's been read from the stream, possibly this means waiting for the data to come over the connection.

When you've called .Next() 3 times on a massive recordset, and made use of the results, there could still be data coming over the wire, so you can see how this can be good for making everything as efficient as possible. At this point perhaps the stored procedure is also still working, but most likely it's finished and the database per se is done with the query, but the part of it that handles connection still has a buffer full of results to send on.

After that the connection will still be open anyway. It will be open until you close it (whether explicitly or by coming to the end of a using block). You are free to use it again with another query.

After you close the SqlConnection object, the actually connection to the database will probably still be open, because pooling those connections is normally cheaper than creating a new one every time. https://stackoverflow.com/a/3845924/400547 explains this part of it a bit more.

score 0 · Answer 4 · answered Dec 15 '11 at 17:22

0

DataReader does not close automatically, you need to call close on the datareader object.

answered Dec 15 '11 at 17:22

Kashif Khan

2,615
14
14

score -1 · Answer 5 · edited Dec 15 '11 at 19:13

-1

//never assume always check if != null
if(m_oleDatabaseReader != null)
{  
   ((IDisposable)m_oleDatabaseReader).Dispose();
}

edited Dec 15 '11 at 19:13

casperOne

73,706
19
184
253

answered Dec 15 '11 at 17:27

MethodMan

18,625
6
34
52

1

This is not an answer to the question being asked. – Ben Robinson Dec 15 '11 at 17:32

Data Reader connection state

5 Answers5