Saturday, April 12, 2008

How to store files in a MS-SQL Server database using ASP.NET

In this post we will take a look at how to store data in a MS-SQL Server database using ASP.NET. We will start by creating the database table in which we will store the actual file data, and then create a new Web Form with a file upload control and an upload button which will save the uploaded file to the database. After that we need to create a generic HTTP Handler which will be responsible for flushing out the file to the client. The reason to why we will use a HTTP Handler for this is that we don't need all the functionality that the System.Web.UI.Page provides, so having both performance and simplicity in mind a HTTP Handler is a better choice.

1. Start by creating a table named File in your database, add the following fields to it:

2. Create a new Web Form, give it the name UploadFile.aspx and add the following code:


C#:
<%@ Page Language="C#" AutoEventWireup="false" CodeFile="UploadFile.aspx.cs" Inherits="UploadFile" %>
<!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>Upload file</title>
</head>
<body>
    <form id="form1" runat="server">
 <asp:FileUpload runat="server" ID="ctrFile" /> <asp:Button ID="btnUploadFile" runat="server" Text="Upload" />
 <asp:HyperLink runat="server" ID="ctrResult" Target="_blank" />
    </form>
</body>
</html>

VB.NET:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="FileUpload.aspx.vb" Inherits="FileUpload" %>
<!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>Upload file</title>
</head>
<body>
<form id="form1" runat="server">
 <asp:FileUpload runat="server" ID="ctrFile" /> <asp:Button ID="btnUploadFile" runat="server" Text="Upload" />
 <asp:HyperLink runat="server" ID="ctrResult" Target="_blank" />
</form>
</body>
</html>

UploadFile.aspx.cs:


C#:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

/// 
/// Handles uploading- and storing a file in a database.
/// 
public partial class UploadFile : System.Web.UI.Page
{
 protected override void OnInit(EventArgs e)
 {
  base.OnInit(e);

  btnUploadFile.Click += new EventHandler(btnUploadFile_Click);
 }
 void btnUploadFile_Click(object sender, EventArgs e)
 {
  // exit if file-upload has no file
  if (!ctrFile.HasFile) return;
  
  // generate new fileId
  Guid fileId = Guid.NewGuid();
  // create insert query
  using (SqlCommand command = new SqlCommand())
  {
   command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
   command.CommandText = @"insert into [File](FileId, FileName, FileType, FileSize, FileContent) 
      values(@FileId, @FileName, @FileType, @FileSize, @FileContent)";
   command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId;
   command.Parameters.Add("@FileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(ctrFile.PostedFile.FileName);
   command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = ctrFile.PostedFile.ContentType;
   command.Parameters.Add("@FileSize", SqlDbType.Int).Value = ctrFile.PostedFile.ContentLength;
   // filecontent, convert from stream to byte array
   byte[] fileContent = new byte[ctrFile.PostedFile.ContentLength];
   ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength);
   command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;
   command.Connection.Open();
   command.ExecuteNonQuery();
  }
  // show result
  ctrResult.NavigateUrl = "FileStream.ashx?FileId=" + fileId.ToString();
  ctrResult.Text = "Click here to view the uploaded file";
 }
}

VB.NET:

Imports System Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Imports System.IO ''' ''' Handles uploading- and storing a file in a database. ''' Partial Class UploadFile Inherits System.Web.UI.Page Protected Sub btnUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUploadFile.Click ' exit if file-upload has no file If Not ctrFile.HasFile Then Exit Sub ' generate new fileId Dim fileId As Guid = Guid.NewGuid() ' create insert query Dim command As New SqlCommand() Try command.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) command.CommandText = "insert into [File](FileId, FileName, FileType, FileSize, FileContent)" _ & "values(@FileId, @FileName, @FileType, @FileSize, @FileContent)" command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId command.Parameters.Add("@FileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(ctrFile.PostedFile.FileName) command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = ctrFile.PostedFile.ContentType command.Parameters.Add("@FileSize", SqlDbType.Int).Value = ctrFile.PostedFile.ContentLength ' filecontent, convert from stream to byte array Dim fileContent(ctrFile.PostedFile.ContentLength) As Byte ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength) command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent command.Connection.Open() command.ExecuteNonQuery() Finally command.Dispose() End Try ' show result ctrResult.NavigateUrl = "FileStream.ashx?FileId=" + fileId.ToString() ctrResult.Text = "Click here to view the uploaded file" End Sub End Class

3. Create a new Generic Handler (available under Add New Item > Generic Handler), give it the name FileStream.ashx and add the following code:


C#:
<%@ WebHandler Language="C#" Class="FileStream" %>
using System;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// 
/// Reponsible for flushing out the file from the database to the user.
/// 
public class FileStream : IHttpHandler
{
    public void ProcessRequest (HttpContext context)
    {
 // get the file id
 Guid fileId = new Guid(context.Request.QueryString["FileId"]);

 using (SqlCommand command = new SqlCommand())
 {
  // get the file from database
  command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
  command.CommandText = "select * from [File] where FileId = @FileId";
  command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId;
  command.Connection.Open();
  SqlDataReader reader = command.ExecuteReader();
  if (reader.Read())
  {
   // flush out the binary data to the user
   context.Response.Clear();
   context.Response.ContentType = (string) reader["FileType"];
   context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", reader["FileName"].ToString()));
   context.Response.AddHeader("Content-Length", reader["FileSize"].ToString());
   context.Response.BinaryWrite((byte[]) reader["FileContent"]);
   context.Response.End();
  }
 }
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

VB.NET:

<%@ WebHandler Language="VB" Class="FileStream" %> Imports System Imports System.Web Imports System.Configuration Imports System.Data Imports System.Data.SqlClient ''' ''' Reponsible for flushing out the file from the database to the user. ''' Public Class FileStream : Implements IHttpHandler Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest ' get the file id Dim fileId As New Guid(context.Request.QueryString("FileId")) Dim command As New SqlCommand() Try ' get the file from database command.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) command.CommandText = "select * from [File] where FileId = @FileId" command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId command.Connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() If reader.Read() Then ' flush out the binary data to the user context.Response.Clear() context.Response.ContentType = reader("FileType").ToString() context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", reader("FileName").ToString())) context.Response.AddHeader("Content-Length", reader("FileSize").ToString()) context.Response.BinaryWrite(DirectCast(reader("FileContent"), Byte())) context.Response.End() End If Finally command.Dispose() End Try End Sub Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property End Class

4. Run your code, upload a file using UploadFile.aspx, and then click on the hyperlink "Click here to view the uploaded file" to view the uploaded file.