LANGUAGES: VB .NET | SQL
ASP.NET VERSIONS: 1.0 | 1.1
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: