SQL Server Report Compress solution (Zip format)

Introduction

Microsoft SQL Server Reporting Services is the emerging technology in reporting services. As the reporting service is being shipped along with MS SQL Server, nowadays in each .NET based projects this is often being used.

Reports are often used for decision making purpose in a management and they have to be 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 kinds of errors and to win customer satisfaction we can dynamically compress the report in a ZIP file format and allow the user to download the file into their local hard disc. The above said 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 a web application project developed using ASP.NET and C#.

Requirements

• SharpZipLib� free Open Source .NET zip library. (Download URL: ic#code).
• 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 byte array through the reporting web service.
• Check for the length of byte array, if it cross 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 strDSNPath = "" // Report DSN path ;

string strReportFolderName = "" // Report folder name;

string strDataSourceName  = "" // DataSourceName;

ReportService.DataSourceReference objDataSourceReference = 
                            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 used

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:

  //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)
  {
    I 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 purpose, customize it as per your requirement.
• Saved Zip files in the temp folder are to be deleted on a timely basis either through a scheduler or through a program.

Summary

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

Conclusion

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

Happy programming.