c# - 如何在不使用 DataReader 的情况下找到我的 product_id？

Question

我的变量名，数据库表都是对的，我得到这样的错误；

MySql.Data.MySqlClient.MySqlException: 'There is already an open DataReader associated with this Connection which must be closed first.'

public string urun_adi_bul(string urun_id)// find product name 
{
     if (genel.baglanti.State == ConnectionState.Closed)
     {
         genel.baglanti.Open();
     }
     string urun_adi = "";
     genel.sqlgonder.Connection = genel.baglanti;
     genel.sqlgonder.CommandText = "Select * from urunler where urun_id="+urun_id;
     MySqlDataReader oku = genel.sqlgonder.ExecuteReader();
     while (oku.Read())
     {
         urun_adi = oku["urun_id"].ToString();
     }
     oku.Close();
     return urun_adi;// product name
}

---

public void hesapGetir(ListView lvSiparis, string masa_id)
{
        genel.baglanti.Open();
        MySqlCommand sqlgonder = new MySqlCommand();
        sqlgonder.Connection = genel.baglanti;
        sqlgonder.CommandText = "Select * from adisyonlar where masa_id = "+masa_id;
        MySqlDataReader oku = sqlgonder.ExecuteReader();
        lvSiparis.Items.Clear();
        string urun_adi = "",urun_id="";
        while (oku.Read())
        {
            urun_id = oku["urun_id"].ToString();
            decimal fiyat = Convert.ToDecimal(urun_fiyati_bul(urun_id)); // price
            decimal adet = Convert.ToDecimal(oku["urun_adet"]); // piece
            decimal toplam = fiyat * adet; // total
            urun_adi = urun_adi_bul(urun_id);

            ListViewItem item = new ListViewItem(urun_adi);
            item.SubItems.Add(adet.ToString());
            item.SubItems.Add(toplam.ToString());
            lvSiparis.Items.Add(item);
        }
        oku.Close();
        genel.baglanti.Close();
}

如何在循环中使用 2 个数据读取器？

Answer 1

每个连接只能有一个打开的阅读器(或任何类型的查询)。但如果您将两个查询与 JOIN 结合起来，您实际上只需要一个阅读器:

SELECT adisyonlar.urun_id, urun_adet, urunler.urun_id
FROM adisyonlar
LEFT JOIN urunler
ON adisyonlar.urun_id = urunler.urun_id

这样您就可以让数据库完成查找匹配记录的工作，您只需要一个查询，而不是针对 adisyonlar 中的每一行一次又一次地询问数据库。

请注意，最好明确命名要获取的列，而不是使用 SELECT * 获取所有列。

另外:您的查询是开放给SQL injection !请使用参数化查询。