File Upload and Save Submitted Files to SQL Server 2005, Sample ASP.NET Web Site Application

Saving files into SQL Server is a method that we programmers may apply even it is uncommon. Keepin images like photos, or web desing components like graphics, backgrounds, etc will cost an overhead if the read image file is not cached and has to be fetched and read from the database each time it is requested. So if you decide to keep your web application's images or documents, in short binary data in SQL Server, make sure that you have valid reasons for doing so.

In this document, I'll try to explain the codes of a sample web site application which web users can upload files of certain types into a Microsoft SQL Server 2005 database by using the GUI designed for this purpose. Users may also select uploading the files to a web folder instead of saving the binary files into a database. In an other page of this web application, a list of uploaded and saved in SQL database files will be listed in a web page using a GridView ASP.NET object. In this list page by clicking a hyperlink over the listed file item, a new page will display the image or serve the requested file after reading it from the SQL 2005 database.

The database in which the binary files will be kept has to contain the following table

GO
CREATE TABLE [dbo].[Files](
[id] [int] IDENTITY(1,1) NOT NULL,
[fname] [nvarchar](1000) NOT NULL,
[file] [varbinary](max) NULL
)
GO


One important point about this file is it that it uses the [file] column of type varbinary(max) to keep the submitted or uploaded binary files, including images, documents, executables, etc. It is noteworthy that since SQL Server will not support "image" data type as well as the text and ntext data types with the next release "Katmai", you should use the varbinary(max) data type column to manage files up to 2 GB in sizes without a problem in future versions of SQL Server.

FileUpload.aspx

Let's start examining the sample application from page FileUpload.aspx. This page has one important web control which is the FileUpload control.

<asp:FileUpload ID="FileUpload1" runat="server" Width="454px" />

You can add a button to submit the web form after the file is selected in order to upload. When the ASP.NET button web control is clicked the page posts backs and you can read and save the submitted binary file data with the submitted form.

Here is some important parts of the btnUpload_Click event code.

If FileUpload1.HasFile Then
...

The above if statement controls whether any file is submitted or not with the form. If a file is selected in the FileUpload control and it is submitted FileUpload1.HasFile property will be True, otherwise HasFile property will equal to False. If we have any file submitted we can continue else we can exit event sub procedure.

If you want to check and validate the uploaded file extension, you can use the following lines of codes. First, in order to get the uploaded files extension, below line of code will guide us.

System.IO.Path.GetExtension(FileUpload1.FileName)

If we set the file extension of the uploaded file's extension to a variable, we can compare it in the future with valid file extensions.

Dim fileExtension As String = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower()

