为什么我得到“连接已经打开"?MySQL更新语句期间出错? [英] Why do I get a "connection is already open" error during a MySQL update statement?

问题描述

我正在使用 Winforms、MySQL 和 C# 进行大学出勤项目.

I am doing a college attendance project with Winforms, MySQL, and C#.

因为我想在一张表中更新Total Absent"和Total Present".所以我为此编写了代码:

In that I want to update "Total Absent" and "Total Present" in one table. So I wrote code for that:

int p = 0;
int a = 0;
string sno = "";

MySqlDataReader Reader;
command.CommandText = "select * from attendance_monthly_rpt";
connection.Open();
Reader = command.ExecuteReader();           

while (Reader.Read())
{
    p = 0;
    a = 0;
    sno = Reader[1].ToString();
    for (int k = 3; k <= 33; k++)
    {
        if ((Reader[k].ToString() == "P") || (Reader[k].ToString() =="OD"))
        {
            p += 1;
        }
        else if (Reader[k].ToString() == "Ab")
        {
            a += 1;
        }
    }
}
connection.Close();

现在我得到了sno"、a"、p"值.但我想更新 MySQL 表中的这条记录.所以我添加了一个 UPDATE 语句:

Now I get "sno", "a", "p" values for that. But I want to update this record in the MySQL table. So I added an UPDATE statement:

while (Reader.Read())
{
    p = 0;
    a = 0;
    sno = Reader[1].ToString();
    for (int k = 3; k <= 33; k++)
    {
        if ((Reader[k].ToString() == "P") || (Reader[k].ToString() =="OD"))
        {
            p += 1;
        }
        else if (Reader[k].ToString() == "Ab")
        {
            a += 1;
        }
    }           
    command.CommandText = "update attendance_monthly_rpt set tot_persent = " +
                          p + ", tot_absent = " + a + " where student_no = '" +
                          sno + "'";
    connection.Open();  // the connection is already open
    command.ExecuteNonQuery();
    connection.Close();
}
connection.Close()

这会引发错误连接已经打开".如果我将更新语句放在循环之后，我只会得到最后一条记录.

This throws the error "The connection is already open". If I place the update statement after the loop, I get the last record only.

请帮我更新每个学生"的Tot缺席"，Tot present".

Please help me to update my "Tot absent", "Tot present" for each "Student".

推荐答案

在删除大部分与打开或关闭连接无关的内容后查看您的代码:

Look at your code after you've removed most of what's not related to opening or closing the connection:

…
connection.Open();       // <--------------+
…                        //                |
                         //                |
while (…)                //                |
{                        //                |
    …                    //                |
    connection.Open();   // the connection is already open
    …
    connection.Close();  // <--------------+
}                        //                |
connection.Close()       // likewise, it'll already be closed here

现在很容易看出为什么在 while 循环的第一次运行期间连接已经打开了.

It's easy to see now why the connection is already open during the first run of the while loop.

建议:我建议你不要 .Open() 和 .Close() 连接对于每个命令.(连接确实不应该保持打开的时间超过必要的时间，但是您可能在那里夸大了它.:) 相反，只需在开始更新之前打开连接一次，然后在 while<之后关闭它/code> 循环.并且，就像 CodeBuzz 已经建议的那样，将 connection.Close() 放在 finally 块中，以确保即使出现问题也能关闭连接.

Apropos: I would suggest that you not .Open() and .Close() the connection for every single command. (Connections should indeed not be kept open any longer than is necessary, but you're probably exaggerating it a little there. :) Instead, just open the connection once before you start your updates, and close it after the while loop. And, like CodeBuzz suggested already, put the connection.Close() in a finally block to make sure the connection is closed even if something goes wrong.

PS: 不要忘记 .Dispose() 所有属于 IDisposable 的对象(即数据读取器和连接对象)，例如通过 using 块.

P.S.: Don't forget to .Dispose() all objects that are IDisposable (ie. the data reader and connection objects), e.g. via a using block.

这篇关于为什么我得到“连接已经打开"?MySQL更新语句期间出错?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！