Sql Filestream Update problems

Question

Hello, I have implemented the filestream feature of SQL Server 2008 in a VB.Net application.

I can insert files, and then retrieve/view them just fine. However, I have huge problems trying to update a file.

Eg. The user selects a file from the grid which I execute via process.start. If that file is a .txt file, the user may choose to edit it. In case that happens, I need to save the changed file back to the database. So far I have failed to do that.

What I do, is take the retrieved file, copy it (cause i got some errors about it being used), and then Process.Start it. After that, via .NET filestream, I convert the file to bytes and try to update the record. SQL Profiler and a manual SELECT on the varbinary(max) column tell me that the file is updated properly, but the very next try to retrieve it I get an unchanged file.

After that I also tried to update the file by changing its File-System Version, but the file still wouldn't seem to update. Does Anyone have a code sample of how I can achieve this operation? Like 500 sites on the internet have examples of how to Insert And Retrieve the file, but not a single example on how to update.

This is how my second attempt of trying to update the file via the filesystem looks like. The code for inserting/retrieving is very similar and it works properly.

Public Sub UpdateFile(ByVal intGUID As Guid, ByVal strName As String)
    Dim objConnection As SqlConnection = GetConnection()
    Dim objTransaction As SqlTransaction = objConnection.BeginTransaction()
    Dim cmd As New SqlCommand("SELECT [FLE_Data].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " + _
                              "FROM TSKt_File " + _
                              "WHERE File_ID = @ID", objConnection)
    cmd.Transaction = objTransaction
    cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = intGUID
    Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
    rdr.Read()
    Dim strFilePath As String = rdr.GetString(0)
    Dim trxID As Byte() = DirectCast(rdr(1), Byte())
    rdr.Close()

    Using fs As IO.FileStream = IO.File.OpenWrite(strName)
        Using sqlFS As New SqlTypes.SqlFileStream(strFilePath, trxID, IO.FileAccess.ReadWrite)
            Dim buffer As Byte() = New Byte(512 * 1024) {}
            Dim intPos As Integer = sqlFS.Read(buffer, 0, buffer.Length)
            Do While intPos > 0
                fs.Write(buffer, 0, intPos)
                intPos = sqlFS.Read(buffer, 0, buffer.Length)
            Loop
        End Using
    End Using
    objTransaction.Commit()
    objConnection.Close()
End Sub

Answer 1

There's an official sample of updating filestream value (under the Overwriting FILESTREAM Data Example section).

By the way, it is unclear to me what you're trying to achieve by manually copying the file. It seems like your first approach (where you're claiming the file is left unchanged) was correct and should work - if it doesn't, please provide the code you're using.