Geeks With Blogs
Title Of Blog

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.

 

I should show how this method of hydration is used in conjunction with an ADO call. See below:

public List<Template> GetFooList()
        {
            List<foo> list;

            const string uspName = "usp_GetFoo";

            using (var conn = new SqlConnection(_sqlConnection))
            using (var cmd = new SqlCommand(uspName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@fooID", SqlDbType.Int) { Direction = ParameterDirection.Input, Value = DBNull.Value });
                conn.Open();
                using (var dr = cmd.ExecuteReader())
                {
                    list = (from row in dr.Cast<DbDataRecord>()
                                  select new foo(row)
                                 ).ToList();
                }
            }

            return list;
        }

 

EDIT: Here’s the extension method I mentioned above which checks whether a given column exists in the data record…

 

/// <summary>
/// Extension method allowing quick check for existence of column.
/// </summary>
/// <param name="record"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static bool ColumnExists(this IDataRecord record, string columnName)
{
     for (var i = 0; i < record.FieldCount; i++)
     {
          var tempName = record.GetName(i);
          if (columnName == tempName)
          {
               return true;
          }
     }
     return false;
}

Posted on Wednesday, April 11, 2012 9:50 AM C# , SQL | Back to top


Comments on this post: Auto-hydrate your objects with ADO.NET

# re: Auto-hydrate your objects with ADO.NET
Requesting Gravatar...
So where's that "ColumnExists" extension method now???
Left by Marc Scheuner on Apr 12, 2012 10:35 AM

# re: Auto-hydrate your objects with ADO.NET
Requesting Gravatar...
Just wondering - couldn't the "Hydrate" method also be an extension method for the "IDataRecord" object class??

I like the idea - and it works really well and super easy - but being forced to inherit from a base class just to get this functionality might be a problem - especially in an existing large application.....

Have you ever thought about turning ".Hydrate()" into an extension method?? That would be absolutely awesome!
Left by Marc Scheuner on Apr 13, 2012 5:31 AM

# re: Auto-hydrate your objects with ADO.NET
Requesting Gravatar...
Possibly. I started working on that yesterday, unfortunately I don't know that I have sufficient grasp of generics just yet in order to accomplish that feat.

Could be the way I tried to implement it, but is seems as though you'd have to pass the type of object you want to hydrate into the extension method. Since the extension method itself off of IDataRecord you'd have to have a separate static class to hold the cached properties for each type.

That's where things got a little tricky. With my current approach the cached properties and the Hydrate method are part of the same class. Need to figure out out to separate the two when using extension method and generics.
Left by Jake on Apr 17, 2012 8:48 AM

Your comment:
 (will show your gravatar)


Copyright © Jake Rutherford | Powered by: GeeksWithBlogs.net