and Files with SQL Server: Part 1 |
Article Categories: Data Access | Quick Code
Rated: | Number Of Ratings 11 | Add Rating/View Ratings
This article is the first in a series of using images and files with SQL Server and focuses first on uploading binary data to the database. The code is actually pretty straight forward, but before talking about the code, let's setup our table.
Create a table in your database called "TestFiles" with the following columns/data types (you can alter these as you see fit for length of filename and file type):
We're storing the FileName in "MyFileName", the actual file in "MyFile" and the type of file in "FileType".
The code below creates a form which has inputs for a filename, and the actual file to be uploaded. After the file is submitted we use the HtmlInputFile Class property PostedFile to get access to the file posted by our Web browser. Once we've done that we can access the HttpPostedFile Class properties and methods to read data, save data, and get other information about the file.
In our case we won't be using the method SaveAs because this method writes a file to the Web server. Since we want to store the information in our database, we need to use the property InputStream which initializes a stream for reading our data. At the same time we're using the ContentLength property to get the size of the file in bytes for reading the data and using the property ContentType to store the type of file we're uploading. What's nice about using that property is the user doesn't have to worry about selecting a file type (for instance from a drop down menu) and possibly not selecting the right one, resulting in an error when the file is read back from the database.
Once we have that information we can create a new byte array and read our file stream into it. From there we simply upload the data into our Image column.
Below is the full code for the page with more notes and information following:
Notice that we're allowing the user to set a File Name through the form itself. However this can also be accomplished through using the HttpPostedFile Class property FileName. The one thing to remember however is that this will return the fully qualified name of the actual file on the user's computer as in C:\Inetpub\wwwroot\images\map.gif, so if you use this property to get the name of the file you may want to manipulate the string before inserting it into your database. Also note that for these examples we're not doing any error checking for brevity of code.
As you can see, compared to classic ASP this is much easier and allows easy storing of binary data into a SQL database. In Part 2 we'll look at different ways to retrieve images and files from the database and write them to the browser as well as forcing "Save As" dialog boxes with a filename and extension.
Original articles and site content may not be reproduced from our
site without consent from superdotnet.com.