How to serve binary resources from a database (images and others) in ASP.NET?
Note: this entry has moved.
This is such a frequent question, that I thought I’d better
spend some time outlining a customizable and versatile
solution so that it can be reused in future scenarios.
Often, binary resources such as employee pictures, office
documents in a document management system, and others, are
stored in a database. Now the issue is how to pull that
information out of it and show it using ASP.NET. One way to
solve this problem is creating a dummy ASP.NET page that
simply uses Response.BinaryWrite() to output
the binary field brought by the DB query, as is explained in
an
ASP Alliance
article. However, you don’t get encapsulation and
reusability, having to resort to copy/paste as new projects
need such a feature.
My advice is to always use a custom
IHttpHandler that serves these binary
resources. Of course this is not a new idea, I'm just
putting it here so I can refer to the countless persons that
asked me.
You have to map a path to your handler in the
Web.config file, and to avoid having to modify
the IIS extensions mapping, you should use one of the
built-in extensions that are already mapped to ASP.NET at
installation time: .ashx or .axd. It's always better to use
an specific "file name" for your handler, to avoid
interfering with the ASP.NET built-in handlers. Such a
handler should be configured in the
Web.config as follows:
<configuration>
<system.web>
...
<httpHandlers>
<add verb="GET" path="getresource.axd" type="NMatrix.Web.BinaryResourceHandler, NMatrix.Core" />
</httpHandlers>
From now on, each request sent to the "file" getresource.axd
will be passed to our handler. The ".ashx" extension could
have been used too: it's already mapped in IIS to the
ASP.NET ISAPI filter, it doesn't affect the normal ASP.NET
execution, and it was conceived as the natural extension to
use by so-called "web handlers" (see
other articles). However, that extension is usually applied to files that
look like .aspx with inline server-side code with an @
webhandler directive. That's why I prefer the "axd"
extension. We map only the GET verb as it's the
only one we will need to retrieve resources. This could
easily be extended by allowing the POST verb
meaning a new resource should be stored. I'll leave that as
homework for the reader. Mostly, uploading resources is a
more complex process that involves authorization at least,
maybe even a full document management system.
The handler will need two parameters: the resource ID and
the connection string to the repository. The first one will
be provided by the calling client, as a query string value.
The later is stored in the Web.config for
simplicity:
<configuration>
<appSettings>
<add key="connection" value="Data Source=.\NetSdk;Initial Catalog=Resources;User Id=sa;Pwd=;" />
</appSettings>
To make the storage flexible, the table storing the resources will also contain a ContentType to allow the storage of arbitrary types. Now the handler code:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
namespace NMatrix.Web
{
/// <summary>
/// Serves binary resources stored in a database.
/// </summary>
/// <author>Daniel Cazzulino</author>
public class BinaryResourceHandler : IHttpHandler
{
#region Ctor & Vars
//Key used in Web.config
public const string ConnectionStringKey = "connection";
//Holds the DB connection
private static string _connectionstring;
//Size of the chunk to read from the DB
private const int ChunkSize = 1024;
static BinaryResourceHandler()
{
_connectionstring = ConfigurationSettings.AppSettings[ConnectionStringKey];
if (_connectionstring == null || _connectionstring.Length == 0)
throw new ArgumentException("A 'connection' attribute must be provided to the handler.");
}
#endregion Ctor & Vars
#region IHttpHandler Members
public void ProcessRequest(HttpContext context)
{
string id = context.Request.QueryString["id"];
if (id == null || id.Length == 0)
throw new ArgumentException("An 'id' query string value must be specified.");
using (SqlConnection cn = new SqlConnection(_connectionstring))
{
SqlCommand cmd = new SqlCommand("SELECT ContentType, BinaryData FROM Resources WHERE ResourceId=@id", cn);
cmd.Parameters.Add("@id", SqlDbType.VarChar, 50);
cmd.Parameters["@type"].Value = id
cn.Open();
//CommandBehavior.SequentialAccess avoids loading the entire BLOB in-memory.
SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (r.Read())
{
context.Response.ContentType = r.GetString(0);
byte[] buffer = new byte[ChunkSize];
long idx = 0;
long size = 0;
//Write the BLOB chunk by chunk.
while ((size = r.GetBytes(1, idx, buffer, 0, ChunkSize)) == ChunkSize)
{
context.Response.BinaryWrite(buffer);
idx += ChunkSize;
}
//Write the last bytes.
byte[] remaining = new byte[size];
Array.Copy(buffer, 0, remaining, 0, size);
context.Response.BinaryWrite(remaining);
}
}
}
/// <summary>
/// This instance is freely reusable.
/// </summary>
public bool IsReusable
{
get { return true; }
}
#endregion IHttpHandler Members
}
}
The code is straightforward. The only tip is that by using
CommandBehavior.SequentialAccess, the
SqlDataReader can read the binary data in chunks, avoiding
to load it completely in-memory (thanks Pablo Castro!).
A client application performing uploads could simply have a
file upload HTML control (with
runat="server" to allow our code to access the
Request.Files collection) and with the
following code could simply save arbitrary files to the
database:
private void btnUpload_ServerClick(object sender, System.EventArgs e)
{
using (SqlConnection cn = new SqlConnection(ConfigurationSettings.AppSettings[
NMatrix.Web.BinaryResourceHandler.ConnectionStringKey]))
{
SqlCommand cmd = new SqlCommand("INSERT INTO Resources (ContentType, BinaryData) VALUES (@type, @data)", cn);
cmd.Parameters.Add("@type", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@data", SqlDbType.Image);
cmd.Parameters["@type"].Value = txtContent.Text;
byte[] data = new byte[Request.Files[0].InputStream.Length];
//Trunc the stream length. Nobody will be able to send Int.MaxValue bytes (around 2Gb!)
Request.Files[0].InputStream.Read(data, 0, (int) Request.Files[0].InputStream.Length);
cmd.Parameters["@data"].Value = data;
cn.Open();
cmd.ExecuteNonQuery();
}
}
The ContentType field has a purpose: if someone
specifies application/msword or
application/msexcel (or any other content type
understood and handled specially by the browser), IE will
automatically instantiate the appropriate Office application
inside the browser window to open the incoming "file"
(stream, more properly).
To refer to a resource of type image/jpg from
an <img> HTML tag, we could use:
<img alt="Dynamically served image" src="getresource.axd?id=3">
To directly open a Word document from the server, we could simply point IE to the same handler passing its id.