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

Question

MySqlCommand Sql1 = new MySqlCommand("SELECT * FROM animal WHERE idAnimal ='" + label1.Text + "'", Connection);

MySqlDataReader dr1;
dr1 = Sql1.ExecuteReader();

while (dr1.Read())
{
    String idAnimal = dr1["idAnimal"].ToString();

    MySqlCommand Sql2 = new MySqlCommand("SELECT * FROM town WHERE id ='" + idAnimal + "'", Connectio);
    MySqlDataReader dr2;
    dr2 = Sql2.ExecuteReader();

    while (dr2.Read())
    {
        dataGridView1.Rows.Add(dr2["number"], dr2["name"]);
    }

    dr2.Close();
}

dr1.Close();
Connection.Close();

Answer 1

解决这个问题的最佳方法是使用JOIN(并修复这个巨大的sql注入(inject)漏洞):

string sql = "SELECT t.number, t.name FROM animal a INNER JOIN town t ON t.ID = a.idAnimal WHERE a.idAnimal= @idAnimal";

using (var cn = new MySqlConnection("connection string here"))
using (var cmd = new MySqlCommand(sql, cn))
{
    cmd.Parameters.Add("@idAnimal", MySqlDbType.Int32).Value = int.Parse(label1.Text);

    cn.Open();
    using (var dr = cmd.ExecuteReader())
    {
        while(dr.Read())
        {
            dataGridView1.Rows.Add(dr["number"], dr["name"]);
        }
        dr.Close();
    }
}

此外，您可能应该研究数据绑定(bind)以将这些结果连接到网格，而不是手动添加行。这会让你编写这样的代码:

string sql = "SELECT t.number, t.name FROM animal a INNER JOIN town t ON t.ID = a.idAnimal WHERE a.idAnimal= @idAnimal";
using (var cn = new MySqlConnection("connection string here"))
using (var cmd = new MySqlCommand(sql, cn))
{
    cmd.Parameters.Add("@idAnimal", MySqlDbType.Int32).Value = int.Parse(label1.Text);
    cn.Open();
    using (var dr = cmd.ExecuteReader())
    {
        dataGridView1.DataSource = dr;
        dr.Close();
    }
}

但是，如果您确实想知道如何让两个 DataReader 一起事件，可以通过使用两个连接对象来实现:

using (var cn1 = new MySqlConnection("connection string here"))
using (var sql1 = new MySqlCommand("SELECT * FROM animal WHERE idAnimal = @idAnimal", cn1))
{
    sql1.Parameters.Add("@idAnimal", MySqlDbType.Int32).Value = int.Parse(label1.Text);
    cn1.Open();
    using (var dr1 = sql1.ExecuteReader())
    {    
        while (dr1.Read())
        {
            String idAnimal = dr1["idAnimal"].ToString();

            using (var cn2 = new MySqlConnection("connection string here"))
            using (var sql2 = new MySqlCommand("SELECT * FROM town WHERE id = @idAnimal", cn2))
            {
                cn2.Parameters.Add("@idAnimal", MySqlDbType.Int32).Value = int.Parse(idAnimal);
                cn2.Open();
                using(var dr2 = sql2.ExecuteReader())
                {
                    while (dr2.Read())
                    {
                        dataGridView1.Rows.Add(dr2["number"], dr2["name"]);
                    }
                    dr2.Close();
                }
            }
        }
        dr1.Close();
    }
}

但请注意，这比 JOIN + DataBinding 选项的代码多出两倍多。

另请注意，在 ADO.Net 提供程序中保留一个数据库连接以便在应用程序中重复使用是一种糟糕的做法。正如我们在此处看到的，除了限制您同时使用多个数据库查询的能力之外，ADO.Net 还使用称为“连接池”的功能，并且重复使用相同的连接对象会干扰这一点。在大多数情况下，最好创建一个新的连接对象，然后简单地重新使用连接字符串。