How do i create a method or function in C# that can reduce the stock in the database as it is being-CodePudding

Am developing a C# windows desktop application in Visual Studio and am literally stack with regards to how i should phrase my code when it has been sold in my supermarket system so that it reduces the available stock in the database any assistance rendered will be much appreciated I tried with this Sql but it failed miserably cause i didnt even have the know the C# to add to it

    string query = "update ProductTable set Product_Quantity = (Product_Quantity-'{OrderProductQuantity}') where Product_Name ='{productName}'";

CodePudding user response：

The answer? Is you don't write any code at all to reduce the stock.

A simple query will get you the current stock

WITH MYstock as
(SELECT StockID, 
  (SELECT SUM(StockSold) from Invoices WHERE Invoices.StockID = Stock.StockDI) as ItemsSold, 
 (SELECT SUM(StockAdded) from tblStock WHERE TblStock.StockID) AS ItemsAdded)
 FROM tblSTock
)
SELECT * from MySTock, (ItemsAdded - ItemsSold) as InventoryLevel

So, as you can see, you don't have to write any code at all. You simple sum the items added, less the items sold, and you are done.

Now, above is SQL server syntax, and you could say use a MySQL view - I don't know how (or if) aliased columns can be re-used, but above gives you the basic idea here.

Really nice? Maybe you have to edit a sales transaction, or even delete a row - it will automatic update the inventory value - since you compute it on the fly as required.

As a result, your UI code becomes VERY easy to write. You never have to write ANY code to update the inventory amounts, since you are free to calculate the in stock value anytime you want based on above.