Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Samples » Revisions
 
Go to top

Upload and Download Files with SQL Servers 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 servers in ASP.Net.
This is an old version of the currently published article.

Introduction

This article presents an example to upload and download files with SQL servers 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 is ASP.Net. He asked me to give him an example on uploading and downloading files with SQL servers in ASP.Net applications. Since I have been using "MVC" for a while, I do 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 can simply download and run, so I created this example. By realizing that there are still a large portion of classical ASP.Net developers and not all the developers know how to upload and download files, I decided to post the example here to keep a record for myself, and hopefully it can help some people who are 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 advantages on whatever advantages that these techniques can provide you. Again for simplicity, no Javascript is used in this example, so we can simply focus on how to upload and download files with SQL servers in ASP.Net for the 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 made my effort not to use the "new" features coming with the 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 the files. It also provides a hyperlink for each of the files so we can download it.
  • 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 the files from the database.
  • The database connection string is configured in the "Web.config" file. 

In this article, I will first introduce you 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" permission to a 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 creates 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 <a href="mailto:ID=@ID">ID=@ID</a>";
                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:

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

These methods are implemented by plain "ADO.Net". They will be used by the application to list, upload and download the 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 the files that have been uploaded and saved in the database.

In the "GridView", I added an "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 the following:

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 are 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 the saved files is refreshed to the "GridView".

The "GetFile.aspx" page

When the list of the 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 through the "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, the "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 finish this example application and we can test it. Set the "Default.aspx" page as the start page, 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 servers in ASP.Net.
  • Compared with "MVC", "ASP.Net" is now considered as "classical", but there are still a large portion of developers who still 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 servers some years ago, I found the same method from a web site by "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 some people who are still interested in this subject.
  • Although this example uses a SQL server, the same method can be applied to any database engines that support binary data. What you need to do is simply changing 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 postings and I hope this article can help you one way or the other.

History

First revision - 01/01/2012.

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


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
GeneralA Nice Article PinmemberWaleedIbrahim17-Aug-14 23:41 
QuestionNeed To save Value of TicketID in session variable so that i can Save File through file upload based on session variable of TicketID PinmemberMember 104508024-Jul-14 3:58 
QuestionPDF will not open with error-"Adobe Reader could not open 'file.pdf' because it is either not a supported file type or because the file has been damaged..." PinmemberdmsWebDev19-Jun-14 9:10 
QuestionI am getting a parser error when I after downloading the source code and running the application in vs2010 PinmemberMember 1081973514-May-14 19:44 
AnswerRe: I am getting a parser error when I after downloading the source code and running the application in vs2010 PinmemberdmsWebDev19-Jun-14 9:15 
QuestionCould not load type 'ASPNetFileUpDownLoad.Default'. PinmemberMember 1052818613-Mar-14 10:19 
AnswerRe: Could not load type 'ASPNetFileUpDownLoad.Default'. PinmvpDr. Song Li13-Mar-14 10:58 
GeneralRe: Could not load type 'ASPNetFileUpDownLoad.Default'. PinmemberMember 1052818613-Mar-14 10:59 
QuestionI AM NOT ABLE TO ADD FILES GREATER THAN SIZE 1 MB PinmemberMember 106528237-Mar-14 19:54 
AnswerRe: I AM NOT ABLE TO ADD FILES GREATER THAN SIZE 1 MB PinmvpDr. Song Li8-Mar-14 3:25 
AnswerRe: I AM NOT ABLE TO ADD FILES GREATER THAN SIZE 1 MB PinmvpDr. Song Li8-Mar-14 3:29 
QuestionMultiple file attachment Pinmemberseja96629-Oct-13 0:52 
AnswerRe: Multiple file attachment PinmemberDr. Song Li9-Oct-13 3:54 
GeneralRe: Multiple file attachment Pinmemberseja96629-Oct-13 3:58 
GeneralRe: Multiple file attachment PinmemberDr. Song Li9-Oct-13 4:30 
GeneralRe: Multiple file attachment Pinmemberseja966216-Oct-13 2:21 
QuestionThank you and Jquery mobile Pinmemberseja966227-Aug-13 4:37 
AnswerRe: Thank you and Jquery mobile PinmemberDr. Song Li27-Aug-13 4:43 
GeneralRe: Thank you and Jquery mobile Pinmemberseja966227-Aug-13 4:48 
Generalthank Pinmembermostafaeyni17-Jul-13 10:08 
QuestionConverted to VB and works like a charm! PinmemberMember 1012789227-Jun-13 2:58 
AnswerRe: Converted to VB and works like a charm! PinmemberDr. Song Li27-Jun-13 10:41 
GeneralMy vote of 5 Pinmemberzhangpo19-Jun-13 10:51 
QuestionSharePoint Deploy PinmemberMember 88315173-Jun-13 1:27 
AnswerRe: SharePoint Deploy PinmemberDr. Song Li3-Jun-13 11:26 
QuestionHow to save current web page as ,jpg or ,pdf in database from asp.net because content of current page has many words? Pinmembermyint myint htay16-May-13 16:49 
SuggestionThanks Pinmembermyint myint htay16-May-13 16:42 
Questionnot to repeat my code for another form PinmemberMember 790918412-May-13 19:48 
QuestionSome Changes to the SQL should be made .... PinmemberEduard Paul3-May-13 3:35 
QuestionProblem with download PinmemberMember 87796831-May-13 16:22 
AnswerRe: Problem with download PinmemberDr. Song Li1-May-13 17:31 
GeneralMy vote of 5 PinmemberCodeDog Billy9-Apr-13 12:51 
GeneralMy vote of 1 PinmemberFireman_duck@126.com31-Mar-13 15:24 
QuestionProblem reading file PinmemberPebbs12327-Dec-12 22:26 
AnswerRe: Problem reading file PinmvpDr. Song Li28-Dec-12 3:44 
QuestionLogin Failed for User FileLoader PinmemberKenmod20-Dec-12 12:58 
GeneralMy vote of 5 PinmemberWühlmaus18-Nov-12 4:00 
QuestionObject reference not set to an instance of an object Pinmemberserigraphie7-Nov-12 4:14 
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 
GeneralMy vote of 5 Pinmemberhajivalie29-Oct-12 21:03 
QuestionCompilation Error Pinmemberasaduk9327-Oct-12 7:11 
QuestionLogin failed for user 'FileLoader'. The user is not associated with a trusted SQL Server connection. Pinmemberpiyush.talele13-Sep-12 16:24 
AnswerRe: Login failed for user 'FileLoader'. The user is not associated with a trusted SQL Server connection. PinmvpDr. Song Li14-Sep-12 3:35 
QuestionRe: Login failed for user 'FileLoader'. The user is not associated with a trusted SQL Server connection. [modified] Pinmemberpiyush.talele15-Sep-12 19:01 
QuestionThank you PinmemberMember 840167417-Aug-12 10:27 
QuestionUpload multiple files to the one table. Pinmembergilmourd124-Aug-12 22:12 
AnswerRe: Upload multiple files to the one table. PinmvpDr. Song Li6-Aug-12 3:29 
QuestionDelete? PinmemberMember 827760726-Jun-12 22:45 
AnswerRe: Delete? PinmvpDr. Song Li27-Jun-12 3:35 

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