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);
        list.Add(item);
      }
    }
  }
  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);
  }
  else
  {
    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
{
    [TypeConverter(typeof(MyIdTypeConverter))]
    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.