ADO.NET - What is best practice for getting datareader values?

Question

Just wondering what is best practice for getting the values of a datareader. For example:

I have been doing this:

MyValue = Dr("ColumnName")

But have noticed other people doing:

MyValue = Dr.GetString("ColumnName")

I am intested to know the benifits of the 2nd method

Answer 1

DataReader returns type object so you need to cast the value from the DataReader to whatever type MyValue is so in the case of int

MyValue = (int)Dr("ColumnName");

or

MyValue = Convert.ToInt(Dr("ColumnName"));

or

MyValue = Dr("ColumnName").ToInt();

I'm not sure of performance differences but I think that could be construed as micro-optimizing unless working on extremely large data sets

Answer 2

I've been using a wrapper that does a similar thing for a while that does the trick nicely.

     RowHelper helper = new RowHelper(Dr);
     MyValue = helper.IntColumn("ColumnName");

The nice thing about this is the helper will return pre-defined defaults for NULL columns (this is configurable). I've got methods for all the main SQL data types.

To answer your question, the second method has some boxing / unboxing overhead but it would probably be a waste of your time to optimise this for a typical business application.

Answer 3

The DbDataReader.GetString(int) method can only be used if you know the index of the column. Using DbDataReader.GetString("ColumnName") is not possible as there is no such overload. I.e. you have the following two options:

 string myValue1 = (string) Dr["ColumnName"];
 string myValue2 = Dr.GetString(columIndex);

The first line internally calls DbDataReader.GetOrdinal("ColumnName").

Answer 4

I made a couple of generic extension methods for that:

public static class DataReaderExtensions
{
    public static T GetValueOrNull(this IDataRecord reader, string fieldName)
        where T : class
    {
        int index = reader.GetOrdinal(fieldName);
        return reader.IsDBNull(index) ? null : (T)reader.GetValue(index);
    }
}

You get the picture... I also have a GetValueOrDefault for value types.

Answer 5

Using AutoMapper:

var dataReader = ... // Execute a data reader
var views = Mapper.Map<IDataReader, IEnumerable<SomeView>>(_dataReader);