Latest News

Well after much searching and trial and error and pouring through knowledgebases I have found a method for successfully saving a PDF File to a SQL Server 2005 Database and then viewing it using VB.Net. I thought I would post it for all those other poor lost souls out there who need the help I needed.

The code uses a form where a user can select a pdf file using OpenFileDialog. The form saves the filepath to a textbox named txtFileName. The database table contains three fields as follows

FileName - varchar(50)
Extension - varchar(5)
Content - varbinary(max)

Here is the code

Private Sub SavePDFtoDB()
  Dim fInfo As New FileInfo(Me.txtFileName.Text)
  Dim numBytes As Long = fInfo.Length
  Dim fStream As New FileStream(Me.txtFileName.Text, FileMode.Open, FileAccess.Read)
  Dim br As New BinaryReader(fStream)
  Dim data As Byte() = br.ReadBytes(CInt(numBytes))
  br.Close()
  fStream.Close()


  'Insert the details into the database
  Dim strSQL As String = "UPDATE tblMyTable SET Filename = @Filename ,Extension = @Extension ,Content = @Content WHERE MyID = 9"
  Dim myTransaction As System.Data.SqlClient.SqlTransaction
  Dim cmd As New System.Data.SqlClient.SqlCommand
  With cmd
    .Connection = basProcedureManager.GetConnection
    .CommandType = CommandType.Text
    .CommandText = strSQL
    .Parameters.Add(New System.Data.SqlClient.SqlParameter("@FileName", Me.txtFileName.Text))
     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Extension", ".pdf"))
    .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", data))
    myTransaction = .Connection.BeginTransaction
    cmd.Transaction = myTransaction
    Try
      .ExecuteNonQuery()
       myTransaction.Commit()
    Catch objdb As System.Data.OleDb.OleDbException
      myTransaction.Rollback()
    Finally
      myTransaction = Nothing
    End Try
  End With
End Sub

Private Sub ViewPDF()
  Try
    Dim objVersion As New Landscape.ProcedureManager.BL.SystemVersion
    Dim ds As System.Data.DataSet
    With objVersion
      .Connection = basProcedureManager.GetConnection
      ds = .GetFullDetails(Me.VersionID)
     End With

     Dim strExtenstion As String = ".pdf"
    Dim bytFile As Byte() = CType(ds.Tables(0).Rows(0).Item("Content"), Byte())
    Dim sFilePath As String
    sFilePath = System.IO.Path.GetTempFileName()
    System.IO.File.Move(sFilePath, System.IO.Path.ChangeExtension(sFilePath, ".pdf"))
    sFilePath = System.IO.Path.ChangeExtension(sFilePath, ".pdf")
    System.IO.File.WriteAllBytes(sFilePath, bytFile)
    Using p As New System.Diagnostics.Process
      p.StartInfo = New System.Diagnostics.ProcessStartInfo(sFilePath)
      p.Start()
      p.WaitForExit()
      Try
         System.IO.File.Delete(sFilePath)
      Catch
      End Try
    End Using
  Catch ex As Exception
    MsgBox("Unable to Show Report - " & ex.Message)
  End Try
End Sub