Wednesday, October 21, 2009

Strongly Typed GetValue Extension Method for SqlDataReader

SqlDataReader GetValue Extension MethodFor a very long time, I've been bitching about the fact that the methods on the System.Data.SqlDataReader class which get a strongly typed result don't have overloads that take column names. I've seen a number of blogs that say, "the reason you can't do this is because you can't overload a method on return type," which is true but irrelevant. I don't see any reason the GetInt32(int i) method couldn't have a GetInt32(string name) overload that handles the GetOrdinal(string name) on your behalf (other than the fact that nobody wanted to write the two dozen overloads.

Well, finding this state of affairs unacceptable, I decided to rectify it. I expected it to be either difficult or annoying depending on the solution I accepted. First, I decided that it should be an extension method because I love extension methods and the fluency they provide. I also decided that it should be generic so you could have one method and specify the desired type. The tough decision came when I was trying to figure out how to handle all of the conversions between types.

Should I switch on the generic type and call the associated method on the SqlDataReader? That is to say, if you call GetValue should I call SqlDataReader.GetInt32 or should I just get the value as an object and handle all of the converting myself? Well, I decided that I'd rather deal with the difficulty of the type conversions than have a big ugly case statement in my method.

Then, I was looking around in the .net classes and found System.Convert.ChangeType and it made the whole process considerably easier. We also had a need to attempt to return the value as a specified type without throwing an exception if it failed (like int.TryParse) so I included a TryGetValue method as well.
public static T GetValue<T>(this SqlDataReader reader, string name)
{
    return (T)Convert.ChangeType(reader[name], typeof(T));
}

public static bool TryGetValue<T>(this SqlDataReader reader, string name, out T output)
{
    try
    {
        output = reader.GetValue<T>(name);
        return true;
    }

    catch (Exception ex)
    {
        if (ex is InvalidCastException || ex is FormatException || ex is OverflowException)
        {
            output = default(T);
            return false;
        }

        else
            throw;
    }
}

// usage examples

// string testString;
// bool result = reader.TryGetValue<string>("ColumnName", out testString);

// int testInt;
// bool result = reader.TryGetValue<int>("ColumnName", out testInt);

// int i = reader.GetValue<int>("ColumnName");
// string s = reader.GetValue<string>("ColumnName");
// DateTime dt = reader.GetValue<datetime>("ColumnName");

No comments:

Post a Comment