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.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s