在.NET基础项目中,SQL Server报告服务常被用于决策支持,因为其能够快速展示结果。然而,当数据量巨大且网络带宽有限时,报告加载速度会变慢,甚至导致报告服务超时。为了避免这种情况,提高客户满意度,可以将报告动态压缩成ZIP文件格式,允许用户下载到本地硬盘。本文将介绍如何使用C#或VB.NET编程实现这一解决方案。
要实现上述解决方案,需要以下条件:
以下步骤仅适用于报告数据量较大的情况:
以下代码通过SQL Server报告服务获取报告的字节数组形式:
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 = new ReportService.DataSourceReference();
objDataSourceReference.Reference = "\"" + strDSNPath;
ReportService.DataSource[] objArrDataSources = new ReportService.DataSource[1];
ReportService.DataSource 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.
string strDeviceInfo = "True 0 ";
//Passing parameters to report
ReportService.ParameterValue[] 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.
byte[] byteResultStream = objReportService.Render(strReportName, strReportType, strHistoryID, strDeviceInfo, arrParameters, objDataSourceCredentials, strShowHideToggle, out strEncoding, out strMimeType, out objParametersUsed, out objWarnings, out strArrStreamIdentifiers);
解决方案涉及两个私有方法,详细讨论如下:
该方法用于在temp文件夹中写入报告。以下是代码:
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;
}
该方法用于压缩保存的报告文件。代码可在下载的示例中找到,并根据需要进行了定制:
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));
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;
}
}
在上述代码片段中,没有进行异常处理,但在实际场景中这是必需的。上述代码是为项目目的编写的,请根据需求进行定制。保存在temp文件夹中的ZIP文件需要通过计划任务或程序定期删除。
本文记录了一种克服SQL Server报告服务超时的方法,以便查看包含大量数据的报告。解决方案是将报告压缩成ZIP格式,并允许用户下载ZIP文件。