Learning ADO.NET 3.5 Cookbook:(4) Improving DataReader Performance with Typed Accessors

Problem

You need to improve performance when accessing data from a DataReader.

Output for DataReaderTypedAccessors solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace DataReaderTypedAccessors
{
    class Program
    {
        static void Main(string[] args)
        {
            int loops = 1000;

            int contactID;
            string firstName;
            string middleName = null;
            string lastName;

            int startTick = 0;
            int elapsedTick;

            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT ContactID, FirstName, MiddleName, LastName " +
                "FROM Person.Contact";

            Console.WriteLine("---DataReader column value access timing test, " +
                "{0} iterations---\n", loops);

            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create the command and open the connection
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open();

            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a typed accessor with a column ordinal
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = dr.GetInt32(0);
                        firstName = dr.GetString(1);
                        middleName = dr.IsDBNull(2) ? null : dr.GetString(2);
                        lastName = dr.GetString(3);
                    }
                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Typed accessor: Ticks = {0}",
                elapsedTick);

            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a column ordinal
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = Convert.ToInt32(dr[0]);
                        firstName = Convert.ToString(dr[1]);
                        middleName = Convert.ToString(dr[2]);
                        lastName = Convert.ToString(dr[3]);
                    }

                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Column ordinal: Ticks = {0}", elapsedTick);

            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a column field name
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = Convert.ToInt32(dr["ContactID"]);
                        firstName = Convert.ToString(dr["FirstName"]);
                        middleName = Convert.ToString(dr["MiddleName"]);
                        lastName = Convert.ToString(dr["LastName"]);
                    }

                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Column name: Ticks = {0}", elapsedTick);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}