How to Get the PDF, Word and Excel Files From the Database

Introduction

This article shows how to fetch the document files, like PDF, Word and Excel documents, from a database.

For an explanation of that in this article, I will use the following procedure:

1. Create a table in the database to store the document files and some other relevant data by which I can fetch the file from the table and store some data into the table.
2. Create a website with a generic handler (.ashx) with some code, that will fetch the specific file from the database.
3. Create a page, that shows all the files and call the generic handler (.ashx) for the specific file.

The following are the details of the preceding procedure.

Step 1

1. Create a table named "Documents" that will store:

• Identity column for Serial number
• Name of the file
• Display File name that you want to show
• Extension of file
• Content Type of file
• File in binary format
• Size of file
• Date of file insertion 

create table Documents
(
    SNo int identity,
    Name_File   varchar(100),
    DisplayName varchar(50),
    Extension   varchar(10),
    ContentType varchar(200),
    FileData    varbinary(max),
    FileSize    bigint,
    UploadDate  datetime
)

2. After storing 3 different files into the table named "Test".

Note

To understand how to save the document files in the database, read my previous article "How to Save PDF, Word and Excel Files Into The DataBase".

Step 2

1. Create a new empty Website named "FilesToBinary".
   
   
   
   
2. Add a new Generic Handler named "DocHandler.ashx".
   
   
   
   Which will look like:
   
   
   

Step 3

1. Add a web form named "GetFiles.aspx".
   
   
   
   
2. Add some code in the ".cs" file of the GetFiles page for fetching all the files from the database. 

protected void Page_Load(object sender, EventArgs e)
{
    //Fetch the Data from Database
    string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
    SqlConnection objConn = new SqlConnection(sConn);
    objConn.Open();
    string sTSQL = "select * from Documents";
    SqlCommand objCmd = new SqlCommand(sTSQL, objConn);
    objCmd.CommandType = CommandType.Text;
    SqlDataAdapter ada = new SqlDataAdapter(objCmd);
    DataTable dt = new DataTable();
    ada.Fill(dt);
    objConn.Close();
    objCmd.Dispose();
     //Bind the Data into the html anchor tag which will call the handler with ID
    if (dt.Rows.Count > 0)
    {
        string tbl = "";
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            tbl += @"<li>
    <a target='_blank' href='DocHandler.ashx?ID=" + dt.Rows[i]["SNo"].ToString();
            tbl += @"' title='";
            tbl += @"' >" + dt.Rows[i]["DisplayName"].ToString();
            tbl += @"</a>
            </li>";
        }
        Response.Write(tbl);
    }
}

Step 4

Get the file from the database via a handler as in the following:

1. Delete or comment the 2 default generated lines, that are mentioned below. just because of irrelevancy.

context.Response.ContentType = "text/plain";
       context.Response.Write("Hello World");

2. Write the code to fetch the file from the database.

string id = context.Request.QueryString["ID"].ToString();
string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();
string sTSQL = "select Name_File,Extension,ContentType,FileData,FileSize from Documents where SNo=@ID";
SqlCommand objCmd = new SqlCommand(sTSQL, objConn);
objCmd.CommandType = CommandType.Text;
objCmd.Parameters.AddWithValue("@ID", id);
SqlDataAdapter ada = new SqlDataAdapter(objCmd);
DataTable file = new DataTable();
ada.Fill(file);
objConn.Close();
objCmd.Dispose();
if (file.Rows.Count > 0)
{   DataRow row = file.Rows[0];
   string name = (string)row["Name_File"];
   string contentType = (string)row["ContentType"];
   Byte[] data = (Byte[])row["FileData"];
   int FileSize = Convert.ToInt32(row["FileSize"].ToString());
    // Send the file to the browser
   context.Response.AddHeader("Content-type", contentType);   context.Response.AddHeader("Content-   Disposition", "attachment; filename=" + name);
   context.Response.OutputStream.Write(data, 0, FileSize);
   context.Response.Flush();
   context.Response.End();
}

 Step 5

 Run the page that will be like:

Click on the file that you want to download.

1. DocFile
   
   
   
   
2. PDF
   
   
   
   
3. Excel
   
   
   

Result

Now you have all 3 files stored in the database in binary format.