LANGUAGES: VB .NET | SQL
ASP.NET VERSIONS: 1.x
Easy Uploads
Get files to your site with SQL server.
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.
(advertisement)
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.
(advertisement)
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.
(advertisement)
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.
(advertisement)
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 Microsoft's planned WinFS file system extension 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.
This article was originally published in
ASP.NET Pro Magazine.