处理常见数据库编程任务和问题
前一节在高层次上对基于 CLR 的编程与 TSQL中间层和扩展存储过程 (XP) 进行了比较在这一节中我们将考虑数据库应用程序开发人员经常遇到的一些编程任务和模型并且讨论如何使用 CLR（以及在一些情况下如何不使用）进行处理
使用 Framework 库进行数据验证
SQL Server 中的 CLR 集成允许用户利用 NET Framework 类库提供的丰富功能来解决其数据库编程问题
常规表达式的使用可以很好地说明 CLR 集成如何增强了验证和过滤功能在处理数据库中存储的文本数据方面常规表达式提供的模式匹配功能比通过 TSQL 查询语言中的 LIKE 运算符可用的模式匹配功能多考虑以下 C# 代码它只是 SystemTextRegularExpressions 命名空间中的 RegEx 类的一个简单包装
using System;
using SystemDataSql;
using SystemDataSqlTypes;
using SystemTextRegularExpressions;
public partial class StringFunctions
{
[SqlFunction(IsDeterministic = true IsPrecise = true)]
public static bool RegExMatch(string pattern string matchString)
{
Regex r = new Regex(patternTrimEnd(null));
return rMatch(matchStringTrimEnd(null))Success;
}
[SqlFunction(IsDeterministic = true IsPrecise = true)]
public static SqlString ExtractAreaCode(string matchString)
{
Regex r = new Regex(\\((?<ac>[][][])\\));
Match m = rMatch(matchString);
if (mSuccess)
return mValueSubstring( );
else return SqlStringNull;
}
};
假设 StringFunctionsRegExMatch 和 StringFunctionsExtractAreaCode 方法已经被注册为带有 RETURNS NULL ON NULL INPUT 选项的数据库中的用户定义函数（这允许该函数在任何输入都为 NULL 时返回 NULL这样在该函数内就没有特殊的 NULL 处理代码）
现在可以在使用上述代码的表的列中定义约束以验证电子邮件地址和电话号码如下所示
create table Contacts
(
FirstName nvarchar()
LastNamenvarchar()
EmailAddress nvarchar() CHECK
(dboRegExMatch([azAZ_\]+@([azAZ_\]+\)+(com|org|edu)
EmailAddress) = )
USPhoneNo nvarchar() CHECK
(dboRegExMatch(\([][][]\) [][][]\[][][][]
UsPhoneNo)=)
AreaCode AS dboExtractAreaCode(UsPhoneNo) PERSISTED
)
另外请注意 AreaCode 列是使用 dboExtractAreaCode 函数从 USPhoneNo 列中取出地区代码而得到的列然后可以对 AreaCode 列建立索引这样便于在表格中根据特定地区代码查找联系人的查询
更一般地讲此示例演示了如何利用 NET Framework 库来增强带有有用函数的 TSQL 内置函数库这些有用函数很难用 TSQL 表达
产生结果集
需要从运行在服务器内的数据库对象（如存储过程或视图）中产生结果集可能是最常见的数据库编程任务之一如果可以使用单个查询（SELECT 语句）来构建结果集则这只需使用视图或在线表值函数即可实现然而如果需要多个语句（过程逻辑）来构建结果集则有两个选择存储过程和表值函数虽然 SQL Server 有表值函数但是它们只能用 TSQL 进行编写在 SQL Server 中通过 CLR 集成还可以使用托管语言来编写这样的函数在这一节中我们将讨论如何决定使用存储过程还是使用表值函数以及使用 TSQL 还是使用 CLR
从 T SQL 过程可以将相关的结果作为表值函数的返回值返回或者通过存储过程内曾经隐式存在的调用者管道返回从存储过程的任何位置（不管执行的嵌套程度如何）执行 SELECT 语句都会把结果返回给调用者更严格地讲实际上 SELECT 语句并没有进行变量赋值而且FETCHREADTEXTPRINT 和 RAISERROR 语句也隐式地将结果返回给调用者
请注意调用者一直没有正确地定义它实际上取决于存储过程的调用上下文
如果从任何客户端数据访问 API（如 ODBCOLEDB 和 SQLClient）中调用存储过程则调用者是实际的 API并且它提供的任何一种抽象都可以表示结果（如 hstmtIRowset 或 SqlDataReaderand）这意味着通常从存储过程中产生的结果将始终返回到调用 API 中而跳过堆栈中所有的 TSQL 框架如以下示例中所示
create proc proc as
select col from dbotable;
create proc proc as
exec proc;
在执行过程 proc 时proc 产生的结果将转到 proc 的调用者proc 中只有一种方法可以捕获产生的结果即通过使用 INSERT/EXEC 将其存储到永久表临时表或表变量中从而将结果流式处理到磁盘
create proc proc as
declare @t table(col int);
insert @t (col) exec proc;
do something with results
在使用 INSERT/EXEC的情况下调用者是 INSERT 语句的目标表/视图
SQL Server CLR 存储过程引入了新的调用者类型当通过托管框架中的 inproc 提供程序执行查询时就可以通过 SqlDataReader 对象使结果可用并且可以在存储过程中使用结果
SqlCommand cmd=SqlContextGetCommand();
cmdCommandText= select col from dbotable;
SqlDataReader sdr=cmdExecuteReader();
while (sdrRead())
{
// do something with current row
}
下面的问题是托管存储过程如何将结果返回给它的调用者而不是通过 SqlDataReader 来使用它这可以通过称为 SqlPipe 的新类来实现通过 SqlContext 类的静态方法可以使此类的实例对托管存储过程可用SqlPipe 有几种方法可以将结果返回给存储过程的调用者这两个类都是在 Sqlaccessdll 中定义的
SqlPipe
在 SqlPipe 类中可以使用的方法中最容易理解的就是 Execute 方法它将命令对象作为参数接受这个方法主要执行命令并且没有使执行的结果可用于托管框架而是将结果发送给存储过程的调用者发送结果的这种形式在语义上与将语句嵌入 TSQL 存储过程内是一样的在本文前面描述的性能方面SqlPipeExecute 与 TSQL 是等价的
create proc proc as
select col from dbotable;
The equivalent in C# would be:
public static void proc()
{
SystemDataSqlServerSqlCommand cmd=SqlContextGetCommand();
cmdCommandText= select col from dbotable;
SqlContextGetPipe()Execute(cmd);
}
对于返回的数据是由执行的查询直接产生的情况SqlPipeExecute 可以很好地工作然而在某些情况下可能希望）从数据库中获得结果进行操作或者转换然后发送它们或者 ）将结果发送回原地而不是本地 SQL Server 实例
SqlPipe 提供了一组可以协同工作以使应用程序可以将任何结果返回给调用者的方法SendResultsStartSendResultsRow 和 SendResultsEnd在很大程度上这些 API 类似于对扩展存储过程的开发人员可用的 srv_describe 和 srv_sendrow API
SendResultsStart 将 SqlDataRecord 作为参数接受并且指示返回的新结果集的开头该 API 从记录对象读取元数据信息并且将其发送给调用者该方法有重载以允许发送元数据以及记录中的实际值
随后可以返回行方法是对要发送的每行调用一次 SendResultsRowows在发送完全部所需的行之后需要调用 SendResultsEnd 来指示结果集的结尾
例如下面的 C# 代码片段表示一个存储过程它读取 XML 文档（来自 MSDN 的 Really Simple Syndication [RSS] 供给）使用 SystemXml 类进行解析并且以相关的形式返回信息请注意这些代码应该创建为 EXTERNAL_ACCESS（或 UNSAFE）程序集因为访问 Internet 所需的代码访问安全 (CAS) 权限只有在这些权限集中才是可用的
// Retrieve the RSS feed
XPathDocument doc = new XPathDocument();
XPathNavigator nav = docCreateNavigator();
XPathNodeIterator i = navSelect(//item);
// create metadata for four columns
// three of them are string types and one of the is a datetime
SqlMetaData[] rss_results = new SqlMetaData[];
rss_results[] = new SqlMetaData(Title SqlDbTypeNVarChar );
rss_results[] = new SqlMetaData(Publication Date SqlDbTypeDateTime);
rss_results[] = new SqlMetaData(Description SqlDbTypeNVarChar );
rss_results[] = new SqlMetaData(Link SqlDbTypeNVarChar );
// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(rss_results);
// cache a SqlPipe instance to avoid repeat
