public void UpdateFood(FoodDetails food)
{
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute("DELETE FROM FoodNutrient WHERE FoodId=@Id", new { food.Id }, tran);
conn.Execute("DELETE FROM RecipeIngredient WHERE RecipeId=@Id", new { food.Id }, tran);
conn.Execute("DELETE FROM FoodPortion WHERE FoodId=@Id AND Id NOT IN @ids", new { food.Id, ids = food.Portions.Select(p => p.Id) }, tran);
conn.Execute("UPDATE Food SET Name=@Name,Manufacturer=@Manufacturer,CookedWeight=@CookedWeight,NutrientPortionId=@NutrientPortionId,Ean=@Ean WHERE Id=@Id", food, tran);
conn.Execute("INSERT INTO FoodNutrient(FoodId,NutrientId,Amount,PortionAmount) VALUES(@FoodId,@NutrientId,@Amount,@PortionAmount)", food.Nutrients.Select(n => new
{
FoodId = food.Id,
n.NutrientId,
n.Amount,
n.PortionAmount
}), tran);
conn.Execute("UPDATE FoodPortion SET Name=@Name,Weight=@Weight,Amount=@Amount WHERE Id=@Id AND FoodId=@FoodId", food.Portions.Where(p => p.Id != Guid.Empty).Select(p => new
{
p.Id,
FoodId = food.Id,
p.Name,
p.Weight,
p.Amount
}), tran);
conn.Execute("INSERT INTO FoodPortion(Id,FoodId,Name,Weight,Amount) VALUES(@Id,@FoodId,@Name,@Weight,@Amount)", food.Portions.Where(p => p.Id == Guid.Empty).Select(p => new
{
Id = Guid.NewGuid(),
FoodId = food.Id,
p.Name,
p.Weight,
p.Amount
}), tran);
if (food.Ingredients != null)
{
conn.Execute("INSERT INTO RecipeIngredient(Id,RecipeId,[Index],FoodId,Quantity,PortionId,Weight) VALUES(newid(),@RecipeId,@Index,@FoodId,@Quantity,@PortionId,@Weight)", food.Ingredients.Select((i, index) => new
{
RecipeId = food.Id,
Index = index,
i.FoodId,
i.Quantity,
i.PortionId,
i.Weight
}), tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
public void CreateFood(FoodDetails food)
{
food.Id = Guid.NewGuid();
food.Created = DateTimeOffset.Now;
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute("INSERT INTO Food(Id,UserId,Name,Manufacturer,IsRecipe,FineliId,Created,CookedWeight,NutrientPortionId,Ean) VALUES(@Id,@UserId,@Name,@Manufacturer,@IsRecipe,@FineliId,@Created,@CookedWeight,@NutrientPortionId,@Ean)", food, tran);
conn.Execute("INSERT INTO FoodNutrient(FoodId,NutrientId,Amount,PortionAmount) VALUES(@FoodId,@NutrientId,@Amount,@PortionAmount)",
food.Nutrients.Select(n => new { FoodId = food.Id, n.NutrientId, n.Amount, n.PortionAmount }), tran);
if (food.Portions != null)
{
conn.Execute("INSERT INTO FoodPortion(Id,FoodId,Name,Amount,Weight) VALUES(@Id,@FoodId,@Name,@Amount,@Weight)", food.Portions.Select(p => new
{
Id = p.Id == Guid.Empty ? Guid.NewGuid() : p.Id,
FoodId = food.Id,
p.Name,
p.Amount,
p.Weight
}), tran);
}
if (food.Ingredients != null)
{
conn.Execute("INSERT INTO RecipeIngredient(Id,RecipeId,[Index],FoodId,Quantity,PortionId,Weight) VALUES(newid(),@RecipeId,@Index,@FoodId,@Quantity,@PortionId,@Weight)", food.Ingredients.Select((i, index) => new
{
RecipeId = food.Id,
Index = index,
i.FoodId,
i.Quantity,
i.PortionId,
i.Weight
}), tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
public void RestoreFood(Guid id, out FoodDetails food)
{
using (var conn = CreateConnection())
using (var tran = conn.BeginTransaction())
{
try
{
conn.Execute("UPDATE Food SET Deleted=NULL WHERE Id=@Id", new { Id = id }, tran);
tran.Commit();
food = GetFood(id);
}
catch
{
tran.Rollback();
food = null;
throw;
}
}
}