Here is a function that can be used to compare if the file submitted by the web user is among the list of valid extensions (you can use the same below code to compare the file's extension against a banned list of extentions.) 

Private Function FileExtensionIsApproved(ByVal FileExtension As String) As Boolean

FileExtensionIsApproved = False

Dim AllowedFileExtensions As String() = System.Configuration.ConfigurationManager.AppSettings("AllowedFileExtensions").Split(",")
'Dim AllowedFileExtensions As String() = {".jpg", ".jpeg", ".gif", ".doc"}

For i As Integer = 0 To AllowedFileExtensions.Length - 1
If FileExtension = AllowedFileExtensions(i) Then
FileExtensionIsApproved = True
Exit For
End If
Next

End Function


If you examine the web.config file for the AllowedFileExtensions AppSettings key value pair, you can easily understand that AllowedFileExtensions key is used to store the list of valid file extension. Look at the below section copied down here from the web.config configuration file of the web site.

<add key="AllowedFileExtensions" value=".jpg, .jpeg, .gif, .doc"/>

If we split the AllowedFileExtensions key value, we can get an array of the confirmed extensions.
Below is a similar way of keeping valid extensions hardcoded as an array in the application instead of using the web configuration file.

Dim AllowedFileExtensions As String() = {".jpg", ".jpeg", ".gif", ".doc"}

If the file extension tests are successfull then we have two choice, one is saving the uploaded file in a file folder that can be reached from the web server, and also that has the write permission.
Or the second one is saving the uploaded file into the Microsoft SQL Server 2005 database.
Let's select the first one, save the file on a web folder.

Dim path As String = Server.MapPath(System.Configuration.ConfigurationManager.AppSettings("UploadFolder").ToString)

FileUpload1.PostedFile.SaveAs(path & FileUpload1.FileName)


Saving the file to the web folder that is parameterized using the web.config file UploadFolder AppSettings key-value pair is so easy.

<add key ="UploadFolder" value="~/UploadedImages/" />

Let's continue now with the saving to database selection.
First create a SqlConnection object getting the connection string from the configuration file connection strings section.

System.Configuration.ConfigurationManager.ConnectionStrings("PhotosConnectionString").ToString

And the PhotosConnectionString is declared in the web.config file as shown below;

<connectionStrings>
<add name="PhotosConnectionString" connectionString="Data Source=kodyaz;Initial Catalog=photos;Persist Security Info=True;User ID=kodyaz;Password=kodyaz" providerName="System.Data.SqlClient" />
</connectionStrings>


Then a SqlDataAdapter object is created from the Files database table using the following select sql statement.

SELECT * FROM Files WHERE 1 <> 1

The below VB.NET codes saves the uploaded file to be handled as a IO.Stream object.

Dim fs As System.IO.Stream = FileUpload1.PostedFile.InputStream()
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()


And the following codes creates a new row of the Files datatable in the dataset and sets its columns with the filename and the stream object as the binary file.

da.Fill(ds, "Files")
Dim myRow As DataRow
myRow = ds.Tables("Files").NewRow()

myRow("file") = MyData
myRow("fname") = FileUpload1.PostedFile.FileName
ds.Tables("Files").Rows.Add(myRow)
da.Update(ds, "Files")


ListFiles.aspx

This page is used as a displayed list of files uploaded successfully into the sql server database. Here is the ASP.NET codes in the design page of the Visual Studio 2005.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" SortExpression="id" />
<asp:BoundField DataField="fname" HeaderText="fname" SortExpression="fname" />
<asp:ImageField HeaderText="file">
<ItemStyle Width="50px" />
</asp:ImageField>
<asp:HyperLinkField DataNavigateUrlFields="id" DataNavigateUrlFormatString="~/file.aspx?id={0}" Target="_blank" Text="View" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PhotosConnectionString %>" SelectCommand="SELECT * FROM [Files]"></asp:SqlDataSource>


When the HyperLinkField column of the GridView object is clicked the id field value which is unique in the Files database table is passed as a querystring parameter to the file.aspx page.

File.aspx

This page reads the requested file from the database tables with its id value. And displays the requested image file.

Dim id As Int32 = CInt(Page.Request.Item("id"))

Page.Request.Item("id") enables us to read the querystring parameters for the id parameter value.

Then the following lines of codes is used to read image file from the database.

Dim con As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("PhotosConnectionString").ToString)
Dim da As New SqlDataAdapter("SELECT * FROM Files WHERE id = " & id.ToString, con)
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()

con.Open()
da.Fill(ds, "files")
Dim myRow As DataRow
myRow = ds.Tables("files").Rows(0)

Dim MyData() As Byte
MyData = myRow("file")

Page.Title = myRow("fname")


Now it is time to response the request with the file fetched from database table named Files with its id IDENTITY field.

Response.Buffer = True
Response.ContentType = "Image/JPEG"
Response.BinaryWrite(MyData)


Content type "Image/JPEG" is used for image files. You can better keep the content type or the MIME type of the saved filed as a property of itself. Because if the uploaded file is a .doc file you have to use the content type as "text/html", etc.

You can download the sample web site application source codes from the Files section of the site following File Upload and Save to SQL2005 Database Sample Code for an ASP.NET Web Site link.

BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows