Uploading,Saving and Retrieving Binary (image)Files With SQL Server, ASP.Net ,VB

Overview

SQL Server supports the ability for clients to store objects within tables. Generically known as Binary Large Objects (BLOBs), these objects can be complex data types and represent physical file objects. Common uses for this capability within the database layer include storing photos or thumbnail information for personnel databases, and storing specific content for Web sites such as images, or documents that can be retrieved and stored. The benefit of storing this information in binary format in the database is that the data is returned as part of the Tabular Data Stream. This eliminates file-level access and simplifies the overall physical implementation architecture. Also, the binary data can be backed up and restored along with the rest of the database.

Another major advantage of storing documents in the database is SQL Server’s built in capability to Full-Text Index the documents. This provides applications and clients the ability to search for words and phrases inside of a document stored within a table using the Full-Text search predicates built into TSQL (i.e., the CONTAINS operator). When a binary column is Full-Text Indexed in SQL Server, developers can write code using stored procedures that will search within the document, as well as meta data about the document.

To achieve the goal of storing, searching, and retrieving documents from SQL Server, it is important for developers to be able to write interfaces that enable users to place documents into the database as well as retrieve them. The retrieval methods should be provided through application searching interfaces (i.e., Web forms or Windows Forms and stored procedures).

This part of the Saving and Retrieving Binary Files with SQL Server series will focus on the database configuration portion of the solution. The second part of the series will expound upon the application configuration and coding portion of the solution.

SQL Server Setup

In establishing the data layer for allowing an application to store and retrieve binary objects, Microsoft recommends using several data types designed to support binary and text data. Developers will need to know how to best choose the correct data type for the data the application will store.

Data Type Selection

Binary data can be stored in several ways within a SQL Server table. Which storage methods are optimal depends on how much data storage is required for the application. The data types include binary, image, and varbinary, and the difference between these is primarily in their storage capabilities. A binary data type column must hold one row’s worth of data in a SQL Server table (equivalent to 8KB for each row). Similarly, the varbinary data type only allocates the amount of space required for the storage of the object up to 8KB. The relationship between the binary and varbinary is similar to the relationship between the char and varchar data type, in that the varchar column will expand to store the data in the column up to its specified limit.

The image data type is designed to hold data that is greater in size than 8KB. The image data type is the data type of choice for developers interested in storing complex binaries within the database.

Data Model

Since data will be stored in an image data type within a table, developers should give thought to storing additional data along with the binary document. Meta data such as file name, a description of the file, and the name of the user who uploaded the document is particularly valuable information that can be maintained and provided to the application to help users navigate the document store within the database. Additionally, the Full-Text Index feature of SQL Server requires the storage and specification for each document of its file extension to allow for searching.

Typically, in a Third Normal Form schema, this information will exist in an entity of its own, and can potentially be related to other entities within the database. Stored procedures should provide the capability to retrieve and store this information.

Binary Storage and Retrieval Stored Procedures

Binary document storage and retrieval can be accomplished through a two-layer interface from the database perspective. In essence, there should be a layer that solely provides the capability to upload and retrieve documents, and a layer that allows users to search for documents to be retrieved.

The document storage and retrieval stored procedures should be quite compact and elementary. The save stored procedure, for example, should accept an image parameter and other basic parameters dealing with the document meta data. This stored procedure conducts an insert into the table that stores the document and can be written as a simple insert statement using the parameters supplied.

The retrieval methodology should include a stored procedure that contains a series of methods to enable users to search for the document. One method involves maintaining a table of details (an independent table of meta data linked to the document table). The stored procedure can search through these details using the LIKE operator in TSQL. The other approach is to maintain a Full-Text Index on the documents table to allow searching using the CONTAINS operator within the documents. The stored procedure can combine these approaches with direct searches on the meta data using TSQL LIKE operators (i.e., users can search based on their name to see all documents they uploaded, the documents file name, upload dates, and so on). A bit-driven interface into the stored procedure can reflect which type of search should be conducted.

