In this article I will explain with an example, how to download Excel (.xls and .xlsx) 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.
![Save (Insert) Excel file in Database in ASP.Net]()
I have already inserted a record in the table.
![Save (Insert) Excel file in 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 the 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 Excel from database
When the Download LinkButton is clicked, first the 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 to 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 Excel.
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 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 Excel file from Database in ASP.Net]()
Downloads
