与数据库的连接未在 VB.NET 中关闭

Connection to database not closing in VB.NET

提问by Bottopia

I have the function below. Sometimes after using it I need to delete the db and re-create it but this function keeps using the database (so it cannot be deleted) even though I ask it to drop the database and close all connections. Any help would greatly be appreciated.

我有下面的功能。有时在使用它后我需要删除数据库并重新创建它，但即使我要求它删除数据库并关闭所有连接，该函数仍会继续使用数据库（因此无法删除）。任何帮助将不胜感激。

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim oledbCnn As OleDbConnection
    Dim oledbCmd As OleDbCommand
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    oledbCnn = New OleDbConnection(connetionString)
    Try
        oledbCnn.Open()
        oledbCmd = New OleDbCommand(sql, oledbCnn)
        Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

        If oledbReader.Read Then
            'MsgBox("Found")
            Return True
        Else
            'MsgBox("Not found")
            Return False
        End If
        'While oledbReader.Read
        ' MsgBox(oledbReader.Item(0))
        'End While
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        oledbCmd = New OleDbCommand(sql)
        oledbCmd.ExecuteNonQuery()

        oledbCmd.Cancel()
        oledbReader.Close()
        oledbCmd.Connection.Close()
        oledbCnn.Close()
        oledbCmd.Dispose()
        oledbCnn.Dispose()


    Catch ex As Exception

    End Try


End Function

回答by Satal

You should have a finally section to your try catch and close the connection within there. Otherwise if an exception occurs the connection doesn't get closed.

你应该有一个 finally 部分到你的 try catch 并关闭那里的连接。否则，如果发生异常，连接不会关闭。

If you have a look at this answer (https://stackoverflow.com/a/8557227/465404) it will show you how.

如果您查看此答案（https://stackoverflow.com/a/8557227/465404），它将向您展示如何操作。

So you might want to have it similar to this...

所以你可能想要它类似于这个......

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim oledbCnn As OleDbConnection
    Dim oledbCmd As OleDbCommand
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    oledbCnn = New OleDbConnection(connetionString)
    Try
        oledbCnn.Open()
        oledbCmd = New OleDbCommand(sql, oledbCnn)
        Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

        If oledbReader.Read Then
            'MsgBox("Found")
            Return True
        Else
            'MsgBox("Not found")
            Return False
        End If
        'While oledbReader.Read
        ' MsgBox(oledbReader.Item(0))
        'End While
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        oledbCmd = New OleDbCommand(sql)
        oledbCmd.ExecuteNonQuery()

        oledbCmd.Cancel()
        oledbReader.Close()
        oledbCmd.Connection.Close()
        oledbCnn.Close()
        oledbCmd.Dispose()
        oledbCnn.Dispose()


    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
         If oledbCnn.State = ConnectionState.Open Then oledbCnn.Close()
    End Try


End Function

回答by Tobberoth

Remove the try catch. You're probably getting an exception, then ignoring it, which means the connection never got to close and you never found out. Alternatively, you can add a finally clause where you close the connection, since it will run no matter what.

删除尝试捕获。您可能会收到一个异常，然后忽略它，这意味着连接永远不会关闭，您也永远不会发现。或者，您可以在关闭连接的位置添加 finally 子句，因为无论如何它都会运行。

回答by user3094804

You are closing the connection twice:

您正在关闭连接两次：

oledbCmd.Cancel()
oledbReader.Close()
oledbCmd.Connection.Close()  '<--------
oledbCnn.Close()             '<--------
oledbCmd.Dispose() 
oledbCnn.Dispose()  

Maybe, it is generating an exception that prevents the connection Dispose call.

也许，它正在生成阻止连接 Dispose 调用的异常。

Try to comment de first connection close attempt (oledbCmd.Connection.Close())

尝试评论第一次连接关闭尝试（oledbCmd.Connection.Close()）

Regards

问候

回答by Markus

Your connection will not be closed in case of an error. You can wrap the relevant sections in a Using-block (basically adds a Try-Finally automatically) and by that also shorten your code.:

如果出现错误，您的连接不会关闭。您可以将相关部分包装在Using-block 中（基本上自动添加 Try-Finally），这样也可以缩短您的代码。：

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    Using oledbCnn = New OleDbConnection(connetionString)
        oledbCnn.Open()
        Using oledbCmd = New OleDbCommand(sql, oledbCnn)
            Using oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()
            If oledbReader.Read Then
                Return True
            Else
                Return False
            End If
        End Using
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        Using oledbCmd = New OleDbCommand(sql)
            oledbCmd.ExecuteNonQuery()
            oledbCmd.Cancel()
        End Using
    End Using
End Function