Using a SqlDataReader
Use a SqlDataReader obtained by calling the ExecuteReader method of the SqlCommand object when:
- You are dealing with large volumes of data-too much to maintain in a single cache.
- You want to reduce the memory footprint of your application.
- You want to avoid the object creation overhead associated with the DataSet.
- You want to perform data binding with a control that supports a data source that implements IEnumerable.
- You wish to streamline and optimize your data access.
- You are reading rows containing binary large object (BLOB) columns. You can use the SqlDataReader to pull BLOB data in manageable chunks from the database, instead of pulling all of it at once. For more details about handling BLOB data,
If you use the SqlDataReader, note the following:
- The underlying connection to the database remains open and cannot be used for any other purpose while the data reader is active. Call Close on the SqlDataReader as soon as possible.
- There can be only one data reader per connection.
- You can close the connection explicitly when you finish with the data reader, or tie the lifetime of the connection to the SqlDataReader object, by passing the CommandBehavior.CloseConnection enumerated value to the ExecuteReader method. This indicates that the connection should be closed when the SqlDataReader is closed.
- When accessing data by using the reader, use the typed accessor methods (such as GetInt32 and GetString) if you know the column's underlying data type because they reduce the amount of type conversion required when you read column data.
- To avoid unnecessary data being pulled from server to client, if you want to close the reader and discard any remaining results, call the command object's Cancel method before calling Close on the reader. Cancel ensures that the results are discarded on the server and are not pulled unnecessarily to the client. Conversely, calling Close on the data reader causes the reader to unnecessarily pull the remaining results to empty the data stream.
- If you want to obtain output or return values returned from a stored procedure and you are using the ExecuteReader method of the SqlCommand object, you must call the Close method on the reader before the output and return values are available.