单独线程中的SqlFileStream写入不起作用

Question

使用Sql FILESTREAM学习和测试web应用程序。客户端从网页上传一个大文件，这需要'X‘时间，当完全上传时，显示100%完成。但是，非常大的文件也需要时间让SqlFileStream写入文件系统，所以我想使用一个线程来完成这一部分。我得到的代码似乎工作得很好，但是没有数据在文件流文件中结束。

我将初始记录创建包装在它自己的事务作用域中，并在线程中使用单独的事务作用域。在线程例程中，我有适当的PathName()和TransactionContext，但我假设我在使用线程时遗漏了一些东西。

我注释掉了正常的SqlFileStream调用，它工作得很好。你能看出我在这里做的事情有什么问题吗？

    Public Function StoreFileStream()
        Dim json As New Dictionary(Of String, Object)
        Dim parms As New FileStreamThreadParameters

        If HttpContext.Current.Request.Files.Count > 0 Then
            Dim file As HttpPostedFile = HttpContext.Current.Request.Files(0)

            If "contentType" <> String.Empty Then
                Dim fs As Stream = file.InputStream
                Dim br As New BinaryReader(fs)
                Dim noBytes As New Byte()

                Try
                    Dim filePath As String = ""
                    Dim trxContext As Byte() = {}
                    Dim baseFileId As Integer

                    Using trxScope As New TransactionScope
                        Using dbConn As New SqlConnection(DigsConnStr)
                            Using dbCmd As New SqlCommand("ADD_FileStreamFile", dbConn)
                                dbConn.Open()
                                Using dbRdr As SqlDataReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow)
                                    dbRdr.Read()
                                    If dbRdr.HasRows Then
                                        filePath = dbRdr("Path")
                                        trxContext = dbRdr("TrxContext")
                                        baseFileId = dbRdr("BaseFileID")
                                    End If
                                    dbRdr.Close()
                                End Using

                                ' Code below writes to file, but trying to offload this to a separate thread so user is not waiting
                                'Using dest As New SqlFileStream(filePath, trxContext, FileAccess.Write)
                                '    fs.CopyTo(dest, 4096)
                                '    dest.Close()
                                'End Using
                            End Using
                            dbConn.Close()
                        End Using
                        trxScope.Complete()
                    End Using ' transaction commits here, not in line above

                    parms.baseFileId = baseFileId
                    parms.fs = New MemoryStream
                    fs.CopyTo(parms.fs)

                    Dim fileUpdateThread As New Threading.Thread(Sub()
                                                                     UpdateFileStreamThreaded(parms)
                                                                 End Sub)
                    fileUpdateThread.Start()

                    json.Add("status", "success")
                Catch ex As Exception
                    Elmah.ErrorSignal.FromCurrentContext().Raise(ex)
                    json.Add("status", "failure")
                    json.Add("msg", ex.Message)
                    json.Add("procedure", System.Reflection.MethodBase.GetCurrentMethod.Name)
                End Try
            Else
                json.Add("status", "failure")
                json.Add("msg", "Invalid file type")
                json.Add("procedure", System.Reflection.MethodBase.GetCurrentMethod.Name)
            End If
        End If
        Return json
    End Function

    Public Class FileStreamThreadParameters
        Public Property baseFileId As Integer
        Public fs As Stream
    End Class

    Private Sub UpdateFileStreamThreaded(parms As FileStreamThreadParameters)
        Dim filePath As String = ""
        Dim trxContext As Byte() = {}

        Try
            Using trxScope As New TransactionScope
                Using dbConn As New SqlConnection(DigsConnStr)
                    Using dbCmd As New SqlCommand("SELECT FileBytes.PathName() 'Path', GET_FILESTREAM_TRANSACTION_CONTEXT() 'TrxContext' FROM FileStreamFile WHERE Id = " & parms.baseFileId, dbConn)
                        dbConn.Open()
                        Using dbRdr As SqlDataReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow)
                            dbRdr.Read()
                            If dbRdr.HasRows Then
                                filePath = dbRdr("Path")
                                trxContext = dbRdr("TrxContext")
                            End If
                            dbRdr.Close()

                            Using dest As New SqlFileStream(filePath, trxContext, FileAccess.Write)
                                parms.fs.CopyTo(dest, 4096)
                                dest.Close()
                            End Using
                        End Using
                    End Using
                    dbConn.Close()
                End Using
                trxScope.Complete()
            End Using
        Catch ex As Exception
            'Elmah.ErrorSignal.FromCurrentContext().Raise(ex)
        End Try
    End Sub

Answer 1

显然，这是一个复杂的问题。实际上，我用下面的代码让它工作。然而，我最终完全放弃了使用SQL FILESTREAM，因为设置它太复杂了。

这是一个现有的web应用程序，sql服务器在不同的机器上。在我让文件流起作用后，下一个障碍是身份验证设置。Filestream要求在连接字符串上使用集成安全性。即使在我们的Intranet应用程序上使用windows身份验证，我也无法让web应用程序在连接到数据库时使用windows凭据。它似乎总是使用计算机名称或应用程序池服务。我尝试了许多我在网上找到的例子，但在这里都没有用。即使我让它起作用了，我也会希望在个人登录时使用和Active Directory组，这看起来是另一个障碍。

