如何使用SQL CLR表值函数进行扩展(6)

部署和调试 GetProximity：

部署使用 Web 服务的表值函数比前一示例要更为复杂。部署使用 Web 服务的表值函数的全部步骤如下：

1.将包含 GetProximity 函数的项目配置为预先生成 XML 序列化程序集。当 .NET Framework 调用 Web 服务时，它会动态生成一个程序集来处理 SOAP XML 的序列化和反序列化。这就出现了一个问题，因为 SQL Server CLR 主机不允许在运行时动态加载程序集。因此，必须在编译时生成 Web 服务调用的 XML 序列化程序集，并向 SQL Server 注册它。若要预先从 Visual Studio 生成此程序集，请从“项目”菜单中，单击“属性”并选择“生成”。将“生成序列化程序集”设置为“开启”。XML 序列化 DLL 随即会随您的项目生成并被添加到 Bin 目录中。其名称为 [ProjectName].XmlSerializers.dll。

2.将 System.Security.AllowPartiallyTrustedCallers 属性添加到该程序集中。此操作可通过在该项目的 AssemblyInfo.cs 中添加以下行来完成：

[assembly: System.Security.AllowPartiallyTrustedCallers]

这样，XML 序列化程序集就可以和包含 GetProximity 函数的主程序集通信。

3.向 SQL Server 注册在步骤 1 中创建的 XML 序列化 DLL。SAFE 权限集就足够了。

4.为包含 GetProximity 表值函数的 DLL 创建一个非对称密钥。

5.为该非对称密钥创建登录信息，并授予它 EXTERNAL ACCESS 权限集。

6.使用 EXTERNAL ACCESS 权限集注册包含 GetProximity 的程序集。

7.注册表值函数 GetProximity。

由于这个依赖关系链相对而言更为冗长和复杂，所以我放弃了 Visual Studio 的部署机制，而选择了作为生成后步骤运行的 Transact-SQL 脚本，它执行部署步骤 3-7。示例项目中包含了该脚本。

调试表值函数非常简单。数据库项目有一个 Test Scripts 目录。您可以将脚本添加到该目录中并直接从 Visual Studio 运行它们。成功部署了该函数后，您可以创建一个调用该函数的 Transact-SQL 查询，并在不离开 Visual Studio 的情况下执行完该函数的 C# 代码。

若要测试 GetProximity，请在 Test Scripts 目录中创建一个名为“Test.sql”的测试脚本，并在该文件中添加以下查询：

SELECT * FROM GetProximity('Redmond', 'WA', 5, 'SIC3578')

请注意函数的参数。我将我的临近区查询集中在华盛顿州的雷蒙德市，因此我将“Redmond”用于 @city 参数，将“WA”用于 @state 参数。我为 @count 值提供了数字 5，这是我希望返回的实体数。我还为 @entityTypeName 参数提供了值“SIC3578”，这是我使用的 MapPoint 数据源中的 ATM 的实体名。有关 MapPoint 数据源和实体类型的详细信息，请参阅 MapPoint 数据源。

若要在 Visual Studio 中运行该查询，请右键单击解决方案资源管理器中的 Test.sql 文件并选择“调试脚本”。您将在 Visual Studio 输出窗口中得到与以下内容类似的结果：

HitName             HitAddress                          MapImage
----------------------------------------------------------------------
Woodgrove Bank      8502 160th Ave NE Redmond WA        <BINARY>
Woodgrove Bank      16025 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      16150 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      8867 161st Ave NE Redmond WA        <BINARY>
Woodgrove Bank      15600 Redmond Way Redmond WA        <BINARY>
No rows affected.
(5 row(s) returned)

若要调试 GetProximity 函数，请在 C# 代码中为该函数设置一个断点，并再次运行该脚本。在指定的点，执行将中止，您可以像对任何其他托管过程一样对它进行调试。

使用 MapPoint Web 服务创建报表：

随 SQL Server 2005 附带的 AdventureWorks 示例数据库描述了虚构的自行车和自行车配件制造商，该厂商向全美国的零售店出售产品。在本示例中，Adventure Works Cycles 决定停止接受信用卡或支票。从现在起，他们希望所有发票都只以现金支付。在此过渡期间，为了方便其客户，该厂商正在制作一份报表，在其中显示距离其客户的零售店位置最近的五台 ATM 的地址和地图。这不是一个现实场景，但可以起到说明如何使用表值函数将传统数据源（SQL 数据库）与非传统数据源（MapPoint Web 服务）联接在一起的目的。

创建我们的报表的第一步是在 Visual Studio 中创建一个新的 Report Server 项目，并指定数据源。我的报表的数据源是 SQL Server 2005 AdventureWorks 示例数据库，其中有我以前创建的 MapPoint 表值函数。有一个用于该报表的数据集。它包含零售店名、零售店所在的城市、州、ATM 名、ATM 地址和 ATM 路线图等字段。

对于每个零售店，我们都希望调用 GetProximity 并找出五台最近的 ATM。在 SQL Server 2005 中，有一个新的 APPLY 子句类型可处理此问题。这与联接稍有不同，因为我们希望联接函数参数，而不是函数结果。这意味着表值函数是为 APPLY 左侧返回的每一行调用的。然后，就可以将函数结果的并集联接到该查询的其余部分。以下是该报表数据集的 Transact-SQL 查询：

SELECT TOP(40) Sales.Store.Name, Person.Address.City,
   Person.StateProvince.StateProvinceCode, GetProximity_1.HitName,
   GetProximity_1.HitAddress, GetProximity_1.MapImage
   FROM Sales.CustomerAddress
      INNER JOIN Person.Address
         ON Sales.CustomerAddress.AddressID = Person.Address.AddressID
         AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
      INNER JOIN Sales.Store
      INNER JOIN Sales.StoreContact ON Sales.Store.CustomerID = 
Sales.StoreContact.CustomerID
         ON Sales.CustomerAddress.CustomerID = 
Sales.StoreContact.CustomerID
      INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID 
= Person.StateProvince.StateProvinceID
         AND Person.Address.StateProvinceID = 
Person.StateProvince.StateProvinceID
CROSS APPLY dbo.GetProximity(Person.Address.City,
   Person.StateProvince.StateProvinceCode, 5, 'SIC3578') AS 
GetProximity_1

请注意，其中使用了 CROSS APPLY 来将 GetProximity 函数参数与 Person.Address.City 和 Person.StateProvince.StateProvinceCode 表示的其他查询数据进行链接。

注意 若要在数据集查询中使用 APPLY 子句，必须使用通用查询设计器。基于 GUI 的查询设计器不能以图形方式显示它，并会引发异常。