File from SQL Image column

[MattP]

I've run into a problem getting a file out of an image column in SQL. My method works when I run it against a pdf document. When I try it with an xls file I'm getting back gibberish due to the Utf-8 charset (I think).

Can somebody give me a nudge on how to incorporate System.Text.Encoding to get around the problem?

    Private Sub SqlBlob2File(ByVal DestFilePath As String, ByVal CMID As Integer)
        Dim DocumentCol As Integer = 0 
        Dim cn As New SqlConnection(My.Settings.ViewpointConnectionString.ToString())
        Dim cmd As New SqlCommand("SELECT DATAPROP FROM CMDATA WHERE CMID = @CMID", cn)
        cmd.Parameters.AddWithValue("@CMID", CMID)
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        Dim b(dr.GetBytes(DocumentCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
        dr.GetBytes(DocumentCol, 0, b, 0, b.Length)
        dr.Close()
        cn.Close()
        Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
        fs.Write(b, 0, b.Length)
        fs.Close()
    End Sub

 

[cjard]

where's the UTF8?

 

[MattP]

where's the UTF8?

Oops, the content types are listed as "application/vnd.ms-excel; charset=utf-8" & "text/html; charset=utf-8" in the SQL database.

I've cleaned up the code a bit but I'm still getting the same results.

    Private Sub SqlBlob2File(ByVal DestFilePath As String, ByVal CMID As Integer)
        Dim cn As New SqlConnection(My.Settings.ViewpointConnectionString.ToString())
        Dim cmd As New SqlCommand("SELECT DATAPROP FROM CMDATA WHERE CMID = @CMID", cn)
        cmd.Parameters.AddWithValue("@CMID", CMID)
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.Read() Then
            Dim fs As IO.FileStream = New IO.FileStream(DestFilePath, IO.FileMode.Create)
            Dim b() As Byte = dr.Item("DATAPROP")
            fs.Write(b, 0, b.Length)
            fs.Close()
        End If
        dr.Close()
    End Sub