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

　　處理常見數據庫編程任務和問題
　　
　　前一節在高層次上對基於 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()
　　LastName　nvarchar()
　　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
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22049.html