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.
