Auto-hydrate your objects with ADO.NET
- by Jake Rutherford
Recently while writing the monotonous code for pulling data out of a DataReader to hydrate some objects in an application I suddenly wondered "is this really necessary?" You've probably asked yourself the same question, and many of you have: - Used a code generator - Used a ORM such as Entity Framework - Wrote the code anyway because you like busy work In most of the cases I've dealt with when making a call to a stored procedure the column names match up with the properties of the object I am hydrating. Sure that isn't always the case, but most of the time it's 1 to 1 mapping. Given that fact I whipped up the following method of hydrating my objects without having write all of the code. First I'll show the code, and then explain what it is doing. /// <summary> /// Abstract base class for all Shared objects. /// </summary> /// <typeparam name="T"></typeparam> [Serializable, DataContract(Name = "{0}SharedBase")] public abstract class SharedBase<T> where T : SharedBase<T> { private static List<PropertyInfo> cachedProperties; /// <summary> /// Hydrates derived class with values from record. /// </summary> /// <param name="dataRecord"></param> /// <param name="instance"></param> public static void Hydrate(IDataRecord dataRecord, T instance) { var instanceType = instance.GetType(); //Caching properties to avoid repeated calls to GetProperties. //Noticable performance gains when processing same types repeatedly. if (cachedProperties == null) { cachedProperties = instanceType.GetProperties().ToList(); } foreach (var property in cachedProperties) { if (!dataRecord.ColumnExists(property.Name)) continue; var ordinal = dataRecord.GetOrdinal(property.Name); var isNullable = property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof (Nullable<>); var isNull = dataRecord.IsDBNull(ordinal); var propertyType = property.PropertyType; if (isNullable) { if (!string.IsNullOrEmpty(propertyType.FullName)) { var nullableType = Type.GetType(propertyType.FullName); propertyType = nullableType != null ? nullableType.GetGenericArguments()[0] : propertyType; } } switch (Type.GetTypeCode(propertyType)) { case TypeCode.Int32: property.SetValue(instance, (isNullable && isNull) ? (int?) null : dataRecord.GetInt32(ordinal), null); break; case TypeCode.Double: property.SetValue(instance, (isNullable && isNull) ? (double?) null : dataRecord.GetDouble(ordinal), null); break; case TypeCode.Boolean: property.SetValue(instance, (isNullable && isNull) ? (bool?) null : dataRecord.GetBoolean(ordinal), null); break; case TypeCode.String: property.SetValue(instance, (isNullable && isNull) ? null : isNull ? null : dataRecord.GetString(ordinal), null); break; case TypeCode.Int16: property.SetValue(instance, (isNullable && isNull) ? (int?) null : dataRecord.GetInt16(ordinal), null); break; case TypeCode.DateTime: property.SetValue(instance, (isNullable && isNull) ? (DateTime?) null : dataRecord.GetDateTime(ordinal), null); break; } } } } Here is a class which utilizes the above: [Serializable] [DataContract] public class foo : SharedBase<foo> { [DataMember] public int? ID { get; set; } [DataMember] public string Name { get; set; } [DataMember] public string Description { get; set; } [DataMember] public string Subject { get; set; } [DataMember] public string Body { get; set; } public foo(IDataRecord record) { Hydrate(record, this); } public foo() {} } Explanation: - Class foo inherits from SharedBase specifying itself as the type. (NOTE SharedBase is abstract here in the event we want to provide additional methods which could be overridden by the instance class) public class foo : SharedBase<foo> - One of the foo class constructors accepts a data record which then calls the Hydrate method on SharedBase passing in the record and itself. public foo(IDataRecord record) { Hydrate(record, this); } - Hydrate method on SharedBase will use reflection on the object passed in to determine its properties. At the same time, it will effectively cache these properties to avoid repeated expensive reflection calls public static void Hydrate(IDataRecord dataRecord, T instance) { var instanceType = instance.GetType(); //Caching properties to avoid repeated calls to GetProperties. //Noticable performance gains when processing same types repeatedly. if (cachedProperties == null) { cachedProperties = instanceType.GetProperties().ToList(); } . . . - Hydrate method on SharedBase will iterate each property on the object and determine if a column with matching name exists in data record foreach (var property in cachedProperties) { if (!dataRecord.ColumnExists(property.Name)) continue; var ordinal = dataRecord.GetOrdinal(property.Name); . . . NOTE: ColumnExists is an extension method I put on IDataRecord which I’ll include at the end of this post. - Hydrate method will determine if the property is nullable and whether the value in the corresponding column of the data record has a null value var isNullable = property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof (Nullable<>); var isNull = dataRecord.IsDBNull(ordinal); var propertyType = property.PropertyType; . . . - If Hydrate method determines the property is nullable it will determine the underlying type and set propertyType accordingly - Hydrate method will set the value of the property based upon the propertyType That’s it!!! The magic here is in a few places. First, you may have noticed the following: public abstract class SharedBase<T> where T : SharedBase<T> This says that SharedBase can be created with any type and that for each type it will have it’s own instance. This is important because of the static members within SharedBase. We want this behavior because we are caching the properties for each type. If we did not handle things in this way only 1 type could be cached at a time, or, we’d need to create a collection that allows us to cache the properties for each type = not very elegant. Second, in the constructor for foo you may have noticed this (literally): public foo(IDataRecord record) { Hydrate(record, this); } I wanted the code for auto-hydrating to be as simple as possible. At first I wasn’t quite sure how I could call Hydrate on SharedBase within an instance of the class and pass in the instance itself. Fortunately simply passing in “this” does the trick. I wasn’t sure it would work until I tried it out, and fortunately it did. So, to actually use this feature when utilizing ADO.NET you’d do something like the following: public List<foo> GetFoo(int? fooId) { List<foo> fooList; const string uspName = "usp_GetFoo"; using (var conn = new SqlConnection(_dbConnection)) using (var cmd = new SqlCommand(uspName, conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@FooID", SqlDbType.Int) {Direction = ParameterDirection.Input, Value = fooId}); conn.Open(); using (var dr = cmd.ExecuteReader()) { fooList= (from row in dr.Cast<DbDataRecord>() select new foo(row) ).ToList(); } } return fooList; } Nice! Instead of having line after line manually assigning values from data record to an object you simply create a new instance and pass in the data record. Note that there are certainly instances where columns returned from stored procedure do not always match up with property names. In this scenario you can still use the above method and simply do your manual assignments afterward.
