使用 SQL Server 2005中的 CLR 集成（2）

aaa112007

　　处理常见数据库编程任务和问题 　　 　　前一节在高层次上对基于 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 　　} 　　... 　　 　　下面的问题是托管存储过程如何将结果返回给它的调用者而不是通过 SqlDataReader 来使用它。这可以通过称为 SqlPipe 的新类来实现。通过 SqlContext 类的静态方法可以使此类的实例对托管存储过程可用。SqlPipe 有几种方法可以将结果返回给存储过程的调用者。这两个类都是在 Sqlaccess.dll 中定义的。 　　 　　SqlPipe 　　 　　在 SqlPipe 类中可以使用的方法中，最容易理解的就是 Execute 方法，它将命令对象作为参数接受。这个方法主要执行命令，并且没有使执行的结果可用于托管框架，而是将结果发送给存储过程的调用者。发送结果的这种形式在语义上与将语句嵌入 T-SQL 存储过程内是一样的。在本文前面描述的性能方面，SqlPipe.Execute 与 T-SQL 是等价的。 　　 　　create proc proc1 as 　　select col1 from dbo.table1; 　　The equivalent in C# would be: 　　public static void proc1() 　　{ 　　System.Data.SqlServer.SqlCommand cmd=SqlContext.GetCommand(); 　　cmd.CommandText= "select col1 from dbo.table1"; 　　SqlContext.GetPipe().Execute(cmd); 　　} 　　 　　对于返回的数据是由执行的查询直接产生的情况，SqlPipe.Execute 可以很好地工作。然而，在某些情况下可能希望1）从数据库中获得结果，进行操作或者转换，然后发送它们，或者 2）将结果发送回原地而不是本地 SQL Server 实例。 　　 　　SqlPipe 提供了一组可以协同工作以使应用程序可以将任何结果返回给调用者的方法：SendResultsStart、SendResultsRow 和 SendResultsEnd。在很大程度上，这些 API 类似于对扩展存储过程的开发人员可用的 srv_describe 和 srv_sendrow API。 　　 　　SendResultsStart 将 SqlDataRecord 作为参数接受，并且指示返回的新结果集的开头。该 API 从记录对象读取元数据信息，并且将其发送给调用者。该方法有重载，以允许发送元数据以及记录中的实际值。 　　 　　随后可以返回行，方法是对要发送的每行调用一次 SendResultsRowows。在发送完全部所需的行之后，需要调用 SendResultsEnd 来指示结果集的结尾。 　　 　　例如，下面的 C# 代码片段表示一个存储过程，它读取 XML 文档（来自 MSDN 的 Really Simple Syndication [RSS] 供给），使用 System.Xml 类进行解析，并且以相关的形式返回信息。请注意，这些代码应该创建为 EXTERNAL_ACCESS（或 UNSAFE）程序集，因为访问 Internet 所需的代码访问安全 (CAS) 权限只有在这些权限集中才是可用的。 　　 　　// Retrieve the RSS feed 　　XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml"); 　　XPathNavigator nav = doc.CreateNavigator(); 　　XPathNodeIterator i = nav.Select("//item"); 　　 　　// create metadata for four columns 　　// three of them are string types and one of the is a datetime 　　SqlMetaData[] rss_results = new SqlMetaData[4]; 　　rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250); 　　rss_results[1] = new SqlMetaData("Publication Date", SqlDbType.DateTime); 　　rss_results[2] = new SqlMetaData("Description", SqlDbType.NVarChar, 2000); 　　rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000); 　　 　　// construct the record which holds metadata and data buffers 　　SqlDataRecord record = new SqlDataRecord(rss_results); 　　 　　// cache a SqlPipe instance to avoid repeat <