Introduction:
Here I will explain how to save files to sql server database using file upload control in asp.net and retrieve files from sql server database in asp.net gridview control using c#, vb.net with example or save / upload files to database and load it from database and display it in asp.net gridview using c#, vb.net.
Description:
In previous articles I explained save & retrieve images from database with handler in asp.net, jQuery upload multiple files using handler file in asp.net, display images from database path in asp.net gridview, save images in folder and path in database display it in asp.net gridview, gridview examples in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to save and retrieve files from sql server database in asp.net gridview control using c#, vb.net with example.
In previous articles I explained save & retrieve images from database with handler in asp.net, jQuery upload multiple files using handler file in asp.net, display images from database path in asp.net gridview, save images in folder and path in database display it in asp.net gridview, gridview examples in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to save and retrieve files from sql server database in asp.net gridview control using c#, vb.net with example.
Before
implement this example first design one table fileinfo in your database as
shown below
Column Name
|
Data Type
|
Allow Nulls
|
id
|
int(IDENTITY=TRUE)
|
NO
|
filename
|
varchar(50)
|
Yes
|
filetype
|
varchar(50)
|
Yes
|
filedata
|
varbinary(MAX)
|
Yes
|
Once
table created in database that would be like as shown below
 |
Now
open your aspx page and write the code like as shown below
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1">
<title>Save Files to SQL
Server Database using FileUpload Control</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida
Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif;
color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color:
#df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</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 class="GridviewDiv">
<asp:GridView ID="gvDetails"
CssClass="Gridview"
runat="server"
AutoGenerateColumns="false"
DataKeyNames="Id">
<HeaderStyle CssClass="headerstyle"
/>
<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>
|
Now add following namespaces in codebehind file
C#
Code
using System;
using System.Data.SqlClient;
using System.IO;
using
System.Web.UI.WebControls;
|
After completion of adding namespaces you need to write the
code like as shown below
string strCon = "Data Source=Suresh;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
fileinfo";
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 file =
fileUpload1.PostedFile.FileName;
string filename = Path.GetFileName(file);
string extension = Path.GetExtension(file);
string filetype = string.Empty;
Stream str =
fileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(str);
Byte[] size =
br.ReadBytes((int)str.Length);
switch (extension) {
case ".jpg":
filetype = "image/jpg";
break;
case ".jpeg":
filetype = "image/jpg";
break;
case ".png":
filetype = "image/png";
break;
case ".gif":
filetype = "image/gif";
break;
case ".pdf":
filetype = "application/pdf";
break;
case ".doc":
filetype = "application/vnd.ms-word";
break;
case ".docx":
filetype = "application/vnd.ms-word";
break;
case ".xls":
filetype = "application/vnd.ms-excel";
break;
case ".xlsx":
filetype = "application/vnd.ms-excel";
break;
}
using (SqlConnection con = new
SqlConnection(strCon))
{
using (SqlCommand cmd = new
SqlCommand())
{
cmd.CommandText = "insert into
fileinfo(filename,filetype,filedata) values(@Name,@Type,@Data)";
cmd.Parameters.AddWithValue("@Name",
filename);
cmd.Parameters.AddWithValue("@Type",
filetype);
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 FileInfo 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=Suresh;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
fileinfo"
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 file As String =
fileUpload1.PostedFile.FileName
Dim filename As String = Path.GetFileName(file)
Dim extension As String = Path.GetExtension(file)
Dim filetype As String = String.Empty
Dim str As Stream =
fileUpload1.PostedFile.InputStream
Dim br As New BinaryReader(str)
Dim size As [Byte]() =
br.ReadBytes(CInt(str.Length))
Select Case extension
Case ".jpg"
filetype = "image/jpg"
Exit Select
Case ".jpeg"
filetype = "image/jpg"
Exit Select
Case ".png"
filetype = "image/png"
Exit Select
Case ".gif"
filetype = "image/gif"
Exit Select
Case ".pdf"
filetype = "application/pdf"
Exit Select
Case ".doc"
filetype = "application/vnd.ms-word"
Exit Select
Case ".docx"
filetype = "application/vnd.ms-word"
Exit Select
Case ".xls"
filetype = "application/vnd.ms-excel"
Exit Select
Case ".xlsx"
filetype = "application/vnd.ms-excel"
Exit Select
End Select
Using con As New SqlConnection(strCon)
Using cmd As New SqlCommand()
cmd.CommandText = "insert into
fileinfo(filename,filetype,filedata) values(@Name,@Type,@Data)"
cmd.Parameters.AddWithValue("@Name",
filename)
cmd.Parameters.AddWithValue("@Type",
filetype)
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 FileInfo 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="""
+ dr("FileName") + """")
Response.BinaryWrite(DirectCast(dr("FileData"), Byte()))
Response.[End]()
End If
End Using
End Using
End Sub
End Class
|
Now run your application and check output that would be
like as shown in following output
Demo
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
3 comments :
Thanks for this informative article on using fileupload contol.
Hi Suresh,
Nice post. I read it.I don't know about this feature of ASP.net. Thanks to share this informative post. I know somewhat about ASP.net 4.5 from "Myasp.net" when I hosted my website on this site.
hi
when i try to upload a file to database as per example only first letter of file can be stored so i cant get file name properly.. can you pls tell me what i want to do for write full name of file
thanks in advance
Note: Only a member of this blog may post a comment.