Tuesday 18 June 2013

Upload Files to Database in Asp.net Download Files From Database in SQL Server

Upload Files to Database in Asp.net Download Files From Database in SQL Server



To implement this first design table in your database like below to save file details in database.
Column Name
Data Type
Allow Nulls
Id
int(set identity property=true)
No
FileName
varchar(50)
Yes
FileType
varchar(50)
Yes
FileData
varbinary(MAX)
Yes
Once table creation completed open visual studio and create new website. After that write the following code in your aspx page like this

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1">
<title>Upload Word Files to Database and Download files from database in asp.net
</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="fileUpload1" runat="server" /><br />
<asp:Button ID="btnUpload" runat="server" Text="Upload" onclick="btnUpload_Click" />
</div>
<div>
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" DataKeyNames="Id">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="FileName" HeaderText="FileName" />
<asp:TemplateField HeaderText="FilePath">
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="lnkDownload_Click"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
After completion of aspx page design add the following namespaces in code behind

C# Code


using System;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI.WebControls;
After that write the following code in code behind


string strCon = "Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewData();
}
}
// Bind Gridview Data
private void BindGridviewData()
{
using (SqlConnection con=new SqlConnection(strCon))
{
using (SqlCommand cmd=new SqlCommand())
{
cmd.CommandText = "select * from FileInformation";
cmd.Connection = con;
con.Open();
gvDetails.DataSource = cmd.ExecuteReader();
gvDetails.DataBind();
con.Close();
}
}
}
// Save files to Folder and files path in database
protected void btnUpload_Click(object sender, EventArgs e)
{
string filename = Path.GetFileName(fileUpload1.PostedFile.FileName);
Stream str = fileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(str);
Byte[] size = br.ReadBytes((int) str.Length);
using (SqlConnection con=new SqlConnection(strCon))
{
using (SqlCommand cmd=new SqlCommand())
{
cmd.CommandText = "insert into FileInformation(FileName,FileType,FileData) values(@Name,@Type,@Data)";
cmd.Parameters.AddWithValue("@Name", filename);
cmd.Parameters.AddWithValue("@Type""application/word");
cmd.Parameters.AddWithValue("@Data", size);
cmd.Connection =con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindGridviewData();
}
}
}
// This button click event is used to download files from gridview
protected void lnkDownload_Click(object sender, EventArgs e)
{
LinkButton lnkbtn = sender as LinkButton;
GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;
int fileid = Convert.ToInt32(gvDetails.DataKeys[gvrow.RowIndex].Value.ToString());
string name, type;
using (SqlConnection con=new SqlConnection(strCon))
{
using (SqlCommand cmd=new SqlCommand())
{
cmd.CommandText = "select FileName, FileType, FileData from FileInformation where Id=@Id";
cmd.Parameters.AddWithValue("@id", fileid);
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
Response.ContentType = dr["FileType"].ToString();
Response.AddHeader("Content-Disposition""attachment;filename=\"" +dr["FileName"] + "\"");
Response.BinaryWrite((byte[])dr["FileData"]);
Response.End();
}
}
}
}

Demo

No comments:

Post a Comment