public void RestoreMeal(Guid id, out MealDetails meal) { using (var conn = CreateConnection()) using (var tran = conn.BeginTransaction()) { try { conn.Execute("UPDATE Meal SET Deleted=NULL WHERE Id=@Id", new { Id = id }, tran); tran.Commit(); meal = GetMeal(id); } catch { tran.Rollback(); meal = null; throw; } } }
public void UpdateMeal(MealDetails meal) { foreach (var row in meal.Rows.Where(r => r.Id == Guid.Empty)) { row.MealId = meal.Id; row.Id = Guid.NewGuid(); } using (var conn = CreateConnection()) using (var tran = conn.BeginTransaction()) { try { conn.Execute(@" DELETE FROM MealRow WHERE MealId=@Id; DELETE FROM MealRowNutrient WHERE MealId=@Id; UPDATE Meal SET Time=@Time,DefinitionId=@DefinitionId WHERE Id=@Id", new { meal.Id, meal.DefinitionId, meal.Time }, tran); var rowsTable = meal.Rows.ToDataTable(); using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { DestinationTableName = "MealRow" }) { bulk.WriteToServer(rowsTable); } /* * conn.Execute("INSERT INTO MealRow(Id,MealId,[Index],FoodId,Quantity,PortionId,Weight) VALUES(@Id,@MealId,@Index,@FoodId,@Quantity,@PortionId,@Weight)", meal.Rows.Select((r, i) => new * { * r.Id, * MealId = meal.Id, * Index = i, * r.FoodId, * r.Quantity, * r.PortionId, * r.Weight * }), tran); */ var rowNutrientsTable = meal.Rows.NutrientsToDataTable(); using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { DestinationTableName = "MealRowNutrient" }) { bulk.WriteToServer(rowNutrientsTable); } tran.Commit(); } catch { tran.Rollback(); throw; } } }