Storing Binary Files Directly in the Database Using ASP.NET 2.0

---

Introduction

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.

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:

&lt;b>Title:</b> <asp:TextBox ID="PictureTitle" runat="server" /> <br /> <b>Picture:</b> <asp:FileUpload ID="UploadedFile" runat="server" /> <br /> <asp:LinkButton ID="btnInsert" runat="server" Text="Insert" /> <asp:LinkButton ID="btnCancel" runat="server" Text="Cancel" />

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 'Make sure a file has been successfully uploaded If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then ... Show error message ... Exit Sub End If 'Make sure we are dealing with a JPG or GIF file Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower() Dim MIMEType As String = Nothing Select Case extension Case ".gif" MIMEType = "image/gif" Case ".jpg", ".jpeg", ".jpe" MIMEType = "image/jpeg" Case ".png" MIMEType = "image/png" Case Else 'Invalid file type uploaded ... Show error message ... Exit Sub End Select 'Connect to the database and insert a new record into Products Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString) Const SQL As String = "INSERT INTO [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@Title", PictureTitle.Text.Trim()) myCommand.Parameters.AddWithValue("@MIMEType", MIMEType) 'Load FileUpload's InputStream into Byte array Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) myCommand.Parameters.AddWithValue("@ImageData", imageBytes) myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() End Using End Sub

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" ConnectionString="..." InsertCommand="INSERT INTO [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)"> <InsertParameters> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="MIMEType" Type="String" /> <asp:Parameter Name="ImageData" /> </InsertParameters> </asp:SqlDataSource>

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 'Reference the FileUpload control Dim UploadedFile As FileUpload = CType(UploadPictureUI.FindControl("UploadedFile"), FileUpload) 'Make sure a file has been successfully uploaded If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then ... Show error message ... e.Cancel = True Exit Sub End If 'Make sure we are dealing with a JPG or GIF file Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower() Dim MIMEType As String = Nothing Select Case extension Case ".gif" MIMEType = "image/gif" Case ".jpg", ".jpeg", ".jpe" MIMEType = "image/jpeg" Case ".png" MIMEType = "image/png" Case Else 'Invalid file type uploaded ... Show error message ... e.Cancel = True Exit Sub End Select 'Specify the values for the MIMEType and ImageData parameters e.Values("MIMEType") = MIMEType 'Load FileUpload's InputStream into Byte array Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) e.Values("ImageData") = imageBytes End Sub

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 Dim PictureID As Integer = Convert.ToInt32(Request.QueryString("PictureID")) 'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString) Const SQL As String = "SELECT [MIMEType], [ImageData] FROM [Pictures] WHERE [PictureID] = @PictureID" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@PictureID", PictureID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader If myReader.Read Then Response.ContentType = myReader("MIMEType").ToString() Response.BinaryWrite(myReader("ImageData")) End If myReader.Close() myConnection.Close() End Using End Sub

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.

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.

Happy Programming!

By Scott Mitchell