ASP.net(C#): Output an image stored in SQL

July 4, 2010 by Christoff Truter C#   ASP.NET   SQL  

Generally I am not a huge fan of storing images in a database, I do however believe that there is a time/place where this might be desirable - but personally I would avoid it all together (wont go into too much details).

But for those of you feeling the urge/calling to go this route, lets have a quick look at how to achieve this using SQL and ASP.net.

First of all, lets have a look at the SQL (backend) side of things:

Create a simple table for storing the images, note the type "image" which will contain the binary data.

CREATE TABLE [dbo].[images](
	[imageID] [int] IDENTITY(1,1) NOT NULL,
	[title] [varchar](255) NOT NULL,
	[contentType] [varchar](255) NOT NULL,
	[contents] [image] NOT NULL,
 CONSTRAINT [PK_images] PRIMARY KEY CLUSTERED 
(
	[imageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

We're going to create a few stored procedures aiding us in this ungodly quest.

Adding an image to the database:

CREATE PROCEDURE [dbo].[addImage]
	@title VARCHAR(255),
	@contentType VARCHAR(255),
	@contents IMAGE
AS
BEGIN
	INSERT INTO images(title, contentType, contents)
	VALUES(@title, @contentType, @contents)
END

Retrieving an image from the database:

CREATE PROCEDURE [dbo].[viewImage]
	@imageID INT
AS
BEGIN
	SELECT contentType, contents
	FROM images
	WHERE imageID = @imageID
END

Retrieving a list of images from the database:

CREATE PROCEDURE [dbo].[viewImages]
AS
BEGIN
	SELECT imageID, title
	FROM images
END

Lets have a look at the C# (frontend) side of things.

Adding an image to the database:

ASPX

<table>
    <tr>
        <td>
            Title
        </td>
        <td>
            <asp:TextBox runat="server" ID="txtTitle"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Image
        </td>
        <td>
            <asp:FileUpload runat="server" ID="fuImage" />
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button runat="server" ID="btnUpload" Text="Upload" 
                onclick="btnUpload_Click" />
        </td>
    </tr>
</table>

C# CodeBehind

protected void btnUpload_Click(object sender, EventArgs e)
{
	using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
	{
		using (SqlCommand command = new SqlCommand("addImage", connection))
		{
			connection.Open();
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.AddWithValue("@title", txtTitle.Text);
			command.Parameters.AddWithValue("@contentType", fuImage.PostedFile.ContentType);
			command.Parameters.AddWithValue("@contents", fuImage.FileBytes);
			command.ExecuteNonQuery();
		}
	}
}

Note: It is highly advisable to validate data received from an user (required field validators etc etc), omitted from these examples. I know quite a number of sites that allow users to upload malicious scripts to their servers - not very clever.

In order to display the images from the database, we're going to write a generic handler (HttpHandler), alternatively one can output images via aspx file, but personally I would reserve aspx files for html/xhtml output.

HttpHandlers are much lighter objects (more suited for rendering non-html/xhtml) - since it excludes all the hectic objects (control trees etc) generally needed to render a html/xhtml page within ASP.net

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        Int32 imageID = 0;

        if (Int32.TryParse(context.Request.QueryString["imageID"], out imageID))
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("viewImage", connection))
                {
                    connection.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@imageID", imageID);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        context.Response.ContentType = dt.Rows[0].Field<string>("contentType");
                        context.Response.BinaryWrite(dt.Rows[0].Field<byte[]>("contents"));            
                    }
                }
            }            
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Note: Consider handling errors within your handler gracefully, e.g. display an alternate image in the event of an exception

In order to display a list of all the images, we're simply going to call our generic handler within a html img tag.

ASPX

<asp:ListView runat="server" ID="lvImages" ItemPlaceholderID="phImages">
	<LayoutTemplate>
		<table>
			<asp:PlaceHolder runat="server" ID="phImages"></asp:PlaceHolder>
		</table>
	</LayoutTemplate>
	<ItemTemplate>
		<tr>
			<td>
				<%# Eval("title") %>
			</td>
			<td>
				<img src="Handler.ashx?imageID=<%# Eval("imageID") %>" />
			</td>
		</tr>
	</ItemTemplate>
</asp:ListView>

C# CodeBehind

protected DataTable viewImages()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
    {
        using (SqlCommand command = new SqlCommand("viewImages", connection))
        {
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }        
}

protected void Page_Load(object sender, EventArgs e)
{
    lvImages.DataSource = viewImages();
    lvImages.DataBind();
}

Tip:

Save/Open dialog:
To invoke a save/open dialog from the generic handler, simply add the following code within your ashx file (before the ContentType Response)

context.Response.AddHeader("Content-Disposition", "attachment; filename=somefile.jpg");