Storing binaries in the database with ASP.net 2.0

First, the introduction

When building data-driven applications, you often need to capture text and binary data. Such programs may need to store images, Pdf,word files, or other binary data. Can store these binary data in two ways: stored on the Web server's file system and adding a reference to the corresponding file in the database, or directly stored in the database itself.

Text data, such as strings, numbers, dates, GUIDs, currency values, and so on-have the appropriate and appropriate data type definitions in the database system. For example, in Microsoft SQL Server, you can use an 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 provides a type definition for storing binary data. In Microsoft SQL Server 2000 and earlier versions, the image data type was used to store binary data, whereas in SQL Server 2005, the varbinary (MAX) data type was used. Using either of these two methods, these data types can store binary data up to 2GB in size.

However, when storing binary data directly in a database, additional work is needed to implement inserting, updating, and retrieving binary data. Fortunately, we can use more advanced data access libraries-such as ADO. The net-of this complex low-level T-SQL operation makes the problem quite simple. However, using binary data in a ado.net way is a bit different from using text data. In this article, we will analyze how to use the Ado.net and ASP.net 2.0 SqlDataSource controls to store and retrieve image files directly from a database. Please continue reading!

Second, the data stored in the database and stored in the file system comparison

As described earlier, when capturing binary data in an application, the binary data can be stored either directly in the database or as a file on the Web server's file system-keeping only a reference to the files in the database. Based on my experience, I find that most developers prefer to store binary data in file systems, mainly for the following reasons:

• Requires less work-storing and retrieving binaries stored in the database requires more coding work. Also, it would be easier to update these binary data-no need to communicate with the database, just modify the file directly!

• The URL to the file is more straightforward-as we'll see in this article, to provide access to binary data stored in a database, we need to create another ASP that can return that data. NET page. Typically, a unique identifier corresponding to the corresponding record in the database (which returns its binary data) is passed to the page. The result is, in order to access the binary data-say, an uploaded image-that URL looks like http://www.yourserver.com/ShowImage.aspx? id=4352, and if the image is stored directly in the file system, the URL will be more straightforward-for example, jpg/sam.jpg.

• Provide better tool support for displaying images-if you are using ASP.net 2.0, you can use the ImageField control in the GridView or DetailsView control to display an image (its image path is stored in the database). Unfortunately, however, this imagefield does not directly display the image data in the database (since it requires querying an external page and returning the corresponding data).

• Performance-Since binaries are stored on the Web server's file system rather than in the database, applications can access less data in the database, reducing the need for the database and correspondingly reducing network congestion between the Web and the database server.

The main advantage of storing data directly in a database is that it can make the data "self-contained". Since all of the data is contained in the database, it is much easier to support data, move data between database servers, and replicate databases, because there is no problem with the fear of replicating or backing up binary content stored in the file system.

As always, choose which storage scheme depends on the actual place of use and business needs. For example, I have developed a client where binary data must be stored in a database because the reporting software they use can only include binary data in the report-if it comes from a database. In another case, one of my colleagues is developing a project where binaries need to be used for Web applications and can be used via FTP, which is necessary to store binary data in the file system.

Third, create a database table to store binary data

The other sections of this article will analyze a simple asp.net2.0 image gallery application, which I wrote using Microsoft SQL Server Express Edition to showcase the concepts described in this article that store and retrieve binary data directly from a database.

This image gallery application's data model includes a table-pictures, each of which corresponds to a picture in the gallery. The mimetype domain of this pictures table stores the MIME type of the upload image (image/jpeg for jpg files, image/gif for GIF files, and so on); The MIME type here specifies how the binary data is generated by the browser. The ImageData column stores the actual binary content of the picture.