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.