public void SaveOrder(string buyer, string buyerNotification, double summaryOrderValue, List <OrderLineModelDto> list) { using (IDbConnection cnn = new SqlConnection(_sqlDataAccess.GetConnectionString())) { cnn.Open(); using (var transaction = cnn.BeginTransaction()) { string sqlHeader = $"INSERT INTO OrderHeader (Number, BuyerId, Status, BuyerNotification, SummaryValue) " + $"VALUES ((select dbo.GetOrderNumber()), @BuyerId, @Status, @BuyerNotification, @SummaryValue);" + $"SELECT CAST(SCOPE_IDENTITY() as int)"; var result = cnn.ExecuteScalar(sqlHeader, new { BuyerId = buyer , Status = Const.StatusesList.Where(x => x.Status == StatusEnum.Submitted).FirstOrDefault().StatusId , BuyerNotification = buyerNotification , SummaryValue = summaryOrderValue }, transaction: transaction); int OrderHeaderId = Convert.ToInt32(result); foreach (var line in list) { string sqlLine = $"INSERT INTO OrderLine ([OrderHeaderId], [ItemId], [ItemName], [PriceNet], [PriceGross], [Tax], [SubmittedQty]) " + $"VALUES (@OrderHeaderId, @ItemId, @ItemName, @PriceNet, @PriceGross, @Tax, @SubmittedQty)"; cnn.Execute(sqlLine, new { OrderHeaderId, line.ItemId, line.ItemName, line.PriceNet, line.PriceGross, line.Tax, line.SubmittedQty }, transaction: transaction); } transaction.Commit(); } } }