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.