The aforementioned stored procedure should return information about the documents found and the primary key from the documents table. Then, the application can use this key to retrieve the specified document later in the application workflow. Using this approach, a user can search for documents without the overhead of bringing back multiple documents and their associated data. This allows the application to retrieve only the documents that are needed by the search. The application will pass the document primary key into a third stored procedure designed specifically for document data retrieval based specifically on the table’s primary key. The outline of the database objects are as follows:

Database Object	Description
Search Stored Procedure	Returns document search information by searching the meta data in the documents table or associated entities. This stored procedure uses a combination of Full-Text Indexing (by specifying the CONTAINS operator) or traditional TSQL searches (by specifying the LIKE operator). It searches on document-related data including upload dates, file names, and descriptions. This procedure returns the search information along with the document primary key for subsequent document retrieval.
Document Save Stored Procedure	Inserts document data as image data into the database table along with associated meta data, including the file type, file name, upload date/time, and so on.
Document Retrieval Stored Procedure	Retrieves the document from the database table by selecting the image column based on the document primary key input parameter.
Document Table	Contains an image column as well as other traditional columns to store the document binary data as well as supporting meta data. For Full-Text Indexing this column must store the document extension.
Supporting Tables	Tables that are related to the documents table via referential integrity that contain diversified data that can be searched in addition to the document meta data for document retrieval.

Table 1.1: Database Objects for document binary storage.

Implementation

Configuring and developing the database portion of the solution is relatively straight-forward. Much of the complexity of managing the conversion of binaries into the image data type is managed by the application. As a result, creating the data model and the stored procedures to facilitate the movement of documents in and out of the database is as simple as searching for a document via its primary key and returning the image column, or inserting a row from a stored procedure with an image parameter.

The searching component provided by the database is where SQL Server will be relied upon to conduct most of the work. By designing a versatile stored procedure, the application will be able to provide users with multiple methods of document location capability. It is important to undergo the traditional database development lifecycle and take into account concepts such as domain integrity, entity integrity and referential integrity, while paying strict attention to indexing and optimization of search queries. Because searching for documents requires much less flexibility than designing a search in a traditional OLTP application, developers have the luxury of avoiding dynamic SQL.

Additionally, developers and administrators must become familiar with all aspects of Full-Text indexing, including its architecture, the concept of incremental and full population and their scheduling requirements and other important aspects of maintaining a Full-Text Index.

The application structure is defined by multiple layers, each responsible for masking unnecessary complexity from the next highest layer. As a result, the implementation outline is a singular class which is responsible for all transmission to and from the database, and a consuming Web form responsible for obtaining the binary from the user’s machine.

Once the database layer has been established, varying types of applications can make use of the document storage and retrieval capabilities of the data model. This article will concentrate on .NET solutions, and speak abstractly about Web forms.

The general structure of the .NET Solution should revolve around utilizing a class for all document related operations, including binary storage and retrieval as well as searching. The Documents class can then be leveraged across all elements of the solution in this fashion. This class should also leverage a solution-wide implementation of a unified data layer which is responsible for conducting database operations, managing connections and returning formatted results sets in (i.e., DataSets, DataReaders, etc.). The Microsoft Data Access Application Block is a freely available data access layer that can be downloaded from Microsoft. This block will abstract the database interaction with in the Documents class and allow the passing of SqlParameters to the application block’s SqlHelper class.

The goal of the solution will be to obtain the document as a stream (System.IO.Stream) in .NET and serialize the stream to a SqlParameter of the VarBinary type. The Documents class will be responsible for this activity. The purpose of the Web form will be to provide an interface to allow the user to upload the document into an HttpPostedFile object.

Documents Class

The Documents class will serve as the primary facilitator for the transmission of the document to and from the database, as well as the returning of search results based on criteria provided by the user. The Documents class will directly interface with the SqlHelper class provided by the Microsoft Data Access Application Block. The first method of the class will be responsible for adding the document to the database. This method will be called by the Web form when the HttpPostedFile is obtained from the Web page’s upload control. The method will accept a System.IO.Stream which will be read into a System.Byte array. When declaring the Byte variable, the length of the array should be the length of the uploaded document Stream object.

