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:

  1. create table Documents
  2. (
  3. SNo int identity,
  4. Name_File varchar(100),
  5. DisplayName varchar(50),
  6. Extension varchar(10),
  7. ContentType varchar(200),
  8. FileData varbinary(max),
  9. FileSize bigint,
  10. UploadDate datetime
  11. )
2. After storing 3 different files into the table named "Test".

File in database

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

    Create a new empty Website

  2. Add a new Generic Handler named "DocHandler.ashx".

    Add a new Generic Handle

    Which will look like:

    Genric header looklike
Step 3
  1. Add a web form named "GetFiles.aspx".

    Add a web form

  2. Add some code in the ".cs" file of the GetFiles page for fetching all the files from the database.
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. //Fetch the Data from Database
  4. string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
  5. SqlConnection objConn = new SqlConnection(sConn);
  6. objConn.Open();
  7. string sTSQL = "select * from Documents";
  8. SqlCommand objCmd = new SqlCommand(sTSQL, objConn);
  9. objCmd.CommandType = CommandType.Text;
  10. SqlDataAdapter ada = new SqlDataAdapter(objCmd);
  11. DataTable dt = new DataTable();
  12. ada.Fill(dt);
  13. objConn.Close();
  14. objCmd.Dispose();
  15. //Bind the Data into the html anchor tag which will call the handler with ID
  16. if (dt.Rows.Count > 0)
  17. {
  18. string tbl = "";
  19. for (int i = 0; i < dt.Rows.Count; i++)
  20. {
  21. tbl += @"<li>
  22. <a target='_blank' href='DocHandler.ashx?ID=" + dt.Rows[i]["SNo"].ToString();
  23. tbl += @"' title='";
  24. tbl += @"' >" + dt.Rows[i]["DisplayName"].ToString();
  25. tbl += @"</a>
  26. </li>";
  27. }
  28. Response.Write(tbl);
  29. }
  30. }
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.

  1. context.Response.ContentType = "text/plain";
  2. context.Response.Write("Hello World");
2. Write the code to fetch the file from the database.
  1. string id = context.Request.QueryString["ID"].ToString();
  2. string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
  3. SqlConnection objConn = new SqlConnection(sConn);
  4. objConn.Open();
  5. string sTSQL = "select Name_File,Extension,ContentType,FileData,FileSize from Documents where SNo=@ID";
  6. SqlCommand objCmd = new SqlCommand(sTSQL, objConn);
  7. objCmd.CommandType = CommandType.Text;
  8. objCmd.Parameters.AddWithValue("@ID", id);
  9. SqlDataAdapter ada = new SqlDataAdapter(objCmd);
  10. DataTable file = new DataTable();
  11. ada.Fill(file);
  12. objConn.Close();
  13. objCmd.Dispose();
  14. if (file.Rows.Count > 0)
  15. {
    DataRow row = file.Rows[0];
  16. string name = (string)row["Name_File"];
  17. string contentType = (string)row["ContentType"];
  18. Byte[] data = (Byte[])row["FileData"];
  19. int FileSize = Convert.ToInt32(row["FileSize"].ToString());
  20. // Send the file to the browser
  21. context.Response.AddHeader("Content-type", contentType);
    context.Response.AddHeader(    "Content- Disposition", "attachment; filename=" + name);
  22. context.Response.OutputStream.Write(data, 0, FileSize);
  23. context.Response.Flush();
  24. context.Response.End();
  25. }
Step 5

Run the page that will be like:

Run the Page

Click on the file that you want to download.

  1. DocFile

    click on the file

  2. PDF

    pdf file

  3. Excel

    excel file

Result

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