SQLServer–使用CCLR开发用户自定义表值函数

1、SQL Server 使用C# CLR开发用户自定义表值函数Microsoft SQL Server 2005 的一项新增功能是其与 Microsoft .NET Framework 公共语言运行库 (CLR) 的集成。这使得人们能够将 .NET Framework 类和函数纳入 Transact-SQL 语句和查询。 CLR 集成的机制有多种： CLR 用户定义函数（包括表值函数）。 CLR 用户定义类型。 CLR 存储过程。 CLR 触发器。 本白皮书说明如何使用 CLR 表值函数根据包括数据库在内的各种源创建报表数据，从而创建可靠的 Reporting Services 报表。 扩展 R

2、eporting Services 使用 .NET Framework 扩展 Reporting Services 并集成 CLR 功能的方法有多种，其中包括以下方法： 传递扩展 传递报表来响应事件 呈现扩展 除 Reporting Services 支持的之外，还能以其他格式显示报表 安全扩展 提供您自己的身份验证和授权机制用于查看和管理报表 数据处理扩展 通过开发能够处理来自 Reporting Services 不支持的数据源的数据 自定义报表项 是自定义的服务器控件，可嵌入报表中，以提供除内置控件以外的其他功能 本白皮书阐述如何实现表值函数来处理数据，使之成为使用数据处理扩展的替代方法

3、。有关扩展 Reporting Services 的详细信息，请参阅 SQL Server 2005 联机丛书中的 Reporting Services 扩展。 使用表值函数 表值函数用于通过编程方式在运行时创建表。它们创建的表可象任何其他数据库表一样用 于 Transact-SQL 查询语句。在 SQL Server 2000 中引入表值函数时，只能使用 Transact-SQL 创建它们。以下是在 Transact-SQL 中实现的表值函数的示例。 CREATE function EmployeeNames()returns employeeNames table (id int, nam

4、e nvarchar(20), )as beginINSERT employeeNames values(1, Ryan);INSERT employeeNames values(2, John);INSERT employeeNames values(3, Bob);returnend然后，就可以从 select 语句中引用该函数，就象它是表一样：SELECT name from EmployeeNames() where id = 1。 查询返回以下值： name-Ryan尽管这很有用，但还是受到 Transact-SQL 语言的限制，因为该语言主要应用于关系数据。如果您尝试离开其适用范围

5、，那么 Transact-SQL 会变得多少有点不太灵活。在 SQL Server 2005 中，您现在可以使用自己喜欢的 .NET Framework 语言来创建表值函数，这就可能出现一些令人惊叹的事情。现在，程序员能够将他们所需的任何信息提取到关系数据库表中。 例如，以下代码是在 Microsoft Visual C# 中实现的 SQL Server 2005 表值函数，它返回根据系统事件日志创建的表： using System;using System.Data.Sql;using Microsoft.SqlServer.Server;using System.Collections;u

6、sing System.Data.SqlTypes;using System.Diagnostics;public class TabularEventLog SqlFunction(TableDefinition=logTime datetime,Message + nvarchar(4000),Category nvarchar(4000),InstanceId bigint, Name=ReadEventLog, FillRowMethodName = FillRow) public static IEnumerable InitMethod(String logname) return

7、 new EventLog(logname, Environment.MachineName).Entries; public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId) EventLogEntry eventLogEntry = (EventLogEntry)obj; timeWritten = new SqlDateTime(eventLogEntry.TimeWritten); m

8、essage = new SqlChars(eventLogEntry.Message); category = new SqlChars(eventLogEntry.Category); instanceId = eventLogEntry.InstanceId; 该表值函数是作为 TabularEventLog 类的两个静态方法实现的。第一个方法 (InitMethod) 赋予 SqlFunction 属性，用于将它指定为该表值函数的入口点。此方法必须返回 IEnumerable 或 IEnumerator 对象。该对象包含将用于填充返回表的数据。执行该函数时，SQL Server 将循环

9、访问 IEnumerator 对象中的每个对象，并使用它来填充数据行。为此，它要将该对象传递到该类中的第二个方法 FillRow。此方法会将该对象转换成返回表中的某一行。此方法在 SqlFunction 属性的 FillRowMethodName 参数中指定。 其他元数据在 SqlFunction 属性的参数中定义。在前一示例中，列名和类型以及返回表的名称都是在此属性中定义的。 将此函数部署到某 SQL Server 实例后，就可以运行以下查询来查看应用程序日志中的最后 10 项。 SELECT TOP 10 T.logTime, T.Message, T.InstanceIdFROM dbo

