Mysql连接未与VB.NET应用程序关闭

Question

这是我第一次来这里，因为这个问题我要疯了：

我正在使用VB.NET开发一个windows窗体应用程序，使用多种类型的连接(mysql、odbc和SQL server(，在我进入mysql之前，一切都很好。。MySQL服务器是一台物理的Windows7 pc，我通过IPSEC VPN隧道连接到它。

我需要每x秒执行2次MySQL连接，如果我在第一次连接后得到某种类型的结果，那么我会每x秒打开第二次，以此类推(这都写在我的timer.tick事件处理程序中(。问题是MySQL服务器上的一些连接经常在MySQL服务器上保持活动(ESTABLISED(，我不知道为什么。。。代码看起来很好，同时声明了open和close方法，我也尝试过Dispose、ClearPool和ClearAllPools方法，但我一直保持这些连接，直到我关闭程序或达到连接限制。

这是代码：

类连接：

Public Sub connMySQL()
Dim connstring As String
Try
If stabilimento = "1PR" Then
If cesoia = "" Then
connstring = "server=192.168.123.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
Else
connstring = "server=192.168.123.253;userid=xx;password=xx;database=xx;Connect Timeout=30"
End If
End If
If stabilimento = "2PR" Then
If cesoia = "" Then
connstring = "server=192.168.1.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
Else
connstring = "server=192.168.123.253;userid=root;password=xx;database=xx;Connect Timeout=30"
End If
End If

conMySql = New MySqlConnection(connstring)
If conMySql.State = ConnectionState.Closed Then
conMySql.Open()

End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

执行迭代的类：

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
connMySQL()
comm = New MySqlCommand("SELECT count_1,count_2,start_stop,data_ora,id FROM plc_contatori where plc_nome='" + plc_nome + "' and data_ora > '" + data_ieri.ToString("yyyy/MM/dd") + "' order by data_ora desc limit 1", conMySql)
dr = comm.ExecuteReader()
While (dr.Read())
count_1(0) = dr.GetValue(0)
start_stop(0) = dr.GetValue(2)
data_ora(0) = dr.GetValue(3)
If id <> dr.GetValue(4) And count_2(0) <> dr.GetValue(1) Then
id = dr.GetValue(4)
count_2(0) = dr.GetValue(1)
Else
Exit Sub
End If
End While
dr.Close()
dr.Dispose()
conMySql.Close()
conMySql.Dispose()
conMySql.ClearPool(conMySql)
conMySql.ClearAllPools()
If Not conMySql Is Nothing Then conMySql = Nothing
comm.Dispose()

If start_stop(0) = 1 Then
Exit Sub
End If

Dim dum_count_2 As Integer = count_2(0) - 1       
connMySQL()
comm = New MySqlCommand("select count_1,count_2,start_stop,data_ora from plc_contatori where  plc_nome='" + plc_nome + "' and data_ora > '" + data_ieri.ToString("yyyy/MM/dd") + "'  AND count_2=" + dum_count_2.ToString + " ORDER BY data_ora desc limit 1", conMySql)
dr = comm.ExecuteReader()
While (dr.Read())
count_1(1) = dr.GetValue(0)
count_2(1) = dr.GetValue(1)
start_stop(1) = dr.GetValue(2)
data_ora(1) = dr.GetValue(3)
End While
dr.Close()
dr.Dispose()
conMySql.Close()
conMySql.Dispose()
conMySql.ClearPool(conMySql)
conMySql.ClearAllPools()
If Not conMySql Is Nothing Then conMySql = Nothing
comm.Dispose()

If count_1(0) = count_1(1) And start_stop(1) <> 1 And count_2(0) <> count_2(1) Then
'sub that reads some values from an odbc connection
CheckFermo()
End If
End Sub

注意，我在这部分代码中没有声明的变量是在表单的公共类中声明的。

我想知道可能出了什么问题。。。也许第二个连接是在第一个连接被服务器关闭之前建立的？

Answer 1

我将connMySQL方法更改为返回连接字符串的函数。我已经声明了几个变量，所以代码是有意义的。我对数据类型做了一些假设。如果这些值实际存储为字符串，则可能需要将其改回String和VarChar。(我希望他们不是(

您可以使用单个连接，但所有分配和比较都将在打开的连接中进行。

Private stabilimento As String
Private cesoia As String
Public Function connMySQL() As String
Dim connstring As String
Select Case True
Case stabilimento = "1PR" And cesoia = ""
connstring = "server=192.168.123.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
Case stabilimento = "2PR" And cesoia = ""
connstring = "server=192.168.1.18;userid=xx;password=xx;database=xx;Connect Timeout=30"
Case Else
connstring = "server=192.168.123.253;userid=root;password=xx;database=xx;Connect Timeout=30"
End Select
Return connstring
End Function
Private count_1(10) As Integer
Private count_2(10) As Integer
Private start_stop(10) As Integer
Private data_ora(10) As Date
Private id As Integer
Private plc_nome As String
Private data_ieri As Date
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Using dt As New DataTable
Using cn As New MySqlConnection(connMySQL),
comm = New MySqlCommand("SELECT count_1,count_2,start_stop,data_ora,id 
FROM plc_contatori 
where plc_nome= @plcNome and data_ora > @dataOra 
order by data_ora desc 
limit 1", cn)
comm.Parameters.Add("plcNome", MySqlDbType.VarChar).Value = plc_nome
comm.Parameters.Add("dataOra", MySqlDbType.Date).Value = data_ieri '.ToString("yyyy/MM/dd")
cn.Open()
Using dr = comm.ExecuteReader
dt.Load(dr)
End Using 'closes and disposes reader
End Using 'closes and dispose connection and command
count_1(0) = CInt(dt(0)(0))
start_stop(0) = CInt(dt(0)(2))
data_ora(0) = CDate(dt(0)(3))
If id <> CInt(dt(0)(4)) AndAlso count_2(0) <> CInt(dt(0)(1)) Then
id = CInt(dt(0)(4))
count_2(0) = CInt(dt(0)(1))
Else
Exit Sub
End If
End Using 'disposes DataTable
If start_stop(0) = 1 Then
Exit Sub
End If

Dim dum_count_2 As Integer = count_2(0) - 1
Using dt As New DataTable
Using cn As New MySqlConnection(connMySQL),
comm As New MySqlCommand("select count_1,count_2,start_stop,data_ora 
from plc_contatori 
where  plc_nome= @plcNome 
and data_ora > @dataOra 
AND count_2= @count2 
ORDER BY data_ora desc 
limit 1", cn)
comm.Parameters.Add("@plcNome", MySqlDbType.VarChar).Value = plc_nome
comm.Parameters.Add("@dataOra", MySqlDbType.Date).Value = data_ieri '.ToString("yyyy/MM/dd")
comm.Parameters.Add("@count2", MySqlDbType.Int32).Value = dum_count_2 '.ToString
cn.Open()
Using dr = comm.ExecuteReader()
dt.Load(dr)
End Using
End Using
count_1(1) = CInt(dt(0)(0))
count_2(1) = CInt(dt(0)(1))
start_stop(1) = CInt(dt(0)(2))
data_ora(1) = CDate(dt(0)(3))
End Using
If count_1(0) = count_1(1) AndAlso start_stop(1) <> 1 AndAlso count_2(0) <> count_2(1) Then
'sub that reads some values from an odbc connection
CheckFermo()
End If
End Sub