SqlDataReader underlying data

  • Thread starter Thread starter narshe
  • Start date Start date
N

narshe

I'm trying to figure out how the SqlDataReader works, and this is my
main question:

When SqlCommand.ExecuteReader() is called, are all rows of the query
returned immediately? This would mean the a call to reader.Read() would
just return the first row from the underlying data. Or does a call to
reader.Read() fetch the next row from SqlServer?

I've been digging through a watch of SqlDataReader, and can see that
the reader holds a row of data at a time. I would think it's getting
it's rows from the command, but I really can't find much else.
 
Ok. I found out a lot from this article:

http://msdn.microsoft.com/msdnmag/issues/04/06/datapoints/default.aspx

The reader is connected the whole time, and a call to DataReader.Read()
gets the next row from sql server.

I also noticed that ExecuteNonQuery and ExecuteScaler use the
DataReader to get their data. Does this mean there is one internal data
reader being used in SqlCommand, and a call to ExecuteReader just
returns that reader?

Another thing I don't get; what is the purpose of DataReader.Close()?
What exactly does this close? It doesn't close the connection. Does it
just set a flag so that calls to ExecuteReader/Scaler/NonQuery don't
try and start a new command sequence to sql server while in the middle
of one?
 
Thanks for the reply.
IMO ExecuteNonQuery doesn't use SqlDataReader. and correct ExecuteScalar uses SqlDataReader to get the data...
Click to expand...

I believe that trying to call ExecuteNonQuery on a command/connection
that has a reader that's not closed throws an exception saying there
already is a reader connected to the connection. That's why I thought
that it uses a reader internally.

It could be due to this: "While the SqlDataReader is in use, 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.".

How does the connection know that there's a reader already attached to
it? I couldn't find anything anything while looking through the watch
that signifies it's being used.
 
Back
Top