10、.ReadEventLog(NApplication) as T结果如图 1 所示： 图 1. 查询结果将表值函数用作数据处理扩展的替代方法 Reporting Services 数据处理扩展功能可用于通过实现一组 ADO.NET 接口来对数据源建模。这在概念上类似于如何才能将表值函数用于 Reporting Services。表值函数明显优于数据处理扩展。 优点: 首先，表值函数比数据处理扩展要容易实现的多。只需创建两种方法即可实现表值函数。 而数据处理扩展则必须实现许多接口。同样，部署模型也更简单明了。Microsoft Visual Studio 2005 可自动将 .NET Frame

11、work 表值函数部署到 SQL Server，之后，该函数即立即变得可从 Reporting Services 中使用。为了部署某数据处理扩展，必须将程序集复制到客户端和报表服务器，并在这两处编辑 XML 配置文件。 表值函数的另一个重要优点在于它可以是与之连接的数据库中某一联接的组成部分。这意 味着 SQL Server 中的关系数据在被放入报表之前，可以与该函数中定义的自定义数据混合在一起并筛选出来。这对于数据处理扩展则是不可能的，因为 Reporting Services 不支持数据源之间的联接查询。 缺点: 数据处理扩展较之表值函数要强大和灵活得多。表值函数只能对单个数据库表建模，而

12、数 据处理扩展可以对整个数据库的等效项建模。同样，数据处理扩展可以充当完全自定义的数据源，而且它可以有自己的查询语言和连接语法。对于不同类型的数据， 使用 SQL 作为查询语言并非总是理想的选择。例如，Reporting Services 包括 XML 数据的数据处理扩展，它使用类似于 Xpath 的查询语言。当开发人员希望完全控制数据访问代码路径时，数据扩展很有用。 将表值函数与 SQL Server Reporting Services 一起使用 您必须先完成三件事，然后才能将表值函数用于 Reporting Services。首先，必须配置 SQL Server，使之允许 CLR 集成。

13、其次，必须在 Visual Studio 中开发表值函数。最后，必须将该函数部署到某 SQL Server 实例中。 要让 SQL Server 允许 CLR 集成，必须使用 SQL Server 外围应用配置器工具或运行查询来设置一个标志。 配置 SQL Server，使之允许 CLR 集成: 1.单击“开始”按钮，依次指向“所有程序”、Microsoft SQL Server 2005 和“配置工具”，然后单击“外围应用配置器”。 2.在 SQL Server 2005 外围应用配置器工具中，单击“功能的外围应用配置器”。 3.选择您的服务器实例，展开“数据库引擎”选项，然后单击“CLR

14、集成”。 4.选择“启用 CLR 集成”。 此外，您可以在 SQL Server 中运行以下查询（此查询需要 ALTER SETTINGS 权限）: USE mastersp_configure show advanced options, 1;GORECONFIGURE;GOsp_configure clr enabled, 1;GORECONFIGURE;GO开发表值函数: 若要开发表值函数，请在 Visual Studio 中创建一个新的 SQL Server 项目。若要创建 SQL Server 项目，请打开“新建项目”对话框，展开 Visual C#，然后选择“数据库”。此时，系统会

15、提示您输入数据库连接信息。有关详细信息，请参阅 SQL Server 2005 联机丛书中的如何：创建 SQL Server 项目。在您建立了数据库连接后，就可以编写表值函数了。在项目中创建一个空白 .cs 文件，文件名为 EventLog.cs，然后将示例函数从前一部分复制并粘贴到该文件中。 部署表值函数: 若要部署，您必须向 SQL Server 实例注册该函数和包含它的程序集。这一操作可通过 Transact-SQL 命令完成。以下脚本会注册 tvfEventLogs 程序集和 ReadEventLog 函数： CREATE ASSEMBLY tvfEventLog FROMD:asse

