How to download PDF file from SQL Server Database in ASP.NET?
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
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.
Hi Scott try like this way