VB6，ADO，异步命令并关闭连接 [英] VB6, ADO, Asynchronous command and closing the connection

问题描述

真正的问题是，我需要关闭ADO命令吗？我在很多地方都读过，只是简单地将其设置为什么都不做与关闭它具有相同的作用，但是我还没有找到确切的答案。

Quick question really, do I need to close an ADO command? I have read in several places that simply setting it to nothing does the same thing as closing it but I'm yet to find a definitive answer.

我有一个VB6例程在运行时创建一个连接和命令对象，该对象异步执行一个不返回任何结果的存储过程。

I have a VB6 routine that at runtime creates a connection and command object which execute asynchronously a stored procedure that doesn't return any results.

在此例程结束时，两个对象均未设置为空。下面的代码准确显示了执行的操作

At the end of this routine both objects are set to nothing. The below code shows exactly what's performed

 ' Open connection
con.Open 
'
' Create command to execute stored proc
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = cSQLDelete
cmd.Parameters.Append cmd.CreateParameter _
        ("@ExpiryDate=", adDate, adParamInput, 20, ExpiryDate)
'
' Run procedure, no results
cmd.Execute , , adExecuteNoRecords + adAsyncExecute
'
' Tidy up

Set cmd = Nothing
Set con = Nothing

请注意没有con.Close。如果将它插入到con = nothing之前，则存储过程将不会运行-我认为由于它是异步的，因此在关闭连接之前没有时间执行。

Note the absence of con.Close. If this is inserted before the con=nothing, the stored procedure does not run - I assume that since it's asynchronous it hasn't had time to execute before the connection is closed.

没有con.Close，就可以了。

Without the con.Close, this works fine.

因此，我的问题是不关闭连接会有什么影响。连接会在后台简单地超时吗？请记住，此函数称为A LOT，到目前为止测试还没有显示任何问题。

So, my question is what implications are there for not closing the connection. Will the connection simply timeout in the background?? Bear in mind that this function is called A LOT and so far testing hasn't shown any problems.

如果在存储过程完成后需要关闭连接，如何我会这样做吗？我有C＃背景，不熟悉VB6，发现在运行时创建带有事件的对象是一个尴尬的过程。

If I need to close the connection when the stored procedure finishes, how would I do this? Having a C# background, I'm unfamiliar with VB6 and find creating objects with events at runtime an awkward process.

p.s Sql Server 2008

p.s. Sql Server 2008

谢谢

推荐答案

此代码的作用是执行如果连接不繁忙（状态=打开），则使用该命令。否则，将其放在堆栈上并在当前命令完成时执行它。我对VB阵列的了解有限，因此感到有点难看-可能有更好的方法来创建FIFO队列？

What this code does is execute the command if the connection is not busy (State=Open). Otherwise put it on a stack and execute it when the current command completes. My knowledge of VB arrays is limited so sorry its a bit ugly - there may be a better way to do a FIFO queue?

Dim WithEvents mobjAdoConn As ADODB.Connection
Dim CommandArray() As String

Private Sub Command1_Click()
Dim cmdNo As Integer
    If mobjAdoConn.State <> adStateOpen Then
       cmdNo = UBound(CommandArray)
       ReDim Preserve CommandArray(cmdNo + 1)
       CommandArray(cmdNo) = "WAITFOR DELAY '000:00:10'"
       Label2.Caption = cmdNo
    Else
        mobjAdoConn.Execute "WAITFOR DELAY '000:00:10'", , adExecuteNoRecords + adAsyncExecute
    End If
End Sub

Private Sub Form_Load()
On Error GoTo Err
    Set mobjAdoConn = New ADODB.Connection

    mobjAdoConn.ConnectionTimeout = 30
    mobjAdoConn.ConnectionString = "Provider..."

    mobjAdoConn.Open

    ReDim CommandArray(1)
    Exit Sub
Err:
    MsgBox Err.Description, vbOKOnly

End Sub

Private Sub mobjAdoConn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Dim cmd As String
Dim i As Integer
Dim cmds As Integer
    cmds = UBound(CommandArray)
    If cmds > 1 Then
        cmd = CommandArray(1)
        If cmds = 2 Then
            ReDim CommandArray(1)
            Label2.Caption = 0
        Else
            For i = 2 To cmds - 1
                CommandArray(i - 1) = CommandArray(i)
            Next i
            ReDim Preserve CommandArray(cmds - 1)
            Label2.Caption = cmds - 2
        End If
        mobjAdoConn.Execute cmd, , adExecuteNoRecords + adAsyncExecute
    End If
End Sub

这篇关于VB6，ADO，异步命令并关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！