Friday, August 31, 2007

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 !!!

 

 

2 comments:

Anonymous said...

Hi can we do the same with win form applications? Can Once my data is stored in the database, how do I open the files on client's PC?

snehalata said...

Your article is very helpful.But how we upload a winzip file.can u help me?