Retrieve files from a table in Database using Generic Handler

 

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 –

DownloadFromDB-DatabaseTable[10]

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 –

DownloadFromDB-DatabaseStructure2

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", "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.

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

DownloadFromDB-FolderStructure2

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

OUTPUT

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

Default.aspx – Before Download –>

DownloadFromDBBeforedownload

Default.aspx – After Download ->

DownloadFromDBAfterdownload

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.

You may also like...

  • murali

    hi,

    sir i need to down load a file using fileId,from one tale and emailId from another table………

    means i have two text boxes , to enter emailId and Id(may be fileId)..
    I need to compare those textbox values with database value and need to down load file..

    how can i create stored procedure for that also please send details of code if it is possible to design like above

  • Tj

    Hi
    I am trying to play videos using SQL db. I have successfully save videos using a GUI application that i have created. And tried your example to download video from a web page. So all i need is to how to play it in web page?

  • adi

    Hi.
    I wrote your code in my project to work with my data base in sql server.
    after i changed everything i need (part one was OK), i got problem in part two :
    “Object reference not set to an instance of an object”

    for this line :
    Byte[] bytes = (Byte[])ds.Tables[0].Rows[0][“DocData”];

    * DocDate is the name of the data in my table…

    Any Idea ?

    Thanks.

  • Era

    very useful and helpful article. need not check any other site after reading this. Thanks a lot!!!!

  • Sneha

    very useful and helpful article. need not vivit any other site.
    Thanks a lot!!

  • Sneha

    Very helpful and systematic description
    Thanks a lot!!!

  • RamiVemula

    @Adi – Please check what data you are getting from your database, debugging is the best option.

  • Ron

    Sent this on your contact page as well:

    This article was excellent, all I really needed was the following:

    Response.Clear();
    Response.AddHeader(“Content-Disposition”, “inline;attachment; filename=\”” + fileName + “\””);
    Response.AddHeader(“Content-Length”, bytes.Length.ToString());
    Response.ContentType = “application/octet-stream”;
    Response.BinaryWrite(bytes);
    Response.Flush();

    to get over my hump. And it works great on IE and FireFox. But when I run it on Chrome, it names the file whatever the .aspx page is named, in this cause AccountResume.aspx. The contents of the file are actually correct, but the filename is wrong.

  • Ron

    Following up on the comment I just posted, in case anybody else has this problem, removing ‘inline;’ from the AddHeader line allows this to work perfectly on Chrome, FireFox and IE.

    Hope that helps someone!

  • RamiVemula

    @Ron – thanks for insights. I appreciate it.

  • priya

    Thanx a lot !!!
    The code works perfectly and it was of great help to me :)

  • priya

    what is GetFile() function in DownloadHandler.ashx?? i am getting error with that….

    “GetFile gf = new GetFile();”

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

    please help me out!!

  • priya

    i am getting a type expected error for GetFile() in downloadhandler.ashx

  • RamiVemula

    @Priya – In the same tutorial, there is a Class called GetFile, you need to have that, then you will not get any error. This class is going to connect to DB and get the file specified.

  • RamiVemula

    @Priya – Please refer to my above comments.

  • priya

    thankyou so much!! i have got it:):)

  • priya

    i am getting an error in downloadhandler.ashx.. it says that object reference is not set to instance of an object…
    the error is showing in line
    Dim bytes As [Byte]() = DirectCast(ds.Tables(“FilesData”).Rows(0)(“Data”), [Byte]())
    i tried debugging and also putting
    If ds.Tables.Count >0 Then

    If ds.Tables(“FilesData”).Rows(0).Item(2) IsNot Nothing Then

    –condition but eventhough it is not working!
    please help me!!

  • RamiVemula

    @Priya – with my code exactly followed, you need to have – ds.Tables(0).Rows(0)(“Data”)…
    Thanks,

  • arun gupta

    hi,

    i have run to your code upload is know error but retrived the file show this error

    Parser Error

    Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

    Parser Error Message: Could not create type ‘FRACUS.DownloadHandler’.

  • Rash

    hey!! im gettin an error called DataBinding: ‘System.Data.DataRowView’ does not contain a property with the name ‘Id’. in the line <a href='’>. can u help?

  • Rash

    and i also didnt understand from where are we passing the ID value and FileName value in this line.. <a href='’>.. it says Id={0}&filename={1}, can u plz expalin dis..

  • RamiVemula

    @Rash – Cross check your code if you have the property Id for the object you are binding. I have that property binding in ProcessRequest of IHttpHanlder like gf.Id = fileId; check that whether you have the same code or not.

  • RamiVemula

    @Rash – FileName and Id are passed as QueryStrings in the URL, which I am grabbing in PRocessRequest of IHttpHanlder.

  • RamiVemula
  • Rakhee

    in procedure there is a small mistake i.e…. in SELECT statement there is id=@id it was wrong and we must place id=id similarly for filename also

  • RamiVemula

    @Rakhee – Nope, stored procedure is right. If you look at the names of the parameters you can see there is @ symbol used. So There should be @Id and @fileName.

  • puspa rout

    thank u so much.

  • puspa rout

    hi thank u so much

  • modeste

    hi am very happy for this tutorial for upload and download
    i have tried for upload and its okay but for downloading am getting this error and i ve tried to check others s comment to solve the problem but am still having this error:Object reference not set to an instance of an object On Byte[] bytes = (Byte []) ds.Tables[0].Rows[0][“Data”];
    thank you so much i will appreciate your help

  • RamiVemula

    @Modeste – Debug your code line by line and see what are the values at each state. I think your SQL is wrong, thats the reason why you not getting anything in Databset.

  • ajay

    Very good and simple way to learn generic handler thanx