Upload Files to Database in Asp.net Download Files From Database in SQL Server
Introduction:
Here I will explain how to save or insert or upload word document files into SQL Server database and download files from database in asp.net using C#, VB.NET.
Here I will explain how to save or insert or upload word document files into SQL Server database and download files from database in asp.net using C#, VB.NET.
Description:
In previous articles I explained Save/Upload files in folder and Download files from folder, Save Images in folder and images path in database and show images from folder and many articles relating to asp.net,C#, VB.NET etc…. Now I will explain how to upload word document files into SQL Server database and download files from database in asp.net using C#, VB.NET.
In previous articles I explained Save/Upload files in folder and Download files from folder, Save Images in folder and images path in database and show images from folder and many articles relating to asp.net,C#, VB.NET etc…. Now I will explain how to upload word document files into SQL Server database and download files from database in asp.net using C#, VB.NET.
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();
}
}
}
}
|
VB.NET Code
Imports System.Data.SqlClient
Imports System.IO
Imports System.Web.UI.WebControls
Partial Class VBCode
Inherits System.Web.UI.Page
Private strCon As String = "Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewData()
End If
End Sub
' Bind Gridview Data
Private Sub BindGridviewData()
Using con As New SqlConnection(strCon)
Using cmd As New SqlCommand()
cmd.CommandText = "select * from FileInformation"
cmd.Connection = con
con.Open()
gvDetails.DataSource = cmd.ExecuteReader()
gvDetails.DataBind()
con.Close()
End Using
End Using
End Sub
' Save files to Folder and files path in database
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim filename As String = Path.GetFileName(fileUpload1.PostedFile.FileName)
Dim str As Stream = fileUpload1.PostedFile.InputStream
Dim br As New BinaryReader(str)
Dim size As [Byte]() = br.ReadBytes(CInt(str.Length))
Using con As New SqlConnection(strCon)
Using cmd As 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()
End Using
End Using
End Sub
' This button click event is used to download files from gridview
Protected Sub lnkDownload_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim lnkbtn As LinkButton = TryCast(sender, LinkButton)
Dim gvrow As GridViewRow = TryCast(lnkbtn.NamingContainer, GridViewRow)
Dim fileid As Integer = Convert.ToInt32(gvDetails.DataKeys(gvrow.RowIndex).Value.ToString())
Dim name As String, type As String
Using con As New SqlConnection(strCon)
Using cmd As New SqlCommand()
cmd.CommandText = "select FileName, FileType, FileData from FileInformation where Id=@Id"
cmd.Parameters.AddWithValue("@id", fileid)
cmd.Connection = con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
Response.ContentType = dr("FileType").ToString()
Response.AddHeader("Content-Disposition", "attachment;filename=""" & Convert.ToString(dr("FileName")) & """")
Response.BinaryWrite(DirectCast(dr("FileData"), Byte()))
Response.[End]()
End If
End Using
End Using
End Sub
End Class
| |
Demo
 |
Download sample code attached
No comments:
Post a Comment