asp:Cover Story

LANGUAGES: VB .NET | SQL

ASP.NET VERSIONS: 1.0 | 1.1

 

Easy Uploads

Get files to your site with SQL server.

 

By Steve C. Orr

 

These days you have many reasons to allow uploading to your Web site, from simple member photos to full-featured document-management systems. In the past, the complexity involved was enough to make you think twice about adding such a feature. With ASP.NET, however, the process has been simplified dramatically. In this article, I'll pull together the pieces you need to implement this functionality in your Web application.

 

When designing a file-upload system, you begin to realize it's composed of two main parts: the code that transfers the file from the client to the server (and maybe back again) and the code that manages the files once they've been uploaded.

 

For a simple system with few users, you might get away with simply tossing the files into a folder on your Web server. But more advanced systems require a more advanced file-management system. For instance, you need a custom garbage collector to clean up old files, a system to manage duplicate filenames, and a system for tracking descriptions and other metadata about each file. You also might need some level of security to protect the files from tampering, whether accidental or purposeful. You could write your own system to manage all this, but why bother? SQL Server does it all, and you're using it to store the rest of your data already - why treat files so differently?

 

Storing files in SQL Server is easy, and it's more reliable and secure. The files are tucked away safely in your database instead of lying around on a Web server where they could be subjected to deletion, moving, renaming, or any number of other seemingly harmless actions that could wreak havoc on an unsuspecting software system. Best of all, the files are backed up automatically as part of your normal database-maintenance routine. Another nice aspect of this technique is there are no headaches related to file or folder permissions on your Web server.

 

Now that you've decided to let SQL Server manage the most complex part of your system, you need to create a table in which to store your files. Create a table in your database named tblAttachments and configure it as shown in Figure 1.

 

Figure 1. You need only a few specific fields in your table to get the desired functionality.

 

As you can see, you need to store the size of the file (in bytes). You also need to store the Content Type so the browser knows how to interpret the data you'll end up throwing at it. You store the file data itself in an image field. Don't let the name fool you - it can store any type of data, images or otherwise.

 

Create the Main Web Form

To begin, open a Visual Studio .NET project and add a new Web form to it. You must let the user's browser know you will be working with some outside files from this Web page. To do this, switch to the HTML view of your form and add an enctype attribute to your existing form tag so it looks like this:

 

<form id="Form1" method="post" runat="server"

   enctype="multipart/form-data">

 

Next, switch back to design mode and drag a SQLConnection control onto your form from the data section of the toolbox. Name it dbConn and configure its connection string property appropriately for your database.

 

Now that you have a general connection to your database, you need to give it some specific commands. Drag a SQLCommand control onto your form from the same area of the toolbox. Name it cmdInsertAttachment and set its connection property to dbConn. Set the CommandText property to this SQL statement:

 

INSERT INTO tblAttachments

(FileName, FileSize, FileData, ContentType)

VALUES (@FileName, @FileSize, @FileData, @ContentType)

 

Because putting files into SQL Server isn't useful unless you have a way to retrieve them, you should add another command to handle your select statement. Name this new SQLCommand control cmdSelectAttachments and set its connection property to dbConn. Set the CommandText property of your new control to this SQL statement:

 

SELECT Attachment_ID, FileName, FileSize, UploadDate

FROM tblAttachments

 

This statement will give you a list of all the attachments in your table. Later you'll hook it up to a datagrid to give users access to their files. But for now, let's get back to the details of getting the files into the database by putting a user interface on your page.

 

Drag a file-field control onto your form from the toolbox's HTML section. This essential control provides the functionality necessary to upload a binary or text file from the client to the server. It also provides a field for the user to type in the filename and a browse button for convenience. Because it is an HTML control, you need to right-click on it and ensure that "run as server control" is checked to let you work with the control's methods and properties from your server-side code.

 

Next, drag a button control onto your form from the Web Controls section of the toolbar. Name it btnAttach and set its text property to Attach Now. You'll put your code to upload the file and store it in SQL Server in this button's click event. As you can see from the code in Figure 2, there's not much to it, especially compared to classic ASP.

 

