WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
I still don't quite understand why images aren't treated like first class data types. Binding almost anything to a control and the data shows up; it's "magically delicious"! Use the Web Image control and you have to use an URL. The obvious implication is that the image is stored on the file system somewhere or at a remote location.
Disk space is cheap, and I prefer storing images in the database whenever possible. Storing the images in the database makes them portable with the database, less likely to be tampered with, and the images get backed up when the database gets backed. The only challenge is getting the image from the database column into a control.
The solution presented here uses an HttpContext to display an image. Another part of the solution strips the OLE header out of the images in the Northwind database's categories table, and the final bit of the example demonstrates how to customize the image by creating a GDI+ object and drawing on the image.
Displaying an Image from the Northwind Database
Displaying an image from a database works when you write the image's bytes to the response stream; that is, you stuff the image bytes into the data going back to the client. If you just write the image bytes to the response stream of the page the image will be on then you inadvertently write over all of the other bytes and only get the image. The added element needed to get the image and the other data to show up together is to write the picture to a second page (or UserControl's) stream and embed that UserControl on the main page. The user control contains an image, and the image's source (src or ImageUrl) attribute still refers to a file path-the path of an .ashx (IHttpHandler) file. The handler file puts the image's bytes into the response stream.
The basic solution emulates the most published assemblage of elements (published by me and others), but I have added some twists for a better overall result and more control over the image. Here are the steps:
- Create a Web page
- Define a SqlDataSource pointing to the Northwind's Categories table
- Add a template column form the Smart tags menu and add a template column for the Picture column
- Add a UserControl to the project and drag and drop the UserControl into the Picture's template column
- On the UserControl add an <img> tag to the UserControl
- Add a Generic Handler (.ashx file) to the project
- Change the <img> tag's src property to refer to the Handler.ashx file
- Add query parameters for the CategoryID, CategoryName, and Picture to the src's attribute
- Add script blocks to the UserControl reference in the Web page. You want to daisy chain the CategoryID, CategoryName, and Picture from the grid, to the UserControl, and pass it on to the Handler.ashx file
The Handler.ashx file does all of the work by rendering an image to a GDI+ canvas (the Graphics object) and drawing the CategoryName on the image. Finally, the image bytes are sent to the Handler's response stream. Listing 1 contains the ASP.NET for the Web page. (There is no code-behind for the Web page.) Listing 2 shows the ASP.NET for the UserControl and the UserControl's code-behind (see Listing 3). Listing 4 contains the Handler.ashx code.
Listing 1: The ASP.NET for the Web page shows the declarative code for the SqlDataSource and the binding statements for the UserControl.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <%@ Register src="PhotoControl.ascx" tagname="PhotoControl" tagprefix="uc1" %> <!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> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" /> <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" /> <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /> <asp:TemplateField HeaderText="Picture" SortExpression="Picture"> <ItemTemplate> <uc1:PhotoControl ID="PhotoControl1" runat="server" CategoryID='<%# Eval("CategoryID") %>' CategoryName='<%# Eval("CategoryName") %>' Picture='<%# Eval("Picture") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource> </div> </form> </body> </html>
Listing 2: The ASP.NET for the UserControl shows how to load the Handler.ashx file and pass it the nneeded values to tweak the image.
<%@ Control Language="VB" AutoEventWireup="false" CodeFile="PhotoControl.ascx.vb" Inherits="PhotoControl" %> <img alt="" src="Handler.ashx?CategoryID=<%# Eval("CategoryID") %>&CategoryName=<%# Eval("CategoryName") %>" />
Listing 3: The code-behind for the UserControl. The Picture is placed in the Application cache and everything else is passed as query parameters to Handler.ashx file.
Partial Class PhotoControl Inherits System.Web.UI.UserControl Protected Sub Page_Unload(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Unload Application(_categoryID.ToString()) = _picture End Sub Private _categoryID As Integer Public Property CategoryID() As Integer Get Return _categoryID End Get Set(ByVal Value As Integer) _categoryID = Value End Set End Property Private _categoryName As String Public Property CategoryName() As String Get Return _categoryName End Get Set(ByVal Value As String) _categoryName = Value End Set End Property Private _picture As Byte() Public Property Picture() As Byte() Get Return _picture End Get Set(ByVal Value As Byte()) _picture = Value End Set End Property Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub End Class
Listing 4: The code for the Handler.ashx file.
<%@ WebHandler Language="VB" Class="Handler" %> Imports System Imports System.Web Imports System.IO Imports System.Drawing Imports System.Drawing.Imaging Public Class Handler : Implements IHttpHandler Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest Dim categoryID As String = context.Request.QueryString("CategoryID") Dim categoryName As String = context.Request.QueryString("CategoryName") Dim picture As Byte() = context.Application(categoryID) If (picture Is Nothing) Then Return Dim oleHeader As Integer = 78 Dim stream As MemoryStream = New MemoryStream(picture, oleHeader, picture.Length - oleHeader) Dim image As Image = image.FromStream(stream) Dim bitmap As Bitmap = New Bitmap(image.Width, image.Height) Dim graphics As Graphics = graphics.FromImage(bitmap) graphics.DrawImage(image, 0, 0) Dim font As Font = New Font("Courier New", 10) graphics.FillRectangle(Brushes.Black, 0, image.Height - 22, image.Width, 22) graphics.DrawString(categoryName, font, Brushes.White, 2, image.Height - 20) context.Response.ContentType = "image\jpeg" bitmap.Save(context.Response.OutputStream, ImageFormat.Jpeg) End Sub Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property End Class
The explanation of the ProcessRequest method is divided into the next two sub-sections.
Removing the OLE Header
From information I found on the Web (because Northwind images didn't want to display, so I went searching for a reason) it sounds like Northwind images were originally created in Access-if memory serves this sounds right-and Access added an OLE (Object Linking and Embedding) header. The first 78 bytes of the image represents the header not part of the image. To display the Northwind images strip the header out of the image before rendering the image.
In the ProcessRequest method the MemoryStream strips the 78 leading bytes out of the image. If you aren't using OLE images then create the MemoryStream without the second two parameters (see Listing 4).
Customizing the Stored Image
The rest of the code in ProcessRequest method converts the bytes to an image. The bytes are retrieved from the Application cache. Next, a MemoryStream is created to load the bytes into a stream and from there into an image. A Bitmap is created based on the size of the image and a Graphics object (the GDI+ equivalent of an HDC canvas) is created from the bitmap. Once you have the Graphics object you can perform any task supported by GDI+. In the example the CategoryName is written on top of the Category picture.
When you are finished set the HttpContext response type and save the bitmap image to the HttpResponse.OutputStream. The results are show in Figure 1.
Figure 1: The images with text displayed in the grid.
Northwind images were created with Microsoft Access so they have a 78 byte OLE header. Strip the header out before rendering the image. If you use the Web page-template column-UserControl-IHttpHandler technique then you can send an image from the database right into a control, like a grid column. In the IHttpHandler.ProcessRequest method you can use a Bitmap object to create a Graphics object (GDI+ HDC/Canvas) and paint the image and anything else you want onto the canvas and return that to the client. The result is images from the database with any customization you can imagine.