Click here to Skip to main content
Click here to Skip to main content
Go to top

Upload and Download Files with SQL Server in ASP.NET

, 1 Jan 2012
Rate this:
Please Sign up or sign in to vote.
This article presents an example to upload and download files with SQL Server in ASP.NET.

Introduction

This article presents an example to upload and download files with SQL Server in ASP.NET.

Background

Compared with MVC, ASP.NET is now considered "classical". But recently, one of my old colleagues was assigned a web project and his development environment was ASP.NET. He asked me to give him an example on uploading and downloading files with SQL Server in ASP.NET applications. Since I have been using "MVC" for a while, I did not have an example in ASP.NET on hand. I tried to "Google" him a running example but I could not find one that he could simply download and run, so I created this example. By realizing that there is still a large portion of classical ASP.NET developers and not all developers know how to upload and download files, I decided to post the example here to keep a record for myself, and hopefully help someone still interested in this topic.

The only focus of this example is to upload and download files, so I did not pay any attention on OO design, Design Patterns, and coding standards. For simplicity, I did not even put exception handling in the code. I used in-line SQL through ADO.NET to access the database, but you can definitely use more advanced techniques such as Entity Framework, Hibernate, and Stored Procedures to take advantage of whatever advantages these techniques can provide you. Again for simplicity, no JavaScript was used in this example, so we can simply focus on how to upload and download files with SQL Server in ASP.NET for those interested.

The example application is developed in Visual Studio 2010 and SQL Server 2008. If you are using earlier versions of Visual Studio and SQL Server, you may not be able to open the attached solution. Although I did not test it myself since I do not have earlier versions myself, I feel that you can simply copy and paste the code in your application in earlier versions. I have tried not to use the "new" features that comed with Visual Studio 2010 and SQL Server 2008.

The attached Visual Studio 2010 solution is shown in the following picture:

Solution.jpg

  • The "Default.aspx" page is the main user interface of this application. It has the control to let us browse and upload files. If there are files saved in the database, it will display the list of files. It also provides hyperlinks for the files so we can download them.
  • The "GetFile.aspx" file is an empty ASPX page. When we click a download hyperlink in the Default.aspx page, the code-behind file of the GetFile.aspx page will load the correct file saved in the database and send it to the web browser.
  • The "Utilities/FileUtilities.cs" file implements the database access methods to save and retrieve files from the database.
  • The database connection string is configured in the "Web.config" file.

In this article, I will first introduce how to create the SQL Server database for this application and then show you how to upload and download files in this ASP.NET application.

Create the database

To create the database, you need to have "administrative" permissions in SQL Server. In my environment, I have a SQL Server 2008 installed on my local computer. You can run the following script to create the database:

USE [master]
GO
 
-- Create the [AFileStorageDB] database
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AFileStorageDB')
BEGIN
 ALTER DATABASE [AFileStorageDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE [AFileStorageDB]
END
GO
 
CREATE DATABASE [AFileStorageDB]
GO
 
-- Create the FileLoader login to the server
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'FileLoader')
DROP LOGIN [FileLoader]
GO
 
EXEC sp_addlogin @loginame = 'FileLoader', @passwd  = 'Pd123456';
GO
 
USE [AFileStorageDB]
GO
 
-- Create the [Files] table to the [AFileStorageDB] database
CREATE TABLE [dbo].[Files](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 [ContentType] [varchar](50) NOT NULL,
 [Size] [bigint] NOT NULL,
 [Data] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
-- Grant the required database access to the login 
sp_grantdbaccess 'FileLoader'
GO
sp_addrolemember 'db_datareader', 'FileLoader'
GO
sp_addrolemember 'db_datawriter', 'FileLoader'
GO
 
-- Bring the database on-line
ALTER DATABASE [AFileStorageDB] SET MULTI_USER
GO

This SQL script adds the following to your SQL Server:

  • A database named "AFileStorageDB";
  • A table named "Files" in the "AFileStorageDB" database;
  • A SQL Server login named "FileLoader", its password is "Pd123456";
  • The script also grants the login "FileLoader", "db_datareader", and "db_datawriter" access to the "AFileStorageDB" database.

The above script is "safe". But before you run it, please double check if your server accidentally already has a database named "AFileStorageDB". If so, the script will drop your database and create the new one and you may risk losing all your data. The database table "Files" created by the script is shown in the following picture:

DBTable.jpg

The connection string to the AFileStorageDB database is saved in the "Web.config" file in the ASP.NET application:

<?xml version="1.0"?>
 
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <httpRuntime executionTimeout="240" maxRequestLength="20480" />
  </system.web>
 
  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
 
  <appSettings>
    <add key="DBConnectionString"
         value="Data Source=localhost;Initial Catalog=AFileStorageDB;
          User Id=FileLoader;Password=Pd123456;pooling=false"/>
  </appSettings>
</configuration>

The database access utility

The "Utilities/FileUtilities.cs" file implements the database access utility class:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
namespace ASPNetFileUpDownLoad.Utilities
{
    public class FileUtilities
    {
        private static string GetConnectionString()
        {
            return ConfigurationManager.AppSettings["DBConnectionString"];
        }
 
        private static void OpenConnection(SqlConnection connection)
        {
            connection.ConnectionString = GetConnectionString();
            connection.Open();
        }
 
        // Get the list of the files in the database
        public static DataTable GetFileList()
        {
            DataTable fileList = new DataTable();
            using (SqlConnection connection = new SqlConnection())
            {
                OpenConnection(connection);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
 
                cmd.CommandText = "SELECT ID, Name, ContentType, Size FROM Files";
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter adapter = new SqlDataAdapter();
 
                adapter.SelectCommand = cmd;
                adapter.Fill(fileList);
 
                connection.Close();
            }
 
            return fileList;
        }
 
        // Save a file into the database
        public static void SaveFile(string name, string contentType,
            int size, byte[] data)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                OpenConnection(connection);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
 
                string commandText = "INSERT INTO Files VALUES(@Name, @ContentType, ";
                commandText = commandText + "@Size, @Data)";
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
 
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@size", SqlDbType.Int);
                cmd.Parameters.Add("@Data", SqlDbType.VarBinary);
 
                cmd.Parameters["@Name"].Value = name;
                cmd.Parameters["@ContentType"].Value = contentType;
                cmd.Parameters["@size"].Value = size;
                cmd.Parameters["@Data"].Value = data;
                cmd.ExecuteNonQuery();
 
                connection.Close();
            }
        }
 
        // Get a file from the database by ID
        public static DataTable GetAFile(int id)
        {
            DataTable file = new DataTable();
            using (SqlConnection connection = new SqlConnection())
            {
                OpenConnection(connection);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
 
                cmd.CommandText = "SELECT ID, Name, ContentType, Size, Data FROM Files "
                    + "WHERE ID=@ID";
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter adapter = new SqlDataAdapter();
 
                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = id;
 
                adapter.SelectCommand = cmd;
                adapter.Fill(file);
 
                connection.Close();
            }
 
            return file;
        }
    }
}

This class implements three public static methods:

  • GetFileList retrieves the list of files saved in the database.
  • SaveFile saves a file into the database.
  • GetAFile retrieves a single file including the binary file data from the database.

These methods are implemented using plain ADO.NET. They will be used by the application to list, upload, and download files. When you retrieve the list of all the files, please make sure that you do not select the binary file data, which will significantly slow down your application.

The "Default.aspx" page

The main user interface of the example application is implemented in the "Default.aspx" file:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
    Inherits="ASPNetFileUpDownLoad.Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ASP.Net Up & Download Files</title>
    <link href="Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="frmDefault" enctype="multipart/form-data" runat="server">
<div style="width: 400px">
    <div style="clear: both; width: 100%">
        <input type="file" name="fileInput" />
        <asp:Button ID="btnUpload" Text="Upload File" runat="server" 
            onclick="btnUpload_Click" />
    </div>
    <div style="margin-top: 5px; clear: both">
        <asp:GridView ID="gvFiles" CssClass="GridViewStyle"
            AutoGenerateColumns="true" runat="server">
            <FooterStyle CssClass="GridViewFooterStyle" />
            <RowStyle CssClass="GridViewRowStyle" />    
            <SelectedRowStyle CssClass="GridViewSelectedRowStyle" />
            <PagerStyle CssClass="GridViewPagerStyle" />
            <AlternatingRowStyle CssClass="GridViewAlternatingRowStyle" />
            <HeaderStyle CssClass="GridViewHeaderStyle" />
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:HyperLink runat="server"
                            NavigateUrl='<%# Eval("ID", "GetFile.aspx?ID={0}") %>'
                            Text="Download"></asp:HyperLink>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
</div>
</form>
</body>
</html>

In this "ASPX" page, we have the following components:

  • An HTML "<input>" of type "file". It is used to browse the file to be uploaded.
  • An ASP.NET Button. When the users click this button, the selected file will be uploaded to the server.
  • An ASP.NET GridView. It is used to display the list of all files that have been uploaded and saved in the database.

In the GridView, I added a HyperLink column. The URLs of the hyperlinks point to the GetFile.aspx page with the correct file ID to download the corresponding file.

The code-behind file of the "Default.aspx" page is implemented as follows:

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ASPNetFileUpDownLoad.Utilities;
 
