Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET

Storing Images in SQL Server using EF and ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.55/5 (35 votes)
23 Oct 2013CPOL4 min read 138.2K   5K   69   16
A quick help for storing images in SQL Server and displaying them on a ASP.NET Website

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: Image 1

[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:

Image 2

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).

C#
[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.

C#
[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:

C#
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:

C#
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.

JavaScript
$(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:

JavaScript
$(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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect http://4dotnet.nl
Netherlands Netherlands
I'm developing software for over two decades. I'm working as a cloud solution architect and a team lead at 4DotNet in The Netherlands.

In my current position, I love to help customers with their journey to the cloud. I like to create highly performant software and to help team members to a higher level of software development.

My focus is on the Microsoft development stack, mainly C# and the Microsoft Azure Cloud. I also have a strong affinity with Angular. As a result of my contributions to the community, I received the Microsoft MVP Award for Microsoft Azure.

Comments and Discussions

 
QuestionWhen I run the Upload button didn't work :doh: Pin
praveendubey30-Dec-13 17:06
praveendubey30-Dec-13 17:06 
QuestionNice project .. one questin though Pin
angeloa3-Nov-13 8:22
angeloa3-Nov-13 8:22 
AnswerRe: Nice project .. one questin though Pin
Eduard Keilholz4-Nov-13 4:26
Eduard Keilholz4-Nov-13 4:26 
SuggestionNo information about caching Pin
hVostt28-Oct-13 20:19
hVostt28-Oct-13 20:19 
GeneralRe: No information about caching Pin
Eduard Keilholz28-Oct-13 22:28
Eduard Keilholz28-Oct-13 22:28 
GeneralRe: No information about caching Pin
hVostt28-Oct-13 22:58
hVostt28-Oct-13 22:58 
QuestionMy vote 5 Pin
Rahat Yasir2-Oct-13 9:20
Rahat Yasir2-Oct-13 9:20 
AnswerRe: My vote 5 Pin
Eduard Keilholz2-Oct-13 11:11
Eduard Keilholz2-Oct-13 11:11 
GeneralHelpful to store binary data Pin
Eone James30-Sep-13 20:34
Eone James30-Sep-13 20:34 
GeneralRe: Helpful to store binary data Pin
Eduard Keilholz30-Sep-13 21:12
Eduard Keilholz30-Sep-13 21:12 
GeneralMy vote of 5 Pin
FernandoUY25-Sep-13 11:35
professionalFernandoUY25-Sep-13 11:35 
GeneralRe: My vote of 5 Pin
Eduard Keilholz25-Sep-13 20:26
Eduard Keilholz25-Sep-13 20:26 
QuestionMinor point... Image datatype? Pin
gduncan41125-Sep-13 5:27
gduncan41125-Sep-13 5:27 
AnswerRe: Minor point... Image datatype? Pin
Eduard Keilholz25-Sep-13 8:26
Eduard Keilholz25-Sep-13 8:26 
QuestionBinary data made easy Pin
Andy Bantly25-Sep-13 3:51
Andy Bantly25-Sep-13 3:51 
AnswerRe: Binary data made easy Pin
Eduard Keilholz25-Sep-13 3:55
Eduard Keilholz25-Sep-13 3:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.