July 30, 2010

Cleaning up my data access layer with generics and extension methods

Last night Cory Fowler (@SyntaxC4) and I got to discussing helper methods to clean up database interaction for our data access layers (DALs). I'm currently working on a project where I'm having to pull large amounts of data from a SQL Server database and apply it to properties. In small batches, this isn't a problem, but something just doesn't feel right about hundreds of lines of:

if(!DBNull.Equals(reader["fieldX"]) myClass.PropertyX = 
    rdr.GetString(reader.GetOrdinal("fieldX"));

...even with the correct base classes set up to take the heavy lifting so you're only setting any given property in only one place.

Between us we managed to come up with a pretty neat generic method of just a couple of lines of code that allowed us to condense this a fair bit

public static T GetDbValue<T>(object value){
    if (!DBNull.Value.Equals(value))
        return (T)Convert.ChangeType(value, typeof(T));
    return default(T);
}

Which could also be applied as an extension in .NET 3.5 (and up)

public static class DBExtensions
{
    public static T GetDbValue<T>(this object value){
        if (!DBNull.Value.Equals(value))
            return (T)Convert.ChangeType(value, typeof(T));
        return default(T);
    }
}

And I can call it using:

myClass.PropertyX = rdr["fieldX"].GetDbValue<int32>();

Awesome! and thanks to Cory for his initial inspiration that got us started which he blogged about this morning in his post Database Safe Values, Using Generics, and Extension Methods.

This morning though, something was niggling me - I don't want this extension applying to any object. It just feels...dirty, because this is only really relevant in this form for database values (because of the DBNull). So this leaves me with 2 thoughts... do I allow this to be applied to any type of object and somehow account for the fact that database values may be DBNull and "regular values" may either be null or nullable? Or do I find a way to apply this only to IDataReader fields?

I wasn't even sure I could distinguish the difference between a generic object and a IDataReader field, and indeed, upon further investigation it seems that the IDataReader field object is just a simple generic object, which leaves no way to distinguish it from any other object... so I guess that's it, I'm stuck with that...

Then I had my morning tea...

IDataReader has a bunch of GetXXX fields already, all I'm really trying to do is mimic them, but with an additional null check [plus, I don't want different methods for each data type, that's just so... well... I just don't like it]. So why don't I just attach my extension method directly to the IDataReader, which is what it really applies to, chasing down to the field level was one level too far.

public static T GetValue<T>(this IDataReader reader, 
                            string fieldName)
{
    object value = reader[fieldName];
    if (!DbNull.Value.Equals(value))
        return (T)Convert.ChangeType(value, typeof(T));
    return default(T);
}

Sweet, now I can call it using the much more standard:

myClass.PropertyX = rdr.GetValue<int>("fieldX");

I've combined my null checking into the method so I don't have hundreds of checks coded, they're contained in the method, and it's type-safe via the wonder of generics.

All this said, I could have left it attached as an extension method to object, and just added an extra check for null to the if line:

if (value != null && !DBNull.Value.Equals(value))
    return (T)Convert.ChangeType(value, typeof(T));

This will now work with null and DBNull, so you could apply it to any object*. I think my preference is to extend IDataReader in this instance purely because I prefer the calling syntax of:

myClass.PropertyX = rdr.GetValue<int>("fieldX");
over:
myClass.PropertyX = rdr["fieldX"].GetValue<int>();

For no other reason than it just looks prettier to me. But for other scenarios, the second method may be more suitable.

Addendum:

As John pointed out, this doesn't account for true null values being passed (vs. DBNull). If a null value is passed into the method, it will crash attempting to do the conversion. In this case, a slight modification to the code is in order:

public static T GetValue<T>(this IDataReader reader, 
                            string fieldName)
{
    object value = reader[fieldName];

    if (value == null || DBNull.Value.Equals(value))
        return default(T);

    Type t = typeof(T);
    return (T)Convert.ChangeType(value, 
        Nullable.GetUnderlyingType(t) ?? t);
}

So now we should be able to translate null as well as DBNull into the correct type specified by the generic.

