How Do I Store A .PDF File In SQL Database Using Web Application...?

Question

_{Wednesday, September 30, 2015 5:25 PM}

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class admin_home
    Inherits System.Web.UI.Page
    Dim con As New SqlConnection("Data Source=T-LAPTOP\SQLEXPRESS;Initial Catalog=ebook_demo_db;Integrated Security=True")
    Dim cmd As New SqlCommand

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click

        Dim filename As String = Path.GetFileName(FileUpload2.PostedFile.FileName)
        Dim contentType As String = FileUpload2.PostedFile.ContentType

        Using fs As Stream = FileUpload2.PostedFile.InputStream
            Using br As New BinaryReader(fs)
                Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
                Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
                Using con As New SqlConnection(constr)
                    Dim query As String = "Insert INTO book_tb values (@id, @name,@aname,@publish,@Data,@ver,@course)"
                    Using cmd As New SqlCommand(query)
                        cmd.Connection = con
                        cmd.Parameters.Add("@id", SqlDbType.Int).Value = TextBox5.Text
                        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox6.Text
                        cmd.Parameters.Add("@aname", SqlDbType.NVarChar).Value = TextBox7.Text
                        cmd.Parameters.Add("@publish", SqlDbType.DateTime).Value = TextBox8.Text
                        cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value = bytes
                        cmd.Parameters.Add("@ver", SqlDbType.TinyInt).Value = vbYesNo
                        cmd.Parameters.Add("@course", SqlDbType.NVarChar).Value = DropDownList2.SelectedValue
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                    End Using
                End Using
                Response.Redirect("admin_home.aspx")
            End Using
        End Using
    End Sub
End Class

All replies (2)

_{Thursday, October 1, 2015 2:27 AM ✅Answered}

Hi Ellis,

When we store PDF file in SQL Server database, we need to convert the PDF file into a byte array and then put it into the varbinary(max) field of a table.

From your description, you want to use VB code to store the PDF file into SQL Server database, I recommend you post the question in the VB.NET forums. It is appropriate and more experts will assist you.

In addition, there are some examples about storing a pdf file in database using vb.net in the following blogs for your reference.

Retrieve and display PDF Files from database in browser in ASP.Net

http://www.aspsnippets.com/Articles/Retrieve-and-display-PDF-Files-from-database-in-browser-in-ASPNet.aspx

Save Store Files In Sql Database Download From GridView Asp.Net
http://csharpdotnetfreak.blogspot.com/2012/01/save-store-files-sqlserver-database.html

Thanks,
Lydia Zhang

Lydia Zhang
TechNet Community Support

---

_{Thursday, October 1, 2015 2:41 AM ✅Answered}

Hi Ellis

In addition to Lydia answer, it is important to mention that there are several options to store files in SQL Server, and each option uses different "way of use" in the application side (the vb.net code). Therefore, the answer is, it depend (on your files characteristics, your database architecture, and the way your application use the files). I will try to summarize the basic options for you.

You must chose which type you want to use in the database. This can be related to the version of your SQL Server, the files size, number of users, and the way you use the files in the application. these are common options:

1. Store on the filesystem with a link in the database (this is not fit your discription, therefore I will not post example)

2. Store in the database using a blob type like Binary,varbinary(n),varbinary(max)

3. Store files using FILESTREAM type (from SQL Server 2008)
example 1: http://weblogs.asp.net/aghausman/saving-and-retrieving-file-using-filestream-sql-server-2008
example 2: http://csharpdotnetfreak.blogspot.com/2012/01/save-store-files-sqlserver-database.html

4. Store files using FileTables (from SQL Server 2012)
https://www.mssqltips.com/sqlservertip/2667/filetable-feature-in-sql-server-2012/

The big advantage of this option (option 4) is that using the vb.net code you do not need to insert the file to SQL Server but simply use the IO system to copy the files into the virtual folder. The SQL Server will manage everything behind the scene

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]