Storing Images in SQL Server using EF and ASP.NET

• Download ASP.NET MVC4 and ASP.NET source code - 82.2 KB

Introduction

This article was actually driven by the demand for knowledge about this subject in the forum. Questions about how to store binary data (images) in a SQL Server database and how to retrieve them, and even return the image with ASP.NET (MVC) to display on a website. For the ASP.NET WebForms developers, I have also included an ASP.NET WebForms example project.

Background

The solution contains two demo projects. One ASP.NET WebForms project, and an ASP.NET MVC 4 project. The MVC demo project contains two controllers. One HomeController and one DocumentController. The DocumentController contains two action methods to upload and download images. The HomeController uses these methods to upload and display images from the database. To nicely present the upload functionality, I included dropzone.js (http://www.dropzonejs.com/).

The ASP.NET WebForms project contains a single web form and two Generic Handlers. One handler handles the upload, the other the download of images to and from the database. The web form then uses these handlers to post images to and to get images from.

Setting Up a Database

I assume you're familiar with SQL Server databases. I created a table called Document which looks like this:

[Please note that when downloading the demo project, a connection string with the name DocumentEntities needs to be changed in the web.config file of the ASP.NET project]

If you open the Entity Framework data model (DocumentEntities.edmx) diagram, the table above will look like this:

Using the Code

To keep things simple, I added another project to the solution, a simple class library project that will contain the Entity Framework model I created from my database containing a Document table.

A Little Piece of MVC

I created a new controller, I called it HomeController and chose 'Empty MVC Controller' as a template. This controller is just fine by returning a view from the Index action method.

The action methods look like this (yeah I know, the magic doesn't happen here, but for completeness I just wanted to add them).

[HttpGet]
public ActionResult Show(int? id)
{
    string mime;
    byte[] bytes = LoadImage(id.Value, out mime);
    return File(bytes, mime);
}

The above method calls a function that retrieves the file type and the file's byte[] and returns a File object. The int? id will be used to find the image by record ID in the database.

[HttpPost]
public ActionResult Upload()
{
    SuccessModel viewModel = new SuccessModel();
    if (Request.Files.Count == 1)
    {
        var name = Request.Files[0].FileName;
        var size = Request.Files[0].ContentLength;
        var type = Request.Files[0].ContentType;
        viewModel.Success = HandleUpload(Request.Files[0].InputStream, name, size, type);
    }
    return Json(viewModel);
}

This action method will be used by dropzone.js. Dropzone posts some information about the file being uploaded, and of course the file's binaries as well. All values are passed to a HandleUpload method that stores the file in the database.

A Little Piece of WebForms

For the ASP.NET WebForms project, I added a WebForm (and called it Default.aspx). There's no code-behind in this webform, just a bunch of HTML and JavaScript, which are by the way pretty similar to the HTML rendered by the MVC version. There's one slight difference, the post address of the upload form, and the JavaScript triggering an update of image uses a different address.

Show Me the Magic

OK, now this is what you want to see! The methods that actually put the files in the database and read the image from the database. Are you ready? Here we go:

private bool HandleUpload(Stream fileStream, string name, int size, string type)
{
    bool handled = false;

    try
    {
        byte[] documentBytes = new byte[fileStream.Length];
        fileStream.Read(documentBytes, 0, documentBytes.Length);

        Document databaseDocument = new Document
        {
            CreatedOn = DateTime.Now,
            FileContent = documentBytes,
            IsDeleted = false,
            Name = name,
            Size = size,
            Type = type
        };

        using (DocumentEntities databaseContext = new DocumentEntities())
        {
            databaseContext.Documents.Add(databaseDocument);
            handled = (databaseContext.SaveChanges() > 0);
        }
    }
    catch (Exception ex)
    {
        // Oops, something went wrong, handle the exception
    }

    return handled;
}

First, we create a byte array since that's how EntityFramework maps the binary file type. Then we read the incoming stream (the file) into the byte array. Then we create a new Document object, which is a representation of our database table. Then we instantiate the database context and add the document to the Document table in the database, and then save it.

The method for retrieving the file from the database is even easier:

private byte[] LoadImage(int id, out string type)
{
    byte[] fileBytes = null;
    string fileType = null;
    using (DocumentEntities databaseContext = new DocumentEntities())
    {
        var databaseDocument = databaseContext.Documents.FirstOrDefault(doc => doc.DocumentId == id);
        if (databaseDocument != null)
        {
            fileBytes = databaseDocument.FileContent;
            fileType = databaseDocument.Type;
        }
    }
    type = fileType;
    return fileBytes;
}

Create an instance of the database context and fetch the first document which DocumentId matches the document id requested. If a document is found, return the bytes of the document (its content) and the file type.

Points of Interest

Nothing really special here, but please note that I added a little JavaScript to the Index view of the home controller.

$(document).ready(function () {
    $("#preview").fadeOut(15);
    $("#refreshButton").click(function () {
        var imageToLoad = $("#imageId").val();
        if (imageToLoad.length > 0) {
            $("#preview").attr("src", "/Document/Show/" + imageToLoad);
            $("#preview").fadeIn();
        }
    });
});

This little friend enables us to retrieve document from the database and display it as an image. The HTML shows a textbox and a button, enter the database ID of a document in the textbox, and hit the button to display the image.

The ASP.NET WebForms project uses exactly the same JavaScript, but with a different address:

$(document).ready(function () {
    $("#preview").fadeOut(15);
    $("#refreshButton").click(function () {
        var imageToLoad = $("#imageId").val();
        if (imageToLoad.length > 0) {
            $("#preview").attr("src", "/Download.ashx?id=" + imageToLoad);
            $("#preview").fadeIn();
        }
    });
}); 

Download Instructions

Please note that the project uses a couple of NuGet packages. The names (and versions) of the packages are stored in [UsedPackages.txt]. Also, please don't forget to change the connectionstring settings in the web.config of both web projects.

The zip file also includes a file called [CreateDocumentsTable.sql] which is a SQL Script that you can run against a SQL Server database. It will create the Documents table in the database that I use in the article.

History

Keep a running update of any changes or improvements you've made here.

• 2013/09/25 - First version - Wow! Exciting
• 2013/09/30 - Added ASP.NET source code and download instructions
• 2013/10/02 - Added SQL Script for creating DB table to source and article and changed title
• 2013/10/07 - Added description of ASP.NET WebForms project