如何将BLOB从vb.net winforms应用程序流式传输到SQL Server并报告进度

I am trying to upload files (possibly quite large) to SQL Server 2008 from my VB.Net Winforms application (C# answers would also be acceptable).

我试图从我的VB.Net Winforms应用程序上传文件（可能非常大）到SQL Server 2008（C＃答案也是可以接受的）。

The files are stored as Varbinary(MAX) using SQL Server's FILESTREAM datatype.

使用SQL Server的FILESTREAM数据类型将文件存储为Varbinary（MAX）。

I am sending the files to the server by passing in a FileStream as a SqlParameter.

我通过传递FileStream作为SqlParameter将文件发送到服务器。

This works ok. However, due to large files taking a while to upload, I would like to report progress back to a ProgressBar on the UI.

这很好用。但是，由于大文件需要一段时间才能上传，我想将进度报告回UI上的ProgressBar。

I am pretty sure that I will need to use Async/Await. The main issue is actually getting the progress value. As I am not doing anything with the FileStream, and just passing it as an SqlParameter, I don't know how I can get a progress value back. How can I achieve this?

我很确定我需要使用Async / Await。主要问题实际上是获得进步价值。由于我没有对FileStream做任何事情，只是将它作为SqlParameter传递，我不知道如何获得进度值。我怎样才能做到这一点？

I have considered copying the stream to another and getting the progress value there, but I think that would mean reading the whole file into memory and I am not even sure that it would work.

我曾考虑将流复制到另一个并获取进度值，但我认为这意味着将整个文件读入内存，我甚至不确定它是否可行。

Is there an Async method of FileStream which would do what I need? Or is there an altogether, better way for me to do this?

是否有FileStream的Async方法可以满足我的需求？或者我有更好的方式来做这件事吗？

Thanks.

谢谢。

1 个解决方案

#1

Just to letting you know what I did to solve this...

只是为了让你知道我做了什么来解决这个问题......

I am well aware that that this solution is far from ideal. There must be a better way and I am still looking to improve it... but for the moment, it seems to do what I need. Any comments or suggestions would be gratefully received.

我很清楚这个解决方案远非理想。必须有一个更好的方法，我仍然希望改进它...但目前，它似乎做我需要的。我们将非常感激地收到任何意见或建议。

Please see my simplified, fully commented code below:

请参阅下面的简化，完整评论代码：

    //Create the FileStream
    Using SourceStream As New FileStream("PathToTheFile", FileMode.Open)
        //Set the max value of your ProgressBar to the length of the stream
        ProgressPb.Maximum = SourceStream.Length
        //Start the task of sending the file to the DB (saving a reference to the task for later use.
        Dim FileUpload As Task(Of Boolean) = Task.Run(Function() SendToDB())
        //declare a variable to hold the last known position in the stream 
        Dim LastPosition As Long = 0
        //loop until we we are done (until the current position is at the end of the stream)
        Do
            //only do something if the position in the strang has changed since we last checked.
            If SourceStream.Position <> LastPosition Then
                //save the current position for next time
                LastPosition = SourceStream.Position
                //set the value of your progress bar to the current position in the stream
                ProgressPb.Value = SourceStream.Position
                //set your status label text as you wish
                StatusLbl.Text = "Uploading: " & Math.Round(SourceStream.Position / 1048576) & "MB of " & Math.Round(SourceStream.Length / 1048576) & "MB"
                //call do events to save locking up your UI
                Application.DoEvents()
            End If
            //limit the checks (8 times per second seems reasonably responsive)
            Threading.Thread.Sleep(1000 / 8)
        Loop Until LastPosition = SourceStream.Position
        //set your status label text as "Finalising" or similar (there is a short delay of 1-2 seconds after we reach the end of the stream but before we recieve a response back from the DB).
        StatusLbl.Text = "Uploading: " & Math.Round(SourceStream.Position / 1048576) & "MB of " & Math.Round(SourceStream.Length / 1048576) & "MB Complete. Finalising..."
        //wait for the response from the database
        Res = Await FileUpload
        //set your status label text "Complete" or similar
        StatusLbl.Text = "Uploading: " & Math.Round(SourceStream.Position / 1048576) & "MB of " & Math.Round(SourceStream.Length / 1048576) & "MB Complete. Finalising... Complete!"
        //check the result from the database and do stuff accordingly.
        If Res Then
            MsgBox("Success")
        Else
            MsgBox("Failed")
        End If
    End Using

EDIT

编辑

Just an update. I have refactored the code into a "StreamWatcher" class. Please see the code for the class below:

只是一个更新。我已将代码重构为“StreamWatcher”类。请参阅下面的课程代码：

Imports System.IO

Public Class StreamWatcher
    Private Const MaxProgressEventsPerSecond As Integer = 200
    Private Property _Stream As Stream
    Private Property _PreviousPosision As Long
    Public ReadOnly Property StreamPosition As Long
        Get
            If IsNothing(_Stream) Then
                Return 0
            Else
                Return _Stream.Position
            End If
        End Get
    End Property
    Public ReadOnly Property StreamLength As Long
        Get
            If IsNothing(_Stream) Then
                Return 0
            Else
                Return _Stream.Length
            End If
        End Get
    End Property
    Private Property _TimeStarted As DateTime? = Nothing
    Private Property _TimeFinished As DateTime? = Nothing
    Public ReadOnly Property SecondsTaken As Double
        Get
            If IsNothing(_TimeStarted) Then
                Return 0.0
            Else
                If IsNothing(_TimeFinished) Then
                    Return (DateTime.Now - _TimeStarted.Value).TotalSeconds
                Else
                    Return (_TimeFinished.Value - _TimeStarted.Value).TotalSeconds
                End If
            End If
        End Get
    End Property
    Private Property _UpdatesCalled As Integer = 0
    Public ReadOnly Property Updates As Integer
        Get
            Return _UpdatesCalled
        End Get
    End Property
    Private Property _Progress As IProgress(Of EventType)
    Private Enum EventType
        Progressed
        Completed
    End Enum
    Public Event Progressed()
    Public Event Completed()

    Public Sub Watch(ByRef StreamToWatch As Stream)
        Reset()
        _Stream = StreamToWatch
        Dim ProgressHandler As New Progress(Of EventType)(Sub(Value) EventRaiser(Value))
        _Progress = TryCast(ProgressHandler, IProgress(Of EventType))
        _TimeStarted = DateTime.Now
        Task.Run(Sub() MonitorStream())
    End Sub

    Private Sub MonitorStream()
        Do
            If _PreviousPosision <> StreamPosition Then
                _PreviousPosision = StreamPosition
                _Progress.Report(EventType.Progressed)
                //limit events to max events per second
                Task.Delay(1000 / MaxProgressEventsPerSecond).Wait()
            End If
        Loop Until (Not IsNothing(_Stream)) AndAlso (StreamPosition = StreamLength)
        _TimeFinished = DateTime.Now
        _Progress.Report(EventType.Completed)
    End Sub

    Private Sub EventRaiser(ByVal EventToRaise As EventType)
        Select Case EventToRaise
            Case EventType.Progressed
                _UpdatesCalled += 1
                RaiseEvent Progressed()
            Case EventType.Completed
                _UpdatesCalled += 1
                RaiseEvent Completed()
        End Select
    End Sub

    Private Sub Reset()
        _Stream = Nothing
        _PreviousPosision = 0
        _TimeStarted = Nothing
        _TimeFinished = Nothing
        _UpdatesCalled = 0
    End Sub

End Class

And usage is as so:

用法如下：

    Private WithEvents StreamWatcher As New StreamWatcher

    Private Async Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
        Dim Res As Boolean
        Using FS As New FileStream("MyFilePath", FileMode.Open)
            //this attaches the the watcher to the stream
            StreamWatcher.Watch(FS)
            ProgressBar.Maximum = FS.Length
            //This waits for the function to finish before continuing, without locking up the UI... be careful not to dispose the stream before it's finished.
            Res = Await Task.Run(Function() MySendToDBFunction(MyParam1))
            StatusLbl.Text += " Complete!"
        End Using
        If Res Then
            MessageBox.Show("Success")
        Else
            MessageBox.Show("Fail")
        End If
    End Sub

    //Called whenever the position in the stream changes (upto the max calls per second as defined in the StreamWatcher class - 200 seems to be more than enough))
    Private Sub FSWithProgeress_Progressed() Handles StreamWatcher.Progressed
        ProgressBar.Value = StreamWatcher.StreamPosition
        StatusLbl.Text = "Uploading: " & Math.Round(StreamWatcher.StreamPosition / 1048576) & "MB of " & Math.Round(StreamWatcher.StreamLength / 1048576) & "MB"
    End Sub

    //called once the end of the stream is reached
    Private Sub FSWithProgeress_Completed() Handles StreamWatcher.Completed
        StatusLbl.Text = "Upload: Complete. Finalising..."
    End Sub