Introduction:
In this article I will explain how to save or upload word document files into SQL Server database and download files from database in asp.net.
In this article I will explain how to save or upload word document files into SQL Server database and download files from database in asp.net.
Implementation:
First
of all create a database ex. “Test” and then create a table”File_info”.
Column
Name
|
Data
Type
|
Allow
Nulls
|
File_Id
|
int(set identity property=true)
|
No
|
FileName
|
varchar(50)
|
Yes
|
FileType
|
varchar(50)
|
Yes
|
FileData
|
varbinary(MAX)
|
Yes
|
Design Section:
Design your page as given below:
<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">
<fieldset style="width:450px">
<legend><strong>Upload
and Download Word Files from Database in asp.net</strong></legend>
<div>
<br />
<asp:FileUpload ID="fileUpload1"
runat="server"
/><br /><br />
<asp:Button ID="btnUpload"
runat="server"
Text="Upload"
onclick="btnUpload_Click"
/><br /><br />
</div>
<div>
<asp:GridView ID="gvDetails"
runat="server"
AutoGenerateColumns="false"
DataKeyNames="file_id">
<HeaderStyle BackColor="#df5015"
Font-Bold="true"
ForeColor="White"
/>
<Columns>
<asp:BoundField DataField="file_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>
<br />
</div></fieldset>
</form>
</body>
</html>
Asp.Net Code (Code behind using C#)
Write following code in code behind file:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI.WebControls;
public partial class
File_Info : System.Web.UI.Page
{
string strCon = "Data
Source=localhost;Integrated Security=true;Initial Catalog=test";
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 File_Info";
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 File_Info(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 File_Info
where file_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
Write following code in code
behind file:
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=localhost;Integrated Security=true;Initial Catalog=test"
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 File_Info"
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 File_Info(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 File_Info where File_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
0 comments:
Post a Comment