Click here to Skip to main content
12,819,738 members (33,894 online)
Click here to Skip to main content
Add your own
alternative version


36 bookmarked
Posted 14 Mar 2005

SQL Server report compress solution (Zip format)

, 14 Mar 2005
Rate this:
Please Sign up or sign in to vote.
To compress large SQL Server reports and allow the user to download the same in ZIP format.


Microsoft SQL Server reporting service is the emerging technology in reporting services. As the reporting service is being shipped along with the MS SQL Server almost all .NET based projects use them.

Reports are often used for decision making purpose in a management and they have to be really fast when the results are to be shown. What if the data to be shown is huge and the network bandwidth is less? The result is, it will take more time which in turn results in a timeout error of the reporting service. To avoid these kind of errors and to win the customer satisfaction we can dynamically compress the report in a ZIP file format and allow the user to download the zip file in their local hard disk. The above solution can be achieved programmatically using C# or VB.NET.

Note: Reports with any extension (.xls, .pdf, .html etc.) can be compressed.

The solution covered in this article uses the sample code snippets written in C# language.


  • SharpZipLib: A free Open Source .NET ZIP library (Download the most current build).
  • A full control access to the Temp folder of the project in the server where the application resides.

Step by step procedure

The procedure explained below can be used only if the data in the report is large:

  • Get the report data in the form of a byte array through the reporting web service.
  • Check for the length of the byte array, if it crosses the limit specified we can compress the report or else leave it as default.
  • Specify the filename and extension for which the report has to be saved in the Temp folder.
  • Create the report file in the Temp folder.
  • Zip the report saved in the Temp folder and delete the report file.
  • Allow the user to download the saved Zip file through the browser.

Empty the Temp folder periodically through a separate thread.

Code summary

The following code deals with getting the report in the form of a byte array through the SQL Server reporting service:

ReportingService objReportService = new ReportService.ReportingService(); 
NetworkCredential objCredentials = new NetworkCredential("","","");
objReportService.Credentials = objCredentials;
string strReportFolderName = "" // Report folder name;
string strDSNPath = "" // Report DSN path ;
string strDataSourceName = "" // DataSourceName;
ReportService.DataSourceReference objDataSourceReference = 
                           new ReportService.DataSourceReference();
objDataSourceReference.Reference = "\"+ strDSNPath;
objArrDataSources = new ReportService.DataSource[1];
objDataSources = new ReportService.DataSource();
objDataSources.Item = 
    (ReportService.DataSourceDefinitionOrReference) objDataSourceReference;
objDataSources.Name = strDataSourceName;
objArrDataSources[0] = objDataSources;
objReportService.SetReportDataSources(strReportName.Trim(), objArrDataSources);
//Device Information settings....Section attribute 
// made to zero to show all the results.
//if this is made to 1 shows the first report only.

Private methods

The solution suggested involves two private methods which are discussed in detail below.

  • WriteFile: This method is used to write the report in the Temp folder. Following is the code for the same:
    strpath – A string which holds the path where the 
              report files has to be saved.
    strFileNameWithExt – A string which holds the name of 
       report file with extension (.html, .pdf, .xls etc).
    byteResultStream – A byte array which holds the data 
       of the report in the form of bytes.
    private int WriteFile (string strPath, 
            string strFileNameWithExt,byte[] byteResultStream)
         int intResult = 0;
         FileStream stream = 
         stream.Write(byteResultStream, 0, 
         intResult = ”1”;
         return intResult;
  • CreateZip: This method is used to zip the saved report file. The code is available in one of the samples downloaded along with the library. I have customized it as per my requirement:
    strFileName – A string which holds the filename without the extension
    strFileNameWithExt – A string which holds the filename with extension
    Method :*/
    private string CreateZip(string strFileName,string strFileNameWithExt)
          string strZipFileName = string.Empty;
          Crc32 objCrc32 = null;
          ZipOutputStream objZipOutputStream = null;
          ZipEntry objZipEntry = null;
          string strPath = string.Empty;
          strPath = HttpContext.Current.Server.MapPath(“\Temp”);
          if (File.Exists(strPath+strFileNameWithExt))
               strZipFileName = strPath+strFileName+”.zip”;
               objCrc32 = new Crc32();
               objZipOutputStream = 
                   new ZipOutputStream(File.Create(strZipFileName));
               FileStream objFileStream = 
               Byte[] abyBuffer = new Byte[objFileStream.Length];
               objFileStream.Read(abyBuffer, 0, abyBuffer.Length);
               objZipEntry = new ZipEntry(strFileNameWithExt);
               objZipEntry.DateTime = DateTime.Now;
               objZipEntry.Size = objFileStream.Length;
               objZipEntry.Crc = objCrc32.Value;
               objZipOutputStream.Write(abyBuffer, 0, abyBuffer.Length);
               strZipFileName = strFileName+”.zip”;
               return strZipFileName;

To do

  • In the above code snippet I have not done any exception handling, but this is a must in a real time scenario.
  • The above code was written for my project needs, you can customize it as per your requirement.
  • Saved zip files in the Temp folder is to be deleted on a timely basis either through a scheduler or through a program.


I hope this article helps everyone. I will write more articles whenever I come across something interesting and useful.


This article documents a solution to overcome SQL Server reporting service timeouts, to view reports with huge data. The solution is to compress the report in Zip format and allow the user to download the file.

Happy programming.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
India India
Hi ,

Iam working as a Software Engineer in one of the Top Indian IT companies.

I have been working with Microsoft Technologies for the past 4 years.

With Regards,

You may also be interested in...


Comments and Discussions

GeneralUsing the solution Pin
yohj24-Sep-09 22:41
memberyohj24-Sep-09 22:41 
QuestionWhere should i put the customized code? Pin
vinsonjob24-Aug-08 18:37
membervinsonjob24-Aug-08 18:37 
Questionwhat is .exe file can not found error? Pin
dristhy7-Feb-07 20:40
memberdristhy7-Feb-07 20:40 
QuestionHow can i sharing an which containing database using within LAN? Pin
jackNwan14-Jun-05 21:10
memberjackNwan14-Jun-05 21:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 14 Mar 2005
Article Copyright 2005 by prabhu.subbiah
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid