VB.NET 异步运行多个 SQL 命令

问：

我在 SQL Server 数据库中有很多存储过程，需要按顺序执行。

我将它们设置在一个表中，该表具有多个序列，每个序列最多包含 5 个存储过程 - 因此基本上是 5 个线程：

我想遍历每个序列并同时启动每个序列的存储过程。然后，当所有存储过程都完成时，可以启动下一个存储过程序列。

我将使用 .NET 中的 SQL 命令来启动每个存储过程，但我需要一种方法让它们同时启动，并让该过程等待所有命令完成，然后再继续下一个序列。

这样做的目的是为了节省处理数据的时间。如果每个存储过程运行 2 分钟，则一次运行 5 个存储过程将需要 2 分钟，而不是 10 分钟。

现在我被告知TPL将是一个很好的方法。这是我第一次使用多线程/ TPL。

下面是一个类，其中包含用于执行 SQL Server 存储过程的属性和函数：

Public Class clsSQLStoredProcedure

Private mConnection As SqlClient.SqlConnection
Private mCommand As SqlClient.SqlCommand
Private mSQLStoredProcedure As String
Private mName As String
Private mStatus As String = "Pending"
Private mFeedback As DataTable
Public Property Connection() As SqlClient.SqlConnection
    Set(ByVal o As SqlClient.SqlConnection)
        mConnection = o
    End Set
    Get
        Return mConnection
    End Get
End Property

Public Property SQLStoredProcedure() As String
    Set(ByVal o As String)
        mSQLStoredProcedure = o
    End Set
    Get
        Return mSQLStoredProcedure
    End Get
End Property

Public Property Name() As String
    Set(ByVal o As String)
        mName = o
    End Set
    Get
        Return mName
    End Get
End Property

Public ReadOnly Property Status() As String
    Get
        Return mStatus
    End Get
End Property

Public ReadOnly Property Feedback() As DataTable
    Get
        Return mFeedback
    End Get
End Property

Public Function ExecuteSQLStoredProcedure()

    On Error GoTo Errorhandler
    Debug.Print(mName & " Start @ " & Now().ToString)
    mStatus = "Running"
    If mConnection.State <> ConnectionState.Open Then mConnection.Open()
    mCommand = New SqlClient.SqlCommand(mSQLStoredProcedure, mConnection)
    mCommand.CommandTimeout = 0
    mCommand.ExecuteNonQueryAsync()
    mStatus = "Completed"
    Debug.Print(mName & " Completed @ " & Now().ToString)
    Exit Function

Errorhandler:
    mStatus = "Failed"
    Debug.Print(mName & " Failed @ " & Now().ToString & " - " & Err.Description)

End Function

End Class

这是我用来执行任务 （TPL） 以同时启动 3 个存储过程的代码：

 Imports System.Threading
 Imports System.Threading.Tasks

 Module modThreading

Public Sub TestThreading()

    Dim oSP1 As New clsSQLStoredProcedure
    oSP1.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLFactCollectorActivity -99"
    oSP1.Connection = gGDM.Database.Connection
    oSP1.Name = "usp_ETLFactCollectorActivity"
    'oSP1.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP1.ExecuteSQLStoredProcedure())

    Dim oSP2 As New clsSQLStoredProcedure
    oSP2.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLDimPerson -99"
    oSP2.Connection = gGDM.Database.Connection
    oSP2.Name = "usp_ETLDimPerson"
    'oSP2.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP2.ExecuteSQLStoredProcedure())

    Dim oSP3 As New clsSQLStoredProcedure
    oSP3.SQLStoredProcedure = "SELECT 1"
    oSP3.Connection = gGDM.Database.Connection
    oSP3.Name = "TEST"
    'oSP3.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP3.ExecuteSQLStoredProcedure())


    MsgBox("Done")

End Sub

End Module

然而，它们似乎仍然一个接一个地运行，尽管有些应该立即运行，而另一些则需要大约 1 分钟。