c# - MySql.Data.dll 中发生类型 'MySql.Data.MySqlClient.MySqlException' 的异常，但未在用户代码中处理

Question

我不断收到以下错误消息:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

每次运行代码时，

at da.Fill(dtCounts);。有谁知道原因吗？

出于某种原因，S/O 不允许我粘贴代码，因此我将其上传到此处:http://pastebin.com/LCnTBVY4

编辑:这是它:

public static void RefreshPlot()
{        
    string query = "SELECT Count(*) AS count, plot_id FROM booking WHERE postcode=@postcode AND " +
                   "status='open' GROUP BY plot_id";
    var cmd = new MySqlCommand(query, DbConnect.Connection);
    cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);

    var da = new MySqlDataAdapter(cmd);
    var dtCounts = new DataTable();
    da.Fill(dtCounts);

    if (dtCounts.Rows.Count > 0)
    {
        query = "UPDATE plot SET jobs = @jobCount WHERE plot_id = @plotID AND postcode=@postcode;";

        query += "UPDATE plot " +
                 "LEFT JOIN booking " +
                 "ON plot.plot_id = booking.plot_id " +
                 "SET plot.jobs = 0 " +
                 "WHERE plot.postcode=@postcode " +
                 "AND booking.plot_id IS NULL;";

        query += "update plot p " +
                 "inner join " +
                 "(select sum(case when status = 'Open' then 1 else 0 end) cnt, plot_id " +
                 "from booking group by plot_id) p2 on p.plot_id = p2.plot_id " +
                 "set p.jobs = p2.cnt;";

        cmd = new MySqlCommand(query, DbConnect.Connection);
        foreach (DataRow row in dtCounts.Rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);
            cmd.Parameters.AddWithValue("@jobCount", int.Parse(row["count"].ToString()));
            cmd.Parameters.AddWithValue("@plotID", int.Parse(row["plot_id"].ToString()));
            cmd.ExecuteNonQuery();               
        }
    }
    else if ((dtCounts.Rows.Count == 0))
    {
        query = "UPDATE plot SET jobs=0 WHERE postcode=@postcode;";
        cmd = new MySqlCommand(query, DbConnect.Connection);
        cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);
        cmd.ExecuteNonQuery();
    }
}

Answer 1

尝试在每个新的 MySqlCommand 和 Adapter 上使用 using 语句来处理资源。

例如:

var dtCounts = new DataTable();
using (var cmd = new MySqlCommand(query, DbConnect.Connection))
{
     cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);

     using (var da = new MySqlDataAdapter(cmd))
     {
          da.Fill(dtCounts);
     }
}

您的连接中有一个打开的数据读取器，但未关闭。

也许您在其他地方有一些使用相同连接的代码。在任何地方的命令上使用 using 语句，那么它们应该被关闭。

为了安全起见，您可以创建一个新连接。