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 toyour Web site, from simple member photos to full-featured document-managementsystems. In the past, the complexity involved was enough to make you thinktwice about adding such a feature. With ASP.NET, however, the process has beensimplified dramatically. In this article, I'll pull together the pieces youneed to implement this functionality in your Web application.

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

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

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

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

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

As you can see, you need to store the size of the file (inbytes). You also need to store the Content Type so the browser knows how tointerpret the data you'll end up throwing at it. You store the file data itselfin 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 newWeb form to it. You must let the user's browser know you will be working withsome outside files from this Web page. To do this, switch to the HTML view ofyour form and add an enctype attribute to your existing form tag so it lookslike this:

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

   enctype="multipart/form-data">

Next, switch back to design mode and drag a SQLConnectioncontrol onto your form from the data section of the toolbox. Name it dbConn andconfigure 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 yourform from the same area of the toolbox. Name it cmdInsertAttachment and set itsconnection property to dbConn. Set the CommandText property to this SQLstatement:

INSERT INTO tblAttachments

(FileName, FileSize, FileData, ContentType)

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

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

SELECT Attachment_ID, FileName,FileSize, UploadDate

FROM tblAttachments

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

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

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

Private Sub btnAttach_Click(ByValsender As _

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

Handles btnAttach.Click

    Dim iLength As Integer= _

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

    If iLength = 0 ThenExit Sub 'not a valid file

    Dim sContentType AsString = _

File1.PostedFile.ContentType

    Dim sFileName AsString, i As Integer

    Dim bytContent AsByte()

    ReDimbytContent(iLength) 'byte array, set to file size

    'strip the path offthe 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)

        WithcmdInsertAttachment

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

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

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

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

sContentType

            .ExecuteNonQuery()

        End With

    Catch ex As Exception

        'Handle yourdatabase error here

        dbConn.Close()

    End Try

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

End Sub

Figure 2. In ASP.NET, you need surprisingly littlecode 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 youdon't have a way to get them out? It's time to add some functionality to thisform to let the user list and interact with the files you've stored.

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

Drag a datagrid onto your form, name it grdAttachments,and set its AutoGenerateColumns property to False. You will manually customizethe columns to appear in your grid and how they act. To do this, right-click onthe grid and select Property Builder. Click on columns, located on the rightside of the dialog box. Add a hyperlink column and set its Header Text to FileName. Set its text field to FileName to associate this grid column with thedatabase field of that name. Set its URL Field to Attachment_ID and its URLFormat String to ViewAttachment.aspx?AttachmentID={0}. This references the newWeb 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 toyour grid to display other details about the file, such as the file size andupload date. To do this, remember to set the data field to the name of thedatabase field you want to display in that grid column.

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

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

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

Dim myDataReader AsSystem.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 yourprogress thus far. You should be able to upload files and view a list of thefiles you've uploaded. Of course, you won't actually be able to view the filesyou'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 itViewAttachment.aspx. Switch to HTML mode and delete everything you see exceptthe 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'tsend any HTML to the user's browser. Instead, it'll stream the file's contentsto the browser, and the browser will decide how best to display the data basedon the content type you give it.

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

Next, drag a SQLCommand control onto your form, name itcmdGetFile, and set its connection property to dbConn. Set the CommandTextproperty 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 inFigure 4 to your Page_Load event, then execute your code. Now everything shouldbe completely functional.

Dim dr AsSystem.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("FileNot Found.")

End If

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

First, the code grabs the ID of the attachment thedatagrid sent via the query string, then it executes the SQL Command object toget the file contents and associated data. The next step is crucial. You setthe Content Type property of the Response object to the same type you got outof the file when it was uploaded originally. This is sent to the browser, andthe browser decides how best to deal with that type of content. The finalrequired step is to grab the data and convert it back into a byte array as youhad it originally, then you write the bytes directly into the Response object'sOutput Stream. The final line, which is somewhat optional, adds a Header to theResponse that specifies the name of the file being downloaded. This can beuseful if the user chooses to save the file to disk; it will guarantee that thedefault filename the user is presented is the same as it was when the file wasuploaded.

Know the Limitations

No technology is perfect. Uploading your files into SQLServer is a great technique to use in many situations, but it's important tounderstand both the benefits and drawbacks of any coding technique before settlingon the best solution for your particular project. So let's clear some hurdlesthat might affect you in implementing a system similar to the one I describe inthis article.

Storing files in SQL Server is not considered particularlyscalable, especially if you are dealing with many large files. SQL Server takessome time to stream out the contents of a large file across a limited-bandwidthInternet connection because a significant amount of its resources are tied upwhile performing this operation. Therefore, uploading files to SQL Server tendsto work better for small- to medium-sized projects, which happens to be thesize of most projects. Of course, the right combination of hardware andinfrastructure can go a long way toward increasing the scalability of asolution like this.

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

Even so, if you determine that SQL Server is not the bestplace to store the files for your particular project, the main alternatives areto write your own file-management system or buy a third-party solution, whichcan 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 whatto do with the files sent to it. Different browsers can make slightly differentdecisions. For instance, Internet Explorer might choose to open a Word documentembedded within its own window, though some versions of Netscape might open itin a separate window with an independent instance of Word. But you have littlecontrol over this kind of behavior from the server side - that's life on theInternet. Using the techniques I've described in this article, hopefully you'llfind a solution that works well for your project. Good luck and happy coding!

The sample code in thisarticle is available for download.

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

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