Improving DataReader Performance with Column Ordinals

[ Team LiB ] Recipe 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

Nội dung Text: Improving DataReader Performance with Column Ordinals

[ Team LiB ]
Recipe 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;

dr.GetSqlString(co_ShipRegion)).ToString( ) +
Environment.NewLine + Environment.NewLine);
}
// Close the DataReader and connection.
dr.Close( );
conn.Close( );
resultTextBox.Text = sb.ToString( );
}
Discussion
The GetOrdinal( ) method of the DataReader object gets the column ordinal for a
specified column name. As discussed in Recipe 9.6, reading data from a DataReader is
significantly faster using column ordinals instead of column names. The GetOrdinal( )
method can be used in the constructor to retrieve all column ordinals based on the column
names. Column ordinals can then be used to read data from the DataReader to improve
performance without having to code absolute column ordinal values.
The GetName( ) method of the DataReader takes a column ordinal and returns the
column name.
[ Team LiB ]