In building a data-driven application, oftentimes both text and binary data needs to be captured. Applications might need to store images, PDFs, Word documents, or other binary data. Such binary data can be stored in one of two ways: on the web server's file system, with a reference to the file in the database; or directly in the database itself.
Text data - things like strings, numbers, dates, GUIDs, currency values, and so on - all have appropriate and corresponding data types defined in the database system being used. With Microsoft SQL Server, for example, to store an integer value you'd use the int data type; to store a string value you would likely use a column of type varchar or nvarchar. Databases also have types defined to hold binary data. In Microsoft SQL Server 2000 and earlier, use the image data type; for SQL Server 2005, use the varbinary(MAX) data type. In either case, these data types can hold binary data up to 2GB in size.
When storing binary data directly in the database, a bit of extra work is required to insert, update, and retrieve the binary data. Fortunately, the complex, low-level T-SQL needed to perform this work is neatly abstracted away through higher-level data access libraries, like ADO.NET. Regardless, working with binary data through ADO.NET is a bit different than working with text data. In this article we will examine how to use ADO.NET and the ASP.NET 2.0 SqlDataSource control to store and retrieve image files directly from a database. Read on to learn more!
Storing Data in the Database vs. Storing it in the File System
As mentioned in the Introduction, when capturing binary data in an application the binary data can either be stored directly in the database or saved as a file on the web server's file system with just a reference to the file in the database. In my experience, I've found that most developers prefer storing binary data on the file system for the following reasons:
- It requires less work - storing and retrieving binary data stored within the database involves a bit more code than when working with the data through the file system. It's also easier to update the binary data - no need for talking to the database, just overwrite the file!
- The URL to the files is more straightforward - as we'll see in this article, in order to provide access to binary data stored within a database, we need to create another ASP.NET page that will return the data. This page is typically passed a unique identifier for the record in the database whose binary data is to be returned. The net result is that to access the binary data - say an uploaded image - the URL would look something like
http://www.yourserver.com/ShowImage.aspx?ID=4352, whereas if the image were stored directly on the file system, the URL would be more straightforward, such as:http://www.yourserver.com/UploadedImages/Sam.jpg. - Better tool support for displaying images - if you're using ASP.NET 2.0, the ImageField can be used in the GridView or DetailsView to display an image given the path to the image from the database. The ImageField, unfortunately, will not display image data directly from the database (since it requires an external page to query and return that data).
- Performance - since the binary files are stored on the web server's file system rather than on the database, the application is accessing less data from the database, reducing the demand on the database and lessening the network congestion between the web and database server.
The main advantage to storing the data directly in the database is that it makes the data "self-contained". Since all of the data is contained within the database, backing up the data, moving the data from one database server to another, replicating the database, and so on, is much easier because there's no worry about copying over or backing up the binary content stored in the file system.
As always, what choice you make depends on the use case scenarios and business needs. For example, I've worked with one client where the binary data had to be stored in the database because the reporting software they used could only include binary data in the report if it came from the database. In another case, a colleague of mine worked on a project where the binary files needed to be available to the web application and available via FTP, which necessitated storing the binary data in the file system.
| Additional Reasons to Consider Storing Binary Data in the Database... |
|---|
Helpful reader Shan McArthur wrote in to share his suggestions:
Excellent article on how to store files in the database and deliver them through the web! As a CMS vendor, we have a lot of experience with this. Additional reasons for storing in the database are:Thanks for the feedback, Shan! |
Creating a Database Table to Store Binary Data
The remainder of this article explores a simple ASP.NET 2.0 image gallery application I wrote that uses Microsoft SQL Server 2005 Express Edition illustrate the concepts involved in storing and retrieving binary data directly from a database. The working demo application - along with the complete source code and database files - is available to download at the end of this article.
The image gallery application's data model consists of one table, Pictures, with a record for each picture in the gallery. The Pictures table's MIMEType field holds the MIME type of the uploaded image (image/jpeg for JPG files, image/gif for GIF files, and so on); the MIME type specifies to the browser how to render the binary data. The ImageData column holds the actual binary contents of the picture.
Uploading an Image and Using ADO.NET Code to Store the Binary Data
The image gallery allows visitors to upload picture files - GIFs, JPGs, and PNGs - to the application. Once uploaded, a new record is added to the Pictures table and the image file's contents are stored in that new record's ImageData column. To upload files from the web browser to the web server in ASP.NET 2.0, use the FileUpload control. Working with the FileUpload control is a walk in the park - just drag it onto your page from the Toolbox. The FileUpload control renders as the standard file upload in the user's browser - a Browse button that, when clicked, allows the user to select a single from from their hard drive to upload to the web server.
For example, to create an interface for adding a new image, I used a TextBox to capture the picture's title and a FileUpload control to allow the user to specify the image to upload:
<b>Title:</b> |
This results in a page from which the user can specify a file from their hard drive to upload to the web server.
Once the user has selected a file and posted back the form (by clicking the "Insert" button, for example), the binary contents of the specified file are posted back to the web server. From the server-side code, this binary data is available through the FileUpload control's PostedFile.InputStream property, as the following markup and code illustrates:
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click |
This event handler starts off by ensuring that a file has been uploaded. It then determines the MIME type based on the file extension of the uploaded file. (See the Internet Assigned Numbers Autority's MIME Media Types listing for a formal list of MIME types.)
The key lines of code to note are those where the @ImageData parameter is set. First, a byte array named imageBytes is created and sized to the Length of the InputStream of the uploaded file. Next, this byte array is filled with the binary contents from the InputStream using the Read method. It's this byte array that is specified as the @ImageData's value.
Uploading an Image and Using an ASP.NET 2.0 Data Source Control Code to Store the Binary Data
While the ADO.NET approach will work in an ASP.NET 2.0 application, you can also use ASP.NET 2.0's data source controls to store binary data in a database, which requires writing no ADO.NET code. The download available at the end of this article provides an example of using a SqlDataSource control and a DetailsView for adding new pictures to the gallery. (See Accessing Database Data for more information on using ASP.NET 2.0's SqlDataSource control.) The SqlDataSource control in this demo contains an InsertCommand and parameters for the Title, MIMEType, and ImageData values:
<asp:SqlDataSource ID="UploadPictureDataSource" runat="server" |
Note that the ImageData parameter does not have a Type specified. If you attempt to use the GUI wizard to build the SqlDataSource's syntax, it will likely assign it Type="Object". However, the Type="Object" results in a parameter type of sql_variant. sql_variants, however, cannot be used to store image or varbinary(MAX) data types because the sql_variant's underlying data cannot exceed 8,000 bytes of data. (If you leave in Type="Object" and then attempt to save binary data that exceeds 8,000 bytes, an exception will be thrown with the message: Parameter '@ImageData' exceeds the size limit for the sql_variant datatype; if you attempt to add binary data less than 8,000 bytes, the exception's message will read: Implicit conversion from data type sql_variant to varbinary(max) is not allowed. Use the CONVERT function to run this query..)
The DetailsView contains two TemplateFields - one with a TextBox for the Title column and one with a FileUpload control for the ImageData column. The net result is a user interface that looks just like the one shown in the "Uploading an Image and Using ADO.NET Code to Store the Binary Data" section. When the DetailsView's Insert button is clicked, it's Inserting event fires, at which point the binary data must be taken from the FileUpload control, read into a byte array, and assigned to the appropriate parameter:
Protected Sub UploadPictureUI_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles UploadPictureUI.ItemInserting |
Like with the Insert button's Click event handler in the ADO.NET example from the "Uploading an Image and Using ADO.NET Code to Store the Binary Data" section, the DetailsView's Inserting event handler performs the same logic with a few minor syntactical differences. First off, since the FileUpload control is within a template it must be programmatically referenced using the FindControl("controlID") method. Once it's been referenced, the same checks are applied to ensure that a file has been uploaded and that its extension is allowed. One small difference with the DetailsView's Inserting event handler is that if something is awry, we need to inform the DetailsView to stop the insert workflow. This is accomplished by setting the e.Cancel property to True.
After the checks pass, the MIMEType and ImageData parameters are assigned using the sytnax e.Values("parameterName") = value. Just like in the ADO.NET example, the binary data is first read into a byte array and then that byte array is assigned to the parameter.
Displaying the Binary Content
Regardless of what technique you employ to store the data in the database, in order to retrieve and display the binary data we need to create a new ASP.NET page. This page, named ShowPicture.aspx, will be passed a PictureID through the querystring and return the binary data from the specified product's ImageData field. Once completed, the a particular picture can be viewed by visiting /ShowPicture.aspx?PictureID=picutreID. Therefore, to display an image on a web page, we can use an Image control whose ImageUrl property is set to the appropriate URL.
The ShowPicture.aspx does not include any HTML markup in the .aspx page. In the code-behind class's Page_Load event handler, the specified Pictures row's MIMEType and ImageData are retrieved from the database using ADO.NET code. Next, the page's ContentType is set to the value of the MIMEType field and the binary data is emitted using Response.BinaryWrite(ImageData):
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load |
With the ShowPicture.aspx page complete, the image can be viewed by either directly visiting the URL or through an Image web control (or via static <img src="ShowPicture.aspx?ProductID=productID" ... /> markup). The first screen shot below shows an image when viewed directly through ShowPicture.aspx; the second screen shot shows the Default.aspx page of the image gallery, which uses an Image Web control within a FormView control, permitting the user to page through the pictures in the gallery.
| Improving the Performance and Saving Bandwidth When Accessing Binary Data from a Database |
|---|
Reader Shan McArthur wrote in with a suggestion for the ShowPicture.aspx page: "I would recommend implementing rudimentary caching by responding to the If-Modified-Since request header with a 304 Not Modified status (using a comparison to the DateUploaded column you conveniently have in your SQL table). This would substantially reduce the network traffic as visitors use your website and revisit pages. This would require that the developer also add Response.Cache.SetCacheability(HttpCacheability.Public) and Response.Cache.SetLastModified(myReader("DateUploaded")) to the serving page."
Shan is referring to supporting conditional GETs, which allow a client to say to the server, "I last received this content at date X." The server can then determine if the data being requested has changed since then. If it hasn't, it can response with a simple HTTP status code (304), saying, "Nah, it hasn't changed, go ahead and use whatcha got," thereby saving the overhead of pulling in the binary data from the database and sending it to the client. See HTTP Conditional Get for RSS Hackers for a more in-depth discussion and refer to the HTTP specification for more information on the |
Conclusion
When building data-driven applications where binary data must be captured, developers must decide whether to save the binary on the file system or to store it directly in the database. There are pros and cons to each choice, as discussed in this article. If you choose to save the binary data within the database, you'll need to take a little extra effort to insert, update, and retrieve the data. In this article we looked at how to upload image files directly to a database using both ADO.NET code and the ASP.NET 2.0 SqlDataSource.