Click here to Skip to main content
14,549,015 members
Rate this:
Please Sign up or sign in to vote.
See more:
i want to retrieve or download Pdf file from sql server using vb.net.
that code which im going to attached is used for store pdf file sql server

What I have tried:

Public Class Form5

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpenFileDialog1.InitialDirectory = "C:\"
        OpenFileDialog1.Title = "Open a PDF file"
        OpenFileDialog1.Filter = "PDF files|*.pdf"
        OpenFileDialog1.ShowDialog()
        TextBox1.Text = OpenFileDialog1.FileName
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        SavePDFtoDB()
    End Sub
    Private Sub SavePDFtoDB()
        Try
            Dim sqlconn As SqlConnection
            Dim conn As String = (Configuration.ConfigurationManager.AppSettings("ConnectionString").ToString())
            sqlconn = New SqlConnection(conn)
            Dim sqlquery As New SqlCommand

            Dim fInfo As New FileInfo(TextBox1.Text)
            Dim numBytes As Long = fInfo.Length
            Dim fStream As New FileStream(TextBox1.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
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO tbldocument(filename, extension, content)VALUES(@filename, @extension, @content)"
            sqlquery.Parameters.Add(New System.Data.SqlClient.SqlParameter("@filename", TextBox1.Text))
            sqlquery.Parameters.Add(New System.Data.SqlClient.SqlParameter("@extension", ".pdf"))
            sqlquery.Parameters.Add(New System.Data.SqlClient.SqlParameter("@content", data))
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
            MsgBox("Saved")
        Catch err As Exception
            MsgBox(err.Message)
        End Try
    End Sub
End Class
Posted
Updated 24-Jun-19 19:09pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Firstly, you can make your whole life easier if you look at the File class - all of this code:
Dim fInfo As New FileInfo(TextBox1.Text)
Dim numBytes As Long = fInfo.Length
Dim fStream As New FileStream(TextBox1.Text, FileMode.Open, FileAccess.Read)
Dim br As New BinaryReader(fStream)
Dim data As Byte() = br.ReadBytes(CInt(numBytes))
br.Close()
fStream.Close()
can be done with a single line of code:
Dim data As Byte() = File.ReadAllBytes(TextBox1.Text)
Easier to write, easier to read.

Then, your problem: read the data from the DB:
Try

    Using con As SqlConnection = New SqlConnection(strConnect)
        con.Open()

        Using cmd As SqlCommand = New SqlCommand("SELECT content FROM tbldocument WHERE filename = @FN", con)
            cmd.Parameters.AddWithValue("@FN", filename)
            Dim data As Byte() = CType(cmd.ExecuteScalar(), Byte())
            ...
        End Using
    End Using

Catch ex As Exception
    Console.WriteLine(ex.Message)
End Try
You can also use the File class to write the file in a single line of code:
File.WriteAllBytes("D:\Test Data\TheFile.pdf", data)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100