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:
- 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.
- Create a website with a generic handler (.ashx) with some code, that will fetch the specific file from the database.
- 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
- Create a new empty Website named "FilesToBinary".
![Create a new empty Website]()
- Add a new Generic Handler named "DocHandler.ashx".
![Add a new Generic Handle]()
Which will look like:
![Genric header looklike]()
Step 3
- Add a web form named "GetFiles.aspx".
![Add a web form]()
- 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)
- {
-
- 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();
-
- 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());
-
- 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:
![Run the Page]()
Click on the file that you want to download.
- DocFile
![click on the file]()
- PDF
![pdf file]()
- Excel
![excel file]()
Result
Now you have all 3 files stored in the database in binary format.