Part 1  |  Part 2

Overview

In part one of “Saving and Retrieving Binary Files With SQL Server,” the database architecture and design for binary file storage through an .NET application was presented and explained. In this part of the series, the application architecture and design techniques will be presented to provide insight into serializing binaries and storing them in SQL Server.

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.

--

Eric Charran is currently working as a Technical Architect and Web Developer, in Horsham, PA. Eric holds Microsoft Certifications in SQL Server and has significant experience in planning, designing, and modeling n-tier applications using SQL Server 2000, as well as architecting and implementing .NET framework based applications, solutions and Web services. Eric’s other professional skills encompass Database Administration, Data Warehousing and Application Architecture, Modeling and Design, as well as Data Warehouse Transformation and Population using DTS. Eric is also skilled in developing and implementing ASP.NET and Windows forms applications, using technologies such as COM+, the .NET framework, VB.NET and other .NET languages.