Ordinals vs. Column Names on DataReader

There is some contention on whether you should use the name of a column versus hard coding the ordinal when using a DataReader to retrieve data from a procedure call. While using the name is more easily maintained and agnostic on the order of columns, there is an inherent overhead with using the column name. While I understand the performance hit of using the named column, I find the value in code readability and column ordering independence worth it.  While, the hit is negligible on a single row result set, the compounding nature may result in a performance change worth avoiding. I came up with a neat little trick that can be used prior to looping through the result set of a list return.  I created a helper class that will give you the ordinals back locally and allow them to be used on each loop.

public Dictionary<String, Int32> GetOrdinals(IDataReader reader)

{

   Dictionary<String, Int32> retVal = new Dictionary<String, Int32>();

   for (int i = 0; i < reader.FieldCount; i++)

   {

     retVal.Add(reader.GetName(i), i);

   }

   return retVal;

}

This enables us to hit the reader row one time and bring back a local variable containing all of the column names as the key of a dictionary with ordinal as a value.  At this point, you can simply use the keyed reference as follows and get the best of both worlds when retrieving large result set.

Boolean hasData = reader.Read();

List<Contract> listItems = new  List<Contract>();

Dictionary<String,Int32> ordinals = null;

if (hasData)

{

   ordinals = GetOrdinals(reader);

}

while (hasData)

{

   Contract item = new Contract();            

   item.ID =  Convert.ToInt64(reader[ordinals["ID"]]);

   item.Name = reader[ordinals["Name"]].ToString();

   listItems.Add(item);

   hasData = reader.Read();

}

This method would be a performance hit when using against a single row return value. And the break-even point of a large result set may take a few rows to gain back the hit of the first loop. However, it is a nice little trick to get the best of both worlds when hitting a large query.