public string OrderUpdate([FromQuery] updateOrders UpdateData, [FromBody] UpdateOrderDetails UpdateDetailData) { //required information if (UpdateData.OrderID == 0) { return("Please input OrderID"); } string result, updateOrder, updateOrderDetial; var Update = new DataUpdate(); //Determine if Order need to update if (UpdateData.CustomerID != null || UpdateData.CustomerID != null || UpdateData.EmployeeID != null || UpdateData.OrderDate != null || UpdateData.RequiredDate != null || UpdateData.ShipVia != null || UpdateData.Freight != null || UpdateData.ShipName != null || UpdateData.ShipAddress != null || UpdateData.ShipCity != null || UpdateData.ShipRegion != null || UpdateData.ShipPostalCode != null || UpdateData.ShipCountry != null) { updateOrder = Update.UpdateOrderData(UpdateData); } else { updateOrder = "Order not Edit"; } //Determine if Order Detail need to update & ProductID is required information if (UpdateDetailData.ProductID != null & (UpdateDetailData.Quantity != null || UpdateDetailData.UnitPrice != null || UpdateDetailData.Discount != null)) { updateOrderDetial = Update.UpdateOrderDetailData(UpdateDetailData, UpdateData.OrderID); } else { updateOrderDetial = "Order Detail not Edit"; } result = updateOrder + " & " + updateOrderDetial; return(result); }
public string UpdateOrderDetailData(UpdateOrderDetails UOD, int getOrderID) { var dynamicParams = new DynamicParameters();//←動態參數 var sqlCondition = @""; string SqlString, result; var Update = new SqlServices(); SqlConnection conn = new SqlConnection("Data Source=howardorder.database.windows.net;Initial Catalog=OrderDatabase;Persist Security Info=True;User Id =howard;Password=Yihao1222"); conn.Open(); for (int i = 0; i < UOD.ProductID.Count(); i++) { sqlCondition = @""; dynamicParams.Add("OrderID", getOrderID); dynamicParams.Add("ProductID", UOD.ProductID[i]); if (UOD.UnitPrice[i] != null) { dynamicParams.Add("UnitPrice", UOD.UnitPrice[i]); } else { var queryUnitPrice = conn.Query <queryPrice>( @"Select a.Unitprice,b.Discount From [Products] a Join [Order Details] b on a.ProductID = b.ProductID Where b.OrderID = OrderID And b.ProductID = @ProductID Group by a.Unitprice,b.Discount" , dynamicParams); float?EndUnitPrice = queryUnitPrice.First().UnitPrice; //計算金額 if (UOD.Discount[i] != null) { EndUnitPrice = EndUnitPrice - (EndUnitPrice * UOD.Discount[i]); } else { EndUnitPrice = queryUnitPrice.First().Discount *EndUnitPrice; } dynamicParams.Add("UnitPrice", EndUnitPrice); } sqlCondition = sqlCondition + "UnitPrice = @UnitPrice,"; if (UOD.Quantity[i] != null) { sqlCondition = sqlCondition + "Quantity = @Quantity,"; dynamicParams.Add("Quantity", UOD.Quantity[i]); } if (UOD.Discount[i] != null) { sqlCondition = sqlCondition + "Discount = @Discount,"; dynamicParams.Add("Discount", UOD.Discount[i]); } sqlCondition = sqlCondition.Remove(sqlCondition.LastIndexOf(","), 1); var queryCustomer = conn.Query <updateOrders>(@"select PD.ProductID From Products PD Join[Order Details] OD on PD.ProductID = OD.ProductID where OrderID = @OrderID And PD.ProductID = @ProductID", dynamicParams); conn.Close(); if (queryCustomer.Count() != 0) { SqlString = $@"UPDATE [Order Details] SET {sqlCondition} WHERE OrderID = @OrderID And ProductID = @ProductID"; result = Update.SqlUpdate(SqlString, dynamicParams); } else { return("No such ProductID"); } } return("Edit Order Detail - Success"); }