SqlFileStream writing in separate thread not working

Question

Learning and testing using Sql FILESTREAM for a web app. A client uploads a large file form the web page which takes 'X' time and when fully uploaded shows 100% complete. However, very large files also take time for SqlFileStream to write to the file system so I want to spin off a thread to complete that part. The code I've got seems to work fine but no data ends up in the filestream file.

I'm wrapping the initial record creation in it's own transaction scope and using a separate transaction scope in the thread. In the threaded routine I have the appropriate PathName() and TransactionContext but I assume I'm missing something while using a thread.

I've commented out the normal SqlFileStream call which works fine. Can you see anything wrong with what I'm doing here?

    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

Obviously this is a complex issue. I actually got it to work with the code below. However I eventually abandoned using SQL FILESTREAM altogether due to too much complexity in getting it all set up.

This is an existing web application with the sql server on a different box. After I got the filestreaming to work the next hurdle was authentication setup. Filestream requires Integrated Security on your connection string. Even with windows authentication on our Intranet app, I could not get the web app to use the windows credentials when connecting to the database. It always seemed to use the computer name or the app pool service. I tried many many examples I found on the net and here to no avail. Even if I got that to work then I would want to use and Active Directory group over individual logins which looked to be another hurdle.

This app stores documents in a varbinary column so that full text search can be enabled at some point. The issue was with large files which are generally pictures or videos. Since you can't search text on those anyway the strategy now is to store those on the file system and all other searchable docs (.docx, .pptx, etc) will still be stored in the varbinary. I'm actually sad that I could not get filestream to work as it seems like the ideal solution. I'll come back to it some day but it really should not be so frickin complicated. :-(

The code I got working is:

                    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

With the task procedure of:

    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