Manage application images with SQL Server

by Tony Patton | Jul 19, 2005 2:00:00 PM

Tags: .NET, Databases, Programming languages, System.IO namespace, server, Tony Patton, image, database, Microsoft SQL Server, .NET Newsletter

Takeaway: Images are an integral aspect of every Web site; however, it can be headache to manage a site's images. Discover how you can improve this process by using a database such as SQL Server as a storage vehicle.

Image files are a standard part of every ASP.NET application. While most sites use Web server directories to store image files, another option is utilizing a database. The database provides a better approach to managing application image files. In this article, we examine using SQL Server 2000 to manage image files with ASP.NET via both VB.NET and C#.

The backend

Using a database for image management may not be appropriate for smaller applications with images that rarely (if ever) change, but it is an excellent option for an application that contains numerous images that may frequently change. Also, additional information may be stored with the image to facilitate searching, sorting, and so forth.

The key to storing images in a database is the actual backend data store. In this article, we use a SQL Server 2000 table that contains identity, name, description, content type, file size, and image fields. The name and description columns are text. The identity column is numeric and the image data type is used for the actual image. The content type field is text and file size is integer, with these two fields used to display the image. The T-SQL in Listing A creates the table. In addition, we'll use a stored procedure to actually place images into the table. View Listing B. We utilize this stored procedure via our VB.NET and C# code to populate the table.

Placing images in the database

The first step in the process is inserting the images into the SQL Server database. This may be simplified (for the user) by providing a Web user interface. We can easily provide this interface via ASP.NET. The C# code in Listing C provides a simple interface, allowing the user to enter a description for the image and the actual image file via an HTML input field (System.Web.UI.HtmlControls.HtmlInputFile object). This allows the user to browse the file system and submit an image. (Listing D contains the equivalent VB.NET code.)

A few notes about this code:

• An HTML input field <input type=file> allows the user to select the necessary image file (which will be inserted into the database). The PostedFile property of this control provides access to the submitted file. An HttpPostedFile object is used to work with the submitted file.
• The encoding type of the form is set to multipart/form-data (enctype="multipart/form-data") to facilitate the submission of the image file.
• A label control is used to notify the user of any errors processing a submission. It is located at the top of the page, and it is only visible and contains text if an exception is encountered.
• The System.Data and System.Data.SqlClient namespaces are utilized to work with the backend database.
• The System.IO namespace is necessary for working with the stream to save the image.
• SqlParameter objects are used to submit values for the stored procedure parameters.
• The name associated with an image (Name field in database table) is populated via the filename property of the uploaded file. In addition, the content type and file size are retrieved from the uploaded file.
• Object cleanup is handled in the finally block.

Using the stored images

At this point, users may add image files to the database, so we can use these images in an application. The content type and file size stored with the image are used to spit the image to the screen (or wherever it is needed). The C# code in Listing E retrieves an image from the database and displays it via the Response object. (Listing F contains the VB.NET equivalent code.)

A few notes about the code:

• The image id is hard-coded, but we could easily pass a value into the page via the QueryString.
• Values retrieved from the database are cast to the necessary values for the output.
• The Response object is used to spit out the image stored in the database. The image is read and written as a byte stream using the Write method of the Response object's OutputStream property.

Just the beginning

Using a database as a vehicle for image storage and organization provides a much cleaner solution than using the standard file system. Next week, we'll take the concept further by enhancing the administration interface and offering a better approach to using the images on an ASP.NET powered site.

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!

Print/View all Posts Comments on this article

only if youJaqui  | 07/21/05

addendum..Jaqui  | 07/21/05

Aside from a potentialTony Hopkinson  | 07/21/05

can yaJaqui  | 07/22/05

I 've worked for places that have gone down theTony Hopkinson  | 07/23/05

~rotflmao~Jaqui  | 07/23/05

What if?lelandl  | 08/01/05

Source Codecyberhacker@...  | 07/21/05

Store Word Docs as Imagesallarddl35@...  | 07/21/05

Are you sure you want to do this ?Tony Hopkinson  | 07/21/05

Store Word Docpweegar@...  | 08/01/05

RE: Manage application images with SQL Serverkeeneydj@...  | 08/07/07