Poor mans ORM

Recently I’ve been looking at and hearing about data abstraction code that basically just calls a stored procedure and populates an objects on the way back. No identity management, just a simple mapper. I thought about using AutoMapper but as an intellectual exercise I wanted to see how easy (or not) it was to write a single helper that could move data from the stored procedure to an object based on convention. Here is the result;

public TCollection Load<TCollection, TItem>(string procedureName, params object[] arguments) where TCollection : IList
  this.connectionString = ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString;
  TCollection instance = (TCollection)Activator.CreateInstance(typeof(TCollection));
  IList list = instance as IList;
  if (list == null)
    throw new ArgumentOutOfRangeException("procedureName");
  procedureName = "mySchema." + procedureName;
  using (SqlDataReader reader = ExecuteProcedure(procedureName, arguments))
    while (reader.Read())
      TItem item = (TItem)Activator.CreateInstance(typeof(TItem));
      for (int i = 0; i < reader.FieldCount; i++ )
        string fieldName = reader.GetName(i);
        object value = reader[i];
        SetValue(item, fieldName, value);
  return instance;

The code assumes you’ll be getting objects representing lists of something <TCollection,TItem> and that the properties match the result set columns. The ExecuteProcedure command takes the arguments and maps them to the stored procedure arguments, easy enough to write. The SetValue is a little more involved;

private static void SetValue(object instance, string propertyName, object value)
  Type type = instance.GetType();
  PropertyInfo property = type.GetProperty(propertyName);
  var descriptor = TypeDescriptor.GetProperties(instance)[propertyName];
  TypeConverter converter = descriptor.Converter;
  if (converter != null)
    property.SetValue(instance, converter.ConvertFrom(value), null);
    property.SetValue(instance, value, null);

The code uses standard reflection methods to set the property of the instance. The complication is the converter. If you are using a type that isn’t directly represented by a .net variant of a SQLType then you’ll need to use a converter. For example the following Id type needs its accompanying type converter (thanks to Chris Hannon)

public class MyItem
    public MyId Id { get; set; }
public class MyIdTypeConverter : TypeConverter
    public override object ConvertFrom(ITypeDescriptorContext context,
            System.Globalization.CultureInfo culture,
            object value)
      if (value is int)
        return new MyId((int)value);
      else if (value is MyId)
        return value;
      return base.ConvertFrom(context, culture, value);

So there you go, not eactly pain free but if you have a number of stored procedures and classes and you’re not overly worried about the performance hit of reflection then perhaps something like this will fit.

This entry was posted in Computers and Internet, Development and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s