SQL Server report compress solution (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.