sql - VBA for Excel 2016 中的 ADODB 连接超时 - 如何检查连接是否仍处于事件状态？

Question

我使用 VBA 开发了一个小型 Excel 插件，它直接连接到数据库。我通过 DSN 设置连接。打开插件并直接使用它时，它的工作效果非常好。然而，一段时间后，与数据库的连接似乎超时。更准确地说，完全有效的查询在尝试打开记录集时会返回错误。

我的代码是这样的:

'Check Connection
If Not MakeConnectionToDB Then
    'Connection failed
[do something]
        Exit Function
    End If
    'Active connection - run the query!
            If Rs Is Nothing Then Set Rs = New ADODB.Recordset  'make sure its an active object
            If Not Rs.State = adStateClosed Then Rs.Close       'make sure its not full (throws error if a query was called before)
            Rs.Open strSQLQuery, CON                            'Run query

如果应用程序已打开但有一段时间未使用，则 rs.open 语句将失败。尽管 MakeConnectionToDB UDF 看起来像这样:

If Not ConIsActive Then 'If there is no active connection, make it so
            If CON Is Nothing Then  'Connection may be inactive because the object dropped, or because it timed out, or any other reason - Only recreate the object if the former is the case
                Set CON = New ADODB.Connection
            End If
            On Error Resume Next
            CON.Open strCon         'Try to connect - on error resume statement in order to ignore a connection error, that will be caught below
            On Error GoTo 0
            Err.Clear
            MakeConnectionToDB = ConIsActive    'This is where a connection error will be caught if it occurred
        Else
            MakeConnectionToDB = True 'connection is active already
        End If

ConIsActive 看起来像:

Private Function ConIsActive() As Boolean
    'return TRUE if there is an active connection, false otherwise
    Dim blnTemp As Boolean
    blnTemp = False
    If (Not (CON Is Nothing)) And (Not (CON = "")) Then If CON.State = adStateOpen Then blnTemp = True
    ConIsActive = blnTemp
End Function

基本上，我检查连接是否打开。我的问题:所有这些检查都返回 TRUE，但连接根本没有打开。如果我连接，然后离开应用程序一段时间，然后返回它，以上所有内容都会返回连接处于事件状态，但是当尝试使用新查询打开记录集时，它将失败，大概是因为服务器关闭了连接什么的。我需要找到一种方法来检查连接是否确实能够打开记录集。

我可以 ping 服务器之类的吗？如何检查数据库是否确实向我的查询返回结果？有没有一种方法比仅向服务器发送测试查询并结合记录集上的错误处理具有更高的性能？我认为这可行，但我需要一个高性能解决方案，并且我不认为将简单连接检查的查询数量加倍是一个更好的解决方案...

感谢任何帮助!

Answer 1

您的 CON 对象似乎是全局范围的，打开一次，然后在代码中的任何地方使用，并且可能在某个时候关闭......或不关闭。

就像用任何支持对象的语言编写的任何代码库中的每个对象一样，数据库连接应该尽可能短暂。

你打开它，用它做你需要做的事情，然后关闭它。如果您不知道下一个命令将对其执行什么以及何时执行，那么该连接就没有任何业务保持打开状态。

删除您的全局范围CON。用火杀死它。连接应该是使用它的函数或过程的本地连接 - 它在该范围内开始，并在该范围内结束。

或者您可以将其封装在您自己的对象中，如果这对您来说更容易的话。

'@Folder("Data.SqlConnection")
Option Explicit
Private Const CONNECTION_STRING As String = "{CONNECTION STRING}"
Private mConnection As ADODB.Connection

Private Sub Class_Initialize()
    Set mConnection = New ADODB.Connection
    mConnection.Open
End Sub

Private Sub Class_Terminate()
    mConnection.Close
    Set mConnection = Nothing
End Sub

Public Sub ExecuteNonQuery(ByVal sql As String, ParamArray params())
    With New ADODB.Command
        Set .ActiveConnection = mConnection
        Dim p As ADODB.Parameter
        For Each p In params
            .Paramaters.Append p
        Next
        .Execute
    End With
End Sub

'...

该 SqlConnection 类的实例也应该尽可能短暂，但现在大部分管道都被抽象掉了，因此您的调用代码可以如下所示:

 Const sql As String = "exec dbo.LogUserIn @userName=?, @password=?;"
 With New SqlConnection

     Dim userName As ADODB.Parameter
     Set userName = .CreateStringParameter(Environ$("USERNAME"))

     Dim password As ADODB.Parameter
     Set password = .CreateStringParameter(PromptForPassword)

     .ExecuteNonQuery sql, userName, password

 End With

连接从New SqlConnection开始，以End With结束，您可以根据需要调整SqlClass以支持事务和/或如上所示，抽象出参数创建样板。

但想法仍然存在:您不会创建数据库连接并将其保留在全局范围内，不知道某处的某些代码是否可能将其设置为Nothing，或关闭它，或启动它一笔从未提交过的交易，或者天知道是什么。

1. 创建
2. 打开
3. 执行
4. 关闭

永远。范围尽可能严格。那么你就不会有任何对象生命周期问题。