C#结合MySQL实现订单库存扣减：如何循环读取全部商品ID

需求说明 #

开发支持客户下单任意数量商品的项目，需要读取订单包含的每一个商品ID，需通过循环实现适配任意商品数量的场景。

初始实现遇到的问题 #

最初编写的查询代码如下，不清楚while循环内的逻辑怎么写，尝试给单个变量赋值x = reader[0].ToString()只能拿到最后一个商品ID：

string selectCod = "SELECT id FROM order";

MySqlCommand selectCodCmd = new MySqlCommand(selectCod, BDconnection);

reader = selectCodCmd.ExecuteReader();

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

最终可运行方案 #

问题已解决，可直接参考如下代码实现：

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();
}

提示：上述代码直接拼接SQL的写法存在SQL注入风险，生产环境使用建议改为参数化查询优化。

内容的提问来源于stack exchange，提问作者LucOliSil