mysql - SQL 查询和线程

Question

我必须执行多个 SQL 查询并且我想为此使用线程，因为必须每 1 秒执行一次此查询以更新或获取数据库的一些不同值。

当我尝试执行我的代码时，出现以下错误:

WindowsApplication1.vshost.exe Error: 0 : There is already an open DataReader associated with this Connection which must be closed first.

我在 stackoverflow 上读到，连接应该始终尽可能晚地打开并尽快关闭。

这个问题有解决办法吗？

这是我的代码:

Imports MySql.Data.MySqlClient
Imports System.Threading

Public Class Form1

Private server As String = Nothing
Private pass As String = Nothing
Private user As String = Nothing
Private port As String = Nothing
Private db As String = Nothing

Dim Thread1 As Thread
Dim Thread2 As Thread

Dim con As New MySqlConnection
Dim cmd As New MySqlCommand
Dim reader As MySqlDataReader

Public Sub New()
    Me.server = "localhost"
    Me.user = "root"
    Me.pass = ""
    Me.port = "3306"
    Me.db = "diagnosedb"
    cmd.Connection = con
    con.ConnectionString = "Server = " & Me.server & ";
                            Port = " & Me.port & ";
                            Database = " & Me.db & ";
                            Uid = " & Me.user & ";
                            Pwd = " & Me.pass & ";"
    Thread1 = New Thread(AddressOf Querie1)
    Thread2 = New Thread(AddressOf Querie2)

    Thread1.Start()
    Thread2.Start()
End Sub


Private Sub Querie1()
    cmd.CommandText = "UPDATE teileliste
                       SET verschleis = 500
                       WHERE ID = 1;"
    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        con.Close()
    End Try
End Sub


Private Sub Querie2()
    cmd.CommandText = "UPDATE teileliste
                       SET verschleis = 0
                       WHERE ID = 20;"
    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        con.Close()
    End Try
End Sub

End Class

Answer 1

在 Querie1 和 Querie2 方法中创建并关闭一个新连接。您不能在一个连接上进行多个并发操作。 (即使可以，您也在两种方法中关闭连接。一种方法可能会在另一种方法使用它时尝试关闭它。)

.NET Framework 擅长管理连接。当您在代码中打开和关闭它们时，在幕后它实际上是在短时间内保持连接打开。这称为线程池。当您关闭连接时，它实际上暂时保持打开状态。当您打开一个新连接时，它实际上可能会为您提供相同的连接。

大多数时候我们不需要关注它。我们所做的就是创建一个尽可能接近我们打开和使用它的新连接，然后尽快关闭(处置)它。