ADO.NET Cookbook

9.7. Improving DataReader Performance with Column Ordinals

Problem

You want to use column ordinals rather than column names to retrieve data from a DataReader to improve application performance and without hard-coding the ordinal values.

Solution

Enumerate the column ordinals using the GetOrdinal( ) method and use those values to retrieve data from the DataReader.

The sample code contains two event handlers:

Form.Load

Sets up the sample by using a DataReader to retrieve the ordinals for all columns in the Orders table of the Northwind database.

Go Button.Click

Builds a new DataReader containing the TOP 5 records from the Orders table in the Northwind database. The code iterates over the records in the DataReader and demonstrates techniques to retrieve data from the OrderID, CustomerID, OrderDate, and ShipRegion using accessors that are index-based, nonspecific, provider-specific, nonspecific with null check, and provider-specific with null check.

The C# code is shown in Example 9-11.

Example 9-11. File: DataReaderColumnOrdinalForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; private int co_OrderId; private int co_CustomerId; private int co_EmployeeId; private int co_OrderDate; private int co_RequiredDate; private int co_ShippedDate; private int co_ShipVia; private int co_Freight; private int co_ShipName; private int co_ShipAddress; private int co_ShipCity; private int co_ShipRegion; ...