VB.NET中CancellationTokenSource.Cancel无法终止SqlDataReader的问题

我通过SQL Server查询实现文件下载，使用CLR函数将服务器端文件转换为二进制并返回给客户端。目前已通过CopyStream子程序分批将二进制流写入本地文件，功能正常。我需要支持用户随时取消该查询。当前控制台程序会自动调用CancellationTokenSource.Cancel()方法，我也能通过ThrowIfCancellationRequested()捕获并抛出异常，但本地文件停止写入后，SqlDataReader仍会持续读取直至文件传输完成或超时。如何中途终止SqlDataReader？

Imports System.Data.SqlClient
Imports System.IO
Imports System.Threading

Module Module1
    Public running As Boolean = False

    Public Sub CopyStream(ByVal input As Stream, ByVal output As Stream, ByRef i As Integer, cancellationtokensource As CancellationTokenSource)
        Dim buffer As Byte() = New Byte(128) {}
        Dim len As Integer = input.Read(buffer, 0, buffer.Length)
        While (len > 0)
            output.Write(buffer, 0, len)
            len = input.Read(buffer, 0, buffer.Length)
            If i = 1000 Then    ' I manually call the Cancel() function
                cancellationtokensource.Cancel()
                Exit Sub
            End If
            i = i + 1
        End While
    End Sub
    Private Const connectionString As String = connectionstring
    Sub Main()

        running = True
        caller()

        While running = True
            'continue running
        End While

        Console.WriteLine("Done Program")
    End Sub
    Async Sub caller()
        Dim canceltokensource As New CancellationTokenSource
        E2EStreamAsync(canceltokensource)
    End Sub

    Public Async Function E2EStreamAsync(cancellationtokensource As CancellationTokenSource) As Task
        Dim i As Integer = 0
        Using readConn As SqlConnection = New SqlConnection(connectionString)
            Dim openReadConn As Task = readConn.OpenAsync(cancellationtokensource.Token)
            Await Task.WhenAll(openReadConn)
            Using readCmd As SqlCommand = New SqlCommand("blobreturningquery", readConn)
                readCmd.CommandTimeout = 99999
                Using reader As SqlDataReader = Await readCmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationtokensource.Token)
                    While Await reader.ReadAsync(cancellationtokensource.Token)
                        Try
                            Using file As Stream = System.IO.File.Create("c:\Temp\file.pptx")
                                CopyStream(reader.GetStream(0), file, i, cancellationtokensource)
                                cancellationtokensource.Token.ThrowIfCancellationRequested()
                            End Using
                        Catch e As Exception
                            Console.WriteLine("{0} Exception caught.", e)
                            Exit Function
                        End Try
                    End While

                End Using
            End Using
        End Using
        running = False
    End Function
End Module

解决方案 #

问题核心在于：调用Cancel()后，你只停止了本地文件写入，但SqlDataReader仍在继续从服务器读取剩余的二进制流。要彻底终止读取，需要主动关闭SqlDataReader（或底层连接），同时确保取消令牌在所有异步操作环节都被正确检查和响应。

关键修改点：

1. 将CopyStream改为异步方法，在每次读取操作前检查取消令牌，及时抛出取消异常
2. 取消时不仅停止写入，还要确保SqlDataReader被关闭，终止服务器端的数据流传输
3. 优化异步流程，让所有异步调用都绑定取消令牌，确保取消信号能传递到SQL Server端

修改后的代码：

Imports System.Data.SqlClient
Imports System.IO
Imports System.Threading

Module Module1
    Public running As Boolean = False

    ' 改为异步方法，直接接收CancellationToken而非CancellationTokenSource
    Public Async Function CopyStreamAsync(input As Stream, output As Stream, ByRef i As Integer, token As CancellationToken) As Task
        Dim buffer As Byte() = New Byte(1024 * 64) {} ' 增大缓冲区提升效率，可选
        Dim len As Integer
        Do
            ' 每次读取前检查取消令牌，及时终止
            token.ThrowIfCancellationRequested()
            
            len = Await input.ReadAsync(buffer, 0, buffer.Length, token)
            If len <= 0 Then Exit Do
            
            Await output.WriteAsync(buffer, 0, len, token)
            
            i += 1
            ' 模拟触发取消的条件
            If i = 1000 Then
                token.ThrowIfCancellationRequested() ' 直接抛出取消异常，而非手动调用Cancel
            End If
        Loop
    End Function

    Private Const connectionString As String = "your_connection_string_here"

    Sub Main()
        running = True
        Dim task = caller()
        ' 替代死循环，等待异步任务完成
        task.Wait()
        Console.WriteLine("Done Program")
    End Sub

    Async Function caller() As Task
        Using canceltokensource As New CancellationTokenSource
            Try
                Await E2EStreamAsync(canceltokensource.Token)
            Catch ex As OperationCanceledException
                Console.WriteLine("下载已取消")
            Catch ex As Exception
                Console.WriteLine($"异常捕获: {ex.Message}")
            End Try
        End Using
        running = False
    End Function

    Public Async Function E2EStreamAsync(token As CancellationToken) As Task
        Dim i As Integer = 0
        Using readConn As New SqlConnection(connectionString)
            Await readConn.OpenAsync(token)
            
            Using readCmd As New SqlCommand("blobreturningquery", readConn)
                readCmd.CommandTimeout = 99999
                
                ' 注册取消回调：一旦触发取消，立即关闭reader和连接
                Using registration = token.Register(Sub()
                                                      readCmd.Cancel() ' 取消SQL命令
                                                      readConn.Close()
                                                  End Sub)
                
                    Using reader As SqlDataReader = Await readCmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, token)
                        While Await reader.ReadAsync(token)
                            token.ThrowIfCancellationRequested()
                            
                            Using file As Stream = File.Create("c:\Temp\file.pptx")
                                Await CopyStreamAsync(reader.GetStream(0), file, i, token)
                            End Using
                        End While
                    End Using
                End Using
            End Using
        End Using
    End Function
End Module

代码说明：

• 异步CopyStream：使用ReadAsync和WriteAsync替代同步方法，确保取消令牌能在IO操作期间生效
• 取消回调注册：通过token.Register()在取消时主动调用readCmd.Cancel()和readConn.Close()，直接终止服务器端的查询和数据流传输
• 全程检查令牌：在CopyStreamAsync的循环、reader.ReadAsync前都检查ThrowIfCancellationRequested()，确保取消信号能被及时响应
• 简化流程：移除不必要的running变量死循环，改用异步任务等待，代码更简洁可控

这样修改后，当取消触发时，不仅会停止本地文件写入，还会立即终止SqlDataReader的读取操作，同时通知SQL Server取消当前查询，避免不必要的数据流传输。

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