SqlDataReader return rows

Question

If we select a large number of rows and use an SqlDataReader, will it return the rows as they come or will it wait until the operation is complete?

This is with C#.net

Answer 1

It'll return the rows as they come. Each time you call SqlDataReader.Read(); the next row is retrieved from the client's network buffer. Only one row is held in memory on each Read().

// Starts getting the data from the query 
IDataReader reader = cmd.ExecuteReader(behavior); 

// Calling .Read() will get the next result from the client network buffer
while (reader.Read())
{
   // Do something with the row data
}

More information here.

Answer 2

They are streamed. If you review the "remarks" section it states that certain properties can only be safely read once the reader closes. If the complete result set was known prior to starting, these properties would be safe to read while the reader was open.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx