SSRS 多数据源 DPE（数据处理扩展）
5.00/5 (6投票s)
一种将来自两个不同源的数据合并到一个数据源中以进行 SSRS 报表的方法。
引言
SSRS 是一个出色的工具，可以快速从许多不同的数据源检索数据，并在运行时决定的格式向用户呈现数据。SSRS 经常不足的一个领域是当底层数据需要来自多个不同来源时。也许我们想从 Oracle 的总账中检索数据，并将其与 SQL Server 中的员工列表连接起来，以在表中显示。很多时候，当这种情况发生时，我们会得到两个不相关的数据集，并且无法在不使用链接服务器的情况下将它们连接起来。
这里展示的选项是一个自定义数据处理扩展，它将从两个不同源收集数据并将它们合并到一个表中。
背景
在尝试自定义 DPE（数据处理扩展）来解决这个问题之前，请记住，Web 服务和 SSIS 数据迁移等替代方法可能是将数据分阶段到一个数据集中用于报告目的的更简单方法。我将此作为开发针对企业级系统进行报告时的另一个工具。
我强烈建议您在此处阅读以下 Code Project 教程 这里，以更深入地理解使用 SSRS DPE 可以做什么。我选择上传本教程是因为当前教程的功能比实现基本 DPE 所需的功能要多得多，并且我想精简掉一些额外功能。上面的 DPE 用于允许 XML 文件成为 SSRS 报告的数据源 - 我强烈推荐给任何对 DPE 感兴趣的人。
我也强烈建议阅读 MSDN 上有关数据处理扩展的概述，以了解它们在 SSRS 生命周期中的作用：数据处理扩展概述。
Using the Code
在下载了 DBDPE.zip（数据库数据处理扩展）并将其加载到 Visual Studio 2010 中后，您会注意到它依赖于 Microsoft.ReportingServices.Interfaces。当您在计算机上安装 SQL Server Reporting Services 客户端时，您应该会看到一个文件夹
[SQL Server Path]\MSRS[Version]\ReportServer\bin\
在我的 Denali 实例上，它显示为
D:\Program Files\Microsoft SQL Server\MSRS11.DENALI\ReportServer\bin\
这里就是 Microsoft.ReportingServices.Interface.DLL 所在的位置。
解决 .NET 版本也很重要：SQL Server 2005 和 2008 是基于 2.0 框架构建的。2008 R2 和 Denali（SQL Server 2012）是使用 3.5 程序集构建的（我们知道它们是 2.0 加上一些额外功能）。因此，我们不能将 DPE 构建为 .NET 4.0 程序集。- 根据 SQL Server 的版本，我们必须选择 2.0 到 3.5 之间的版本。尝试使用 4.0 构建会引发一些非常有趣的错误。
项目中两个最复杂的自定义对象是 DBConnection 和 DBCommand。这两个对象旨在利用两个独立的数据上下文，每个都有自己的连接和命令文本属性。为了将它们合并，以便只向 SSRS 公开一个命令和一个连接，我们假设属性具有类似 XML 的语法，这将在我们查看 RDL 文件时更清楚地看到。
DBCommand 对象将 XML 文件解析到其两个属性中，如下所示
/// <summary>
/// Gets/sets the connection string
/// </summary>
/// <remarks>
/// String must consist of XML element with <sqlConn1 /> and
/// <sqlConn2 /> attributes"
/// </remarks>
public String ConnectionString
{
get
{
return string.Format("SQL: {0} ORA: {1}",
m_sqlConnection1, m_sqlConnection2);
}
set
{
Debug.WriteLine("Setting IDBConnection.ConnectionString
to '" + value + "'");
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(value);
if (xmlDoc["xml"].Attributes["sqlConn1"] == null)
{
throw (new ArgumentException("'Connection sqlConn1 attribute
is missing in the connection xml", value));
}
m_sqlConnection1 = xmlDoc["xml"].Attributes["sqlConn1"].Value;
if (xmlDoc["xml"].Attributes["sqlConn2"] == null)
{
throw (new ArgumentException("'Connection sqlConn2 attribute
is missing in the connection xml", value));
}
m_sqlConnection2 = xmlDoc["xml"].Attributes["sqlConn2"].Value;
}
}
...
上述代码的目标是将两个潜在的连接字符串合并到一个公开的属性中。任何异常都将由报告环境（BI Developer Studio 的报表设计器或 SQL Server 报表服务器）处理。然后，开发报表的人员可以解决这些异常。
一旦有了两个不同的连接，我们就可以针对这些连接执行两个不同的命令。一种类似的策略被用来将两个命令合并到一个公开的 SSRS 属性中。
/// <summary>
/// Gets/sets the current command text
/// </summary>
/// <remarks>
/// CommandText must be in the format of:
/// <xml leftCommand="TSQL" rightCommand="TSQL"
/// leftMatch="leftJoin" rightMatch="rightJoin" />
/// </remarks>
public String CommandText
{
get
{
string commandText = string.Format("{0}/{1}/{2}/{3}",
m_leftCommand, m_rightCommand, m_leftMatch, m_rightMatch);
Debug.WriteLine("IDBCommand.CommandText: Get Value =" + commandText);
return (commandText);
}
set
{
Debug.WriteLine("IDBCommand.CommandText: Set Value =" + value);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(value);
if (xmlDoc["xml"].Attributes["leftCommand"] == null)
{
throw (new ArgumentException
("'Connection leftCommand attribute is missing
in the connection xml", value));
}
m_leftCommand = xmlDoc["xml"].Attributes["leftCommand"].Value;
if (xmlDoc["xml"].Attributes["leftMatch"] == null)
{
throw (new ArgumentException
("'Connection leftMatch attribute is missing
in the connection xml", value));
}
m_leftMatch = xmlDoc["xml"].Attributes["leftMatch"].Value;
if (xmlDoc["xml"].Attributes["rightCommand"] == null)
{
throw (new ArgumentException
("'Connection rightCommand attribute is missing
in the connection xml", value));
}
m_rightCommand = xmlDoc["xml"].Attributes["rightCommand"].Value;
if (xmlDoc["xml"].Attributes["rightMatch"] == null)
{
throw (new ArgumentException
("'Connection rightMatch attribute is missing
in the connection xml", value));
}
m_rightMatch = xmlDoc["xml"].Attributes["rightMatch"].Value;
}
}
这里的策略是允许一个模拟的“left join”，其中左查询和右查询将返回两个数据集，它们将基于左表中找到的一个属性与右表中找到的一个属性进行连接。此时，我们只想解析命令文本，但稍后将使用该文本来访问底层数据存储。
/// <summary>
/// Retrieves IDataReader interface used to retrieve data and schema information.
/// </summary>
/// <param name="behavior">The requested command behavior behavior</param>
/// <returns>IDataReader Interface</returns>
public IDataReader ExecuteReader(CommandBehavior behavior)
{
Debug.WriteLine("IDBCommand.ExecuteReader with CommandBehavior." + behavior);
SqlCommand sqlCmd1 = new SqlCommand(m_leftCommand, m_connection.sqlConnection1);
SqlCommand sqlCmd2 = new SqlCommand(m_rightCommand, m_connection.sqlConnection2);
foreach (DBParameter param in this.Parameters)
{
sqlCmd1.Parameters.Add(new SqlParameter(param.ParameterName, param.Value));
}
SqlDataAdapter sqlDA1 = new SqlDataAdapter(sqlCmd1);
System.Data.DataSet dsResult1 = new System.Data.DataSet();
sqlDA1.Fill(dsResult1);
SqlDataAdapter sqlDA2 = new SqlDataAdapter(sqlCmd2);
System.Data.DataSet dsResult2 = new System.Data.DataSet();
sqlDA2.Fill(dsResult2);
m_dataTable = MergeTables(dsResult1.Tables[0], dsResult2.Tables[0]);
return new DBDataReader(this);
}
在这里，我们将执行提供的连接和命令。DBConnection 对象负责打开和关闭连接，因此此代码仅关注设置我们的命令对象。下面的 MergeTables 函数将把我们两个独立的数据集合并到一个数据集中，供我们的报表使用。m_dataTable 属性与底层的 DBDataReader 共享，以将数据返回给报告环境。
/// <summary>
/// Merge two different tables together based on the left and right
/// joining column definitions.
/// </summary>
/// <param name="leftTable"></param>
/// <param name="rightTable"></param>
/// <returns></returns>
private System.Data.DataTable MergeTables
(System.Data.DataTable leftTable, System.Data.DataTable rightTable)
{
// Setup output table with all columns in both source tables
System.Data.DataTable dtblResult = new System.Data.DataTable();
foreach (System.Data.DataColumn column in leftTable.Columns)
dtblResult.Columns.Add(column.ColumnName, column.DataType);
foreach (System.Data.DataColumn column in rightTable.Columns)
dtblResult.Columns.Add(column.ColumnName, column.DataType);
// Iterate through left table, joining against right
foreach (System.Data.DataRow leftRow in leftTable.Rows)
{
// Simulated Left Join - Every row in left table will be in our result set
System.Data.DataRow newRow = dtblResult.NewRow();
// Assign resulting properties from left table
foreach (System.Data.DataColumn column in leftTable.Columns)
newRow[column.ColumnName] = leftRow[column];
// Determine matching value we are looking for
object leftMatchVal = leftRow[m_leftMatch];
foreach (System.Data.DataRow rightRow in rightTable.Rows)
{
// Determine value to compare against
object rightMatchVal = rightRow[m_rightMatch];
if (leftMatchVal.GetType() == rightMatchVal.GetType())
{
// Not good enough to do type insensitive match -
// need to unbox before comparison succeeds.
// Once a match has been found, break out of right loop
// and go to next row in left join table
if (leftMatchVal.GetType() == typeof(string) &&
(string)rightMatchVal == (string)leftMatchVal)
{
// Assign resulting properties from right table
foreach (System.Data.DataColumn column in rightTable.Columns)
newRow[column.ColumnName] = rightRow[column];
break;
}
else if (leftMatchVal.GetType() == typeof(int) &&
(int)rightMatchVal == (int)leftMatchVal)
{
// Assign resulting properties from right table
foreach (System.Data.DataColumn column in rightTable.Columns)
newRow[column.ColumnName] = rightRow[column];
break;
}
}
}
// Either found match or completed iteration of
// all rows in right join table - ready for next row
dtblResult.Rows.Add(newRow);
}
// Completed iteration of all rows in left join table - return result
return dtblResult;
}
我强烈建议在生产环境中优化上述匹配算法 - 我的目标是编写简单的代码，因此没有使用缓存和其他优化运行时间的方法。
一旦您成功构建了数据处理扩展，请记住将其部署到您的开发和报告环境中，并修改相应的配置文件。我强烈建议阅读相应的 MSDN 教程 如何：将数据处理扩展部署到报表服务器 和 如何：将数据处理扩展部署到报表设计器。
同样，前面引用的 Code Project 文章 实现数据处理扩展 是在开发和报告环境中设置 DPE 的绝佳教程。
一旦您的报告环境设置完毕，就可以加载 DoubleDBReportProj.zip 中的 Reporting Services 项目了。
项目中唯一的报表是 DoubleDB.rdl，这是使用我们新数据源的示例报表。打开报表并查看 RDL 中定义的数据源。
数据源类型应显示为“自定义 DB 活动扩展”，这来自我们 DBConnection 对象的 LocalizedName 属性。连接字符串属性应为一个 XML 元素（名为 xml），带有两个属性：sqlConn1 和 sqlConn2。在我们的示例中，它们指向同一个数据库，这是一个微不足道的实现 - 在真正的实现中，我们预计它们会是两个不同的数据库连接。
先前定义的数据源的 DataSet 有一个查询，该查询由一个 XML 元素组成。我们的四个属性是我们 DPE 中定义的，用于允许两个不同的查询以及将这些查询相互连接的方式。单击刷新字段将验证一切是否按预期运行。我强烈建议花一些时间调试您的 DPE 以观察事件流。
当 DoubleDBReportProject 在 Visual Studio (BIDS) 中打开时，打开第二个 Visual Studio 实例，并加载 DBDPE 解决方案。在 DBDPE 解决方案中，单击“调试”->“附加到进程”。在您计算机上的进程列表中，您应该会看到 devenv.exe 列为进程。选择合适的 devenv.exe 实例，然后单击“附加”。在 DBDPE 解决方案中添加断点，您应该能够观察进程如何从连接到命令到数据读取器，再返回到报告环境。
祝您好运！
历史
- 第一个版本：2011 年 10 月 22 日