16、mbliestvfEventLogtvfeventlog.dll WITH PERMISSION_SET = SAFE GOCREATE FUNCTION ReadEventLog(logname nvarchar(100) RETURNS TABLE (logTime datetime,Message nvarchar(4000), Category nvarchar(4000),InstanceId bigint) AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod GO此外，您还可以在解决方案资源管理器中右键单击相应的项目，并选

17、择“部署”选项，直接从 Visual Studio 中部署您的程序集。Visual Studio 会使用 SqlFunction 属性来自动确定函数签名和其他必需的元数据。 部署权限: SQL Server 使用权限集在其宿主环境中安全地运行代码。当您在 Visual Studio 中创建数据库项目时，默认的权限集是 SAFE。这是唯一允许您在不进行其他配置的情况下直接从 Visual Studio 中部署的权限集。若要向您的程序集赋予 SAFE 以外的权限集，必须给您的程序集一个强名称，并执行其他配置步骤，然后才将该程序集部署到数据库。 向 SQL Server 注册程序集时，有三种可能的权

18、限集：SAFE、EXTERNAL ACCESS 和 UNSAFE。 SAFE 只允许内部计算和从该程序集中的代码进行本地数据访问。 EXTERNAL ACCESS 允许访问外部系统资源，如文件、网络资源和注册表。 UNSAFE 允许该程序集中的代码不受限制地运行。 若要使用 SAFE 以外的权限集部署您的程序集，必须遵循一些其他步骤。首先，必须从您要向 SQL Server 注册的程序集创建非对称密钥。其次，使用该密钥来创建登录信息。最后，必须向该登录信息授予相应的权限。以下 Transact-SQL 语句使用上述步骤将 UNSAFE 权限集授予根据前一部分的 tvfEventLogs 示例创

19、建的程序集。 USE masterGOCREATE ASYMMETRIC KEY EventLogKey FROM EXECUTABLE FILE =D:assembliestvfEventLogtvfeventlog.dllCREATE LOGIN EventLogLogin FROM ASYMMETRIC KEY EventLogKeyGRANT UNSAFE ASSEMBLY TO EventLogLoginGO这一操作只需进行一次，不是每次部署该程序集时都要进行。有关使用不同的权限集和向 SQL Server 注册程序集的详细信息，请参阅 CLR 集成代码访问安全性和创建程序集，二者都

20、包含在 SQL Server 2005 联机丛书中。 事件日志报表: 在您部署了该表值函数后，该计算机的系统事件日志的虚拟表会添加到数据库中。由于 SQL Server 将该函数视为表，所以它可以无缝地用在 Reporting Services 中。 在您部署了该程序集后，请使用 Visual Studio 来创建新的 Reporting Services 项目。（如果您不熟悉如何使用 Reporting Services 报表设计器来创建报表，请参阅 SQL Server 2005 联机丛书中的 Reporting Services 教程。）使用连接到安装了该函数的同一数据库的 SQL Se

21、rver 数据源创建报表。接着，创建使用以下查询的数据集： SELECT TOP 10 T.logTime, T.Message, T.InstanceIdFROM dbo.ReadEventLog(NSecurity) as TWHERE T.Category = NLogon/Logoff定义了数据集后，将表数据区域添加到报表布局，并将数据集中的各个字段添加到表详细信息行。最后，运行该报表。它将显示本地计算机的 Logon/Logoff 类别中的最后 10 个安全事件。图 2 显示了该报表的一个示例。 图 2. 报表示例此简单示例可加以扩展，在其中包括其他计算机管理和监视任务。例如，可以创

22、建一个表 值函数来分析 Microsoft Internet 信息服务 (IIS) 日志。然后，可以使用 Reporting Services 来创建 Web 流量监视应用程序。 Web 服务和表值函数 表值函数的一个有趣功能是从 Web 服务中提取数据的能力。这可用于创建非常规的报表。我将说明如何在表值函数中使用 Microsoft MapPoint Web 服务，并将它与 AdventureWorks 数据库中的数据联接，从而将空间数据显示在地图上和显示在报表中。 使用 MapPoint Web 服务: 您必须先从 Microsoft 获得免费的开发人员帐户，然后才能开始按照 MapPoi

23、nt Web 服务进行开发。您可以访问 MapPoint Web 服务客户服务网站，从中获取一个帐户。开始根据该 Web 服务进行开发之前，可以从一个好地方获取信息，即 MapPoint Web 服务 SDK 入门。 您需要使用 Visual Studio 将 Web 引用添加到您的项目中，该引用指向 MapPoint 暂存服务器上的 .wsdl 文件。有关为 MapPoint Web 服务配置 Web 引用的详细信息，请参阅访问 MapPoint Web 服务 SOAP API。 MapPoint Web 服务提供四种服务，每种服务都有其自己的 SOAP 端点： 公共服务提供可供其他服务使用

24、的功能。此服务用于检索元数据和用于实用功能。 查找服务可用于搜索位置，查找某一地址的经度和纬度（“地理编码”），以及查找某位置附近的兴趣点。 路线服务指示一个位置到另一个位置的行车路线。 呈现服务可用于利用位置和路线信息创建地图图像。 MapPoint Web 服务表值函数: 最后，我希望我的表值函数使用 MapPoint Web 服务来执行以下任务： 使用查找服务来查找 AdventureWorks 自行车店的经度和纬度。 使用查找服务来查找距此经度和纬度最近的五台自动柜员机 (ATM)。 使用呈现服务来查找从该商店到 ATM 的路线。 使用呈现服务将此路线显示在地图上。 首先，我必须定义一

25、个称为 GetProximity 的表值函数。以下 Transact-SQL 代码显示我的表值函数的签名： CREATE FUNCTION GetProximity(city nvarchar(200), state nvarchar(2), count int, entityTypeName nvarchar(200)RETURNS TABLE (HitName nvarchar(200), HitAddress nvarchar(200), MapImage varbinary(max)GetProximity 使用城市名和两位数的州代码来代表初始位置。它返回实体数，和要搜索的实体类型名。

26、它会搜索 n 个最近的实体，其中，n 由计算参数指定，实体类型由 entityTypeName 参数指定。它会返回一个表和一幅地图（二进制文件图像），表中包含名称列、地址列，地图中包含每个实体的路线。 C# 方法签名如下所示： public static IEnumerable InitMap(string city, string state, int count, string entityTypeName)public static void FillRow(Object obj, out SqlChars name, out SqlChars address, out SqlBinar

27、y map)请注意，nvarchar Transact-SQL 数据类型映射到 SqlChars .NET Framework 数据类型，而 varbinary Transact-SQL 数据类型映射到 SqlBinary .NET Framework 数据类型。有关数据类型之间的映射的完整列表，请参阅 System.Data.SqlTypes 命名空间的文档。 在 InitMap 方法中，我将城市和州转换成经度和纬度。接着，我找到了与此坐标接近的所有实体。最后，我找到了初始位置和找到的实体之间的行车路线。返回值是封装有行车路线的一组 Route 对象。 public static IEnum

28、erable InitMap(string city, string state, int count, string entityTypeName) FindServiceSoap find = new FindServiceSoap(); find.PreAuthenticate = true; find.Credentials = new NetworkCredential(username, passwd); / 对初始城市和州进行地理编码（Geocode） FindAddressSpecification findSpec = new FindAddressSpecification

29、(); Address findAddr = new Address(); findAddr.CountryRegion = US; findAddr.Subdivision = state; findAddr.PrimaryCity = city; findSpec.InputAddress = findAddr; findSpec.DataSourceName = MapPoint.NA; findSpec.Options = new FindOptions(); findSpec.Options.ThresholdScore = 0.45; FindResults results = f

30、ind.FindAddress(findSpec); if (results.NumberFound 0) / 如果城市和州已经存在，则获取经度和纬度 Location startLocation = results.Results0.FoundLocation; LatLong startPoint = startLocation.LatLong; / 查找附近的实体 FindNearbySpecification findNearby = new FindNearbySpecification(); FindFilter filter = new FindFilter(); filter.

31、EntityTypeName = entityTypeName; findNearby.Filter = filter; FindOptions options = new FindOptions(); options.Range = new FindRange(); / 设置计数限制 options.Range.Count = count; findNearby.Options = options; findNearby.DataSourceName = NavTech.NA; findNearby.LatLong = startPoint; findNearby.Distance = 10

32、.0; results = find.FindNearby(findNearby); Route routes = new Routeresults.Results.Length; RouteServiceSoap routeService = new RouteServiceSoap(); routeService.PreAuthenticate = true; routeService.Credentials = new NetworkCredential(username,passwd); RouteSpecification spec = new RouteSpecification(

33、); spec.DataSourceName = MapPoint.NA; / 创建到每个实体的路线 spec.Segments = new SegmentSpecification2; spec.Segments0 = new SegmentSpecification(); spec.Segments0.Waypoint = new Waypoint(); spec.Segments0.Waypoint.Location = startLocation; spec.Segments0.Waypoint.Name = start; for (int x = 0; x results.Resul

34、ts.Length; x+) spec.Segments1 = new SegmentSpecification(); spec.Segments1.Waypoint = new Waypoint(); spec.Segments1.Waypoint.Location = results.Resultsx.FoundLocation; spec.Segments1.Waypoint.Name = end; routesx = routeService.CalculateRoute(spec); return routes; return null;在 FillRow 方法中，我使用呈现服务将每

35、个 Route 对象转换成了地图图像。然后使用这一图像和该实体的位置数据填充行。 public static void FillRow(Object obj, out SqlChars name, out SqlChars address, out SqlBinary map) Route route = (Route)obj; / 构建地址字符串 Address endAddress = route.Specification.Segments1.Waypoint.Location.Address; string entityAddress = endAddress.AddressLine;

36、 string enitityCity = endAddress.PrimaryCity; string entityState = endAddress.Subdivision; string entityName = route.Specification.Segments1.Waypoint.Location.Entity.DisplayName; / 对两列的值进行分配 name = new SqlChars(entityName); address = new SqlChars(entityAddress + + enitityCity + + entityState); / 获取路

37、线视图 ViewByHeightWidth view = route.Itinerary.View.ByHeightWidth; RenderServiceSoap renderService = new RenderServiceSoap(); renderService.PreAuthenticate = true; renderService.Credentials = new NetworkCredential(username, passwd); / 显示带有路线的地图 MapSpecification mapSpec = new MapSpecification(); mapSpe

38、c.DataSourceName = MapPoint.NA; mapSpec.Views = new MapViewview; mapSpec.Route = route; / 将地图指定给地图列 MapImage image = renderService.GetMap(mapSpec); map = new SqlBinary(image0.MimeData.Bits);部署和调试 GetProximity： 部署使用 Web 服务的表值函数比前一示例要更为复杂。部署使用 Web 服务的表值函数的全部步骤如下： 1.将包含 GetProximity 函数的项目配置为预先生成 XML 序列

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

40、.dll。 2.将 System.Security.AllowPartiallyTrustedCallers 属性添加到该程序集中。此操作可通过在该项目的 AssemblyInfo.cs 中添加以下行来完成： assembly: System.Security.AllowPartiallyTrustedCallers 这样，XML 序列化程序集就可以和包含 GetProximity 函数的主程序集通信。 3.向 SQL Server 注册在步骤 1 中创建的 XML 序列化 DLL。SAFE 权限集就足够了。 4.为包含 GetProximity 表值函数的 DLL 创建一个非对称密钥。 5.

41、为该非对称密钥创建登录信息，并授予它 EXTERNAL ACCESS 权限集。 6.使用 EXTERNAL ACCESS 权限集注册包含 GetProximity 的程序集。 7.注册表值函数 GetProximity。 由于这个依赖关系链相对而言更为冗长和复杂，所以我放弃了 Visual Studio 的部署机制，而选择了作为生成后步骤运行的 Transact-SQL 脚本，它执行部署步骤 3-7。示例项目中包含了该脚本。 调试表值函数非常简单。数据库项目有一个 Test Scripts 目录。您可以将脚本添加到该目录中并直接从 Visual Studio 运行它们。成功部署了该函数后，您可

42、以创建一个调用该函数的 Transact-SQL 查询，并在不离开 Visual Studio 的情况下执行完该函数的 C# 代码。 若要测试 GetProximity，请在 Test Scripts 目录中创建一个名为“Test.sql”的测试脚本，并在该文件中添加以下查询： SELECT * FROM GetProximity(Redmond, WA, 5, SIC3578) 请注意函数的参数。我将我的临近区查询集中在华盛顿州的雷蒙德市，因此我将 “Redmond”用于 city 参数，将“WA”用于 state 参数。我为 count 值提供了数字 5，这是我希望返回的实体数。我还为 e

43、ntityTypeName 参数提供了值“SIC3578”，这是我使用的 MapPoint 数据源中的 ATM 的实体名。有关 MapPoint 数据源和实体类型的详细信息，请参阅 MapPoint 数据源。 若要在 Visual Studio 中运行该查询，请右键单击解决方案资源管理器中的 Test.sql 文件并选择“调试脚本”。您将在 Visual Studio 输出窗口中得到与以下内容类似的结果： HitName HitAddress MapImage-Woodgrove Bank 8502 160th Ave NE Redmond WA Woodgrove Bank 16025 NE

44、 85th St Redmond WA Woodgrove Bank 16150 NE 85th St Redmond WA Woodgrove Bank 8867 161st Ave NE Redmond WA Woodgrove Bank 15600 Redmond Way Redmond WA No rows affected.(5 row(s) returned)若要调试 GetProximity 函数，请在 C# 代码中为该函数设置一个断点，并再次运行该脚本。在指定的点，执行将中止，您可以像对任何其他托管过程一样对它进行调试。 使用 MapPoint Web 服务创建报表： 随 SQL Server 2005 附带的 AdventureWorks 示例数据库描述了虚构的自行车和自行车配件制造商，该厂商向全美国的零售店出售产品。在本示例中，Adventure Works Cycles 决定停止接受信用卡或支票。从现在起，他们希望所有发票都只以现金支付。在此过渡期间，为了方便其客户，该厂商正在制作一份报表，在其中显示距离其客户的 零售店位置最近的五台 ATM 的地址和地图。这不是一个现实场景，但可以起到说明如何使用表值函数将传统数据源（S