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.