In this article I will explain with an example, how to download PDF file from database in ASP.Net using C# and VB.Net.
Database
This article makes use of a table named tblFiles whose schema is defined as follows.
![Download PDF file from Database in ASP.Net]()
I have already inserted a record in the table.
![Download PDF file from Database in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download SQL file
HTML Markup
The HTML Markup consists of:
GridView –
GridView consists of a BoundField column and a TemplateField column.
BoundField – For displaying the Name of the File.
TemplateField – For displaying Download LinkButton.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Name" HeaderText="File Name" />
<asp:TemplateField ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Adding ConnectionString to the Web.Config file
You need to add the Connection String in the ConnectionStrings section of the Web.Config file in the following way.
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="constr" connectionString="Data Source=Mudassar-PC\SQL2019;Initial Catalog=dbFiles;Integrated Security=true" />
</connectionStrings>
</configuration>
Namespaces
You will need to import the following namespaces.
C#
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Configuration
Imports System.Data.SqlClient
Displaying the uploaded files in ASP.Net GridView
Inside the
Page Load event handler, the
BindGrid method is called and the GridView is populated with the records i.e. Files from the
SQL Server Database Table.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string query = "SELECT Id, Name FROM tblFiles";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Close();
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim query As String = "SELECT Id, Name FROM tblFiles"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
cmd.Connection = con
con.Open()
GridView1.DataSource = cmd.ExecuteReader()
GridView1.DataBind()
con.Close()
End Using
End Using
End Sub
Downloading PDF file from database
When the Download LinkButton is clicked, first the File ID of the file to be downloaded is fetched using the CommandArgument property of the LinkButton.
Then,the connection string is fetched from the Web.Config file and object of SqlConnection class is created using it.
And an object of SqlCommand class is created and the SELECT query is passed to it as parameter.
The values of the
ID is added as parameter to
SqlCommand object and according ID the details of the File such as
Name,
Content Type and the
Data in Byte Array are fetched from the database using
SqlDataReader.
Then, the Response class properties are set.
Properties:
1. ContentType – It informs the Browser about the file type. In this case it is PDF.
2. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.
Finally, using the BinaryWrite method of the Response class inside which Byte Array is passed as parameter, the File is written to the Response Stream using its Path and File is downloaded.
C#
protected void DownloadFile(object sender, EventArgs e)
{
int id = int.Parse((sender as LinkButton).CommandArgument);
byte[] bytes;
string fileName, contentType;
string query = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes = (byte[])sdr["Data"];
contentType = sdr["ContentType"].ToString();
fileName = sdr["Name"].ToString();
}
con.Close();
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
VB.Net
Protected Sub DownloadFile(sender As Object, e As EventArgs)
Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
Dim bytes As Byte()
Dim fileName As String, contentType As String
Dim query As String = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
cmd.Parameters.AddWithValue("@Id", id)
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
sdr.Read()
bytes = DirectCast(sdr("Data"), Byte())
contentType = sdr("ContentType").ToString()
fileName = sdr("Name").ToString()
End Using
con.Close()
End Using
End Using
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = contentType
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName)
Response.BinaryWrite(bytes)
Response.Flush()
Response.End()
End Sub
Screenshot
![Download PDF file from Database in ASP.Net]()
Downloads
