An extension method for passing a column name to SqlDataReader.GetFieldValue

There are two SqlDataReader.GetFieldValue methods - one that accepts an ordinal value and returns a specific type, and one that accepts a column name but returns an object. Let's try to have the best of both worlds, passing in a column name and getting back a specific type.

An extension method for passing a column name to SqlDataReader.GetFieldValue

I stumbled on a post where someone happened to reference the SqlDataReader.GetFieldValue method. It’s a generic method that returns the value of a column, given its 0-based ordinal value.

using (var conn = new SqlConnection("yourConnectionString"))
{
    using (var cmd = new SqlCommand("SELECT name, age FROM students", conn))
    {
        conn.Open();
        using (var dr = cmd.ExecuteReader())
        {
            var name = dr.GetFieldValue<string>(0);
            var age = dr.GetFieldValue<int>(1);
        }
    }
}

This doesn’t seem very user-friendly to me. What happens if the query changes? If an additional column is added to the beginning, or the existing columns are swapped? It’d be too easy to cause an InvalidCastException to be thrown. On the up side, at least it casts the object to the type we specify, before returning it.

You can also access the column using the indexer in SqlDataReader. Internally, it runs pretty much the same code as the above method, except it doesn’t cast to a particular data type, so all you get back is an object that you have to convert yourself.

using (var conn = new SqlConnection("yourConnectionString"))
{
    using (var cmd = new SqlCommand("SELECT name, age FROM students", conn))
    {
        conn.Open();
        using (var dr = cmd.ExecuteReader())
        {
            var name = dr[0].ToString();
            var age = Convert.ToInt32(dr[1]);
        }
    }
}

You can also use an indexer with the column name. The following method uses the column name to find the ordinal value, then runs the same code as the above method. Both of these methods just look uglier to me.

using (var conn = new SqlConnection("yourConnectionString"))
{
    using (var cmd = new SqlCommand("SELECT name, age FROM students", conn))
    {
        conn.Open();
        using (var dr = cmd.ExecuteReader())
        {
            var name = dr["name"].ToString();
            var age = Convert.ToInt32(dr["age"]);
        }
    }
}

My preference would be to combine features from both, to be able to specify the return data type as in the first method, but to reference the column name as in the third method.

Here’s an extension method that does just that: (grab the code on GitHub)

public static class SqlReaderExtensions
{
    /// <summary>
    /// Gets the value of the specified column as a type, given the column name.
    /// </summary>
    /// <typeparam name="T">The expected type of the column being retrieved.</typeparam>
    /// <param name="reader">The reader from which to retrieve the column.</param>
    /// <param name="columnName">The name of the column to be retrieved.</param>
    /// <returns>The returned type object.</returns>
    public static T GetFieldValue<T>(this SqlDataReader reader, string columnName)
    {
        return reader.GetFieldValue<T>(reader.GetOrdinal(columnName));
    }
}

And here's how you would use it:

using (var conn = new SqlConnection("yourConnectionString"))
{
    using (var cmd = new SqlCommand("yourQuery", conn))
    {
        conn.Open();
        using (var dr = cmd.ExecuteReader())
        {
            var name = dr.GetFieldValue<string>("name");
            var age = dr.GetFieldValue<int>("age");
        }
    }
}