As outlined in the example above, the docData parameter for the function is read into the FileData Byte array using the Read method of the Stream object. It is important to close the docData stream and then assign the FileData Byte array to the value of the DocumentData SqlParameter (defined as a public object in the class). Additional values are assigned to SqlParameters, representing document meta data, including the filename. The file name is later split in the stored procedure to independently store the file extension in a separate column in the documents table. This is a key element in enabling the Full-Text indexing of the document by informing SQL Server of the document type.

Additionally, the function provides the return value from the SQL Server Identity primary key column in the documents table corresponding to the row that was just inserted. This value is returned to the calling Web form and can be used for further processing (i.e., uploading document details and relating them to the data).

The corresponding method to return a specified document from the database based on the document table’s primary key (returned by the search results and selected by the user on the Web interface) utilizes the Byte array as well. The overall strategy employed by this method is to obtain the document as a column and row from the database using a stored procedure into a data reader and place it into a Byte array.

This method accepts the document primary key as an integer and assigns its value to a SqlParameter which is passed to the stored procedure designed to return the document data from the documents table. The stored procedure selects the image column by using the document primary key in its WHERE clause. The method then opens the DataReader returned from the SqlHelper class and defines a Byte array by getting the length of the document in the DataReader. This is accomplished by using the GetBytes method of the DataReader docFileReader to determine its length in bytes.

Once FileData is properly dimensioned, the next step is to read the data from the image column into the FileData array. Using the GetBytes method of the DataReader docFileReader again, we fill the Byte array with the data from the image column housed within the DataReader and return the Byte array FileData to the calling function. This method makes use of a try catch block within the class in order to ensure that the data reader is closed as well as to abstract the logic of Byte array construction from the calling method. The calling function (the Web form) will also encapsulate this method call in a try catch block in order to catch the exception bubbled up from the GetDocument function in its own catch block.

A third method of this class is the simple file search using the aforementioned stored procedure to return a DataSet comprised of search results to the Web form. The Web form will bind this DataSet to a grid which will allow users to select a specific document from their search results and obtain the document using the GetDocument method of the Documents class. This will be an event driven process using the data grid’s item command event to interpret the document selected by the user. The document primary key will be obtained from the DataSet bound to the grid and passed into the GetDocument method in order to retrieve the specified document as a download through the user’s browser.

Web Form

The Web form will contain a method that calls the SaveDocument and GetDocument functions in the Documents class. For facilitating the upload of documents through the Web, the Web form will have a HTML file control that will be run as a server control. The file control will allow the user to browse their local file system and transmit the selected file to the Web server when the Web form posts. To accomplish this task, the Web form houses a private function which is called from the upload control’s post back event. This event accepts the HttpPosted file and instantiates the class. The function then calls the SaveDocument method of the class which sends the document to the database.

            'Load the document to the database

     Dim fileStream As System.IO.Stream

     'Obtain a file stream to send to the stored procedure
     fileStream = upLoad.InputStream

     'Call the class method to save to the database
     documentID = Convert.ToString(doc.AddDocument(fileStream, fn))

Because the SaveDocument method in the Documents class is expecting a System.IO.Stream object, the HttpPostedFile must be converted into this format. As a result, the Web form’s private function to pass the binary to the Documents class assigns the result of the HttpPostedFile.InputStream to a System.IO.Stream variable. The function then returns the document table primary key of the row just inserted into the documents table in the database for further processing.

Implementation

The architecture of the solution is relatively simple. However, attention must be paid to the size of the SQL Server database once users begin to upload documents. Because the binary format of the document is a direct representation of the physical size of the document on the user’s computer, the database which stores the documents can grow quite rapidly. Planning for capacity and storage of the document is required, as well as thea adjustment of several database settings. Chief among those is the assurance that the database growth factor is set appropriately so that SQL Server does not have to grow the database in order to accommodate for increased file sizes (a costly operation).

Additionally, there should be a concerted effort to create and maintain an up-to-date Full-Text Index of all the documents. The Full-Text index is dissimilar to other forms of indexing in that SQL Server does not explicitly manage the Full-Text Index. The database developer must scheduled full and incremental index populations and be vigilant in ensuring that it includes all documents that have been uploaded.

--