Data Management

Enhance the administration interface when storing images in SQL Server

Using SQL Server to store and manage application images can be a welcome approach, but the management interface must provide all features necessary to properly maintain the data. This ASP.NET article offers one approach to providing a useful interface.

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!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox