Storing Binary Data to Database using ASP.NET 2.0

As a developer, you might face few requirements where you want to upload large documents, PDF's and images from your application. Then how do you manage and store such large data? Usually, traditional approach was to store those large files on web server's file system.

But you also have database approach which allows you to store those large documents like PDF's, .zip files, images etc., as binary data directly in the database itself. Let's elaborate on Database approach a bit further. How do we usually store large data objects in Databases like SQL Server 2000? Ok, SQL server 2000 supports exclusive image data type to hold image data. Now SQL Server 2005 supports another new data type varbinary which allows storing binary data up to 2GB in size.

Even with new data types, we still need to understand that working with binary data is not the same as straight forward working with text data. So, we are here to discuss how to use ASP.NET 2.0 SqlDataSource control to store and retrieve image files directly from a database.

We will create application which allows user to upload images and display the uploaded pictures. The uploaded images will be stored in database as binary data. You can download sample project, used in this tutorial. To hold image data, we need to create new table called PictureTable as shown below

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PictureTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PictureTable]
GO

CREATE TABLE [dbo].[PictureTable] (
[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateAdded] [datetime] NOT NULL ,
[MIMEType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Image] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Schema script for PictureTable

This table records details of pictures and content. The PictureTable 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 Image column holds the actual binary contents of the picture.

<asp:Label ID="Label1"  runat="server" Text="Upload Image"</asp:Label>
<asp:Label ID="Label2" runat="server" Text="Title"></asp:Label>
 <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
 <asp:Label ID="Label3" runat="server" Text="Image"></asp:Label>
 <asp:FileUpload ID="FileUpload1" runat="server" />
 <asp:Button ID="Button1" runat="server" Text="Upload"/>

As shown above, we are using Fileupload control to browse picture files on hard disk. FileUpload control is a composite control which includes a textbox and browse button together. To add this control, simply drag and drop FileUpload control from Toolbox as shown below.

Once user selects appropriate picture file using FileUpload control, click upload button which inserts selected image into PictureTable as new record. The logic to insert the image into PictureTable is handled in Click event of Upload button as shown below.

Protected Sub Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upload.Click
        Dim fileUpload1 As FileUpload = CType(Me.FindControl("fileUpload1"), FileUpload)
        'Make sure a file has been successfully uploaded
        If fileUpload1.PostedFile Is Nothing OrElse String.IsNullOrEmpty(fileUpload1.PostedFile.FileName) OrElse fileUpload1.PostedFile.InputStream Is Nothing Then
            Label1.Text = "Please Upload Valid picture file"
            Exit Sub
        End If
        'Make sure we are dealing with a JPG or GIF file
        Dim extension As String = System.IO.Path.GetExtension(fileUpload1.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
                Label1.Text = "Not a Valid file format"
                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], [Image]) VALUES (@Title, @MIMEType, @ImageData)"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@Title", TextBox1.Text.Trim())
            myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
            'Load FileUpload's InputStream into Byte array
            Dim imageBytes(fileUpload1.PostedFile.InputStream.Length) As Byte
            fileUpload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
            myCommand.Parameters.AddWithValue("@ImageData", imageBytes)
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
        End Using
    End Sub

Once the user has selected a file and posted back the form by clicking the "Upload" button, 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.

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. You can observe how @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.

Displaying binary Data

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 for this task. This page, named DisplayPicture.aspx, will be passed ImageID through the Querystring parameter and return the binary data from the specified product's Image field. Once completed, the particular picture can be viewed by browsing the link to view uploaded images. For example
http://localhost:3219/BinaryDataVb/Displaypicture.aspx?ImageID=5.

Therefore, to display an image on a web page, we can use an Image control whose ImageUrl property is set to the appropriate URL.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim ImageID As Integer = Convert.ToInt32(Request.QueryString("ImageID"))
 
        'Connect to the database and bring back the image contents & MIME type for the specified picture
        Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString)
 
            Const SQL As String = "SELECT [MIMEType], [Image] FROM [PictureTable] WHERE [ImageID] = @ImageID"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@ImageID", ImageID)
 
            myConnection.Open()
            Dim myReader As SqlDataReader = myCommand.ExecuteReader
 
            If myReader.Read Then
                Response.ContentType = myReader("MIMEType").ToString()
                Response.BinaryWrite(myReader("Image"))
            End If
 
            myReader.Close()
            myConnection.Close()
        End Using

    End Sub
Code listing for DisplayPicture.aspx

The DisplayPicture.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 Image 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(Image): When DisplayPicture.aspx page complete, the image can be viewed by either directly visiting the URL.

On the same way, we could call .zip, .pdf or any other binary file, stored in database system. If you want to get .zip file from database, you don't need to use Image control. Instead of using src parameter of Image control, for .zip and .pdf files use href parameter of hyperlink tag (e.g. <a href="GetZipFromDB.aspx?id=5">Get great zip file</a> )

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |