1. Introduction
When building data-driven applications, it is often necessary to capture text and binary data. Such a program may need to store images, PDFs, Word files, or other binary data. There are two ways to store this binary data: on the web server's file system and adding a reference to the corresponding file in the database; or directly in the database itself.
Textual data such as strings, numbers, dates, GUIDs, monetary values, etc. - have appropriate and corresponding data type definitions in the database system. For example, in Microsoft SQL Server, you can use the int data type to store an integer value; and to store a string value, you can use a varchar or nvarchar type. In addition, the database also provides type definitions for storing binary data. In Microsoft SQL SERVER 2000 and earlier versions, the image data type is used to store binary data; in SQL SERVER 2005, the varbinary (MAX) data type is used. These data types are capable of storing binary data up to 2GB in size using either of the two methods above.
However, when storing binary data directly in the database, some additional work is required to insert, update, and retrieve the binary data. Fortunately, we can abstract this complex low-level T-SQL operation through higher-level data access libraries - such as ADO.NET - so that the problem becomes quite simple. However, working with binary data via ADO.NET is a bit different from working with text data. In this article, we will analyze how to use ADO.NET and the ASP.NET 2.0 SqlDataSource control to store and retrieve image files directly from a database. Please read on!
2. Comparison between storing data in the database and storing it in the file system.
As just introduced, when capturing binary data in an application, the binary data can be stored directly in the database or as a file on the web. In the server's file system - only a reference to the file in the database is maintained. In my experience, I have found that most developers prefer storing binary data in the file system, mainly for the following reasons:
· Less effort required - Storing and retrieving binaries stored in a database requires more coding effort. Moreover, updating these binary data will be easier - no need to communicate with the database, just modify the file directly!
· URLs pointing to files are more direct - As we will see in this article, in order to provide access to binary data stored in a database, we need to create another ASP.NET page that can return that data. Typically, a unique identifier corresponding to the corresponding record in the database (returning its binary data) is passed to this page. The result is that in order to access the binary data - say an uploaded image - the URL looks like http://www.yourserver.com/ShowImage.aspx?ID=4352 , whereas if the image was stored directly in the file In the system, the URL will be more direct - for example http://www.yourserver.com/UploadedImages/Sam.jpg.
· Better tool support for displaying images - If you are using ASP.NET 2.0, you can use an ImageField control in a GridView or DetailsView control to display an image (its image path is stored in the database). However, unfortunately, this ImageField cannot directly display the image data in the database (since it requires querying an external page and returning the corresponding data).
· Performance - Since the binary files are stored in the web server's file system rather than in the database, the application can access less data in the database, thereby reducing the requirements on the database and correspondingly reducing the requirements on the web and Network congestion between database servers.
The main advantage of storing data directly in the database is that it makes the data "self-contained". Now that all data is contained in the database, data support, data movement between database servers, database replication, etc. are much easier because there is no need to worry about copying or backing up the binary content stored in the file system. question.
As always, which storage solution to choose will depend on the actual use site and business needs. For example, I developed a client where the binary data had to be stored in a database because the reporting software they were using was only able to include binary data in the report - if it came from the database. In another case, a colleague of mine was working on a project where the binaries needed to be used by a web application and available via FTP, in which case it was necessary to store the binary data in the file system.
3. Create a database table to store binary data
The rest of this article will analyze a simple ASP.NET 2.0 image gallery application that I wrote using Microsoft SQL Server 2005 Express Edition to demonstrate the direct Concepts related to storing and retrieving binary data in a database.
The data model of this image gallery application consists of a table - Pictures, where each record corresponds to a picture in the gallery. The MIMEType field of this Pictures table stores the MIME type of the uploaded image (image/jpeg for JPG files, image/gif for GIF files, etc.); the MIME type here specifies to the browser how to generate the binary data. The ImageData column stores the actual binary content of the image.
4. Upload an image and store binary data using ADO.NET code
This image gallery allows visitors to upload image files (GIF, JPG and PNG formats) into this application. Once uploaded, a new record is added to the Pictures table and the contents of the image file are stored in the new record's ImageData column. In order to upload files from the web browser to the web server in ASP.NET 2.0, the FileUpload control is used in this example. Using the FileUpload control is very simple - just drag it from the toolbar onto your page. Ultimately, this FileUpload control will be generated in the user's browser as a standard file upload form - a "Browse" button (when clicked) allows the user to select a file 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 control to capture the title of the image, and a FileUpload control to allow the user to specify the image to upload:
＜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" />
The above code creates a page where the user can specify a file from their hard drive to be uploaded to the web server.
Once the user selects a file and sends the form (for example, by clicking the "Insert" button), the binary content of the specified file is sent to the web server. From server-side code, this binary data becomes available through the PostedFile.InputStream property of the FileUpload control, as demonstrated by the following markup and code:
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert .Click
'Make sure a file is uploaded successfully
If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then
...display error message...
Exit Sub
End If
'Make sure we are working 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 upload... error message displayed...
Exit Sub
End Select
'Connect to the database and insert a new record into the Products table
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 the InputStream of the FileUpload control into a 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
SubHere, this event handler first ensures that a file has been uploaded. It then determines the MIME type based on the file extension being uploaded.
The most noteworthy part of the above is the section of code that sets the @ImageData parameters. First, create a byte array named imageBytes and make its length the corresponding InputStream of the file being uploaded. Then, use the Read method from the InputStream to fill the binary content into this byte array. Note that it is this byte array that is specified as the value of @ImageData.