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