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. 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.
File upload control on toolbox
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
picture using QueryString parameter
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> )
