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

Upload Files to Database in Asp.net Download Files From Database in SQL Server

Jan 3, 2013
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.
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.



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
  

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

68 comments :

panda239 said...

capture error "NullReferenceException" in line GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;

panda239 said...
This comment has been removed by the author.
Anonymous said...

nice

Anonymous said...

great man...u r genious

Anonymous said...

good

Anonymous said...

very useful and informative site
keep it up...laxmi

Anonymous said...

great...

Anonymous said...

Unable to cast object of type 'System.String' to type 'System.Byte[]'.

I am getting this exception. Please give any suggestions.

Sharath Kurup
hari19113@rediffmail.com

nan said...

int fileid = Convert.ToInt32(Convert.ToBoolean(gvDetails.DataKeys[gvrow.RowIndex].Value.ToString()));


String was not recognized as a valid Boolean.

Help me with this error.

venkatesh said...

after uploading when user download it file is corrupted. please help me

Shiva Katta said...

Good post . Learning many realtime examples .

Anonymous said...

Nothing happening when clicking on Upload Button...

Jatin Waghela said...

Simply awesome!! Thank tonnes

priyanka said...

Unable to cast object of type 'System.String' to type 'System.Byte[]'..
getting this error...is any 1 there plz help me its imp....

Anonymous said...

You r Really Awesommmm..

Anonymous said...

hi

Parveez Ahmed said...

hi sir

Abhishek Yadav said...

Thank you once again ...
You and your articles ROCKS!!!!
Your tutorials and posts are really helpful for my Project...
Thank You very much Sir.. :) :) :)

Irtekaz Ahmed Khan said...

Really more benefit thanks

Anonymous said...

Thank you!
Very useful!

Anonymous said...

Superb:)

Brijesh soni said...
This comment has been removed by the author.
laiza Nisar said...

I am getting this error
SqlException (0x80131904): Operand type clash: image is incompatible with char

laiza Nisar said...

The code is really nice and helpful .
But i am getting the error
"Input string was not in a correct format" in the following line....


int fileid = Convert.ToInt32(Convert.ToBoolean(gvDetails.DataKeys[gvrow.RowIndex].Value.ToString()));


Plz help me

Lindy Jiyane said...

I had problems with this line of code anyone with a solution please help
LinkButton lnkbtn = sender asLinkButton;
it did not know the "asLinkButton"
email me at lindy.jiyane@gmail.com

deva prasad said...

its not working in vs2008

Anonymous said...

pls its not working in vs2008 give solution for it

Suresh Dasari said...

@deva prasad....
For your kind information i did this sample in vs2008 only. Please check your code i hope there is mistake in your code.

Vijay said...

Thank you very much, it helps lot

Dash0Dash said...

Hi Suresh Dasari,
I'm currently doing what you have posted here. However, i'm using Microsoft Access as my database and Oledb as connection. May i know how to do it?

Chaitanya Kumar said...

thank you so much

Gaurav A Mistry said...

its very helpful to me.
my project point of view its really good n use full article.
i need your help
how to modify this entry with update code
can you post that code how to update same data..

Anonymous said...

Good Code

sajid zafar said...

how to style the browse button of this file upload control ??? changes the text or color of browse button ???

K Samba said...

Its Great Suresh but when I'm Downloading this Files It wont showing original data how i'll convert it
Let me Know
tahnk you

Muthu kumar said...

int fileid = Convert.ToInt32(gvDetails.DataKeys[gvrow.RowIndex].Value.ToString());

I got the error message such as (Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index)

Anybody pls solve my problem.

Anonymous said...

plz i want to show the Download file In Html Fromat on browser what i have to Do Plz tell me
9990185725

venky venkatesh said...

hello sir,
when i click on upload btn. its getting uploaded and when i click on download link its showing that."DOWNLOAD IS COMPLETED.OPEN OR SAVE?" WHEN I CLICK ON OPEN its showing a default.aspx page.

y like this?

Anonymous said...

I never post on blogs, but I just had to for this one. You saved me a ton of headache. Thanks sooooooo much!!

Sedia Kayu Asli Kalimantan said...

'Upload' is already declared as 'Protected WithEvents Upload As System.Web.UI.WebControls.Button' in this class.

what should i do with this error? thanks for answer

Anonymous said...

i need a counting code

Anonymous said...

i need a words lines and space count coding for c# pls give me today.

Anonymous said...

Thanks for help me...

Anonymous said...

It is superb. Please keep uploading new codes.
Thank you !

Anonymous said...

all your posts are very useful ...thanks

Anonymous said...

grate sir ur example are very usefull for beginners developer thanx u 1nc again

Anonymous said...

well

Anonymous said...

Thank you! It Worked !!

anbu johni said...

your work is amesing and really congratz bro...
pls help me during the runing time ,Nothing happening when clicking on Upload Button... pls tel me how to solve this problem

anbu johni said...

your work is amesing and really congratz bro...
pls help me during the runing time ,Nothing happening when clicking on Upload Button... pls tel me how to solve this problem... i am using this code in my project so reivew date tommorrow after so pls reply to ksanbutnj@gmail.com

anbu johni said...

your work is amesing and really congratz bro...
pls help me during the runing time ,Nothing happening when clicking on Upload Button... pls tel me how to solve this problem... i am using this code in my project so reivew date tommorrow after so pls reply to ksanbutnj@gmail.com

Anonymous said...

Uncast of object type system.string to type system.byte[].
Help me with dis error

Anonymous said...

How to implement this article when you want to insert other TextBox field data in the database, using a single table?
For example in your table, you add another Column with Nvarchar data type?

praveena meenu said...
This comment has been removed by the author.
Anonymous said...

Hi,
I want to open this Word File created in Read Only mode, can you please suggest solution for this?

Manish Rathod said...

Thanx a tonn.....Always works

Ruchi Tandel said...

thanks a lot...it superb

VtBear said...

On Con.Open() I am getting "SqlException was unhandled by user code"

ateeq ahmed said...

Its good ,thks

ARMAAN said...

Sir, Good Blog But I need Upload File Without Refreshing Page and When download Also Need without Refreshing.. Pls Write this blog completely

Utkarsh Walke said...

Its a very nice article. I have done with my stuff using this without getting any error. Thanks alot!!

Tamil Selvan said...

hi i using your code but this error occured.

'lnkDownload_Click' is not a member of 'ASP.file_upload_aspx'

abdul gaffar said...
This comment has been removed by the author.
Ashwini Gangadhar said...

Superb ya thank u so much Bt @last the file content ll b appearing on d web page...instead i wanna download tat file to some folder...hoow it can b done??

abdul gaffar said...

Thanks the code works fine.But what size in MB's does it saves to database.

VtBear said...

This worked beautifully but how can I delete the file from the folder without having to into the folder to remove the file then the table to remove the data?

Suresh Dasari said...

to delete files from folder check this article

http://www.aspdotnet-suresh.com/2012/12/aspnet-delete-file-from-folder-using-c.html

prakash singh said...

Nice ..blog thanks for share it.

Give your Valuable Comments

Other Related Posts

© 2010-2012 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.