Quantity minus UnitsInStock

Guest User

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

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted 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.
Guest User14y ago
Absolute magic Vulpes it works a treat!

Many thanks indeed for all your help and patience : )

Regards
Steven
Guest User14y ago
Or even something like

Product p = new Product();
if (p.ProductID == od.ProductID)
{
p.UnitsInStock -= od.Quantity;
}

Thanks
Steven
Guest User14y ago
Vulpes,

How does this look to you?

Product p = new Product();
p.UnitsInStock - od.Quantity;

Thanks

Steven
Guest User14y ago
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
Vulpes14y ago
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.
Guest User14y ago
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
Vulpes14y ago
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.
Guest User14y ago
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
Vulpes14y ago
You'll need an UPDATE statement here. Something like the following:

UPDATE Products SET UnitsInStock = UnitsInStock - OrderDetails.Quantity WHERE ProductID = OrderDetails.ProductID
Guest User14y ago
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