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:

      <add verb="GET" path="getresource.axd" type="NMatrix.Web.BinaryResourceHandler, NMatrix.Core" />

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:

  <add key="connection" value="Data Source=.\NetSdk;Initial Catalog=Resources;User Id=sa;Pwd=;" />

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
        //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)
            idx += ChunkSize;
           //Write the last bytes.
           byte[] remaining = new byte[size];
           Array.Copy(buffer, 0, remaining, 0, size);


    /// <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[
    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;

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.


  • Additional tweak: if you're serving large binary objects off the database, you can use CommandBehavior.SequentialAccess in the call to ExecuteReader. That will turn-off full-row buffering. Otherwise, we'll copy the full binary value into our internal buffer (which is bad for large chunks of memory, specially if you're serving many hits concurrently). Then you can use SqlDataReader.GetBytes to get the value in small chunks and send them over the wire through BinaryWrite.

  • Thanks Pablo for that one!

  • This is a very nice handler, but it has a major security vulnerability. When setting the value of the ID in the WHERE clause, don't use string concatenation. Instead, use a SqlParameter. Otherwise, the code is highly susceptible to a SQL injection attack.

    Interestingly enough, the second example uses parameters...

  • You're certainly right.

  • binary doc in sql server

  • Sorry if this is a stupid question, but why shouldn't we use a filename with an .aspx extension as the handler, such as getresource.aspx?

  • Because you'd be incurring the cost of the whole page framework (creating the handler, parsing the page -even if it's empty-, calling the full control life-cycle members, etc) without a purpose. By using &quot;axd&quot; you don't pay for what you don't use.

  • What if the field-content is NULL? I've tried to test that by using rst.IsDBNull(0), but that fails because of SequentialAccess - have i to read it twice?

  • Do we have to do anything different to read PDF files like this?

  • You'd have to specify the &quot;application/pdf&quot; content type for the data, and that's it.

  • Don't use &quot;using (SqlConnection cn = new SqlConnection(_connectionstring))&quot;

    Disposing the connection causes connection-pooling to not work. Instead use cn.Close() to return the connection to the pool.

    I'm using this:


    struct ConnectionUser : IDisposable{

    IDbConnection _connection;

    bool _closeConnection;

    public ConnectionUser(IDbConnection connection){

    if(connection.State != ConnectionState.Open){


    _closeConnection = true;

    } else

    _closeConnection = false;

    _connection = connection;


    public void Dispose(){

    if(_closeConnection &amp;&amp; _connection.State == ConnectionState.Open)





    ... Which enables me to do:

    using(new ConnectionUser(connection))...

  • You should reflector the Dispose method on the SqlConnection. It has nothing to do with the connection pool. Disposing the connection basically just closes it. Actually, your code is pretty much the same as in the SqlConnection.Close() method.

    De hecho, el &quot;cerrar&quot; la conexion es en realidad retornarla al pool, y el Dispose llama a Close(), o sea q es lo mismo. O sea, la implementacion de Dispose es un wrapper de Close.

  • Thanks for the article, kzu! You renewed my interest in my attempts to pull PDF files stored in a Sybase database. I've been having to go through a middle-tier (CORBA) component to get these files. NO MORE!

    Just wanted to share a couple quick things:

    1) Not sure if this is OleDb specific or Sybase driver related, but wanted to make note of this for other readers. To get this to work against Sybase 12.5.1, I changed the ordinal property (the first property in the GetBytes method) from &quot;1&quot; to &quot;0&quot;.

    2) I noticed the &quot;getresource.axd&quot; file is executing twice on my machine. I see this in both the &quot;trace.axd&quot; and in the IIS weblogs file. Can anyone else confirm this?

    Again, thanks for the information!

  • Thanks very much for the article - it was a great help!
    I was wondering, is there a work around for opening files from a modal window which targets itself ?

  • hi all, this is about how to retrieve the saved data in a doc file from data,
    Can someone let me know how to store a doc/xls file into SQL server database?

Comments have been disabled for this content.