Tech Treasure: 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();