Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Save (Upload) Files to SQL Server Database using FileUpload Control in Asp.net

Jul 3, 2015
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.


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

Save (Upload) Files to SQL Server Database using FileUpload Control in Asp.net

Download Sample Code Attached

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 RSS subscribe by email Subscribe by Email

2 comments :

Peridot Systems said...

Thanks for this informative article on using fileupload contol.

Smith Cole said...

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.

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.