如何通过VB.Net在Azure SQL Database执行异步长时存储过程？

当前代码的问题 #

你当前的代码调用Cmd.BeginExecuteNonQuery()后，立刻关闭了数据库连接并释放了命令对象。由于异步操作依赖活跃的数据库连接，连接提前关闭会直接终止SQL Server上的存储过程执行，这就是操作没实际运行的原因。

方案一：修正异步回调逻辑 #

使用BeginExecuteNonQuery的回调函数，确保异步操作完成后再释放资源，同时将执行错误记录到日志表：

Protected Sub Button_Click(sender As Object, e As System.EventArgs) Handles Button.Click
    txtUsrID.Value = Session.Item("s_lngUsrID")
    txtUsrTypIDfk.Value = Session.Item("s_lngUsrTypIDfk")

    Dim Cnn As New SqlConnection("TheString")
    Dim Cmd As New SqlCommand("sptblTemp_GndrRprtPrGrp_Insrt", Cnn)
    Cmd.CommandType = Data.CommandType.StoredProcedure
    Cmd.Parameters.Add("@UsrIDfk", Data.SqlDbType.Int).Value = txtUsrID.Value
    Cmd.CommandTimeout = 0

    Try
        Cnn.Open()
        Cmd.Connection = Cnn
        ' 传入回调函数和命令对象作为异步状态
        Cmd.BeginExecuteNonQuery(AddressOf AsyncProcCallback, Cmd)
        txtMssgs.Value = "任务已提交，稍后查看相关表确认执行状态"
    Catch Excptn As Exception
        txtMssgs.Value = $"提交失败：{Excptn.Message}"
        ' 提交失败时立即释放资源
        Cmd.Dispose()
        Cnn.Dispose()
    End Try
End Sub

Private Sub AsyncProcCallback(result As IAsyncResult)
    Dim cmd As SqlCommand = DirectCast(result.AsyncState, SqlCommand)
    Dim conn As SqlConnection = cmd.Connection
    Try
        ' 完成异步操作
        cmd.EndExecuteNonQuery(result)
    Catch ex As Exception
        ' 将错误写入日志表，方便后续排查
        Using logConn As New SqlConnection("TheString")
            logConn.Open()
            Using logCmd As New SqlCommand("INSERT INTO OperationErrorLog (UsrID, ErrorMessage, CreateTime) VALUES (@usrId, @msg, GETDATE())", logConn)
                logCmd.Parameters.AddWithValue("@usrId", cmd.Parameters("@UsrIDfk").Value)
                logCmd.Parameters.AddWithValue("@msg", ex.Message)
                logCmd.ExecuteNonQuery()
            End Using
        End Using
    Finally
        ' 操作完成后统一释放资源
        If conn.State = Data.ConnectionState.Open Then
            conn.Close()
        End If
        cmd.Dispose()
        conn.Dispose()
    End Try
End Sub

方案二：使用Fire-and-Forget异步任务（Web Forms 4.5+） #

将存储过程执行逻辑放到后台线程，页面立即返回反馈，同时记录执行异常：

Protected Async Sub Button_Click(sender As Object, e As System.EventArgs) Handles Button.Click
    txtUsrID.Value = Session.Item("s_lngUsrID")
    txtUsrTypIDfk.Value = Session.Item("s_lngUsrTypIDfk")
    Dim usrId As Integer = Integer.Parse(txtUsrID.Value)

    Try
        ' 将执行逻辑放到后台线程，不等待完成
        _ = Task.Run(Async Sub()
            Using Cnn As New SqlConnection("TheString")
                Using Cmd As New SqlCommand("sptblTemp_GndrRprtPrGrp_Insrt", Cnn)
                    Cmd.CommandType = Data.CommandType.StoredProcedure
                    Cmd.Parameters.Add("@UsrIDfk", Data.SqlDbType.Int).Value = usrId
                    Cmd.CommandTimeout = 0

                    Await Cnn.OpenAsync()
                    Await Cmd.ExecuteNonQueryAsync()
                End Using
            End Using
        End Sub).ContinueWith(Sub(task)
            If task.Exception IsNot Nothing Then
                ' 记录异常到日志表
                Using logConn As New SqlConnection("TheString")
                    logConn.Open()
                    Using logCmd As New SqlCommand("INSERT INTO OperationErrorLog (UsrID, ErrorMessage, CreateTime) VALUES (@usrId, @msg, GETDATE())", logConn)
                        logCmd.Parameters.AddWithValue("@usrId", usrId)
                        logCmd.Parameters.AddWithValue("@msg", task.Exception.InnerException.Message)
                        logCmd.ExecuteNonQuery()
                    End Using
                End Using
            End If
        End Sub)

        txtMssgs.Value = "任务已提交，稍后查看相关表确认执行状态"
    Catch Excptn As Exception
        txtMssgs.Value = $"提交失败：{Excptn.Message}"
    End Try
