Hydrating entities for a custom DAL

There seems to be an endless debate on which data layer tools to use, and not to disappoint, I have some of my own opinions. 

I tend to stay away from ORM's because they are usually a bit heavy handed.  One of my favorite auto generated datalayers is .NetTiers.  It's a CodeSmith template that generates a whole data layer for you.  The downside to this is a lot of code and SP's.

My current employer is very concerned about scalability and performance, so we opt more for handwritten code over generated code.  As a rule, every stored proc, even simple CRUD, is written by a dedicated DB developer.  Some might say it's overkill, but I like how it's working so far.

So, with that in mind, one of the most tedious aspects of creating a DAL is creating the entities and writing the code to hydrate them.

LINQ to SQL is nice for this, but if you're still using ADO.Net this may be useful for you.

Here's a little snippit of code I've been using for a few years now to get data out of a datareader and into a new instance of an entity:

Here's a sample entity:
public class Customer

{
  private int _id;
  private int _username;


  [Column(IsPrimaryKey = true, Storage = "id")]
  public int Id
  {
    get { return _id; }
    set { _id = value; }
  }

  [Column(Storage = "Customer_Username")]
  public int Username
  {
    get { return _username; }
    set { _username = value; }
  }
}

One thing you may notice above is the Column attribute. To keep this example simple, I just used the System.Data.Linq.Mapping.ColumnAttribute class. In the past I created custom attributes, but this post isn't about how to create custom attributes, it's about DAL's. So I'm simply using this attribute to keep track of the column name that the property maps to. It's not necessary, as you'll see further down, but without it your property names must match your column names in your resultset. In this example, the resultset will have 2 columns (id, Customer_username), but I have effectively mapped that to a Customer with properties (Id, Username). Note that it is case sensitive so the mapping for Id was necessary.

Here's my entityhydrator method:

public static class EntityHydrator
{
  public static T HydrateEntity<T>(IDataRecord dataRow)
  {
    Type type = typeof(T);

    CacheProvider cache = CacheProvider.GetInstance();

    ConstructorInfo constructorInfo;
    PropertyInfo[] properties;

    if (!cache.Contains(type.Name + "_constructor"))
    {
      cache.Add(type.Name + "_constructor", type.GetConstructor(Type.EmptyTypes));

    }
    constructorInfo = (ConstructorInfo)cache[type.Name + "_constructor"];

    if(!cache.Contains(type.Name+"_propertyInfo"))
    {
      cache.Add(type.Name + "_propertyInfo", type.GetProperties());

    }
    properties = (PropertyInfo[])cache[type.Name + "_propertyInfo"];

    var instance = constructorInfo.Invoke(null);
    foreach (var propertyInfo in properties)
    {

      string columnName;
      if (!cache.Contains(type.Name + "_"+propertyInfo.Name+"_columnattribute"))
      {
        columnName = propertyInfo.Name;
        var attributeArray = propertyInfo.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), true);
        if (attributeArray.Count() > 0)
        {
          columnName = ((System.Data.Linq.Mapping.DataAttribute)attributeArray[0]).Storage;
        }
        cache.Add(type.Name + "_"+propertyInfo.Name+"_columnattribute",columnName);
      }
      columnName = (string) cache[type.Name + "_"+propertyInfo.Name+"_columnattribute"];


      try
      {
        propertyInfo.SetValue(instance, dataRow[columnName], null);
      }
      catch { }
    }

    return (T)instance;
  }
}

In short, what this is doing is looking at the type T, getting a constructor for T using reflection, instanciating T, getting each property in the instance, and setting each property to be the value of the datafield in the current row of the datareader that matches by name (or Column attribute if present).
There are some key things to note here:

• The method is generic, simply specify the type when you call it and it should work
• The cache object is storing the ConstructorInfo, PropertyInfo, and attributes. Reflection is slow. This is important if you want it to be fast
• Note how I look for the ColumnAttribute attached to each property. It's not necessary because if not found, it defaults to using property name as the field name when looking in the recordset for the data
• There is a potential for many exceptions to be happening here. For brevity, I left out the code for dealing with this, but for real world code where speed is a concern, exception handling is slow. Handling them is not enough, you must do something to prevent the exceptions from happening. More on that below.

Something else to note here is that this method is very basic. I didn't want to type a book here, so I left out many other considerations. For example, if a column is an integer and is nullable, but the entity's property is declared as int instead of int?. Additionally, certain datatypes, i.e. enums, require a bit of extra care.

As mentioned above, something else to consider would be the empty catch. It simply does nothing, the property's value is set to the default for it's type. It's there to handle the case where a column doesn't map to a property (i.e. IsDirty) or there is a problem casting the data. In the past, I've had multiple complex catch blocks to determine what is wrong and react appropriately.
Usually at a minimum would be a catch for IndexOutOfRangeException and I would handle this by removing that property from the cached properties to prevent the exception from firing again. Enums (where the data is the name of the enum value), nullable (bit, int, etc.) columns and others are all potential problems as well.
Play with the code, you'll have a robust method in no time.

Finally, on to usage:

.
.
.
var datareader = cmd.ExecuteReader();

List customers = new List<customer\>();
while (datareader.Read())
{
  var customer = EntityHydrator.HydrateEntity<customer>(datareader);
  customers.Add(customer);
}

This snippet of code assumes we have already prepared a sqlcommand to get a resultset for several of our entities. From there, were going to instanciate a new list of our entity and loop through the resultset, hydrating new instances of our entity as we go and adding them to the list.

Like I said above, I've been using this for a few years, and it's been a great way to save my fingers from all that extra typing you have to do when hydrating entities. Something else to look at is using Linq and lambdas to do the same.