Storing Binary Files Directly in the Database Using ASP.NET 2.0By Scott Mitchell
|For More Information on Working with Binary Data...|
|In February 2008 I presented a talk at the San Diego ASP.NET SIG on storing binary data in web applications. The talk covered both storing data in the web server's file system and storing data directly in the database. You can download the PowerPoint slides and demo application at http://datawebcontrols.com/classes/BinaryData.zip.|
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
data type; to store a string value you would likely use a column of type
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
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:
- 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.
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.
gallery application's data model consists of one table,
Pictures, with a record for each picture in the gallery.
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
Picturestable and the image file's contents are stored in that new record's
ImageDatacolumn. 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:
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:
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
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
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
InsertCommandand parameters for the
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
results in a parameter type of
sql_variants, however, cannot be used to store
varbinary(MAX) data types
sql_variant's underlying data cannot exceed 8,000 bytes of data. (If you leave in
and then attempt to save binary data that exceeds 8,000 bytes, an exception will be thrown with the message:
@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
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:
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,
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
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
PictureIDthrough the querystring and return the binary data from the specified product's
ImageDatafield. 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
ImageUrlproperty is set to the appropriate URL.
ShowPicture.aspx does not include any HTML markup in the
.aspx page. In the code-behind class's
Page_Load event handler, the specified
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
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
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 |
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
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.