How to subtract order of stock? c# MySql-CodePudding

I'm working on a project where the customer can make an order of infinite products, I need my code to read every single product ID of his order.

For this to work, it needs to be in a loop, and I could only find ways to make this without a loop, with finite products on the internet.

I tried using SELECT like this:

string selectCod = "SELECT id FROM order";

MySqlCommand selectCodCmd = new MySqlCommand(selectCod, BDconnection);

reader = selectCodCmd.ExecuteReader();

while (reader.Read())
{
   
} 
reader.Close();

But I can't think of anything to put inside the while, I tried using x = reader[0].ToString(); but I only get the last product ID.

If anyone could help me I would appreciate it

Edit1:

Thanks a lot, It worked! Here's the code if anyone has the same problem in future:

string selectCod = "SELECT id FROM OrderItens";
    
var selectCodCmd = new MySqlCommand(selectCod, BDconnection);

reader = selectCodCmd.ExecuteReader();
                
var OrderItensId = new List<string>();

while (reader.Read())
{
     OrderItensId.Add(reader["id"].ToString());
}
reader.Close(); 

foreach(string Code in OrderItensId)
{

string CodeAmount = "SELECT amount FROM OrderItens
                     WHERE id = "  Code  "";

var CodeAmountCmd = new MySqlCommand(CodeAmount, BDconnection);

reader = CodeAmountCmd.ExecuteReader();

int OrderAmount = 0;
while (reader.Read())
{
     OrderAmount = Int32.Parse(reader[0].ToString()); 
}
reader.Close();


string UpdateStock = "UPDATE Stock 
                     SET amount = amount - " OrderAmount " 
                     WHERE id = "  Code  "";

 var UpdateStockCmd = new MySqlCommand(UpdateStock, BDconnection);

 reader = UpdateStockCmd.ExecuteReader();

 while (reader.Read()) { }
 reader.Close();


 }

CodePudding user response：

Well, as i understand, you need to get all the products ids from one specific order right?

With this, you only getting all the orders ids from your table orders

string selectCod = "SELECT id FROM order";

First, you will need the order id and i suppose that you have a table called orderItens or something like that..

Then, you get all the itens for that order with a select like this:

string selectCod = "SELECT id FROM orderItens where orderId = @orderId";

Then just create a loop like this

var orderItensIds = new List<int>();

while (reader.Read())
{
   orderItensIds.Add(int.Parse(reader["id"]));
} 
reader.Close();

return orderItensIds

There you have a list with all the itens id from your order

CodePudding user response：

You are using it right, you just need to put the code var x = reader[0].ToString(); into while loop to let it get all id in all rows.

For example, I will use a listId list to save Id list. You can use it to calculate or insert later.

string selectCod = "SELECT id FROM order";

MySqlCommand selectCodCmd = new MySqlCommand(selectCod, BDconnection);

reader = selectCodCmd.ExecuteReader();

var listId = new List<int>();
while (reader.Read())
{
   var x = reader[0].ToString();
   listId.Add(int.Parse(x));
} 
reader.Close();

// Use this list to any where you want
listId