Poor mans ORM–how to easily persist data

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))             
    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;                             
        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

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 )

Facebook photo

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

Connecting to %s