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

Saving files in SQL Server is a method that we programmers may apply even it is uncommon. Keeping 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 and save image files into 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.

You can use MS SQL Server 2008 as the file storage database also just like SQL Server 2005 database.
The same logic described in this ASP.NET tutorial can be implemented in MS Visual Studio 2005, MS VS2008 or MS VS2010 to save image to sql database instances like SQL2005 or SQL Server 2008.

The sql database in which the binary files will be kept has to contain the following table
In order to save file to SQL Server, we will begin by creating sql table Files to store the uploaded file in SQLServer.


GO

CREATE TABLE [dbo].[Files](

  [id] [int] IDENTITY(1,1) NOT NULL,

  [fname] [nvarchar](1000) NOT NULL,

  [file] [varbinary](max) NULL

)

GO

Code

One important point about this Files table 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 SQL Server 2005, 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 (SQL Server 2008, SQL Server 2008 R2).

FileUpload.aspx to Save File to SQL Server

Let's start examining the sample application which upload file to SQL Server from ASP.NET page FileUpload.aspx.
This page has one important web control, FileUpload control which we will use save file to SQL Server


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

Code

You can add a button to submit the web form after the file is selected in order to upload file to sql.
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

...

Code

The above if statement controls whether any file is submitted or not with the form to upload file SQL Server. If a file is selected for upload 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 to save file to SQL Server, 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)

Code

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()

Code

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.
Developers can use the same .NET code to compare the uploaded file's extension against a banned list of file 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

Code

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 sample web site application.


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

Code

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"}

Code

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)

Code

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/" />

Code

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

Code

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>

Code

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


SELECT * FROM Files WHERE 1 <> 1

Code

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()

Code

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")

Code

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 / Visual Studio 2008.
The GridView will list saved images / files after users save image to sql database.


<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>

Code

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 to Display Image After Save File in SQL Database

File.aspx ASP.NET page reads requested image from SQL Server database tables with its id value.
After reading image file, File.aspx web page displays the requested image file which is as uploaded file in SQLServer 2005 (or any other SQL Server version like SQL2008, etc.)


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

Code

Page.Request.Item("id") enables us to read the querystring parameters for the id parameter value of the uploaded file to SQL Server database.

Then the following lines of codes is used to read image file from sql 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")

Code

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


Response.Buffer = True

Response.ContentType = "Image/JPEG"

Response.BinaryWrite(MyData)

Code

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.

I hope ASP.NET developers get the main tips about the solution which can be used to save image to SQL database

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.

The uploaded zip file is somehow corrupted so I had placed the download files for the sample ASP.NET file upload sources files at Download FilesDBSample.zip file for File Upload and Save Submitted Files to SQL Server 2005, Sample ASP.NET Web Site Application