Before reading the code in this tutorial, have a small recap –

PART – I – Save files to a table in Database using FileUpload Control – Click Here

In the first part of this tutorial (PART I – Click Here), we uploaded files to the database in binary format using a FileUpload control. The plot behind saving files is to take the file from the FileUpload control, convert the same to the binary format, then save it to the DB. To make our accomplishment more flexible and maintainable, we used stored procedures along with custom classes for database transactions.

In this part of the tutorial, we retrieve the files (which are stored in a table of a database previously). For this purpose we first create a custom class which will hold the code for database transactions, then we create a generic handler which will process all the incoming requests for the downloading of files. Finally we create a web page which uses the before mentioned class and handler along with a repeater control to make the file available to the client.

IMP NOTE :-

1. This part of the tutorial assumes a prior knowledge of uploading files to database. In case, if the reader is skeptical about the same, then do read the first part of the tutorial by clicking above mentioned link.
2. In case, this tutorial is followed from the PART I, then there is no need to create the DB as mentioned in the Database Design Section ( as it is assumed that already database is created using PART I). But do create the Stored Procedure mentioned in this tutorial without fail. If the reader skipped PART I, then do create a database with some sample data in it beforehand.
3. To maintain a permissible amount of flexibility of the tutorial, PART I and PART II are divided into two separate websites. And also in each part, every activity (say database transactions, Download process etc) are divided into separate classes.

Lets get started –

================================================================================================

Database Design

Create a sample ‘ExampleDB’ in VS 2010, which holds the table for storing files.

IMP NOTE – If already database is created as shown in PART I, then please dont create a new database but still we got to create ‘GetFileProc’ stored proc.

================================================================================================

1. Open VS 2010 -> View -> Server Explorer
2. Right Click ‘Data Connections’ node -> Add new SQL Server Database
3. Select a Server (from DropDownList) -> Prefer Windows Authentication -> Give a Name to the new database, here we use ‘ExampleDB’.

The database has been created, now we create a table.

1. Expand ‘ExampleDB’ in the Server Explorer -> Right Click ‘Tables’ folder -> Select ‘Add New Table’.

Give the details of the table fields as shown below –

Save (press CTRL + SHFT + S) the table, it asks to give a table name; in this case we use ‘FilesData’.

Save the Database – This completes the Database design. Now lets write some code to retrieve files from database. We get started by creating a StoredProcedure to save files into a table.

================================================================================================

Stored Procedure

Create a Storepd Proc – ‘GetFileProc’– which is used to retrieve a file from the database.

================================================================================================

To create a Stored Proc –

1. Expand ExampleDB in the Server Explorer -> Right Click -> Stored Procedures Folder -> Select -> Add new Stored Procedure

Remove complete code from the Stored Procedure and place the below code –

CREATE PROCEDURE dbo.GetFileProc
    (
    @Id int,
    @FileName Varchar(50)
    )
AS
Begin

select Data from FilesData where Id = @Id and FileName = @FileName

End

And then save the Stored Procedure. This creates a Stored Proc with name –‘GetFileProc’ with a simple select statement which selects data from the table ‘FilesData’.

With this we complete the preparation on the Database side. The Database structure would be –

As the Database is ready, now we can start writing code to retrieve our files from the DB using a Webform. Before we code a Webform, lets create a Generic Handler to handle all the requests to the downloads made from client.

================================================================================================

DownloadFromDB Website

Create a sample ‘DownloadFromDB’ website in VS 2010.

================================================================================================

1. Open Vs 2010 –> File –> New –> Website.
2. select –> C#
3. Select ‘Asp.Net Empty Website’ –> name – ‘DownloadFromDB’.

================================================================================================

GetFile.cs

This class holds the code to access the stored proc (we created above) and retrieve the bytes of information of the file.

================================================================================================

