Download Source Code and Database
In this blog, We will learn Upload and Download Files From SQL Server Database In Asp.Net C#.
In this article, I will explain with an example, how to Upload and Download Files from SQL Server Database in ASP.Net C# using FileUpload control and then download saved files from the SQL Server database using GridView control.
Step by step learn Upload and Download Files From SQL Server Database In Asp.Net C#.
Step 1:Database
For this blog i have created a Database and Simple Table with the four column “ImageId”,”Image”,”ContentType” and “Data”.
[php]
Create Database FileUpload_Demo1
use FileUpload_Demo1
Create table tblFileUpload(
ImageId int primary key identity(1,1),
ContentType nvarchar(250),
Image varchar(50),
Data varbinary(Max)
)
[/php]
Step 2:Stored Procedure For Insert, Retrieve and Download File
Create Stored procedure for Insert data into Database.
[php]
create proc sp_tblFileUpload_Insert
@ContentType nvarchar(250),
@Image varchar(50),
@Data varbinary(Max)
as
begin
insert into tblFileUpload(ContentType,Image,Data) values(@ContentType,@Image,@Data)
end
[/php]
Create Stored procedure for retrieve Data from the database.
[php]
create proc [dbo].[sp_tblFileUpload_Get]
as
begin
select * from tblFileUpload
end
[/php]
Create Stored procedure for Download selected file.
[php]
create proc sp_fileDownload
@ImageId int
as
begin
select Image, Data, ContentType from tblFileUpload where ImageId=@ImageId
end
[/php]
Step 3:Now Open Visual Studion and create the project as:
“Start” –> “All Programs”- “Microsoft Visual Studio 2012”.
“File” – “New Project” – “C#” – “Empty Project” (to avoid adding a master page).
Give the Project name such as “FileUpload_Demo” or another as you wish and specify the location.
Then right-click on Solution Explorer – “Add New Item” – “Default.aspx” page.
Step 4: Go back to your website in Solution Explorer and Add New Folder and Give name as – New Folder, then Uploads.
Step 5:Open Default.aspx page and write this code.
In “Default.aspx” page drag and drop one of an ASP.Net FileUpload control, and one Button and one Gridview control. The Button has been assigned a Click event handler.
FileUpload and Button used to upload and save the files to the database and an ASP.Net GridView control basically used to display the uploaded files and also to allow the user to download the file saved in the database. The ID of the File is bound to the CommandArgument property of the LinkButton. It will later be used in our project to download the selected file.
[php]
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:FileUpload ID="fuimage" runat="server"></asp:FileUpload>
</td>
<td></td>
</tr>
<tr>
<td>
<asp:Button ID="btnUploadImage" runat="server" Text="Upload"
OnClick="btnUploadImage_Click" /></td>
</tr>
<tr>
<td>
<asp:GridView ID="grdBindFiles" runat="server" AutoGenerateColumns="False" >
<Columns>
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="img" runat="server" Width="40px" Height="40px" ImageUrl='<%#Eval("Image","~/Uploads/{0}") %>’ />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="linkDownloadFile" runat="server" Text="Download" OnClick="linkDownloadFile_Click" CommandArgument='<%#Eval("ImageId") %>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
[/php]
Step 4: Open web.config file and Establish a connection with database
Key Point:
- In initial catalog write Database Name
- In data source you can simply write (.) /your server name/(local)
- Integrated security for Authentication
[php]
<connectionStrings>
<add name="DBCS" connectionString="data source=.;initial catalog=FileUpload_Demo1;integrated security=true"/>
</connectionStrings>
[/php]
Step 5:Open Default.aspx.cs file to write code for Upload and Download Files From SQL Server Database In Asp.Net C#.
Default.aspx.cs
[php]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//Need to Import these namespace
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
namespace FileUpload_Demo
{
public partial class Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
GridBindFile();
}
/// <summary>
/// Uploading the files and then saving in to the SQL Server Database table
///The below event handler gets fired when the Upload Button is clicked,
///it simply saves the file as Binary data format in the SQL Server Database.
///The name of the file, the content type (MIME type) and the actual file as an array of bytes are
///inserted into the database table "tblFileUpload".
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUploadImage_Click(object sender, EventArgs e)
{
string FN = "";
FN = Path.GetFileName(fuimage.PostedFile.FileName);
string contentType = fuimage.PostedFile.ContentType;
Stream fs = fuimage.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
byte[] bytes = br.ReadBytes((Int32)fs.Length);
con.Open();
SqlCommand cmd = new SqlCommand("sp_tblFileUpload_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@empid", btnUploadImage.Text == "Save" ? 0 : ViewState["Eid"]);
if (FN != "")
{
fuimage.SaveAs(Server.MapPath("Uploads" + "\\" + FN));
cmd.Parameters.AddWithValue("@Image", FN);
cmd.Parameters.AddWithValue("@ContentType", contentType);
cmd.Parameters.AddWithValue("@Data", bytes);
}
cmd.ExecuteNonQuery();
con.Close();
GridBindFile();
}
//Displaying the uploaded files from Database Table in ASP.Net GridView Control
public void GridBindFile()
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_tblFileUpload_Get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
grdBindFiles.DataSource = ds;
grdBindFiles.DataBind();
}
}
/// <summary>
/// Downloading selected file from Database Table using the Download Button in GridView
///The below event handler is fired when the Download LinkButton is clicked inside the GridView Row.
///When user click on the row of the gridview control the ID of the File is determined using the
///CommandArgument property of the LinkButton
///and then the File data is fetched from the database like ("Image, Contenttype, Data")
///Once the data fetching process is completed the file is sent to the browser for downloading using the
///Response Stream.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void linkDownloadFile_Click(object sender, EventArgs e)
{
int ImageId = int.Parse((sender as LinkButton).CommandArgument);
byte[] bytes;
string fileName, contentType;
con.Open();
SqlCommand cmd = new SqlCommand("sp_fileDownload", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ImageId", ImageId);
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
fileName = sdr["Image"].ToString();
bytes = (byte[])sdr["Data"];
contentType = sdr["ContentType"].ToString();
con.Close();
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
}
}
[/php]
Demo Step By Step
Screenshot describe the output.
Any Question and Suggestion related to this Upload and Download Files From SQL Server Database In Asp.Net C#.
Please comment me.
