Storing Files and Images in Access with ASP.NET

4.21 (19 votes)

Should you store files and images in the database or the filesystem in an ASP.NET application?  With SQL Server this is a dilemma, with both approaches having their pros and cons.  When it comes to Access, the decision is much easier to make.  Because an Access database is restricted to 2Gb, it makes more sense to store files in the file system.  However, sometimes, you may not have the choice. If you have to use the database, here's how.

I'll start by discussing the data type that Access offers to support storing files: the Ole Object data type.  OLE (Object Linking and Embedding) is the technology that the Office suite of products use to assist with sharing files across applications.  For example, when you insert a picture in a Word document, OLE comes into play.  When you see Excel spreadsheets embedded in a Word doc, again, it's the result of OLE.  Finally, if you download the sample Northwind database, you will see the worst possible quality images of the sales people appear on the Employees form:

This is also OLE at work.  OLE servers are applications which are responsible for converting original files into something that OLE can work with. Taking something like a jpeg or gif, the OLE Server responsible will reformat the file and possibly increase its overall size by up to 100 times to make it work with OLE.  It will often reduce quality markedly. The application that displays the result must understand the format, and the correct OLE servers must be in place to decode the database field content. To cap it all, there is a performance overhead required in encoding and decoding OLE's proprietory format.  All of this goes to make OLE totally unsuitable for files to be used in web applications.  It is also OLE that has helped give Access a poor reputation when it comes to storing images or files within it.

The OLE Object field is also happy to accept Long Binary Data, which is basically a BLOB (Binary Large OBject). Being a byte-for-byte copy of the original file, the BLOB is easy to extract and present in its original form over HTTP, but not so easy within the Access application itself.

For this demonstration, I have taken the Northwind sample database and added a few fields to the Employees table.  There is already an OLE Object field for Photo, and to this I add the following:

PhotoFileName TEXT
PhotoMime TEXT
Resume OLE Object
ResumeFileName TEXT
ResumeMime TEXT

The fields with "Mime" in their name will be used to store the content-type of the file.  Browsers look at this property to decide how to treat an HTTP response.  If the MIME type is one that they know, they will either display the content inline (where they are set up to do so, such as with text/html or image/gif) or they will invoke the default application for the known MIME type (e.g MS Word for application/msword).  Finally, if they do not know what do to, they present the Save/Open dialogue box.

The next step is to create an Employee Entry page, so that users can create a new employee, and upload an image and a Resume (or CV as we call them in Rome):

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessUpload.aspx.cs" Inherits="AccessUpload" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 

<html xmlns="">
<head runat="server">

    <style type="text/css">
    body{font-family: tahoma;font-size: 80%;}
    .row{clear: both;}
    .label{float: left;text-align: right;width: 150px;padding-right: 5px;}
    <form id="form1" runat="server">
    <div class="row">
        <span class="label"><label for="FirstName">First Name: </label></span>
        <asp:TextBox ID="FirstName" runat="server"></asp:TextBox>
    <div class="row">
      <span class="label"><label for="Surname">Surname: </label></span>
      <asp:TextBox ID="Surname" runat="server"></asp:TextBox>
    <div class="row"> 
      <span class="label"><label for="Photo">Photo: </label></span>
      <asp:FileUpload ID="PhotoUpload" runat="server" />
    <div class="row">
      <span class="label"><label for="Resume">Resume: </label></span>
      <asp:FileUpload ID="ResumeUpload" runat="server" />
    <div class="row">
    <span class="label">&nbsp;</span>
      <asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />

I've used a bit of CSS to style this and the result looks like this:

All of the action takes place in the Button1_Click event in the code-behind:

protected void Button1_Click(object sender, EventArgs e)
  if (PhotoUpload.HasFile && ResumeUpload.HasFile)
    Stream photoStream = PhotoUpload.PostedFile.InputStream;
    int photoLength = PhotoUpload.PostedFile.ContentLength;
    string photoMime = PhotoUpload.PostedFile.ContentType;
    string photoName = Path.GetFileName(PhotoUpload.PostedFile.FileName);
    byte[] photoData = new byte[photoLength - 1];
    photoStream.Read(photoData, 0, photoLength);

    Stream resumeStream = ResumeUpload.PostedFile.InputStream;
    int resumeLength = ResumeUpload.PostedFile.ContentLength;
    string resumeMime = ResumeUpload.PostedFile.ContentType;
    string resumeName = Path.GetFileName(ResumeUpload.PostedFile.FileName);
    byte[] resumeData = new byte[resumeLength - 1];
    resumeStream.Read(resumeData, 0, resumeLength);

    string qry = "INSERT INTO Employees (FirstName, LastName, Photo, PhotoFileName, PhotoMime, Resume, 
               ResumeFileName, ResumeMime) VALUES (?,?,?,?,?,?,?,?)";
    string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb";
    using (OleDbConnection conn = new OleDbConnection(connect))
      OleDbCommand cmd = new OleDbCommand(qry, conn);
      cmd.Parameters.AddWithValue("", FirstName.Text);
      cmd.Parameters.AddWithValue("", Surname.Text);
      cmd.Parameters.AddWithValue("", photoData);
      cmd.Parameters.AddWithValue("", photoName);
      cmd.Parameters.AddWithValue("", photoMime);
      cmd.Parameters.AddWithValue("", resumeData);
      cmd.Parameters.AddWithValue("", resumeName);
      cmd.Parameters.AddWithValue("", resumeMime);

