DataReader Object Overview

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

• System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).

• System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Every DataReader object implements the System.Data.IDataReader and the System.Data.IDataRecord interfaces. The IDataReader interface provides the core methods shown in Table 5-1, such as Read( ) , which retrieves a single row from the stream. The IDataRecord interface provides the indexer for the DataReader and allows you to access the column values for the current row by column name or ordinal number.

Table 5-1. IDataReader methods

Member	Description
Close( )	Closes the DataReader but not the underlying Connection. This allows you to use the Connection for another task.
GetSchemaTable( )	Retrieves a DataTable object with information about the schema for the current result set.
NextResult( )	When executing a Command that returns multiple result sets, you must use NextResult( ) to move from one result set to another. This method returns true if there are more result sets.
Read( )	Loads the next row into the DataReader. This method returns true if there are more rows left to be read.

The key to understanding the DataReader is to understand that it loads only a single row into memory at a time. This ensures that memory use is kept to a minimum. It’s also important to realize that the DataReader represents a live connection. Thus, you should read the values, close the connection as quickly as possible, and then perform any time-consuming data processing.

You can’t create a DataReader directly. Instead, a DataReader must be generated by the ExecuteReader( ) method of a Command object. You won’t need to manually open the DataReader; it will be initialized as soon as you execute the Command. You can begin using it immediately by calling the Read( ) method.

Typical DataReader access code follows five steps:

1. Create a Command object with an appropriate SELECT query.

2. Create a Connection, and open it.

3. Use the Command.ExecuteReader( ) method, which returns a live DataReader object.

4. Move through the returned rows from start to finish, one at a time, using the DataReader.Read( ) method. You can access a column in the current row by index number or field name.

5. Close the DataReader( ) and Connection( ) when the Read( ) method returns false to indicate there are no more rows.

The DataReader is limited in scope and thus extremely simple to use. For example, the DataReader also has no intrinsic support for table relations, so you will need to perform a JOIN query if you want to see combined information from more than one table.