BlogOfBob

August 17, 2006

@ 02:02 PM

Retrieving Images in ASP.NET from SQL Server

There are literally dozens of articles on how to retrieve images from a database and display them in a web page. The vast majority use one of two variations.

Variation 1

Retrieve the image data, save it to a temporary file and then use the file as the image controls ImageURL or src.

Variation 2

Use an .aspx page with its content type set to an image/xyz type and retrieve the image within the ‘loader’ page and use that page as the image controls ImageURL or src.

I can’t use Variation 1 because I don’t want to create temporary files for a variety or reasons. Variation 2 works for individual images but I need to be able to quickly pull back up to 12 images and I experienced problems using this method within a loop from the calling page.

So here comes Variation 3. I am still not 100% satisfied by this but it works. As an aside since I can retrieve the image as a byte array directly from the database I would love to be able to do something like:

PageLoad Event

Go get image from DB and return it as either a Byte Array or a memorystream

Image1.ImageURL.LoadFromStream(ms)

Alas even though I wish it to be true it does not appear to be possible. However in the Wrox Book “ASP.NET 2.0 MVP Hacks and Tips” I stumbled on what turned out to be my solution in chapter 17 the part titled “Compositing Images with an HttpHandler” which was written by Scott Hanselman

So my solution was a variation on Variation 2, I created a HttpHandler class that in essence does the same thing, it retrieves the image data and then writes the image directly to the output stream and so I can now call my handler and pass in some parameters like this:

Using the html img tag:

Or using the server side model:

Image1.ImageUrl = "~/GetImages.ashx?RecID=90&Size=Model"

This method works like a champ even in a loop, and seems to be much faster than the .aspx page method. Not sure why, perhaps an HttpHandler does not have the overhead a full .aspx page does, but it sure works well.

The full code for my solution is below: Note: this was test code, I would NOT recommend using dynamic SQL like I do below in a production application, instead use parameterized sql.

<%@ WebHandler Language="VB" Class="GetImages" %>

    1 Imports System
    2 Imports System.Web
    3 Imports System.IO
    4 Imports System.Drawing
    5 Imports System.Drawing.Imaging
    6 
    7 Public Class GetImages : Implements IHttpHandler
    8 
    9     Public Sub ProcessRequest(ByVal ctx As HttpContext) Implements IHttpHandler.ProcessRequest
   10         'Create the request object and retrieve the QueryString Parameters
   11         Dim req As HttpRequest = ctx.Request
   12         Dim iRecID As Integer = CType(req.QueryString("RecID"), Integer)
   13         Dim sPicSize As String = CType(req.QueryString("Size"), String)
   14         Dim img() As Byte = GetData(iRecID, sPicSize)
   15         ctx.Response.ContentType = "image/gif"
   16 
   17         If (Not (img) Is Nothing) Then
   18             Dim m As MemoryStream = New MemoryStream(img)
   19             Dim image As Image = System.Drawing.Image.FromStream(m)
   20             image.Save(ctx.Response.OutputStream, ImageFormat.Gif)
   21         End If
   22 
   23     End Sub
   24 
   25     Public Function GetData(ByVal iRecID As Integer, ByVal sPicSize As String) As Byte()
   26 
   27         Dim cnn As Data.SqlClient.SqlConnection
   28         Dim cmd As Data.SqlClient.SqlCommand
   29         Dim msTemp As New MemoryStream
   30         Dim strSQL As String
   31 
   32         Select Case sPicSize
   33             Case "Large"
   34                 strSQL = "SELECT LargeImage FROM cyb_Frames WHERE RecID=" & iRecID
   35             Case "Small"
   36                 strSQL = "SELECT Thumbnail FROM cyb_Frames WHERE RecID=" & iRecID
   37             Case "Model"
   38                 strSQL = "SELECT ImageWithModel FROM cyb_Frames WHERE RecID=" & iRecID
   39             Case Else
   40                 strSQL = "SELECT LargeImage FROM cyb_Frames WHERE RecID=" & iRecID
   41         End Select
   42 
   43         Dim connString As String = Web.Configuration.WebConfigurationManager.ConnectionStrings("xyz").ConnectionString
   44         cnn = New Data.SqlClient.SqlConnection(connString)
   45         cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)
   46         cnn.Open()
   47 
   48         Return cmd.ExecuteScalar
   49 
   50     End Function
   51 
   52     Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
   53         Get
   54             Return False
   55         End Get
   56     End Property
   57 
   58 End Class