Advertisement
In this article I will explain how to
upload and download file in database in asp.net
I Created a table in SQL server as
shown
USE
[Test]
GO
/******
Object: Table [dbo].[tbl_fileuploads] Script Date: 10/29/2013
04:38:32 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_PADDING
ON
GO
CREATE
TABLE
[dbo].[tbl_fileuploads](
[ID]
[int] IDENTITY(1,1)
NOT
NULL,
[FileName]
[nvarchar](max)
NOT
NULL,
[FileData]
[varbinary](max)
NOT
NULL,
CONSTRAINT
[PK_tbl_fileuploads] PRIMARY
KEY
CLUSTERED
(
[ID]
ASC
)WITH
(PAD_INDEX
=
OFF,
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON
[PRIMARY]
)
ON
[PRIMARY]
GO
SET
ANSI_PADDING
OFF
GO
Now I am creating an empty solution
with name DBFiles for that I am adding a webform with name
Files.aspx.
I am designing the UI in Files.aspx
<%@
Page
Language="C#"
AutoEventWireup="true"
CodeBehind="Files.aspx.cs"
Inherits="DBFiles.Files"
%>
<!DOCTYPE
html
PUBLIC
"-//W3C//DTD
XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html
xmlns="http://www.w3.org/1999/xhtml">
<head
runat="server">
<title></title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<div
>
<asp:FileUpload
ID="FileUpload1"
runat="server"
/>
<asp:Button
ID="btnUpload"
runat="server"
Text="Upload"
OnClick="UploadFile"
/>
<asp:Label
ID="lblerr"
runat="server"
Style="color:
Red;"></asp:Label>
</div>
<asp:GridView
ID="GridView1"
runat="server"
AutoGenerateColumns="False"
>
<Columns>
<asp:TemplateField
HeaderText="S.No">
<ItemTemplate>
<asp:Label
ID="lblSRNO"
runat="server"
Text='<%#Container.DataItemIndex+1
%>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField
DataField="FileName"
HeaderText="Name"
InsertVisible="False"
ReadOnly="True"
SortExpression="FileName"
/>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton
ID="lnkDownload"
runat="server"
Text="Download"
OnClick="DownloadFile"
CommandArgument='<%#
Eval("ID") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField
HeaderText="Action">
<ItemTemplate>
<asp:LinkButton
ID="lnkDelete"
Text="Delete"
OnClientClick="return
confirm('Are you sure to Delete this Document')"
CommandArgument='<%#
Eval("ID") %>'
runat="server"
OnClick="DeleteFile"
/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Now I am writing the logic for
uploading,downloading and deleting files from database in
Files.aspx.cs
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data.SqlClient;
using
System.Data;
using
System.Configuration;
using
System.IO;
namespace
DBFiles
{
public
partial
class
Files
: System.Web.UI.Page
{
protected
void
Page_Load(object
sender, EventArgs
e)
{
if
(!IsPostBack)
{
BindGridData();
}
}
private
void
BindGridData()
{
SqlConnection
connection = new
SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString);
SqlCommand
command = new
SqlCommand("SELECT
ID,FileName,FileData from [tbl_fileuploads]",
connection);
SqlDataAdapter
daimages = new
SqlDataAdapter(command);
DataTable
dt = new
DataTable();
daimages.Fill(dt);
GridView1.DataSource
= dt;
GridView1.DataBind();
}
protected
void
UploadFile(object
sender, EventArgs
e)
{
string
fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string
fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string
documentType = string.Empty;
int
fileSize = FileUpload1.PostedFile.ContentLength;
byte[]
documentBinary = new
byte[fileSize];
FileUpload1.PostedFile.InputStream.Read(documentBinary,
0, fileSize);
SqlConnection
con = new
SqlConnection();
con.ConnectionString
=
ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString;
SqlCommand
cmd = new
SqlCommand();
cmd.CommandText
= "INSERT
INTO tbl_fileuploads(FileName,FileData)"
+ "
VALUES (@FileName,@FileData)";
cmd.CommandType
= CommandType.Text;
cmd.Connection
= con;
SqlParameter
FileName = new
SqlParameter("@FileName",
SqlDbType.VarChar,
50);
FileName.Value
= fileName.ToString();
cmd.Parameters.Add(FileName);
SqlParameter
uploadedDocument = new
SqlParameter("@FileData",
SqlDbType.Binary,
fileSize);
uploadedDocument.Value
= documentBinary;
cmd.Parameters.Add(uploadedDocument);
con.Open();
int
result = cmd.ExecuteNonQuery();
con.Close();
BindGridData();
}
protected
void
DownloadFile(object
sender, EventArgs
e)
{
int
id = int.Parse((sender
as
LinkButton).CommandArgument);
byte[]
bytes;
string
fileName;
using
(SqlConnection
con = new
SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString))
{
using
(SqlCommand
cmd = new
SqlCommand())
{
cmd.CommandText
= "select
FileName,FileData from tbl_fileuploads where ID=@id";
cmd.Parameters.AddWithValue("@ID",
id);
cmd.Connection
= con;
con.Open();
using
(SqlDataReader
sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes
= (byte[])sdr["FileData"];
fileName
= sdr["FileName"].ToString();
}
con.Close();
}
}
Response.Clear();
Response.Buffer
= true;
Response.Charset
= "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.AppendHeader("Content-Disposition",
"attachment;
filename="
+ fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
protected
void
DeleteFile(object
sender, EventArgs
e)
{
SqlConnection
con = new
SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString);
int
id = int.Parse((sender
as
LinkButton).CommandArgument);
SqlCommand
cmd = new
SqlCommand("delete
from tbl_fileuploads where id = @Id",
con);
cmd.Parameters.AddWithValue("@Id",
id);
cmd.CommandType
= CommandType.Text;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindGridData();
}
}
}
In Web.Config I am adding the
connectionstring
<connectionStrings>
<add
name="Connstring"
connectionString="Data
Source=DotNetSharePoint;Database=Test;User
Id=sa;Password=password123;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
Press F5,Now we can see the OutPut as .
0 comments:
Post a Comment