Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

SQL Server report compress solution (Zip format)

0.00/5 (No votes)
14 Mar 2005 1  
To compress large SQL Server reports and allow the user to download the same in ZIP format.

Introduction

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.

Requirements

  • 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.
strDeviceInfo=
  "<DeviceInfo><HTMLFragment>True</HTMLFragment>" + 
  "<Section>0</Section></DeviceInfo>";
//Passing parameters to report
arrParameters = new ReportService.ParameterValue[intHTCount];
if (objHTParameters !=null)
{
    intHTCount=objHTParameters.Count;
    arrParameters = new ReportService.ParameterValue[intHTCount];
    IDictionaryEnumerator enumParameterList = objHTParameters.GetEnumerator();
    while ( enumParameterList.MoveNext() )
    {
        arrParameters[intParameterCount]=new ReportService.ParameterValue();
        arrParameters[intParameterCount].Name = 
                                         enumParameterList.Key.ToString();
        arrParameters[intParameterCount].Value = 
                                         enumParameterList.Value.ToString();
        intParameterCount=intParameterCount+1;
    }
}
objReportService.Timeout = -1; //Getting report from web service as bytes.
byteResultStream = objReportService.Render(strReportName, strReportType, 
                      strHistoryID,strDeviceInfo,arrParameters,
                      objDataSourceCredentials,strShowHideToggle,
                      out strEncoding,out strMimeType,out objParametersUsed,
                      out objWarnings,out strArrStreamIdentifiers);

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:
    /*Parameters:
    
    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.
    
    Method:*/
    
    private int WriteFile (string strPath, 
            string strFileNameWithExt,byte[] byteResultStream)
    {
         int intResult = 0;
         FileStream stream = 
                  File.OpenWrite(@strPath+strFileNameWithExt);
         stream.Write(byteResultStream, 0, 
                                    byteResultStream.Length);
         stream.Close();
         intResult = �1�;
         return intResult;
         #endregion
    
    }
  • 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:
    /*Parameters:
    
    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;
          #endregion
          strPath = HttpContext.Current.Server.MapPath(�\Temp�);
          if (File.Exists(strPath+strFileNameWithExt))
          {
               strZipFileName = strPath+strFileName+�.zip�;
               objCrc32 = new Crc32();
               objZipOutputStream = 
                   new ZipOutputStream(File.Create(strZipFileName));
               objZipOutputStream.SetLevel(6);
               FileStream objFileStream = 
                        File.OpenRead(strPath+strFileNameWithExt);
               Byte[] abyBuffer = new Byte[objFileStream.Length];
               objFileStream.Read(abyBuffer, 0, abyBuffer.Length);
               objZipEntry = new ZipEntry(strFileNameWithExt);
               objZipEntry.DateTime = DateTime.Now;
               objZipEntry.Size = objFileStream.Length;
               objFileStream.Close();
               objCrc32.Reset();
               objCrc32.Update(abyBuffer);
               objZipEntry.Crc = objCrc32.Value;
               objZipOutputStream.PutNextEntry(objZipEntry);
               objZipOutputStream.Write(abyBuffer, 0, abyBuffer.Length);
               objZipOutputStream.Finish();
               objZipOutputStream.Close();
               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.

Conclusion

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

Summary

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.

License

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