ADO.NET 3.5 Cookbook, 2nd Edition

10.12. 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 hardcoding the ordinal values.

Solution

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

The solution uses a DataReader to retrieve and output the column ordinals for result set columns—ContactID, FirstName, and LastName—from the Person.Contact table in AdventureWorks. The solution then demonstrates three techniques to retrieve and output these fields for the first five rows in the Person.Contact table. The techniques used are index-based, .NET typed, and provider-specific typed.

The C# code in Program.cs in the project DataReaderColumnOrdinals is shown in Example 10-22.

Example 10-22. File: Program.cs for DataReaderColumnOrdinals solution

using System; using System.Data; using System.Data.SqlClient; namespace DataReaderColumnOrdinals { class Program { static void Main(string[] args) { int coContactID, coFirstName, coLastName; string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 * FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); // Create the command and open the connection SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open(); // Create the DataReader to retrieve ...