讲述如何使用SQL CLR表值函数进行扩展

Microsoft SQL Server 2005 的一项新增功能是其与 Microsoft .NET Framework 公共语言运行库 (CLR) 的集成。这使得人们能够将 .NET Framework 类和函数纳入 Transact-SQL 语句和查询。

CLR 集成的机制有多种：

• CLR 用户定义函数（包括表值函数）。

• CLR 用户定义类型。

• CLR 存储过程。

• CLR 触发器。

本白皮书说明如何使用 CLR 表值函数根据包括数据库在内的各种源创建报表数据，从而创建可靠的 Reporting Services 报表。

扩展 Reporting Services

使用 .NET Framework 扩展 Reporting Services 并集成 CLR 功能的方法有多种，其中包括以下方法：

• 传递扩展 — 传递报表来响应事件

• 呈现扩展 — 除 Reporting Services 支持的之外，还能以其他格式显示报表

• 安全扩展 — 提供您自己的身份验证和授权机制用于查看和管理报表

• 数据处理扩展 — 通过开发能够处理来自 Reporting Services 不支持的数据源的数据

• 自定义报表项 — 是自定义的服务器控件，可嵌入报表中，以提供除内置控件以外的其他功能

本白皮书阐述如何实现表值函数来处理数据，使之成为使用数据处理扩展的替代方法。有关扩展 Reporting Services 的详细信息，请参阅 SQL Server 2005 联机丛书中的 Reporting Services 扩展。

使用表值函数

表值函数用于通过编程方式在运行时创建表。它们创建的表可象任何其他数据库表一样用于 Transact-SQL 查询语句。在 SQL Server 2000 中引入表值函数时，只能使用 Transact-SQL 创建它们。以下是在 Transact-SQL 中实现的表值函数的示例。

CREATE function EmployeeNames()
returns @employeeNames table (id int, name nvarchar(20), )
as begin
INSERT @employeeNames values(1, 'Ryan');
INSERT @employeeNames values(2, 'John');
INSERT @employeeNames values(3, 'Bob');
return
end

然后，就可以从 select 语句中引用该函数，就象它是表一样：SELECT name from EmployeeNames() where id = 1。

查询返回以下值：

name
--------------------
Ryan

尽管这很有用，但还是受到 Transact-SQL 语言的限制，因为该语言主要应用于关系数据。如果您尝试离开其适用范围，那么 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;
using 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 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);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

该表值函数是作为 TabularEventLog 类的两个静态方法实现的。第一个方法 (InitMethod) 赋予 SqlFunction 属性，用于将它指定为该表值函数的入口点。此方法必须返回 IEnumerable 或 IEnumerator 对象。该对象包含将用于填充返回表的数据。执行该函数时，SQL Server 将循环访问 IEnumerator 对象中的每个对象，并使用它来填充数据行。为此，它要将该对象传递到该类中的第二个方法 FillRow。此方法会将该对象转换成返回表中的某一行。此方法在 SqlFunction 属性的 FillRowMethodName 参数中指定。

其他元数据在 SqlFunction 属性的参数中定义。在前一示例中，列名和类型以及返回表的名称都是在此属性中定义的。

将此函数部署到某 SQL Server 实例后，就可以运行以下查询来查看应用程序日志中的最后 10 项。

SELECT TOP 10 T.logTime, T.Message, T.InstanceId
FROM dbo.ReadEventLog(N'Application') as T

结果如图 1 所示：

将表值函数用作数据处理扩展的替代方法

Reporting Services 数据处理扩展功能可用于通过实现一组 ADO.NET 接口来对数据源建模。这在概念上类似于如何才能将表值函数用于 Reporting Services。表值函数明显优于数据处理扩展。

优点:

首先，表值函数比数据处理扩展要容易实现的多。只需创建两种方法即可实现表值函数。而数据处理扩展则必须实现许多接口。同样，部署模型也更简单明了。Microsoft Visual Studio 2005 可自动将 .NET Framework 表值函数部署到 SQL Server，之后，该函数即立即变得可从 Reporting Services 中使用。为了部署某数据处理扩展，必须将程序集复制到客户端和报表服务器，并在这两处编辑 XML 配置文件。

表值函数的另一个重要优点在于它可以是与之连接的数据库中某一联接的组成部分。这意味着 SQL Server 中的关系数据在被放入报表之前，可以与该函数中定义的自定义数据混合在一起并筛选出来。这对于数据处理扩展则是不可能的，因为 Reporting Services 不支持数据源之间的联接查询。

缺点:

数据处理扩展较之表值函数要强大和灵活得多。表值函数只能对单个数据库表建模，而数据处理扩展可以对整个数据库的等效项建模。同样，数据处理扩展可以充当完全自定义的数据源，而且它可以有自己的查询语言和连接语法。对于不同类型的数据，使用 SQL 作为查询语言并非总是理想的选择。例如，Reporting Services 包括 XML 数据的数据处理扩展，它使用类似于 Xpath 的查询语言。当开发人员希望完全控制数据访问代码路径时，数据扩展很有用。

将表值函数与 SQL Server Reporting Services 一起使用

您必须先完成三件事，然后才能将表值函数用于 Reporting Services。首先，必须配置 SQL Server，使之允许 CLR 集成。其次，必须在 Visual Studio 中开发表值函数。最后，必须将该函数部署到某 SQL Server 实例中。

