public void UpdateMealRow(MealRow row)
{
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute(@"
UPDATE MealRow SET FoodId=@FoodId, PortionId=@PortionId, Quantity=@Quantity, Weight=@Weight WHERE Id=@Id;
DELETE FROM MealRowNutrient WHERE MealRowId=@Id;", row, tran);
var rowNutrientsTable = new[] { row }.NutrientsToDataTable();
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)
{
DestinationTableName = "MealRowNutrient"
})
{
bulk.WriteToServer(rowNutrientsTable);
}
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
public void DeleteMealRow(MealRow row)
{
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute("DELETE FROM MealRow WHERE Id=@Id", row, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
public void CreateMealRow(MealRow row, int index)
{
row.Id = Guid.NewGuid();
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute("INSERT INTO MealRow(Id,MealId,[Index],FoodId,Quantity,PortionId,Weight) VALUES(@Id,@MealId,@Index,@FoodId,@Quantity,@PortionId,@Weight)", new
{
row.Id,
row.MealId,
Index = index,
row.FoodId,
row.Quantity,
row.PortionId,
row.Weight
}, tran);
var rowNutrientsTable = new[] { row }.NutrientsToDataTable();
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)
{
DestinationTableName = "MealRowNutrient"
})
{
bulk.WriteToServer(rowNutrientsTable);
}
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
