Storing Files and Images in Access with ASP.NET

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" 
				"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <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;}
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <div class="row">
        <span class="label"><label for="FirstName">First Name: </label></span>
        <asp:TextBox ID="FirstName" runat="server"></asp:TextBox>
    </div> 
    <div class="row">
      <span class="label"><label for="Surname">Surname: </label></span>
      <asp:TextBox ID="Surname" runat="server"></asp:TextBox>
    </div> 
    <div class="row"> 
      <span class="label"><label for="Photo">Photo: </label></span>
      <asp:FileUpload ID="PhotoUpload" runat="server" />
    </div>
    <div class="row">
      <span class="label"><label for="Resume">Resume: </label></span>
      <asp:FileUpload ID="ResumeUpload" runat="server" />
    </div>
    <div class="row">
    <span class="label">&nbsp;</span>
      <asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
    </div>  
    </div>
    </form>
</body>
</html>


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);
      conn.Open();
      cmd.ExecuteNonQuery();
    }
  }

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"]);
        conn.Open();
        using (OleDbDataReader rdr = cmd.ExecuteReader())
        {
          if (rdr.HasRows)
          {
            rdr.Read();
            context.Response.AddHeader("content-disposition", "attachment; filename=" + rdr["ResumeFileName"]);
            context.Response.ContentType = rdr["ResumeMime"].ToString();
            context.Response.BinaryWrite((byte[])rdr["Resume"]);
          }
        }
      }
    }
  }

  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">
<div>
  <a href="ResumeFileHandler.ashx?ID=12">Get Resume</a>
  <br />
  <img src="ImageFileHandler.ashx?ID=12" alt="" />
</div>
</form>

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.

Date Posted: Sunday, December 6, 2009 7:46 PM
Last Updated: Friday, October 10, 2014 9:14 PM
Posted by: Mikesdotnetting
Total Views to date: 32248

7 Comments

Friday, February 26, 2010 7:13 PM - jared

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

Monday, March 1, 2010 4:59 PM - 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!

Tuesday, May 18, 2010 3:14 PM - zelio

its good

Tuesday, August 10, 2010 9:32 AM - 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];

Wednesday, August 11, 2010 6:31 PM - Mike

@Hans

Good spot! I have amended the code accordingly.

Thursday, October 11, 2012 7:41 AM - 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 ASP.net, 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.

Thanks

Thursday, October 11, 2012 7:49 AM - Mike

@Brian,

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: http://www.mikesdotnetting.com/Article/148/Save-And-Retrieve-Files-From-a-Sql-Server-CE-Database-with-WebMatrix
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...