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; } } }