11 comments:

  1. I prefer the passing the field name syntax into the method as well.

    I wonder if modifying this for nullable types wouldn't be useful as well. Say something like this?

    public static T? GetNullableValue(this IDataReader reader,
    string fieldName)
    {
    object value = rdr[fieldName];
    if (value == null)
    return null;
    if (DbNull.Value.Equals(value))
    return null;
    return (T)Convert.ChangeType(value, typeof(T));
    }

    BTW-What is the first 'r' in the IDataReader variable stand for? I usually just use dr.

    ReplyDelete
  2. In the context of your naming schema, you're using letters to represent words, i.e. D = Data, R = Reader; In the context of my naming schema for these code exerpts, it's phonetic shortening rdr = ReaDeR. "dr" or "rdr" makes little difference to me, it's a schema that I often use habitually and the single extra character doesn't cost me any time as my fingers type rdr on autopilot.

    Going on to your point about the return type being a Nullable<T>, this is automatically inferred with my method example as you call it using the generic nullable, i.e.:

    int? val = dr.GetValue<int?>("fieldX");

    It will return whatever type you tell it needs to be returned. When you apply a value to a nullable, it still returns the value as a nullable type. In my testing, everything worked just fine.

    ReplyDelete
  3. Ok, a couple things:
    1. dr vs rdr ... makes sense, I was just wondering.
    2. I think the variable in your sample (and mine) should be reader not rdr (see the parameter)

    But mostly
    3. You can't pass in a nullable value into your method, because it dies on Convert.ChangeType()

    I changed my method a bit when I tried to compile it.
    public static T? GetNullableValue(this IDataReader reader, string fieldName)
    where T : struct
    {
    object value = reader[fieldName];
    if (value == null)
    return null;
    if (DBNull.Value.Equals(value))
    return null;
    return (T)Convert.ChangeType(value, typeof(T));
    }

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Okay, I'll admit I hadn't tested for that scenario as I've never seen an actual null value come back from a database, I've only ever seen DBNull.Value come back which my code excerpt worked for (well, all bar the naming issue with my transcription into the post). In order to account for true null values, I'd modify the code ever so slightly:

    public static T GetValue<T>(this IDataReader reader, string fieldName)
    {
      object value = reader[fieldName];
      if (value == null || DBNull.Value.Equals(value))
        return default(T);
      return (T)Convert.ChangeType(value, typeof(T));
    }

    That should now account for null, DBNull and nullable types without the need for a second method to handle nullable types.

    ReplyDelete
  6. returning default(T) will allow for nullable values to be passed back in the same way that it will pass null back for values that allow null and default values for those that don't, i.e. DateTime and Int.

    ReplyDelete
  7. I did notice however that if (somehow) you managed to get a nullable value (i.e. Nullable) into one of the fields of your IDataReader, the code would bomb out attempting to do the conversion. I don't know if it's even possible for an IDataReader field to return either null or a nullable value (i.e Nullable) though. So I'm not sure if this is even relevant.

    ReplyDelete
  8. Hey, I just reread my last comment, and in number 3, I misspoke, "nullable value" should read "nullable type".

    Anyway, here's a small console app that I wrote to test it. Notice on the last call to your extension method, it passes in 'int?' ... that's the part that's got the problem, since the call to Convert.ChangeType() falls apart doing a conversion to typeof(int?) or any nullable type for that matter.

    //-----------------------------------
    using System;
    using System.Data;
    using System.Data.SqlClient;

    namespace NullableExtensionMethodTest
    {
    class Program
    {
    static void Main(string[] args)
    {
    try
    {
    using (SqlConnection cn = new SqlConnection(@"Data Source=.;Database=master;Integrated Security=SSPI;"))
    {
    cn.Open();
    using (SqlCommand cmd = new SqlCommand("select top 1 id, name from sysobjects where xtype='U'", cn))
    using (IDataReader dr = cmd.ExecuteReader())
    {
    if (!dr.Read())
    {
    Console.WriteLine("No results");
    return;
    }

    Console.WriteLine("-- GetNullableValue()");
    Console.Write("Retrieving id ...");
    int? nullableVariable = dr.GetNullableValue("id");
    string name = dr["name"].ToString();
    if (!nullableVariable.HasValue)
    Console.WriteLine("**ERROR** ... null value returned");
    else
    Console.WriteLine("Success - [{0}] {1}", nullableVariable.ToString(), name);

    Console.WriteLine("-- GetValue() - non-null type");
    Console.Write("Retrieving id ...");
    int nonNullableVariable = dr.GetValue("id");
    name = dr["name"].ToString();
    Console.WriteLine("Success - [{0}] {1}", nonNullableVariable.ToString(), name);

    Console.WriteLine("-- GetValue() - non-null type");
    Console.Write("Retrieving id ...");
    nullableVariable = dr.GetValue("id");
    name = dr["name"].ToString();
    Console.WriteLine("Success - [{0}] {1}", nullableVariable.ToString(), name);
    }
    }
    }
    catch (Exception ex)
    {
    Console.WriteLine("-- Error --");
    Console.Write(ex.ToString());
    }
    finally
    {
    Console.WriteLine("\n\nPress any key to exit...");
    Console.ReadKey();
    }
    }
    }

    public static class DataReaderExtension
    {
    public static T? GetNullableValue(this IDataReader reader, string fieldName)
    where T : struct
    {
    object value = reader[fieldName];
    if (value == null)
    return null;
    if (DBNull.Value.Equals(value))
    return null;
    return (T)Convert.ChangeType(value, typeof(T));
    }

    public static T GetValue(this IDataReader reader, string fieldName)
    {
    object value = reader[fieldName];
    if (value == null || DBNull.Value.Equals(value))
    return default(T);
    return (T)Convert.ChangeType(value, typeof(T));
    }
    }
    }

    ReplyDelete
  9. Okay, I see the issue now, here's a fix that accounts for that:

    public static T GetValue<T>(this IDataReader reader, string fieldName)
    {
      object value = reader[fieldName];
      if (value == null || DBNull.Value.Equals(value))
        return default(T);
      return (T)Convert.ChangeType(value, Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T));
    }

    Because a the default of a nullable type is null, all you've got to do is check the underlying type in the Convert.ChangeType line to have it return the correct type.

    ReplyDelete
  10. Hey, I just reread my last comment, and in number 3, I misspoke, "nullable value" should read "nullable type".

    Anyway, here's a small console app that I wrote to test it. Notice on the last call to your extension method, it passes in 'int?' ... that's the part that's got the problem, since the call to Convert.ChangeType() falls apart doing a conversion to typeof(int?) or any nullable type for that matter.

    http://pastebin.com/P5fAUEPp

    PS-If I can't delete my previous post, the reason there are two is for the formatting & the dropped characters in my previous comment.

    ReplyDelete