读取器读取Do循环时的SQL更新 [英] SQL Update While Reader is Reading Do loop

问题描述

Try
    Connect()
    SQLQuery = "SELECT duedate FROM tblDue WHERE duedate = '"&date.today.date&"'"
    cmd = New SqlCommand(SQLQuery, conn)
    rdr = cmd.ExecuteReader

    Do While rdr.Read
        'UPDATE Command Here...
    Loop

    conn.Dispose()
    conn.Close()
    cmd = Nothing
    rdr.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

推荐答案

您的问题不清楚，但我会尽力填补空白:

SQL阅读器是数据库的快照，而不是当前状态的反映.您在循环中进行的任何更新都不会影响阅读器返回的数据.但是，您所做的任何更新都会受到尊重，并且会影响数据库数据-如果您使用其他SqlConnection-如果尝试使用相同的SQLConnection，则SQL引擎将抛出一个异常，即先打开读取器且必须先关闭霉菌.
例如:

It''s not obvious what your question is, but I''ll try to fill in the gaps:

An SQL reader is a snapshot of the database, not a reflection of the current status. Any update you do within the loop will have no affect on the data returned by the reader. However, any updates you do will be honored and will affect database data - provided that you use a different SqlConnection - if you try to use the same one, the SQL engine will throw an exception that there is a reader open that musty be closed first.
For example:

private void myButton_Click(object sender, EventArgs e)
    {
    using (SqlConnection con = new SqlConnection(DALFactory.DBConnection))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Tracks", con))
            {
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                {
                int before = (int)reader["PlayCount"];
                DoUpdate();
                int after = (int)reader["PlayCount"];
                Console.WriteLine("{0} : {1}", before, after);
                }
            }
        }

    }
private void DoUpdate()
    {
    using (SqlConnection con = new SqlConnection(DALFactory.DBConnection))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("UPDATE Tracks SET PlayCount = PlayCount+1", con))
            {
            cmd.ExecuteNonQuery();
            }
        }
    }

将首次为每条记录显示"0:0"，然后在下次运行时增加记录数.

如果您考虑一下，那正是您想要发生的事情:您的读取是快照，因此它反映了命令执行的时间.但是，当您处理记录时，其他用户可以访问数据库并进行所需的更改.使用第二个SqlConnection可以模仿.

Will display "0 : 0" for each record the first time, and then increase by the number of records the next time you run it.

If you think about it, that is exactly what you want to happen: your read is a snapshot, so it reflects a moment in time when your command is executed. However, while you are processing the records, other users can access the database and make what changes they need to. Using a second SqlConnection imitates that.

这篇关于读取器读取Do循环时的SQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！