IT Bytes

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