Resources » Code Snippets » ASP.NET WebForms

How to download PDF file from SQL Server Database in ASP.NET?


Posted Date: 21-Aug-2011  Last Updated:   Category: ASP.NET WebForms    
Author: Member Level: Diamond    Points: 20


In this article I have explained about how to download PDF file from SQL Server, Previously that file stored in image datatype field as bytes. We already stored PDF file in the SQL Server table as bytes, so we need to convert that PDF data bytes into the PDF format for read that PDF file.



Description

Sometimes we need to store uploaded files in SQL Server table, that time we have create image datatype field in that table and store our files in that field. That image field is allowed to store all files like Image, PDF, doc etc. as Bytes format. If we need to see stored files again in the webpage then we need to convert that bytes values into the specified file format.
For example here we convert Bytes to PDF
Created Table structure like this

CREATE TABLE PDFUPLOAD(ID INT IDENTITY(1,1), PDFNAME varchar(200), PDFCONTENT IMAGE)

Client side:
I have used one file upload control and button in the webpage.

Server side
After user select PDF file in the file upload control click submit button, we are convert that PDF file into the bytes and then store into the database image datatype field.

protected void Button1_Click(object sender, EventArgs e)
{
//PDF Upload Code to SQL SERVER database table
if (FileUpload1.HasFile)
{
Stream fs = default(Stream);
fs = FileUpload1.PostedFile.InputStream;
BinaryReader br1 = new BinaryReader(fs);
byte[] pdfbytes = br1.ReadBytes(FileUpload1.PostedFile.ContentLength);
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("insert into PDFUPLOAD(PDFNAME,PDFCONTENT) values (@pdfname,@pdf)", sqlcon);
sqlcmd.Parameters.Add("@pdfname", FileUpload1.FileName);
sqlcmd.Parameters.Add("@pdf", pdfbytes);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
Label1.Text = "Successfully pdf upload to SQL Server database.";
LoadGrid();
}
}

View PDF
After user click "File Name" in GridView we need to show that PDF content, i.e. convert again those bytes into PDF readable format.

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
qstr = Request.QueryString["ID"];
//Read PDF file from DATABASE table pdf field
SqlCommand sqlcmd = new SqlCommand("Select PDFCONTENT from pdfupload where ID='" + qstr + "'", sqlcon); //use condition to retrieve particulatr PDF
sqlcon.Open();
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
b = ((byte[])dt.Rows[0][0]);
//Collect Bytes from database and write in Webpage
Response.ContentType = "application/pdf";
Response.BinaryWrite(b);
}
}
}


Complete Code:


Default.aspx

Client Side

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="0" align="center" width="600">
<tr>
<td height="30" colspan="2">
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</td>
</tr>
<tr>
<td height="30" colspan="2">
<b>PEDF Upload Retrieve Example</b>
</td>
</tr>
<tr>
<td height="30">
Select Your PDF File
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
</tr>
<tr>
<td height="30" colspan="2" align="center">
<asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />
</td>
</tr>
<tr>
<td height="30" colspan="2" align="center">
<asp:GridView ID="GridView1" runat="server" DataKeyNames="ID" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="ID">
<ItemTemplate>
<%#Eval("ID")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="PDF File Name">
<ItemTemplate>
<asp:HyperLink Target="_blank" ID="HyperLink1" runat="server" NavigateUrl=' <%# "~/Default2.aspx?ID=" + Eval("ID", "{0:d}")%> '><%#Eval("PDFNAME")%></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Server Side

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
LoadGrid();
}
Label1.Text = "";
}
void LoadGrid()
{
sqlcon.Open();
sqlcmd = new SqlCommand("select * from PDFUPLOAD",sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
sqlcon.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
//PDF Upload Code to SQL SERVER database table
if (FileUpload1.HasFile)
{
Stream fs = default(Stream);
fs = FileUpload1.PostedFile.InputStream;
BinaryReader br1 = new BinaryReader(fs);
byte[] pdfbytes = br1.ReadBytes(FileUpload1.PostedFile.ContentLength);
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("insert into PDFUPLOAD(PDFNAME,PDFCONTENT) values (@pdfname,@pdf)", sqlcon);
sqlcmd.Parameters.Add("@pdfname", FileUpload1.FileName);
sqlcmd.Parameters.Add("@pdf", pdfbytes);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
Label1.Text = "Successfully pdf upload to SQL Server database.";
LoadGrid();
}
}
}

Default2.aspx

After User click filename in the grid view redirect ID to other page Default2.aspx and write it.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
string qstr;
byte[] b = null;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
qstr = Request.QueryString["ID"];
//Read PDF file from DATABASE table pdf field
SqlCommand sqlcmd = new SqlCommand("Select PDFCONTENT from pdfupload where ID='" + qstr + "'", sqlcon); //use condition to retrieve particulatr PDF
sqlcon.Open();
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
b = ((byte[])dt.Rows[0][0]);
//Collect Bytes from database and write in Webpage
Response.ContentType = "application/pdf";
Response.BinaryWrite(b);
}
}
}
}

Output
The Output of the above code look like this
Ouput


Source code:
Download the attached source code and try to upload PDF to SQL server Database and download PDF file from SQL Server Database.
Front End: ASP.NET
Code Behind: C#

Conclusion:
I hope this article is help to know about Store and Retrieve PDF file in SQL Server.


Did you like this resource? Share it with your friends and show your love!

Responses to "How to download PDF file from SQL Server Database in ASP.NET?"
Author: scott lowson    17 Oct 2011Member Level: Bronze   Points : 1
Hi Ravindran,
Thanks for the last bit of code for opening pdf files from sql db using query string. It was exactly what I needed. I'm having a problem though and wondered if you could help at all.

I am working in VB so I converted the C# with an online translator which appears to be okay. You have a connection string called "Con", but I don't know where you've declared that so I put my full length connection string in there. This could be the problem as when I run the page I get server Error: Object reference not set to an instance of an object, and the line with connection string is erroring (Private sqlcon As New SqlClient.SqlConnection). I've attached by full VB code and any help is greatly appreciated.

Thanks again.
Scott
Bristol
UK



VB-for-PDF-output-from-SQL-DB.txt
Author: scott lowson    17 Oct 2011Member Level: Bronze   Points : 1
sent in error


Author: scott lowson    17 Oct 2011Member Level: Bronze   Points : 1
Sent in error


Author: Ravindran    21 Oct 2011Member Level: Diamond   Points : 1
Hi Scott try like this way


Dim sqlcon As New SqlConnection("Provider=SQLOLEDB;Data Source=SGEN-SQL02;Initial Catalog=KX_Live;Integrated Security=True;")



Guest Author: Jaz     28 Mar 2012
Thank you ! but how do I open/download different type of files from image field in SQL ??


Author: Ravindran    29 Mar 2012Member Level: Diamond   Points : 1
Jaz,

Option1:

During file upload time store that file type in one field. Based on that field value you can set Response.ContentType in code.

Option2:

Read first few bytes of your store image content and then set

Response.ContentType = "application/pdf"; //change that file format here

In my another article i explained how to chek uploaded file content use that concept to check file is which format
http://www.dotnetspider.com/resources/43209-How-check-Upload-file-real-Image-file-or.aspx



Guest Author: Dian     14 Aug 2012
thanx ...

this is helpfull ... :)



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India