Save binary file from SQL Server

Question

I'm trying to save a binary file stored in a SQL database with a SaveDialog, the function RetrieveFile retrieves the specified file from the database as a Byte array, here's what I have so far:

Private Shared Function RetrieveFile(ByVal filename As String) As Byte()
    Dim connection As New SqlConnection("Data Source=SERVER\SQL2008;Initial Catalog=NorthPole;Integrated Security=True")
    Dim command As New SqlCommand("SELECT pcfFile FROM Items WHERE pcfFileName=@Filename", connection)
    command.Parameters.AddWithValue("@Filename", filename)
    connection.Open()
    Dim reader As SqlDataReader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
    reader.Read()
    Dim memory As New MemoryStream()
    Dim startIndex As Long = 0
    Const ChunkSize As Integer = 256
    While True
        Dim buffer As Byte() = New Byte(ChunkSize - 1) {}
        Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize)
        memory.Write(buffer, 0, CInt(retrievedBytes))
        startIndex += retrievedBytes
        If retrievedBytes <> ChunkSize Then
            Exit While
        End If
    End While
    connection.Close()
    Dim data As Byte() = memory.ToArray()
    memory.Dispose()
    Return data


End Function


Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim saveFileDialog1 As New SaveFileDialog()
    saveFileDialog1.Filter = "PCF File|*.pcf|"
    saveFileDialog1.Title = "Save an pcf File"
    saveFileDialog1.ShowDialog()

    If saveFileDialog1.FileName <> "" Then
        Dim fs As System.IO.FileStream = CType(RetrieveFile("FakePCF.pcf"), System.IO.FileStream)
                    fs.Close()
    End If
End Sub

Files are saved as "SqlDbType.VarBinary" within the database. I get: "Index was outside the bounds of the array." on:

   Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize)

The MemoryStream appears to not be retrieving the data yet the SQL sytax is correct. What am I doing wrong?

Answer 1

Well, first of all, your method returns byte[] and you're trying to cast it to FileStream. You should change your handler to following:

Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim saveFileDialog1 As New SaveFileDialog()
    saveFileDialog1.Filter = "PCF File|*.pcf|"
    saveFileDialog1.Title = "Save an pcf File"
    saveFileDialog1.ShowDialog()

    If saveFileDialog1.FileName <> "" Then
        Dim fs As New System.IO.FileStream (saveFileDialog1.FileName, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim data As Byte() = RetrieveFile("FakePCF.pcf")
        fs.Write(data, 0, data.Length)
        fs.Flush()
        fs.Close()
    End If
End Sub