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