Home  »  Articles  »  ASP.Net  »

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

 Posted By : Manoranjan SahooPosted Date : 15/10/2010 02:31:28 PM Category : ASP.Net Points : 8 (Rs 4.00)

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.

Download Full Source Code Here

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.

Download Full Working Source Code from below links :

1. http://www.filesonic.com/file/1033223344/File_Save_Into_SQL_DB.zip

2. http://www.fileserve.com/file/Rbbs8E3

3. http://uploading.com/files/2a6e2c93/File%2BSave%2BInto%2BSQL%2BDB.zip/

Thank You.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.