要让 SQL Server 允许 CLR 集成，必须使用 SQL Server 外围应用配置器工具或运行查询来设置一个标志。

配置 SQL Server，使之允许 CLR 集成:

1.单击“开始”按钮，依次指向“所有程序”、Microsoft SQL Server 2005 和“配置工具”，然后单击“外围应用配置器”。

2.在 SQL Server 2005 外围应用配置器工具中，单击“功能的外围应用配置器”。

3.选择您的服务器实例，展开“数据库引擎”选项，然后单击“CLR 集成”。

4.选择“启用 CLR 集成”。

此外，您可以在 SQL Server 中运行以下查询（此查询需要 ALTER SETTINGS 权限）:

USE master
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

开发表值函数:

若要开发表值函数，请在 Visual Studio 中创建一个新的 SQL Server 项目。若要创建 SQL Server 项目，请打开“新建项目”对话框，展开 Visual C#，然后选择“数据库”。此时，系统会提示您输入数据库连接信息。有关详细信息，请参阅 SQL Server 2005 联机丛书中的如何：创建 SQL Server 项目。在您建立了数据库连接后，就可以编写表值函数了。在项目中创建一个空白 .cs 文件，文件名为 EventLog.cs，然后将示例函数从前一部分复制并粘贴到该文件中。

部署表值函数:

若要部署，您必须向 SQL Server 实例注册该函数和包含它的程序集。这一操作可通过 Transact-SQL 命令完成。以下脚本会注册 tvfEventLogs 程序集和 ReadEventLog 函数：

CREATE ASSEMBLY tvfEventLog
   FROM'D:\assemblies\tvfEventLog\tvfeventlog.dll'
   WITH PERMISSION_SET = SAFE
   GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
   RETURNS TABLE (logTime datetime,Message nvarchar(4000),
      Category nvarchar(4000),InstanceId bigint)
   AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod GO

此外，您还可以在解决方案资源管理器中右键单击相应的项目，并选择“部署”选项，直接从 Visual Studio 中部署您的程序集。Visual Studio 会使用 SqlFunction 属性来自动确定函数签名和其他必需的元数据。

部署权限:

SQL Server 使用权限集在其宿主环境中安全地运行代码。当您在 Visual Studio 中创建数据库项目时，默认的权限集是 SAFE。这是唯一允许您在不进行其他配置的情况下直接从 Visual Studio 中部署的权限集。若要向您的程序集赋予 SAFE 以外的权限集，必须给您的程序集一个强名称，并执行其他配置步骤，然后才将该程序集部署到数据库。

向 SQL Server 注册程序集时，有三种可能的权限集：SAFE、EXTERNAL ACCESS 和 UNSAFE。

• SAFE 只允许内部计算和从该程序集中的代码进行本地数据访问。

• EXTERNAL ACCESS 允许访问外部系统资源，如文件、网络资源和注册表。

• UNSAFE 允许该程序集中的代码不受限制地运行。

若要使用 SAFE 以外的权限集部署您的程序集，必须遵循一些其他步骤。首先，必须从您要向 SQL Server 注册的程序集创建非对称密钥。其次，使用该密钥来创建登录信息。最后，必须向该登录信息授予相应的权限。以下 Transact-SQL 语句使用上述步骤将 UNSAFE 权限集授予根据前一部分的 tvfEventLogs 示例创建的程序集。

USE master
GO
CREATE ASYMMETRIC KEY EventLogKey FROM EXECUTABLE FILE =
'D:\assemblies\tvfEventLog\tvfeventlog.dll'
CREATE LOGIN EventLogLogin FROM ASYMMETRIC KEY EventLogKey
GRANT UNSAFE ASSEMBLY TO EventLogLogin
GO

这一操作只需进行一次，不是每次部署该程序集时都要进行。有关使用不同的权限集和向 SQL Server 注册程序集的详细信息，请参阅 CLR 集成代码访问安全性和创建程序集，二者都包含在 SQL Server 2005 联机丛书中。

事件日志报表:

在您部署了该表值函数后，该计算机的系统事件日志的虚拟表会添加到数据库中。由于 SQL Server 将该函数视为表，所以它可以无缝地用在 Reporting Services 中。

在您部署了该程序集后，请使用 Visual Studio 来创建新的 Reporting Services 项目。（如果您不熟悉如何使用 Reporting Services 报表设计器来创建报表，请参阅 SQL Server 2005 联机丛书中的 Reporting Services 教程。）使用连接到安装了该函数的同一数据库的 SQL Server 数据源创建报表。接着，创建使用以下查询的数据集：

SELECT TOP 10 T.logTime, T.Message, T.InstanceId
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'

定义了数据集后，将表数据区域添加到报表布局，并将数据集中的各个字段添加到表详细信息行。最后，运行该报表。它将显示本地计算机的 Logon/Logoff 类别中的最后 10 个安全事件。图 2 显示了该报表的一个示例。

此简单示例可加以扩展，在其中包括其他计算机管理和监视任务。例如，可以创建一个表值函数来分析 Microsoft Internet 信息服务 (IIS) 日志。然后，可以使用 Reporting Services 来创建 Web 流量监视应用程序。