使用 SQL Server 2005中的 CLR 集成(5)

前一节在高层次上对基于 CLR 的编程与 T-SQL、中间层和扩展存储过程 (XP) 进行了比较。在这一节中，我们将考虑数据库应用程序开发人员经常遇到的一些编程任务和模型，并且讨论如何使用 CLR（以及在一些情况下如何不使用）进行处理。

使用 Framework 库进行数据验证

SQL Server 2005 中的 CLR 集成允许用户利用 .NET Framework 类库提供的丰富功能来解决其数据库编程问题。

常规表达式的使用可以很好地说明 CLR 集成如何增强了验证和过滤功能。在处理数据库中存储的文本数据方面，常规表达式提供的模式匹配功能比通过 T-SQL 查询语言中的 LIKE 运算符可用的模式匹配功能多。考虑以下 C# 代码，它只是 System.Text.RegularExpressions 命名空间中的 RegEx 类的一个简单包装：

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class StringFunctions
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString) 
{
   Regex r1 = new Regex(pattern.TrimEnd(null));
   return r1.Match(matchString.TrimEnd(null)).Success;
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString ExtractAreaCode(string matchString)
{
   Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
   Match m = r1.Match(matchString);
   if (m.Success)
      return m.Value.Substring(1, 3);
   else return SqlString.Null;
}
};

假设 StringFunctions.RegExMatch 和 StringFunctions.ExtractAreaCode 方法已经被注册为带有 RETURNS NULL ON NULL INPUT 选项的数据库中的用户定义函数（这允许该函数在任何输入都为 NULL 时返回 NULL，这样在该函数内就没有特殊的 NULL 处理代码）：

现在，可以在使用上述代码的表的列中定义约束，以验证电子邮件地址和电话号码，如下所示：

create table Contacts 
(
   FirstName nvarchar(30),
   LastName  nvarchar(30),
   EmailAddress nvarchar(30) CHECK 
 (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)',  
                  EmailAddress) = 1),
   USPhoneNo nvarchar(30) CHECK 
 (dbo.RegExMatch('\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]', 
    UsPhoneNo)=1),
   AreaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED 
)

另外，请注意 AreaCode 列是使用 dbo.ExtractAreaCode 函数从 USPhoneNo 列中取出地区代码而得到的列。然后，可以对 AreaCode 列建立索引，这样便于在表格中根据特定地区代码查找联系人的查询。

更一般地讲，此示例演示了如何利用 .NET Framework 库来增强带有有用函数的 T-SQL 内置函数库，这些有用函数很难用 T-SQL 表达。

产生结果集

需要从运行在服务器内的数据库对象（如存储过程或视图）中产生结果集可能是最常见的数据库编程任务之一。如果可以使用单个查询（SELECT 语句）来构建结果集，则这只需使用视图或在线表值函数即可实现。然而，如果需要多个语句（过程逻辑）来构建结果集，则有两个选择：存储过程和表值函数。虽然 SQL Server 2005 有表值函数，但是它们只能用 T-SQL 进行编写。在 SQL Server 2005 中，通过 CLR 集成，还可以使用托管语言来编写这样的函数。在这一节中，我们将讨论如何决定使用存储过程还是使用表值函数，以及使用 T-SQL 还是使用 CLR。

从 T -SQL 过程可以将相关的结果作为表值函数的返回值返回，或者通过存储过程内曾经隐式存在的“调用者管道”返回。从存储过程的任何位置（不管执行的嵌套程度如何）执行 SELECT 语句都会把结果返回给调用者。更严格地讲，实际上 SELECT 语句并没有进行变量赋值。而且，FETCH、READTEXT、PRINT 和 RAISERROR 语句也隐式地将结果返回给调用者。

请注意，“调用者”一直没有正确地定义，它实际上取决于存储过程的调用上下文。

如果从任何客户端数据访问 API（如 ODBC、OLEDB 和 SQLClient）中调用存储过程，则调用者是实际的 API，并且它提供的任何一种抽象都可以表示结果（如 hstmt、IRowset 或 SqlDataReaderand）。这意味着，通常，从存储过程中产生的结果将始终返回到调用 API 中，而跳过堆栈中所有的 T-SQL 框架，如以下示例中所示：

create proc proc1 as
   select col1 from dbo.table1;

create proc proc2 as 
   exec proc1;

在执行过程 proc2 时，proc1 产生的结果将转到 proc2 的调用者。proc2 中只有一种方法可以捕获产生的结果，即通过使用 INSERT/EXEC 将其存储到永久表、临时表或表变量中，从而将结果流式处理到磁盘。

create proc proc2 as
   declare @t table(col1 int);
   insert @t (col1) exec proc1;
   -- do something with results

在使用 INSERT/EXEC的情况下，“调用者”是 INSERT 语句的目标表/视图。

SQL Server 2005 CLR 存储过程引入了新的“调用者”类型。当通过托管框架中的 in-proc 提供程序执行查询时，就可以通过 SqlDataReader 对象使结果可用，并且可以在存储过程中使用结果。

...
SqlCommand cmd=SqlContext.GetCommand();
cmd.CommandText= "select col1 from dbo.table1";
SqlDataReader sdr=cmd.ExecuteReader();

while (sdr.Read())
{
   // do something with current row
}
...