namespace ASPNetFileUpDownLoad
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (! IsPostBack)
            {
                DataTable fileList = FileUtilities.GetFileList();
                gvFiles.DataSource = fileList;
                gvFiles.DataBind();
            }
        }
 
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            // Although I put only one http file control on the aspx page,
            // the following code can handle multiple file controls in a single aspx page.
            HttpFileCollection files = Request.Files;
            foreach (string fileTagName in files)
            {
                HttpPostedFile file = Request.Files[fileTagName];
                if (file.ContentLength > 0)
                {
                    // Due to the limit of the max for a int type, the largest file can be
                    // uploaded is 2147483647, which is very large anyway.
                    int size = file.ContentLength;
                    string name = file.FileName;
                    int position = name.LastIndexOf("\\");
                    name = name.Substring(position + 1);
                    string contentType = file.ContentType;
                    byte[] fileData = new byte[size];
                    file.InputStream.Read(fileData, 0, size);
 
                    FileUtilities.SaveFile(name, contentType, size, fileData);
                }                
            }
 
            DataTable fileList = FileUtilities.GetFileList();
            gvFiles.DataSource = fileList;
            gvFiles.DataBind();
        }
    }
}
  • In the Page_Load event, if it is not a postback, the list of all the files is retrieved from the database and bound to the GridView.
  • In the btnUpload_Click event, if a file is uploaded, the content of the file is obtained by Request.Files and saved to the database. After the file is saved, the list of saved files is refreshed in the GridView.

The "GetFile.aspx" page

When the list of files is displayed in the Default.aspx page, each file has a corresponding hyperlink to download the file. The hyperlink points to the GetFile.aspx page with the correct file ID. The GetFile.aspx page itself is an empty ASPX page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GetFile.aspx.cs"
         Inherits="ASPNetFileUpDownLoad.GetFile" %>

The trick that the GetFile.aspx page can send the files to the browser lies in the code-behind file:

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ASPNetFileUpDownLoad.Utilities;
 
namespace ASPNetFileUpDownLoad
{
    public partial class GetFile : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Get the file id from the query string
            int id = Convert.ToInt16(Request.QueryString["ID"]);
 
            // Get the file from the database
            DataTable file = FileUtilities.GetAFile(id);
            DataRow row = file.Rows[0];
 
            string name = (string)row["Name"];
            string contentType = (string)row["ContentType"];
            Byte[] data = (Byte[])row["Data"];
 
            // Send the file to the browser
            Response.AddHeader("Content-type", contentType);
            Response.AddHeader("Content-Disposition", "attachment; filename=" + name);
            Response.BinaryWrite(data);
            Response.Flush();
            Response.End();
        }
    }
}

In the Page_Load event, the file ID is obtained using QueryString. The file data is then retrieved from the database using the GetAFile method in the FileUtilities class. The file data is sent to the browser by Response.BinaryWrite. After the file is sent, Response.End is called, which immediately terminates the server response. This leaves the GetFile.aspx page simply a placeholder that enables the browser to access the file downloading capability implemented in the code-behind file.

Run the application

Now we have finished this example application and we can test it. Set the Default.aspx page as the start page, and we can start the application. When the Default.aspx page is first loaded, we can see the file "Browse" control and the "Upload File" button in the browser.

RunAppStart.jpg

We can then browse some files and upload them to the server. The uploaded files will be shown in a GridView. Each file in the list has a hyperlink to download the file.

RunAppUpload.jpg

The following picture shows the "Tiger.jpg" file downloaded from the server.

RunAppDownload.jpg

Points of interest

  • This article presented an example to upload and download files with SQL Server in ASP.NET.
  • Compared with MVC, ASP.NET is now considered as "classical", but there is still a large portion of developers who use ASP.NET.
  • File uploading techniques in web applications have improved significantly these years. There are many utilities that we can use to achieve much better user experience. The method shown here is a very simple and rudimentary method. It is intended for the interested audience to get started in the ASP.NET environment.
  • When I first learned to upload and download files with SQL Server some years ago, I found the same method from a web site using Google. But I am unable to find the same link anymore. It may be because the site is very old or the site is no longer active. I hope this example application can help someone who is still interested in this subject.
  • Although this example uses SQL Server, the same method can be applied to any database engine that supports binary data. What you need to do is simply change the data access methods to fit the specific database engine.
  • One of the advantages of the method shown in the example is that no file is ever saved on the web server, so there is no special permission required on the web server for this application to run.
  • I hope you like my posting and I hope this article can help you one way or the other.

History

  • 01/01/2012: First revision.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dr. Song Li

United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
QuestionObject reference not set to an instance of an object Pinmemberserigraphie7-Nov-12 4:14 
Hi Dr. Li,
Good day. I find your post exactly the functionality I would like to achieve. However, I am receiving this error : Object reference not set to an instance of an object. The error is pointing at gvfiles.Datasource = fileList. I double checked my connectionstring and there seems to be no problem at all since the files have been succesfully uploaded and is reflected correctly in the datagrid. The problem comes in when clicking "download" button. Is this a compatibility issue since I am using earlier version of VS and SQL Server?
 
Looking forward to your response and thank you very much for this post.
AnswerRe: Object reference not set to an instance of an object PinmvpDr. Song Li7-Nov-12 4:27 
GeneralRe: Object reference not set to an instance of an object Pinmemberserigraphie7-Nov-12 4:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 1 Jan 2012
Article Copyright 2012 by Dr. Song Li
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid