10.5 Process the Results of a SQL Query Using a Data Reader

Problem

You need to process the data contained in a System.Data.IDataReader instance returned when you execute the IDbCommand.ExecuteReader method (discussed in recipe 10.3).

Solution

Use the members of the IDataReader instance to move through the rows in the result set sequentially and access the individual data items contained in each row.

Discussion

The IDataReader interface represents a data reader, which is a forward-only, read-only mechanism for accessing the results of a SQL query. Each data provider includes a unique IDataReader implementation. Here is the list of IDataReader implementations for the five standard data providers.

• System.Data.Odbc.OdbcDataReader

• System.Data.OleDb.OleDbDataReader

• System.Data.OracleClient.OracleDataReader

• System.Data.SqlServerCe.SqlCeDataReader

• System.Data.SqlClient.SqlDataReader

The IDataReader interface extends the System.Data.IDataRecord interface. Together these interfaces declare the functionality that provides access to both the data and the structure of the data contained in the result set. Table 10.5 describes some of the commonly used members of the IDataReader and IDataRecord interfaces.

Table 10.5: Commonly Used Members of Data Reader Classes

Member	Comments
Property
FieldCount	Gets the number of columns in the current row.
IsClosed	Returns true if the IDataReader is closed; false if it's currently open .
Item	Returns an object representing the value of the specified column in the current row. Columns can be specified using a zero-based integer index or a string containing the column name . You must cast the returned value to the appropriate type. This is the indexer for data reader classes.
Method
GetDataTypeName	Gets the name of the data source data type for a specified column.
GetFieldType	Gets a System.Type instance representing the data type of the value contained in the column specified using a zero-based integer index.
GetName	Gets the name of the column specified by using a zero-based integer index.
GetOrdinal	Gets the zero-based column ordinal for the column with the specified name.
GetSchemaTable	Returns a System.Data.DataTable instance that contains metadata describing the columns contained in the IDataReader .
IsDBNull	Returns true if the value in the specified column contains a data source null value; otherwise it returns false .
NextResult	If the IDataReader includes multiple result sets because multiple statements were executed, NextResult moves to the next set of results. By default, the IDataReader is positioned on the first result set.
Read	Advances the reader to the next record. The reader always starts prior to the first record.

In addition to those members listed in Table 10.5, the data reader provides a set of methods for retrieving typed data from the current row. Each of the following methods takes an integer argument that identifies the zero-based index of the column from which the data should be returned: GetBoolean , GetByte , GetBytes , GetChar , GetChars , GetDateTime , GetDecimal , GetDouble , GetFloat , GetGuid , GetInt16 , GetInt32 , GetInt64 , GetString , GetValue , and GetValues .

The SQL Server and Oracle data readers also include methods for retrieving data as data source “specific data types. For example, the SqlDataReader includes methods such as GetSqlByte , GetSqlDecimal , and GetSqlMoney , and the OracleDataReader includes methods such as GetOracleLob , GetOracleNumber , and GetOracleMonthSpan . Refer to the .NET Framework SDK documentation for more details.

When you have finished with a data reader, you should always call its Close method so that you can use the database connection again. IDataReader extends System.IDisposable , meaning that each data reader class implements the Dispose method. Dispose automatically calls Close , making the using statement a very clean and efficient way of using data readers.

The following example demonstrates the use of a data reader to process the contents of two results sets returned by executing a batch query containing two SELECT queries. The first result set is enumerated and displayed to the console. The second result set is inspected for metadata information, which is then displayed.

 using System; using System.Data; using System.Data.SqlClient; public class DataReaderExample {     public static void Main() {         // Create a new SqlConnection object.         using (SqlConnection con = new SqlConnection()) {             // Configure the SqlConnection object's connection string.             con.ConnectionString = "Data Source = localhost;" +                  "Database = Northwind; Integrated Security=SSPI";             // Create and configure a new command.             SqlCommand com = con.CreateCommand();             com.CommandType = CommandType.Text;             com.CommandText = "SELECT BirthDate,FirstName,LastName FROM " +                 "Employees ORDER BY BirthDate;SELECT * FROM Employees";             // Open the Database connection and execute the example             // commands through the connection.             con.Open();             // Execute the command and obtain a SqlReader.             using (SqlDataReader reader = com.ExecuteReader()) {                 // Process the first set of results and display the                  // content of the result set.                 Console.WriteLine("Employee Birthdays (By Age).");                 while (reader.Read()) {                     Console.WriteLine("{0,18:D} - {1} {2}",                          reader.GetDateTime(0),  // Retrieve typed data                         reader["FirstName"],    // Use string index                         reader[2]);             // Use ordinal index                 }                 // Process the second set of results and display details                 // about the columns and data types in the result set.                 reader.NextResult();                 Console.WriteLine("Employee Table Metadata.");                 for (int field = 0; field < reader.FieldCount; field++) {                     Console.WriteLine("  Column Name:{0}  Type:{1}",                         reader.GetName(field),                          reader.GetDataTypeName(field));                 }             }         }         // Wait to continue.         Console.ReadLine();     } }