1. Right Click ‘DownloadFromDB’ in Solution explorer of VS 2010.
2. Select ‘Add Asp.Net Folder…’ –> App_code.
3. Add GetFile.cs (Right Click App_Code folder in Solution Explorer –> Add New Item –> Class).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class GetFile
{
    public int Id { set; get; }
    public string FileName { set; get; }

    public DataSet GetFileFromDB()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();

        using (SqlConnection conn = new SqlConnection("Data Source=RAMILU-PC\\SQLEXPRESS;" +
             "Initial Catalog=ExampleDB;Integrated Security=True;Pooling=False"))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetFileProc";
            cmd.Connection = conn;

            cmd.Parameters.AddWithValue("@Id", Id);
            cmd.Parameters.AddWithValue("@FileName", FileName);

            try
            {
                conn.Open();

                sda.SelectCommand = cmd;
                sda.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                sda.Dispose();
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
        }
    }
}

================================================================================================

DownloadHandler.ashx

This handler handles all the requests for the downloads.

================================================================================================

1. Right Click ‘DownloadFromDB’ in Solution explorer of VS 2010.
2. Select ‘Add New Item’
3. Add DownloadHandler.ashx (select ‘Generic Handler’ Template from ‘Add New ITem’).

<%@ WebHandler Language="C#" Class="DownloadHandler" %>

using System;
using System.Web;
using System.Data;

public class DownloadHandler : IHttpHandler {

    public void ProcessRequest(HttpContext context)
    {
        int fileId = 0;
        string fileName = "";

        // get the ID from the querystring
        if (context.Request.QueryString["Id"] != null)
        {
            fileId = Convert.ToInt32(context.Request.QueryString["Id"].ToString());
        }

        // get the FileName from the querystring
        if (context.Request.QueryString["filename"] != null)
        {
            fileName = context.Request.QueryString["filename"].ToString();
        }

        GetFile gf = new GetFile();

        gf.FileName = fileName;
        gf.Id = fileId;

        DataSet ds = gf.GetFileFromDB();
        Byte[] bytes = (Byte []) ds.Tables[0].Rows[0]["Data"];
        try
        {
                context.Response.Clear();
                context.Response.AddHeader("Content-Disposition", "inline;attachment; filename=\"" + fileName + "\"");
                context.Response.AddHeader("Content-Length", bytes.Length.ToString());
                context.Response.ContentType = "application/octet-stream";
                context.Response.BinaryWrite(bytes);
                context.Response.Flush();
        }
        catch (Exception ex)
        {
            context.Response.ContentType = "text/plain";
            context.Response.Write(ex.Message);
        }
        finally
        {
            context.Response.End();
        }
    }

    public bool IsReusable
    {
        get
        {
            return true;
        }
    }

}

================================================================================================

Default.aspx

This is the page which holds the UI for selecting a file, also it uses SaveFile.cs and stores the file to the DB.

================================================================================================

1. Right Click ‘DownloadFromDB’ in Solution explorer of VS 2010.
2. Select ‘Add New Item’
3. Add Default.aspx (select ‘WebForm’ Template from ‘Add New ITem’).

Use a SqlDataSource along with Repeater Data control to display anchor links to the files (which are stored in the Database).

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="Data Source=RAMILU-PC\SQLEXPRESS;Initial Catalog=ExampleDB;Integrated Security=True;Pooling=False"
            ProviderName="System.Data.SqlClient"
            SelectCommand="SELECT [Id], [FileName], [FileExtension] FROM [FilesData]">
         </asp:SqlDataSource>

        <asp:Repeater ID="Repeater1" DataSourceID="SqlDataSource1" runat="server">
            <ItemTemplate>
                <a href='<%# string.Format("DownloadHandler.ashx?id={0}&filename={1}",Eval("Id"),Eval("FileName")) %>'>
                  Download - <%#Eval("FileName") %> with <%#Eval("FileExtension") %> Extension
                </a>
            </ItemTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

================================================================================================

Folder Structure

The complete Solution Explorer structure of ‘SaveToDB’ website.

================================================================================================

================================================================================================

OUTPUT

================================================================================================

Default.aspx – Before Download –>

Default.aspx – After Download ->

PART – I – Save files to a table in Database using FileUpload Control – Click Here

Disclaimer:

All coding and suggestions made in the above discussion is strictly in point of view of the author. It is neither mentioned any where nor even concluded that this is the only possible way to go with, as there will be always a chance for a new approach in achieving the same requirement (in context of programming). The above mentioned code is strictly written and explained for the sake of new comers to C# and ASP.NET world.