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

I was reading some or another question on Stack Overflow today, and 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 value (an object) back 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 in 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.

GetFieldValue<T> with Column Name

Here’s an extension method that does just that:

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));
    }
}

You can use it like this:

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");
        }
    }
}

Subscribe to Weekly Updates!

Get an email with the latest posts, once per week...
* indicates required