ASP.NET: Uploading Documents to SQL Server and Retrieving them

I've just completed a new function within one of my applications that allows users to upload binary files (such as Word, Excel, PDF, Powerpoint and images) and store them within SQL Server itself, rather than loading the files onto the file system of the web server. This reduces the complexity of the system and means that a backup will take care of all user data, including their documents.

There are several steps in getting it set up, none of which are particularly complicated but the documentation I found online was a bit sketchy at best.

Aside from the database components, the whole lot was done in Visual Studio Express 2008 with Visual Basic.

SQL Server Database Layer
The requirement was to be able to upload documents and associate them with individual customers. In order for the browser to know what type of file is being returned, you have to store the MIME type as well as the other bits and bobs:

CREATE TABLE [dbo].[Client_Document](
[Client_Document_ID] [int] IDENTITY(1,1) NOT NULL,
[Filename] [varchar](128) NOT NULL,
[Description] [varchar](2048) NULL,
[Binary_Document] [varbinary] (MAX) NOT NULL,
[MIME_Type] [varchar](50) NULL,
[Upload_Date] [datetime] NULL,
[Uploaded_By] [varchar](255) NULL,
[User_ID] [varchar](7) NULL,
[Lob_ID] [numeric](38,0) NOT NULL

The actual file is in the column [Binary_Document] [varbinary] (MAX). Previously, to store files in the database itself, you were encouraged to use the old 'Image' data type, but since SQL Server 2005 this has been deprecated. Instead, use varbinary(MAX) which automatically scales depending on the size of the file inserted (up to 2Gb - you may want to check the file size in code to keep it to a sensible level).

Then you just need the usual CRUD stored procedures to INSERT, UPDATE, DELETE.

Data Access Layer (DAL)
First, add your TableAdapter to your XSD file, then add your methods which tie up to your stored procedures. Check that the data type of the Binary_Document column has been set to System.Byte().

Business Logic Layer (BLL)
Create a BLL for the Client Document which defines an adapter based on the Client_Document datatable. Add your BLL methods which map to the methods defined in your DAL. You can add some validation in here or just call the DAL methods directly, passing the arguments which have come from the calling ASP page.

Presentation Layer
On my ASP page, I have a GridView which displays the current documents associated with the selected customer, as well as a FormView which allows new documents to be added, as well as allowing updates to existing document details (or to overwrite the file with a new version).

Passing the documents into the database and retrieving them is pretty much the same as anything else you've done, except that you need to capture the binary stream of the selected file and pass it into the database. The file selector control comes for free from the .NET framework:

<asp:FileUpload ID="FileUploadClientDocument" runat="server" Width="320px" />

Then, when you call your methods to save or update you can check the attributes of the selected file, extract its filename, size and MIME type, and save them into the database using your BLL methods, e.g:

' If a file has been selected, load it into the database with its details
If FileUploadDocument.HasFile = True Then
Dim fileBytes(FileUploadDocument.PostedFile.InputStream.Length) As Byte
FileUploadDocument.PostedFile.InputStream.Read(fileBytes, 0, fileBytes.Length)

' Display the uploaded file's details
UploadDetails.Text = String.Format( _
"Uploaded file: {0}<br />" & _
"File size (in bytes): {1:N0}<br />" & _
"Content-type: {2}", _
FileUploadDocument.FileName, _
FileUploadDocument.FileBytes.Length, _
FileUploadDocument.PostedFile.ContentType)

' Insert
clientDocumentInfo.AddClientDocument(cloverClientId, FileUploadDocument.FileName, strDescription, fileBytes, FileUploadDocument.PostedFile.ContentType, strAddedBy, strAddedBySid, Session("lobId").ToString)

Make sure that you not only define the file's length (line 2), but actually use the Read method of the InputStream to add the data to the variable (I forgot to do this and got files in the database which were the right size but completely blank!).

UploadDetails is just a label which displays the attributes of the file which has been loaded (size, MIME etc).

Getting the files back is just a case of retreiving the file from the database using the stored proc, then setting the Response to match the MIME type and the file's binary stream.

In this case, clicking on the filename in the GridView fires the below code and returns the file to the browser:

If e.CommandName.CompareTo("GetClientDocument") = 0 Then
' Since we are not using the Select command, we have to get the selected Client Document ID from the command arguments.
Dim btn As LinkButton = DirectCast(e.CommandSource, LinkButton)
Dim gvr As GridViewRow = DirectCast(btn.NamingContainer, GridViewRow)
' Get the selected document ID
Dim clientDocumentId As Integer = GridViewClientDocuments.DataKeys(gvr.RowIndex)("Client_Document_ID").ToString()
' Invoke the DocumentBLL.AddDocument method to get the file from the database.
Dim clientDocumentInfo As New ClientDocumentBLL()
Dim clientDocuments As cleartrak.cleartrak.Client_DocumentDataTable
Dim clientDocument As cleartrak.cleartrak.Client_DocumentRow

' Get a table with the appropriate row in it
clientDocuments = clientDocumentInfo.GetClientDocumentById(clientDocumentId)
clientDocument = clientDocuments(0)
' Set the response to the MIME type of the document and its binary contents.
Response.ContentType = clientDocument.MIME_Type.ToString
Response.BinaryWrite(clientDocument.Binary_Document)
End If

That's it - the files are now saved in the database and can be retrieved by a single click.