SqlDataReader connection

Question

In my windows service, I am executing a stored procedure that selects the top 2500 rows from a table and returns the data to the service. I am catching the results using the SqlDataReader object.

My question is: does the SqlDataReader object return the 2500 rows to my service and store them in memory at one time and read each record one by one or does it read each record one by one from the stored procedure in SQL Server?

Thank you!

SqlCommand getData = new SqlCommand(adminConnectionString, adminConnection);

// Sets SqlCommand Information
getData.CommandText = adminDatabaseName + ".dbo.SprocToExecute";
getData.CommandType = CommandType.StoredProcedure;
getData.CommandTimeout = 300;

// Creates parameters, sets their values, and adds them to the SqlCommand
SqlParameter sessionIDParameter = new SqlParameter("@SessionID", SqlDbType.Int);
sessionIDParameter.Value = ID;
getData.Parameters.Add(sessionIDParameter);

SqlDataReader dataReader = getData.ExecuteReader();

if (dataReader.HasRows)
{
    while (dataReader.Read())
    {
        // DO SOMETHING WITH RECORD 
    }
}

It reads each record one by one from the connection stream (forward only). You get a strong hint also reading the docs: _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._ — Steve, Dec 31 '18 at 17:00

score 0 · Answer 1 · answered Dec 31 '18 at 17:43

The stored proc continues to serve rows until the end of the "while (dataReader.Read())" loop.

Use SqlDataAdapter if you want to get all rows at once

        DataTable dt = new DataTable();
        SqlDataAdapter adapter = new SqlDataAdapter(getData);
        adapter.Fill(dt);

The stored proc starts and finishes upon execution of "adapter.Fill(dt)"

userfl89 · Answer 2 · 2018-12-31T20:07:17.437

The SqlDataReader object will only store one row at a time in memory. Upon calling the ExecuteReader() method of a SqlCommand object an unbuffered stream of data is created to sequentially process the incoming record. However if you're looking to have all of the rows read into memory at once this can be done by using a SqlDataAdapter object. This will load all records satisfying the query into memory once the Fill method is called. On a side note, make sure to always call the Close() method of the SqlDataReader object once you're finished using it. Not closing this can have implications, for example if output parameters are used these cannot be accessed until the SqlDataReader is closed. The connection (adminConnection in your code) used by the SqlDataReader object exclusively belongs to this reader when the reader is opened and subsequent commands, or other SqlDataReader objects, cannot use this connection until it the SqlDataReader is closed through the Close() method.

SqlDataReader connection

2 Answers2