Introduction:
Here I will explain how to save and retrieve files from SQL Server database in asp.net using C#, VB.NET or save / insert / upload image or word document files into SQL Server database and download files from database in asp.net using C#, VB.NET.
Here I will explain how to save and retrieve files from SQL Server database in asp.net using C#, VB.NET or save / insert / upload image or 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. Now I will explain how to save or upload files into SQL Server database and retrieve or download files from database in asp.net using C#, VB.NET.
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. Now I will explain how to save or upload files into SQL Server database and retrieve or 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 Email
|
|||
1 comments :
Thank you suresh,
But i need to upload multiple files at a time into database.
we are using visual studio 2010 so that is why i'm unable to do this.
i have already tried your JQuery post that is good but need to save in db.
please send me any suggestion on rajkumar.palnati@gmail.com
i am regular follower to this site it helps me a lot. Thanks you very much brother.
Thanks
Regards
Rajkumar Palnati
Note: Only a member of this blog may post a comment.