ORM

Poor mans ORM–how to easily persist data

pauliomLeave a comment

Carrying on from my post about easily loading data from a database into a list, I thought I’d publish how to easily save the data from a list of objects into the database. It works by convention, so you need to create a stored procedure called <type name>_Save and provide an argument for each property you want to be saved.

        
public void <TCollection, TItem> Save(TCollection dataToSave) where TCollection : IList         
{             
  TCollection instance = (TCollection)Activator.CreateInstance(typeof(TCollection));             
  IList list = instance as IList;             
  if (list == null)             
  {                 
    throw new InvalidConstraintException("must provide an IList");             
  }              
  Type itemType = typeof(TItem);             
  string procedureName = string.Format("{0}_Save", itemType.Name);              
  SqlParameter[] commandParameters =                     
    SqlHelperParameterCache.GetStoredProcedureParameterSet(this.connectionString, procedureName);
  using (SqlConnection connection = new SqlConnection(this.connectionString))             
  {                 
    connection.Open();                 
    foreach (var item in dataToSave)                 
    {                     
      using (SqlCommand command = new SqlCommand())                     
      {                         
        command.Connection = connection;                         
        command.CommandType = CommandType.StoredProcedure;                         
        command.CommandText = procedureName;                          
        foreach (SqlParameter inputParam in commandParameters.Where(p => p.Direction == ParameterDirection.Input 
            || p.Direction == ParameterDirection.InputOutput))                         
        {                             
          string parameterName = inputParam.ParameterName.Replace("@","");                             
          object value = GetValue(item, parameterName);                             
          inputParam.Value = value;                             
          command.Parameters.Add(inputParam);                         
        }                         
        int rowsAffected = command.ExecuteNonQuery();                         
        System.Diagnostics.Debug.WriteLine("Called " + procedureName + ", rows affected: " + rowsAffected);
      }                 
    }
  }
}

I hope you find the code useful, I’ve certainly found it a quick and easy way to prototype code

Poor mans ORM

pauliomLeave a comment

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.