此应用程序将文档存储在varbinary列中，以便在某个时候启用全文搜索。问题出在通常是图片或视频的大文件上。因为你不能在这些文档上搜索文本，所以现在的策略是将它们存储在文件系统中，而所有其他可搜索的文档(.docx、.pptx等)仍将存储在varbinary中。实际上，我很遗憾我不能让文件流工作，因为它似乎是理想的解决方案。总有一天我会再来讨论它，但它真的不应该那么复杂。:-(

我运行的代码是：

                    Dim filePath As String = ""
                    Dim trxContext As Byte() = {}
                    Dim baseFileId As Integer

                    Using trxScope As New TransactionScope
                        Using dbConn As New SqlConnection(DigsFSConnStr)
                            Using dbCmd As New SqlCommand("NEW_FileStreamBaseFile", dbConn)
                                dbCmd.CommandType = CommandType.StoredProcedure
                                dbCmd.Parameters.AddWithValue("@Title", fileDesc)
                                dbCmd.Parameters.AddWithValue("@Summary", summary)
                                dbCmd.Parameters.AddWithValue("@Comments", comments)
                                dbCmd.Parameters.AddWithValue("@FileName", uploadedFileName)
                                dbCmd.Parameters.AddWithValue("@ContentType", contentType)
                                dbCmd.Parameters.AddWithValue("@FileExt", ext)
                                'dbCmd.Parameters.AddWithValue("@FileBytes", noBytes)    ' now that were offloading the file byte storage to a thread
                                dbCmd.Parameters.AddWithValue("@UploadedByResourceID", uploadedByResourceID)
                                dbCmd.Parameters.AddWithValue("@UploadedByShortName", uploadedByShortName)
                                dbCmd.Parameters.AddWithValue("@FileAuthor", fileAuthor)
                                dbCmd.Parameters.AddWithValue("@TagRecID", tagRecID)
                                dbCmd.Parameters.AddWithValue("@UserID", samAccountName)
                                dbCmd.Parameters.AddWithValue("@FileDate", fileDate)
                                dbCmd.Parameters.AddWithValue("@FileType", fileType)
                                dbCmd.Parameters.AddWithValue("@FileTypeRecID", fileTypeRecId)

                                ' Save to file system too for xod conversion
                                file.SaveAs(HttpContext.Current.Server.MapPath("~/files/uploaded/") & uploadedFileName)

                                dbConn.Open()
                                Using dbRdr As SqlDataReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow)
                                    dbRdr.Read()
                                    If dbRdr.HasRows Then
                                        filePath = dbRdr("Path")
                                        trxContext = dbRdr("TrxContext")
                                        json.Add("baseFileId", dbRdr("BaseFileID"))
                                        virtualFileRecId = dbRdr("VirtualFileRecID")
                                        dbStatus = dbRdr("status")
                                        If dbStatus = "failure" Then
                                            json.Add("msg", dbRdr("msg"))
                                        End If
                                    End If
                                    dbRdr.Close()
                                End Using

                                ' Prepare and start Task thread to write the file
                                If dbStatus = "success" Then
                                    bytes = br.ReadBytes(fs.Length)
                                    Dim task As New System.Threading.Tasks.Task(
                                        Sub()
                                            UpdateNewFileStreamBytes(virtualFileRecId, bytes)
                                        End Sub)
                                    task.Start()
                                    json.Add("status", "success")
                                Else
                                    json.Add("status", "failure")
                                End If
                            End Using
                            dbConn.Close()
                        End Using
                        trxScope.Complete()
                    End Using ' transaction commits here, not in line above

其任务过程为：

    Private Sub UpdateNewFileStreamBytes(virtualFileRecId As Integer, fileBytes As Byte())
        Dim filePath As String = ""
        Dim trxContext As Byte() = {}

        Try
            Using trxScope As New TransactionScope
                Using dbConn As New SqlConnection(DigsFSConnStr)
                    Using dbCmd As New SqlCommand("UPD_FileStreamBaseFile", dbConn)
                        dbCmd.CommandType = CommandType.StoredProcedure
                        dbCmd.Parameters.AddWithValue("@VirtualFileRecID", virtualFileRecId)

                        dbConn.Open()
                        Using dbRdr As SqlDataReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow)
                            dbRdr.Read()
                            If dbRdr.HasRows Then
                                filePath = dbRdr("Path")
                                trxContext = dbRdr("TrxContext")
                            End If
                            dbRdr.Close()

                            Using dest As New SqlFileStream(filePath, trxContext, FileAccess.Write)
                                dest.Write(fileBytes, 0, fileBytes.Length)
                                dest.Close()
                            End Using
                        End Using
                    End Using
                    dbConn.Close()
                End Using
                trxScope.Complete()
            End Using
        Catch ex As Exception
            Elmah.ErrorSignal.FromCurrentContext().Raise(ex)
        End Try