ASP.Net C#.Net

How to store a file in SQL Server 2005 database using ASP.Net & C#.Net

5
(1)

In this post i explain about how to store a file into Microsoft SQL Server 2005 database using ASP.Net and C#.Net. First I create a database table in which we are going to store the file content and details. Then create a new Website with a sample webform file(default.aspx) which contains one upload control and a button. When we click on the button after selecting the file it will save the file details and content into database. Then we have to create a Generic Handler(.ashx) file which will fetch the data from database and display them to user.

Step 1: Now create a table with name File in your database with following fields:

Column Name           Data Type
ID                          uniqueidentifier
FileName              nvarchar(150)
FileType                nvarchar(100)
FileSize                 int
FileContent          varbinary(MAX)

Step 2: Create a new WebForm in your website with name default.aspx and add the following code:

<%@ Page Language="C#" AutoEventWireup="false" CodeFile="default.aspx.cs" Inherits="default" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title>Home Page</title> 
</head> 
<body> 
<form id="form1" runat="server"> 
<asp:FileUpload runat="server" ID="FileUpload1" /> 
<asp:Button ID="btnSave" runat="server" Text="SaveToDB" OnClick="btnSave_Click" />
<asp:HyperLink runat="server" ID="Result" Target="_blank" /> 
</form> 
</body> 
</html>

Step 3: Write the following code in default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public partial class default : System.Web.UI.Page
{
 void btnSave_Click(object sender, EventArgs e)
 {
 if (!FileUpload1.HasFile) return;
 Guid Id = Guid.NewGuid();// generate new Id
 using (SqlCommand command = new SqlCommand())
 {
 command.Connection = new SqlConnection(ConfigurationManager.AppSettings["sqlconnstr"].ToString());
 command.CommandText = @"insert into [File](ID, FileName, FileType, FileSize, FileContent)
 values(@Id, @FileName, @FileType, @FileSize, @FileContent)";
 command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
 command.Parameters.Add("@FileName", SqlDbType.NVarChar, 150).Value = Path.GetFileName(FileUpload1.PostedFile.FileName);
 command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = FileUpload1.PostedFile.ContentType;
 command.Parameters.Add("@FileSize", SqlDbType.Int).Value = FileUpload1.PostedFile.ContentLength;
 // filecontent, convert from stream to byte array
 byte[] fileContent = new byte[FileUpload1.PostedFile.ContentLength];
 FileUpload1.PostedFile.InputStream.Read(fileContent, 0, FileUpload1.PostedFile.ContentLength);
 command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;
 command.Connection.Open();
 command.ExecuteNonQuery();
 }
 // show result through generic handler
 Result.NavigateUrl = "ShowFile.ashx?Id=" + Id.ToString();
 Result.Text = "Click here to view the uploaded file";
 }
}

Step 4: Then create a Generic Handler named as ShowFile.ashx and write the below code onto that.

<%@ WebHandler Language=”C#” %>

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class ShowFile : IHttpHandler {
 public void ProcessRequest (HttpContext context) 
 {
 Guid Id = new Guid(context.Request.QueryString["Id"]);
 using (SqlCommand cmd = new SqlCommand())
 {
 cmd.Connection = new SqlConnection(ConfigurationManager.AppSettings["sqlconnstr"].ToString());
 cmd.CommandText = "select * from [TableName] where ID = @Id";
 cmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
 cmd.Connection.Open();
 SqlDataReader sdr = cmd.ExecuteReader();
 if (sdr.Read())
 {
 context.Response.Clear();
 context.Response.ContentType = (string)sdr["FileType"];
 context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", sdr["FileName"].ToString()));
 context.Response.AddHeader("Content-Length", sdr["FileSize"].ToString());
 context.Response.BinaryWrite((byte[])sdr["FileContent"]);
 context.Response.End();
 }
 }

 }
 public bool IsReusable {
 get {
 return false;
 }
 }

}

Step 5: Run the Website now.

How useful was this post?

Click on a star to rate it!

Leave a Reply

Your email address will not be published. Required fields are marked *