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


Store any file into SQL Server by C#.Net

Introduction


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.

Thank You.

Reference: http://msahoo.wordpress.com/2009/07/23/how-to-store-a-file-in-sql-server-2005-database-using-asp-net-c-net/


Related Articles

More articles: Save file SQL SQL Server 2005

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: