Quantity minus UnitsInStock

Question

Hi

I have a table in my DB called Products.

Products has various columns but in particular one named ProductId and one named UnitsInStock.

ProductId starts at 1 and increments by 1 and I have a total of 2000 products.

UnitInStock holds the amount of stock for each product ie 5000, 6000 and so on.

I have a SubmitOrder method which persists the data from the ShoppingCart back to the DB in particular the Quantity.

What I would like to do is implement a solution in order to minus the Quantity from the UnitsInStock to enable me to display this on each of the product details.

What I was thinking was to get the UnitsInStock for each ProductId from the Products table and then get the Quantity placed from the Order Details table then subtract one from the other to get a total.

My approach is to get the ProductId and Quanity from the SubmitOrder method ie

OrderDetail od = new OrderDetail();
var getProductId = od.ProductId;
var getQuantity = od.Quantity;

Then somehow call on the Products table getting the UnitsInStock for the particular ProductId passed in and then subtract the Quantity from the UnitsInStock.

Im guessing some sort of Query will be required but im stuck on how to write this.

Thanks

Steven

Vulpes · Answer

The problem with that code is that you're creating a new Product object whereas what you want to do is to alter an existing Product object. With no great confidence I'd suggest the highlighted changes: public int SubmitOrder(string userName) { int NewOrderKey = -1; using(DCECommerceDBEntities db = new DCECommerceDBEntities()) { Order newOrder = new Order(); newOrder.CustomerName = userName; newOrder.OrderDate = DateTime.Now; db.Orders.AddObject(newOrder); db.SaveChanges(); NewOrderKey = (from p in db.Orders select p.OrderID).Max(); String cartId = GetShoppingCartId(); var myCart = (from c in db.ViewCarts where c.CartID == cartId select c); foreach (ViewCart item in myCart) { OrderDetail od = new OrderDetail(); od.OrderID = NewOrderKey; od.ProductID = item.ProductID; od.Quantity = item.Quantity; od.UnitCost = item.UnitCost; db.OrderDetails.AddObject(od); Product prod = (from p in db.Products where p.ProductId == od.ProductId select p).Single(); prod.UnitsInStock -= od.Quantity; var myItem = (from c in db.ShoppingCarts where c.CartID == item.CartID && c.ProductID == item.ProductID select c).FirstOrDefault(); if (myItem != null) { db.DeleteObject(myItem); } } db.SaveChanges(); } return NewOrderKey; } Sorry I was referring earlier to decrementing the Quantity field of the OrderDetails entities when I should of course have been referring to decrementing the UnitsInStock field of the Products entities by the corresponding Quantity field.

S · Answer

Absolute magic Vulpes it works a treat! Many thanks indeed for all your help and patience : ) Regards Steven

S · Answer

Or even something like Product p = new Product(); if (p.ProductID == od.ProductID) { p.UnitsInStock -= od.Quantity; } Thanks Steven

S · Answer

Vulpes How does this look to you? Product p = new Product(); p.UnitsInStock - od.Quantity; Thanks Steven

S · Answer

Ok I understand thanks. So to wrap this up once and for all. This is my SubmitOrder method. public int SubmitOrder(string userName) { int NewOrderKey = -1; using(DCECommerceDBEntities db = new DCECommerceDBEntities()) { Order newOrder = new Order(); newOrder.CustomerName = userName; newOrder.OrderDate = DateTime.Now; db.Orders.AddObject(newOrder); db.SaveChanges(); NewOrderKey = (from p in db.Orders select p.OrderID).Max(); String cartId = GetShoppingCartId(); var myCart = (from c in db.ViewCarts where c.CartID == cartId select c); foreach (ViewCart item in myCart) { OrderDetail od = new OrderDetail(); od.OrderID = NewOrderKey; od.ProductID = item.ProductID; od.Quantity = item.Quantity; od.UnitCost = item.UnitCost; db.OrderDetails.AddObject(od); var myItem = (from c in db.ShoppingCarts where c.CartID == item.CartID && c.ProductID == item.ProductID select c).FirstOrDefault(); if (myItem != null) { db.DeleteObject(myItem); } } db.SaveChanges(); } return NewOrderKey; } Could you be so kind as to add the necessary code as you quote  You just have to iterate though all your OrderDetails entities and change the Quantity field (or property) to its new value using normal C# code. When you've finished that you then update the actual database table by calling the SaveChanges method.  Regards Steven

Vulpes · Answer

When you first asked this question I assumed you'd be using ADO.NET with native SQL queries - hence my suggested UPDATE statement. However if you're using EF then I don't think there's a way to use a native UPDATE statement as the entities are high-level C# abstractions of the actual database tables. You just have to iterate though all your OrderDetails entities and change the Quantity field (or property) to its new value using normal C# code. When you've finished that you then update the actual database table by calling the SaveChanges method.

S · Answer

Ok thanks Vulpes So given:- SELECT Products.UnitsInStock OrderDetails.Quantity OrderDetails.ProductID FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID ORDER BY Products.UnitsInStock ASC UPDATE Products SET UnitsInStock = UnitsInStock - OrderDetails.Quantity WHERE ProductID = OrderDetails.ProductID How and where could I implement this into the SubmitOrder method (obviously before the db.SaveChanges() call). I dont know how I would encapsulate and place this so it is called and fired off to the DB? Im guessing I should use something like the following before the db.SaveChanges() call:- var updateQuantityFromUnitsInStock = (from where select); ? Regards Steven

Vulpes · Answer

I'd place the relevant code in the SubmitOrder() method as you'll (presumably) need to decrement the quantity column when an order is placed. If the order is subsequently canceled then you can of course add back the stock to the quantity column. I'm not very familiar with EF as I don't use it but I'd have thought that you'd need to decrement the Quantity field in your OrderDetails entities and then call SaveChanges on the ObjectContext object to update the database itself.

S · Answer

Right ok thanks. Could you advise me on how to implement this in C# please (im using Entity Framework as my Data Model). Shall I place this code in a Stored Proceedure the SubmitOrder Method or a new Method entirely and if so id need input parameters etc. If I was to go the Stored Proceedure route then I should of included it when I generated my EF Model so I think its too late for that... Therefore if anything can be done in C# that would be my best bet. In Future ill know to include everything DB related in my EF! : ( Regards Steven

Vulpes · Answer

You'll need an UPDATE statement here. Something like the following: UPDATE Products SET UnitsInStock = UnitsInStock - OrderDetails.Quantity WHERE ProductID = OrderDetails.ProductID

S · Answer

Update:- So far ive managed to construct the following SELECT Products.UnitsInStock OrderDetails.Quantity OrderDetails.ProductID FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID ORDER BY Products.UnitsInStock ASC This is now returning the results I would expect to see as follows:- UnitsInStock Quantity ProductID 3000 30 8 What I would like to add to the Query is the functionality which deducts the Quantity from the UnitsInStock. Thanks Steven