In last week’s column, I introduced how to store application
images in SQL Server. This included a basic interface for adding images to
the database table and using an image within an ASP.NET Web Form. In this
article, I’ll show you how to enhance the administration interface, and provide
you with a better approach to using the images.

Image maintenance

Inserting images into the database table is just one aspect
of administering the database. In addition, users will need to delete or edit
data, or even locate an image. So, we’ll build an interface via ASP.NET that
allows the user to edit image data, delete images, add a new image, and locate
an image via a search string entered by the user. The interface will utilize a DataGrid to display individual database entries. Each DataGrid row will contain the following:

  • ID: The unique integer assigned to
    individual images within the database table.
  • Name: An image name is assigned to
    an image when inserted. It is retrieved from the upload control, so it is
    the filename including the complete path from where it is inserted.
  • Type: The image type associated
    with the image.
  • Size: The size of the image in
    bytes.
  • Description: The image description
    entered by the user.
  • Image: The actual image is
    displayed in the DataGrid in a smaller scale
    (depending on the image). It may be thought of as a thumbnail (50 pixels
    wide by 40 pixels tall).
  • Buttons: Edit and Delete buttons
    are available for each entry. When the Edit button is selected, Update and
    Cancel buttons are displayed in place of the Edit button. The Update
    button makes the changes permanent and exits the Edit mode. The Cancel
    button ignores any changes and exits the Edit mode.

In addition, an Add New Image button above the DataGrid allows the user to add a new image to the
database. Also, a text box and Search button allows the user to search the
database. The database is searched for entries with names or descriptions
containing the search string entered in the text box when the Search button is
selected. The results are displayed in the DataGrid.
An additional Clear button is included to clear the search text box and reload the DataGrid with
all records.

Listing A
contains the VB.NET code for the page.
Listing B contains the equivalent C# code. Here are a few notes on the code:

  • The DataGrid’sOnEditCommand
    attribute ties its Edit command (EditCommandColumn)
    to the specified subroutine. This is the same with the OnCancelCommand
    for canceling an edit, OnUpdateCommand for
    saving edit changes, and OnDeleteCommand to
    delete individual rows.
  • The asp:BoundColumn elements tie DataGrid columns to specific columns in the associated
    dataset. The DataField attribute specifies the
    associated column.
  • ASP.NET
    Button controls facilitate searching and adding a new item. Their OnClick attributes allow you to assign a subroutine to
    it, so it is executed when the button is selected.
  • The DoSearch subroutine searches the database table for
    all entries containing the search text in the Description or Name fields.
    The matching rows are displayed in the DataGrid.
  • The ClearSearch method reloads the DataGrid
    with all data and clears the search Text Box.
  • The BindDataSet method handles the actual loading of data
    into the DataGrid and accepts a search string.
    If the search string passed in is empty, the T-SQL statement is set to
    load all rows from the database table. Otherwise, the search string is
    used to create the T-SQL to locate all matching records and display them
    in the DataGrid.

You may notice that the Add Image button redirects the user
to a page named AddImage.aspx. This is the page from
last week’s article where an image is added to the database. Also, the image
column within the DataGrid uses a page named DisplayImage.aspx. Basically, this page reads an image from
the database table and displays it. It uses the image id passed via the querystring id variable to locate the image in the table. Listing C contains the VB.NET for this page.

In addition, this page may be used throughout an application
to use images stored in the database. The ImageURL
attribute of the ASP:Image
control may be used as it is within the DataGrid:

<asp:Image Width="50" Height="40"  ImageUrl='<%# 
ImageLink(DataBinder.Eval(Container.DataItem, "ID")) %>' Runat=server />

You’ll need to include the ImageLink
routine as well:

Function ImageLink(strArgument) as String

Return ("displayImageVB.aspx?id=" & strArgument)

End Function

The downsides

Like most development strategies, using a database server to
store and manage images has its downsides. The obvious downside to this strategy
is additional resources since two different security environments must now be
managed with both a database and Web server. The major downside of storing
images in SQL Server is performance because of the following:

  • SQL
    Server breaks images up into chunks that fit on database pages. This makes
    reassembling the images slower than if they are stored, without additional
    internal structure, in a file.
  • Images
    stored in SQL Server are returned to the application via the TDS protocol
    and the data access APIs. Again, these are not optimal for image
    processing and impose overhead that doesn’t exist with a file.

While these are two glaring issues, the real decision comes
down to the application and how it will be used. A payroll or human resource
application may require the additional security and manageability offered by a
database server.

Gain control of site resources

This approach to managing and storing application images in
SQL Server provides one way to streamline the management of image resources
used within an application. This may be useful for sites with ever-changing
images. One example is an online human resources site that provides employee
snapshots. These pictures need to be deleted as persons exit the organization, added as new employees come aboard, or updated as current employees decide they want a more recent photo. Users will find the Web interface (or even a Windows Form
interface) more useable than browsing the file system.

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!