The two upload controls are checked for the presence of a file each, and then the contents of the files are read into byte arrays, and their content types and names are obtained.  These along with the first name and last name are passed into the parmeters before the whole lot is inserted into the database.

Having got the files there, some way is needed to retrieve them and deliver them to the browser.  A Generic Handler is a good option for this.  I decided to create two: one for the Resume and one for the Photo.  You might be tempted to combine both tasks into one handler.  I'm only going to show the Resume handler, because apart from the SQL and the data fields, all the code is the same as the PhotoHandler:

<%@ WebHandler Language="C#" Class="ResumeFileHandler" %>
using System;
using System.Data.OleDb;
using System.Web;

public class ResumeFileHandler : IHttpHandler

  public void ProcessRequest(HttpContext context)
    string qry = "SELECT Resume, ResumeFileName, ResumeMime FROM Employees WHERE EmployeeID = ?";
    string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb";

    using (OleDbConnection conn = new OleDbConnection(connect))
      if (context.Request.QueryString["id"] != null)
        OleDbCommand cmd = new OleDbCommand(qry, conn);
        cmd.Parameters.AddWithValue("", context.Request.QueryString["id"]);
        using (OleDbDataReader rdr = cmd.ExecuteReader())
          if (rdr.HasRows)
            context.Response.AddHeader("content-disposition", "attachment; filename=" + rdr["ResumeFileName"]);
            context.Response.ContentType = rdr["ResumeMime"].ToString();

  public bool IsReusable
    get { return false; }


A generic handler (.ashx file) implements IHttpHandler, which enforces 2 methods - ProcessRequest() and IsReusable.  All the action takes place in ProcessRequest.  I'm passing in the EmployeeID via the querystring, which should tell you that ASP.NET will serve requests to files ending in .ashx. The code obtains the values for the file name, and content type, and then sets the response headers accordingly, before finally streaming the file as an array of bytes.

An example page which lets the user get the files is as follows:

<form id="form1" runat="server">
  <a href="ResumeFileHandler.ashx?ID=12">Get Resume</a>
  <br />
  <img src="ImageFileHandler.ashx?ID=12" alt="" />

To provide access to the Resume, a simple hyperlink is all that's needed. The file is delivered when the user clicks the link. The image, on the the hand, has it's src pointing to the filehandler.  When the html for the page is first sent to the browser, it then looks at other "resources" that the page needs, and requests them one by one. The src attribute of the img tag points to the location of one of these resources.

Storing files as byte arrays in Access is a lot more efficient than embedding OLE objects.  If you site is one that needs to serve a relatively small amount of users in an Intranet, this approach may serve you well.  However, as I said at the beginning of the article, the preferred option is to store a filename in the database and the files on disc.  I debated whether I should provide guidance on what I believe to be the wrong way to manage files with Access, but I see requests for help on this quite often. However, if you have no choice about how you work, for example because you have to deal with a legacy database with files already in it, hopefully this article will have provided you with some help.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 45036


- jared

I got to had to work with a customers existing database. This article helped me a lot. Thanks!

- Vishal

This article is very useful. Thanks a lot for posting it.
But i wuld like to know the method to change the photo of an employee in future if he/she wis to do so?
Please help!

- zelio

its good

- Hans_V

Mike, when you declare the byte array, you're using the length of the posted file. But since an array is zero based, it should be 1 less. This is usually not that important, but when retrieving office 2007 files (which are in fact zip files), you good get an error.

byte[] photoData = new byte[photoLength-1];
byte[] resumeData = new byte[resumeLength-1];

- Mike


Good spot! I have amended the code accordingly.

- Brian

I'm new to C# and most of VB, I have been writing Classic ASP for years. I have a current ASP Access file storage but am trying to convert it to, your example is great but I do not understand a few things.

Is the code for the Button1_click stored in the same file as the as the main html form page AccessUpload.aspx? Or are you saving it as a seperate file and using it as an include file? If you have a completed demo file I can look at, that would be great. I am just trying to convert as much as my Classic ASP to VB or C# but need a clearer reference.


- Mike


The button_click code is usually in the code behind file - unless you are not using code behind files in which case it will go in the @lt;script runat="server"> block at the top of your web form.

Having said that, with your background you might be better off using the Web Pages framework rather than Web Forms. Here's an example of how to do the same thing with a SQL Compact database and Razor Web Pages:

- Karthekeyan A S

This page is very effective to access the information

Recent Comments

Pam 30/08/2017 11:30
In response to Sending Email in Razor Pages
Mike, RazorPages sound like a nice choice for somebody still working in ASP classic who wants to to...

Robby Robson 15/08/2017 00:43
In response to Routing in Razor Pages
Mike: great stuff. Now that .Core Standard 2.0 is formally out, how soon will you rewrite your book...

Satyabrata Mohapatra 28/07/2017 08:59
In response to Sending Email in Razor Pages
Bit off topic, but congratulation sir for your MVP award. You deserve it !!!...

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...