End Sub

注意：这种方式依赖ASP.Net应用域的稳定性，如果应用池回收，未完成的后台任务会被终止，适合对任务可靠性要求不极高的场景。

方案三：使用Azure队列+后台服务（高可靠性） #

这是最可靠的方案，完全隔离网站请求和后台任务，避免应用池回收影响执行：

1. 网站将任务参数（如UsrID）写入Azure Queue Storage，页面立即返回反馈。
2. 创建Azure Function或WebJob监听队列消息，取出后执行存储过程，并将执行结果写入状态表。

网站端写入队列的示例代码： #

Protected Sub Button_Click(sender As Object, e As System.EventArgs) Handles Button.Click
    txtUsrID.Value = Session.Item("s_lngUsrID")
    Dim usrId As Integer = Integer.Parse(txtUsrID.Value)

    Try
        ' 连接Azure队列
        Dim storageAccount = CloudStorageAccount.Parse("你的Azure存储连接字符串")
        Dim queueClient = storageAccount.CreateCloudQueueClient()
        Dim queue = queueClient.GetQueueReference("proc-exec-queue")
        queue.CreateIfNotExists()

        ' 序列化任务参数
        Dim taskMsg = New CloudQueueMessage(JsonConvert.SerializeObject(New With {.UsrID = usrId}))
        queue.AddMessage(taskMsg)

        txtMssgs.Value = "任务已提交，稍后查看相关表确认执行状态"
    Catch Excptn As Exception
        txtMssgs.Value = $"提交失败：{Excptn.Message}"
    End Try
End Sub

Azure Function监听队列的示例（C#）： #

public static class ProcExecFunction
{
    [FunctionName("ProcExecFunction")]
    public static void Run([QueueTrigger("proc-exec-queue")] string myQueueItem, ILogger log)
    {
        var taskParams = JsonConvert.DeserializeObject<TaskParams>(myQueueItem);
        string connString = "你的Azure SQL连接字符串";

        try
        {
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (var cmd = new SqlCommand("sptblTemp_GndrRprtPrGrp_Insrt", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@UsrIDfk", SqlDbType.Int).Value = taskParams.UsrID;
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                // 写入成功状态到表
                using (var cmd = new SqlCommand("INSERT INTO OperationStatus (UsrID, Status, CreateTime) VALUES (@usrId, 'Success', GETDATE())", conn))
                {
                    cmd.Parameters.AddWithValue("@usrId", taskParams.UsrID);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
            log.LogError(ex, $"执行存储过程失败，UsrID: {taskParams.UsrID}");
            // 写入失败状态到表
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (var cmd = new SqlCommand("INSERT INTO OperationStatus (UsrID, Status, ErrorMessage, CreateTime) VALUES (@usrId, 'Failed', @msg, GETDATE())", conn))
                {
                    cmd.Parameters.AddWithValue("@usrId", taskParams.UsrID);
                    cmd.Parameters.AddWithValue("@msg", ex.Message);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

    private class TaskParams
    {
        public int UsrID { get; set; }
    }
}

这种方案下，即使网站重启，后台服务仍会继续处理队列中的任务，适合长时间运行的关键任务。

内容的提问来源于stack exchange，提问作者ProgrammerKyle