Writing DB data back to a file

[Jessica]

Hello everyone. I am about to pull my hair out tonight trying to solve this issue I have. I was asked to write some code that extracted the data from the db and then save the word and excel files locally.

The application that is running this is a web based document management product. Its stores the documents as type image in the DB. This is an example oh how they are rendered with that application.

         Try
            params(0) = db.MakeParameter("@ItemID", ItemID)
            params(1) = db.MakeParameter("@Table", strTable)
            db.RunProcedure("upDMS_RenderWroxDoc", params, dr)
            If dr Is Nothing Then
               _errorMessage = db.ErrorMessage
            End If
         Catch e As Exception
            _errorMessage = "Unable to retrieve the document [" & e.Message & "]"
            dr = Nothing
         End Try

         dr.Read()
response.ContentType = CType(dr("ContentType"), String)

response.OutputStream.Write(CType(dr("Content"), Byte()), 0, CInt(dr("ContentSize")))

So basically my app is just trying to make a DB connection, get the data and then produce a file. I am running just a VB command line app. I can connect to the server, grab the data. I know I have good data. When I output this...

Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", _
dr(0), dr(1), dr(2), dr(3)))

I get

System.Byte[], application/msword, 24064, DocName

I am really getting stuck on the file creation. I thought I could do the following.

My.Computer.FileSystem.WriteAllBytes _
("C:\Temp\Test.doc", CType(dr(0), Byte()), True)

When I do that all I get is garbage data in the file.

Edit:
I have also tried to do it with a filestream.

Dim fs As FileStream = New FileStream("C:\Temp\Test.doc", FileMode.CreateNew)
fs.Write(dr(0), 0, CType(dr(2), Integer))
fs.Close()

That does not put any data out in the file. I really just don't understand how this should work

Any help is greatly appreciated

Jessica

 

[MattP]

Here's an example using a FileStream. You should be able to modify it to suit your purposes.

    Private Sub btnDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnDownload.Click

        If Not Me.DocumentsDataGridView.CurrentRow.Index = -1 Then
            Dim FileName As String = Me.DocumentsDataGridView.Rows(Me.DocumentsDataGridView.CurrentRow.Index).Cells(0).Value.ToString()
            Me.FolderBrowserDialog1.ShowDialog()
            SqlBlob2File(FileName)
            MessageBox.Show(FileName & " downloaded successfully")
        Else
            MessageBox.Show("Please Select A File")
        End If

    End Sub

    Private Sub SqlBlob2File(ByVal DocName As String)
        Dim cn As New SqlConnection(My.Settings.YourConnectionString.ToString())
        Dim cmd As New SqlCommand("Select YourImageColumn From YourTable WHERE YourImageColumn = @DocName", cn)

        cmd.Parameters.AddWithValue("@DocName", YourImageColumn)
        cn.Open()

        Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.Read() Then
            Dim fs As IO.FileStream = New IO.FileStream(Me.FolderBrowserDialog1.SelectedPath & "\" & DocName, IO.FileMode.Create)
            Dim b() As Byte = dr.Item("YourImageColumn")
            fs.Write(b, 0, b.Length)
            fs.Close()
        End If
        dr.Close()
    End Sub