Private Sub btnAttach_Click(ByVal sender As _

System.Object, ByVal e As System.EventArgs) _

Handles btnAttach.Click

    Dim iLength As Integer = _

CType(File1.PostedFile.InputStream.Length, Integer)

    If iLength = 0 Then Exit Sub 'not a valid file

    Dim sContentType As String = _

File1.PostedFile.ContentType

    Dim sFileName As String, i As Integer

    Dim bytContent As Byte()

    ReDim bytContent(iLength) 'byte array, set to file size

 

    'strip the path off the filename

    i = InStrRev(File1.PostedFile.FileName.Trim, "\")

    If i = 0 Then

        sFileName = File1.PostedFile.FileName.Trim

    Else

         sFileName = Right(File1.PostedFile.FileName.Trim, _

Len(File1.PostedFile.FileName.Trim) - i)

    End If

 

    Try

        File1.PostedFile.InputStream.Read(bytContent, 0, _

iLength)

        With cmdInsertAttachment

            .Parameters("@FileName").Value = sFileName

            .Parameters("@FileSize").Value = iLength

            .Parameters("@FileData").Value = bytContent

            .Parameters("@ContentType").Value = _

sContentType

            .ExecuteNonQuery()

        End With

    Catch ex As Exception

        'Handle your database error here

        dbConn.Close()

    End Try

    Response.Redirect(Request.Url.ToString) 'Refresh page

End Sub

Figure 2. In ASP.NET, you need surprisingly little code to upload a file and stash it away in the database.

 

Download Files

What good is it to have files stored in SQL Server if you don't have a way to get them out? It's time to add some functionality to this form to let the user list and interact with the files you've stored.

 

You could use a variety of controls to provide the file-list functionality. Each has its trade-offs. For this example you'll use one of the more full-featured controls so you can expand the functionality as needed for your application.

 

Drag a datagrid onto your form, name it grdAttachments, and set its AutoGenerateColumns property to False. You will manually customize the columns to appear in your grid and how they act. To do this, right-click on the grid and select Property Builder. Click on columns, located on the right side of the dialog box. Add a hyperlink column and set its Header Text to File Name. Set its text field to FileName to associate this grid column with the database field of that name. Set its URL Field to Attachment_ID and its URL Format String to ViewAttachment.aspx?AttachmentID={0}. This references the new Web form you're about to create to display a file's contents in the browser.

 

You also might want to add a couple of bound columns to your grid to display other details about the file, such as the file size and upload date. To do this, remember to set the data field to the name of the database field you want to display in that grid column.

 

You should now have a Web form that, at design time, looks something like Figure 3.

 

Figure 3. Your form now should look similar to this at design time. The important controls here are the data controls, the file-field control, the datagrid, and a button.

 

Now you need to hook up your grid to the cmdSelectAttachments control you added to the form earlier. To do this, put this code into your Page_Load event:

 

Dim myDataReader As System.Data.SqlClient.SqlDataReader

dbConn.Open()

myDataReader = cmdSelectAttachments.ExecuteReader

grdAttachments.DataSource = myDataReader

grdAttachments.DataBind()

myDataReader.Close()

 

If you like, you can run your program now to test your progress thus far. You should be able to upload files and view a list of the files you've uploaded. Of course, you won't actually be able to view the files you've uploaded until you add the final piece of the puzzle.

 

Download and View Files

Add a new form to your Web project and name it ViewAttachment.aspx. Switch to HTML mode and delete everything you see except the top line. The only line left should look something like this:

 

<%@ Page Language="vb" AutoEventWireup="false"

Codebehind="ViewAttachment.aspx.vb"

Inherits="CIT.ViewAttachment"%>

 

Why delete all the HTML, you ask? Because this page won't send any HTML to the user's browser. Instead, it'll stream the file's contents to the browser, and the browser will decide how best to display the data based on the content type you give it.

 

But first things first. You need a connection to the database so you can retrieve the file. Switch back to design mode (if you haven't already) and drag a SQLConnection control onto your form from the toolbox's data section. Name it dbConn and configure its connection string property appropriately for your database.

 

Next, drag a SQLCommand control onto your form, name it cmdGetFile, and set its connection property to dbConn. Set the CommandText property to this SQL statement:

 

SELECT FileSize, FileName, FileData, ContentType

FROM tblAttachments WHERE (Attachment_ID = @Attachment_ID)

 

Now for the final piece of code. Add the statements in Figure 4 to your Page_Load event, then execute your code. Now everything should be completely functional.

 

Dim dr As System.Data.SqlClient.SqlDataReader

 

cmdGetFile.Parameters("@Attachment_ID").Value = _

Request("AttachmentID").ToString

 

dbConn.Open()

dr = cmdGetFile.ExecuteReader

 

If dr.Read Then

    Response.ContentType = dr("ContentType").ToString

Response.OutputStream.Write(CType(dr("FileData"), _

  Byte()), 0, CInt(dr("FileSize")))

    Response.AddHeader("Content-Disposition", _

      "attachment;filename=" + dr("FileName").ToString())

 

Else

       Response.Write("File Not Found.")

End If

Figure 4. You write the raw file data directly into the Output Stream being sent to the browser.

 

First, the code grabs the ID of the attachment the datagrid sent via the query string, then it executes the SQL Command object to get the file contents and associated data. The next step is crucial. You set the Content Type property of the Response object to the same type you got out of the file when it was uploaded originally. This is sent to the browser, and the browser decides how best to deal with that type of content. The final required step is to grab the data and convert it back into a byte array as you had it originally, then you write the bytes directly into the Response object's Output Stream. The final line, which is somewhat optional, adds a Header to the Response that specifies the name of the file being downloaded. This can be useful if the user chooses to save the file to disk; it will guarantee that the default filename the user is presented is the same as it was when the file was uploaded.

 

Know the Limitations

No technology is perfect. Uploading your files into SQL Server is a great technique to use in many situations, but it's important to understand both the benefits and drawbacks of any coding technique before settling on the best solution for your particular project. So let's clear some hurdles that might affect you in implementing a system similar to the one I describe in this article.

 

Storing files in SQL Server is not considered particularly scalable, especially if you are dealing with many large files. SQL Server takes some time to stream out the contents of a large file across a limited-bandwidth Internet connection because a significant amount of its resources are tied up while performing this operation. Therefore, uploading files to SQL Server tends to work better for small- to medium-sized projects, which happens to be the size of most projects. Of course, the right combination of hardware and infrastructure can go a long way toward increasing the scalability of a solution like this.

 

There are some rather closed-minded developers out there who would go so far as to say SQL Server is an inappropriate place to store files of any type. I hope this article proves otherwise. If you are still among the skeptical, I point to examples such as Oracle, which is used as the file system for the Solaris operating system. And the next version of Windows (codenamed Longhorn) reportedly will replace the NTFS file system with a new one (named WinFS) based on SQL Server. So in the future, you'll be saving all your files in a database whether you want to or not.

 

Even so, if you determine that SQL Server is not the best place to store the files for your particular project, the main alternatives are to write your own file-management system or buy a third-party solution, which can get quite pricey.

 

No matter what kind of file-download system you implement, you should keep in mind that, ultimately, it is the browser that decides what to do with the files sent to it. Different browsers can make slightly different decisions. For instance, Internet Explorer might choose to open a Word document embedded within its own window, though some versions of Netscape might open it in a separate window with an independent instance of Word. But you have little control over this kind of behavior from the server side - that's life on the Internet. Using the techniques I've described in this article, hopefully you'll find a solution that works well for your project. Good luck and happy coding!

 

The sample code in this article is available for download.

 

Steve C. Orr is an MCSD from the Seattle area who has been programming in various flavors of BASIC for nearly 20 years. He's worked on numerous projects for Microsoft and currently works with The Cadmus Group Inc., a company focused on software development and ecological issues. Find him at http://Steve.Orr.net or e-mail him at mailto:Steve@Orr.net.

 

Tell us what you think! Please send any comments about this article to mailto:feedback@aspnetPRO.com. Please include the article title and author.