Storing and Retrieving doc/pdf/xls files in SQL Server

         I have explained how to store and retrieve image file in SQL Server in my previous post. I received many comments mentioning that how can we store and retrieve doc or pdf or excel (or any type of file) in SQL Server. Few friends (developers) have also posted comment that they receive only 13 byte when they retrieve stored image or doc or xls or pdf or rtf file. So I thought let be write another blog for them. Sorry friends I am bit late in writing this article and mean while you also have solved your issues. However this may help some new friends.

         In this example I have used a table which has four fields. Below is the script for table,

CREATE TABLE [TestTable]
(
         [ID] [int] IDENTITY(1,1) NOT NULL,
         [FileName] [nvarchar](15) NOT NULL,
         [Extension] [nvarchar](5) NOT NULL,
         [Content] [image] NULL
)

Fig – (1) Scrpit for Table

         In my demo project I have used one file Upload control (to upload the file), one Textbox (where user can enter ID for uploaded file to retrieve it) and 2 buttons (one for uploading file and other for retrieving).

         When user select the file and click on Upload button the code stores the selected file in database. Below is the code for that,

    using (SqlConnection cnn = new SqlConnection(“Connection String”))
    {
                    cnn.Open();
                    SqlCommand cmd = new SqlCommand(“InsertFile”, cnn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter(“@FileName”, “Name of 
                                                               Uploaded File”));
                    cmd.Parameters.Add(new SqlParameter(“@Extension”, “Extension of
                                                               Uploaded File”));
                    cmd.Parameters.Add(new SqlParameter(“@Content”, “byte array
                                                               (byte[]) of uploaded file”));
                    cnn.Close()
     }

   Fig – (2) Code for inserting selected file in database.    

          Now when user enter FileID for uploaded file in textbox and click on retrieve button we will get the Content and extension field from database for that file id. You can use FillDataSet method to retrieve the byte array. Below code shows how to send retrieved file to user depending on the extension.

                string strExtenstion = “extension of retrieved file”;
                byte[] bytFile =  “Byte array retrieved from database”;

                Response.Clear();
                Response.Buffer = true;

                if (strExtenstion == “.doc” || strExtenstion == “.docx”)
                {
                        Response.ContentType = “application/vnd.ms-word”;
                        Response.AddHeader(“content-disposition”,
                                                          “attachment;filename=Tr.doc”);
                }
                else if (strExtenstion == “.xls” || strExtenstion == “.xlsx”)
                {
                         Response.ContentType = “application/vnd.ms-excel”;
                         Response.AddHeader(“content-disposition”,
                                                         “attachment;filename=Tr.xls”);
                }
                else if (strExtenstion == “.pdf”)
                {
                         Response.ContentType = “application/pdf”;
                         Response.AddHeader(“content-disposition”,
                                                        “attachment;filename=Tr.pdf”);
                }

                Response.Charset = “”;
                Response.Cache.SetCacheability(HttpCacheability.NoCache);

                // If you write,
                // Response.Write(bytFile1);
                // then you will get only 13 byte in bytFile.
                Response.BinaryWrite(bytFile);

                Response.End();

Fig – (3) Code to retrieve the file from database.

Happy Programming !!!

 

 

---

Possibly related posts: (automatically generated)

• Storing and Retrieving Image in SQL Server
• The media set has 2 media families but only 1 are provided.
• How to change App.config file run time using C#
• Store uploaded file content in image field of SQL Server database and retri…