本文描述了數據庫應用程序開發人員和架構師如何利用 SQL Server 2005 中的 CLR 集成功能。本文對基于 CLR 的編程方式與 SQL Server 中支持的現有編程模型（如 TransacT-SQL 和擴展存儲過程）進行了比較，并且強調了各自相對的優缺點。還提供了一組選擇合適的編程替代方法的高級指導，以及一些示例和代碼示例。

　　一、簡介

　　Microsoft 通過宿主 Microsoft .NET Framework 2.0 公共語言運行庫 (CLR)，SQL Server 2005顯著地增強了數據庫編程模型的功能。它支持用任何 CLR 語言（特別是 Microsoft Visual C# .NET、Microsoft Visual Basic .NET 和 Microsoft Visual C++）編寫過程、觸發器和函數。同時，它還提供了擴展數據庫系統中的類型系統和聚合函數的功能，方法是允許應用程序創建它們自己的數據類型和聚合函數。

　　本白皮書從數據庫應用程序開發人員的角度描述了如何最有效地利用這項技術。它還將 CLR 與 SQL Server 中支持的現有編程語言（即 TransacT-SQL (T-SQL) 和擴展存儲過程 (XP)）進行了比較。本白皮書不打算提供這些功能的基本參考資料，SQL Server 2005 Beta 2 Books Online 對其進行了詳細介紹。

　　本白皮書的目標讀者包括數據庫應用程序開發人員、架構師和數據庫管理員。本白皮書假設讀者熟悉基于 .NET Framework 的編程和數據庫編程。

　　二、CLR 集成概述

　　下面簡要概述了 CLR 集成執行的 SQL Server 功能，以及 Visual Studio 2005 最終發布版本如何支持這些功能。

　　注冊和執行數據庫中的托管代碼包括以下步驟：

•	開發人員將托管程序編寫為一組類定義。將 SQL Server 內旨在用作存儲過程、函數或觸發器（下面統稱為例程）的代碼編寫為類的 static（或 Microsoft Visual Basic .NET 中的 shared）方法。將旨在用作用戶定義的類型和聚合的代碼編寫為一個整類。開發人員編譯該程序并創建一個程序集。
•	然后，將此程序集上載到 SQL Server 數據庫，在其中使用 CREATE ASSEMBLY 數據定義語言 (DDL) 將它存儲到系統目錄。
•	接著，創建 TransacT-SQL 對象，例如，例程（函數、過程和觸發器）、類型和聚合，并將其綁定到已經上載的程序集中的入口點（對例程來說是方法，對類型和聚合來說是類）。使用 CREATE PROCEDURE/FUNCTION/ TRIGGER/TYPE/AGGREGATE 語句來完成這一步。
•	在創建了這些例程之后，應用程序就可以像使用 T-SQL 例程一樣使用它們。例如，可以從 T-SQL 查詢中調用 CLR 函數，從客戶端應用程序或從 T-SQL 批處理中調用 CLR 過程，就好像它們是 T-SQL 過程一樣。

　　Visual Studio 2005 Beta 1 支持在 SQL Server 2005 中開發、部署和調試托管代碼。有一種新的項目類型（稱為 SQL Server 項目），它允許開發人員在 SQL Server 中開發、部署和調試例程（函數、過程和觸發器）、類型和聚合。

　　構建和部署

　　SQL Server 項目提供了代碼模板，這使得開發人員能夠輕松地開始為基于 CLR 的數據庫例程、類型和聚合編寫代碼。該項目還允許添加對數據庫中其他的程序集的引用。在構建項目時，可以將其編譯成一個程序集。部署此程序集可以將程序集的二進制文件上載到與該項目相關聯的 SQL Server 數據庫中。部署操作還自動創建在數據庫的程序集中定義的例程、類型和聚合，方法是使用在代碼中定義的自定義屬性（SqlProcedure、SqlFunction 和 SqlTrigger 等等）。它還上載與該程序集相關聯的源代碼和 .pdb 文件（調試符號）。

　　調試

　　對于任何平臺來說，調試都是開發人員體驗的基本部分。SQL Server 2005 和 Visual Studio 2005 為數據庫編程人員提供了這些功能。調試 SQL Server 2005 對象的關鍵部分在于其易于安裝和使用。調試到運行 SQL Server 的計算機的連接在很大程度上同調試傳統操作系統中運行的進程的方式一樣。調試器的功能不會受到客戶端到服務器的連接類型的影響。這樣就可以調試表格數據流 (TDS) 和 HTTP 連接。而且，還可以跨語言進行無縫調試。因此，如果有一個調用 CLR 存儲過程的 T-SQL 存儲過程，調試會允許您從 T-SQL 過程進入到 CLR 過程。

　　三、CLR 與 TransacT-SQL

　　現在我們進入本文的關鍵部分：對 CLR 集成和 SQL Server 中支持的現有編程語言進行比較。

　　TransacT-SQL (T-SQL) 是 SQL Server 支持的本機編程語言。和大多數 SQL Server 版本一樣，它包含數據處理功能和數據定義功能。數據處理功能一般可以分為兩類：查詢語言（由 SELECT/INSERT/UPDATE/ DELETE 語句組成）和過程語言（WHILE、賦值、觸發器、光標等）。一般來說，SQL Server 中的 CLR 支持為過程語言提供了 T-SQL 的替代方法。

　　即使在 SQL Server 中引入 CLR 支持以前，數據庫應用程序應該盡可能多地使用查詢語言，這始終被認為是很重要的。數據庫應用程序應該利用面向集的查詢處理器，并且只在查詢語言無法表示邏輯時才轉向過程編程。對于 SQL Server 中的 CLR 支持，這仍然是正確的。不應該使用 CLR 來編寫可以用簡單的 SELECT 語句表示的過程代碼。在 SQL Server 2005 中增加了許多重要的功能來提高T-SQL 查詢語言的表達能力。

•	遞歸查詢：遍歷表中的遞歸層次的能力
•	分析函數：RANK 和 ROW_NUMBER 允許排列結果集中的行
•	新的關聯操作：APPLY、PIVOT 和 UNPIVOT

　　試圖使用 CLR 功能的開發人員應該確保他們充分地利用查詢語言，包括 SQL Server 2005 中的擴展在內。對于在查詢語言中無法以聲明方式表示的邏輯，他們應該考慮將 CLR 作為有效的替代辦法。

　　現在讓我們看一些方案，其中基于 CLR 的編程能夠補充 T-SQL 查詢語言的表達能力。通常，需要在查詢（可稱為函數）內嵌入過程邏輯。這允許許多方案，例如：

•	根據數據庫表中存儲的值，對每行進行復雜的計算（必須用過程邏輯來表示）。這可以包括將這些計算的結果發送給客戶端，或者使用計算來過濾發送給客戶端的行集，如以下示例中所示： SELECT <complex-calculation>(<column-name>,...) FROM <table> WHERE <complex-calculation>(<column-name>,...) = ...
•	• 使用過程邏輯來評估表格結果，然后在 SELECT 或 DML語句的 FROM 子句中進行查詢。

　　SQL Server 2000 引入了支持這些方案的 T-SQL 函數（標量和表值）。有了 SQL Server 2005，就可以用 CLR 語言更容易地編寫這樣的函數，并且會極大地提高它們的性能。之所以編寫這些函數非常容易，是因為事實上編寫 CLR 代碼的開發人員可以利用 .NET Framework API中存在的大量有用函數和類。這個類/函數庫比 TransacT-SQL 中支持的內置函數要豐富得多。此外，CLR 編程語言提供了 T-SQL 中所沒有的豐富構造（例如數組和列表等）。與 T-SQL（它是一種解釋語言）相比，CLR 編程語言之所以具有更好的性能，是因為托管代碼是已編譯的。對于涉及算術計算、字符串處理、條件邏輯等的操作，托管代碼的性能可能要優于 T-SQL 一個數量級。

　　注：對于函數，幾乎沒有必要從函數中訪問數據庫。外部查詢已經從數據庫中檢索到數據值，并且將其作為參數傳遞給函數。這是 CLR 的優勢，在計算性任務上比 T-SQL 更勝一籌。

　　從 CLR 中訪問數據

　　現在讓我們從編程模型和性能這兩個方面，看看如何用 CLR 來編寫訪問數據庫的業務邏輯。

　　編程模型

　　使用 T-SQL，只是在過程代碼內部嵌入查詢語言語句 (SELECT/INSERT/UPDATE/ DELETE)。通過托管代碼，可以使用 SQL Server 托管提供程序來實現 Microsoft ADO.NET 數據訪問 API（也稱為 in-proc ADO.NET）。使用這種方法，可以將查詢語言語句（SELECT 和 DML 語句）作為動態字符串嵌入，并且將其作為參數傳遞給 ADO.NET API。與 T-SQL 的靜態方法相比，過程代碼內嵌入的 SQL 語句的基于動態 API 的特性是它們在編程模型上的主要區別。不利的是，in-proc ADO.NET 模型會產生比 T-SQL 更冗長的代碼。此外，因為 SQL 語句是動態字符串，所以在執行之前不在語法或語義上進行編譯和驗證。有利的是，帶有 ADO.NET 的數據庫編程模型與客戶端或中間層中使用的模型相似，因而更容易在各層之間移動代碼和利用現有的技術。

　　此外，在基于 T-SQL 和基于 CLR 的編程模型中使用的都是同一 SQL 查詢語言，不同之處在于過程部分，注意到這一點是非常重要的。

　　性能

　　正如已經提到的，在談及過程代碼、計算等方面時，與 T-SQL 相比，托管代碼在性能方面具有決定性的優勢。然而，對于數據訪問方面，T-SQL 在性能方面通常會更好。因此，通用規則是用 CLR 編寫計算和邏輯密集的代碼要比數據訪問密集的代碼好。不過這值得更詳細地考慮。

　　讓我們看看數據訪問編程中的一些典型的基元和模式，以及在這些情況下如何使用 ADO.NET 進行 T-SQL 和托管編程。

　　將結果發送到客戶端

　　這包括將一組行發送到客戶端，而沒有在服務器中“消費”它們（即沒有在例程內導航行）。使用 T-SQL，只需在 T-SQL proc 中嵌入一個 SELECT 語句就可以將 SELECT 產生的行發送到客戶端。通過托管代碼，可以使用 SqlPipe 對象將結果發送到客戶端。T-SQL 和 in-proc ADO.NET 平臺在這種情況下的作用是一樣的。

　　提交 SQL 語句

　　這包括來自過程代碼的 SQL 語句的執行往返。在這種情況下，T-SQL 具有很大的優勢（比 in-proc ADO.NET 快兩倍多）。

　　此處需要重點注意的是，之所以在 CLR 中出現性能降低，是因為增加了額外的代碼層，包括將來自托管代碼的 T-SQL 語句提交給原生 SQL 代碼。在將該語句提交給查詢處理器之后，基于語句源（T-SQL 或 in-proc ADO.NET）的性能方面就沒有什么不同了。

　　注：典型的數據訪問密集的存儲過程可能涉及提交一系列的 SQL 語句。如果 SQL 語句簡單，并且不需要花費大量的時間執行，則來自托管代碼的調用開銷可能占用大部分執行時間，這樣的過程用 T-SQL 編寫將執行得更好。

　　只進、只讀行導航

　　這包括以只進、只讀方式一次導航一個由 SQL 語句產生的行。在 T-SQL 中，這是通過只進、只讀光標實現的。在 CLR 中，這是通過 SqlDataReader 實現的。通常，每一條語句都涉及一些處理。如果忽略了與每行相關聯的處理，則導航行在 CLR 中就比在 T-SQL 光標中稍慢。然而，如果您關心為每行執行的處理，則 CLR 會更有優勢，因為 CLR 在這種處理上比 T-SQL 做得好。

　　帶有更新的行導航

　　如果需要根據光標中的當前位置更新行，則沒有相關的性能比較，因為 in-proc ADO.NET 不支持此功能，而應該通過 T-SQL 可更新光標來進行此操作。

　　注 在任何可能的情況下，最好使用 UPDATE 語句來批量更新行，只有在這樣的修改無法用單一的 UPDATE 語句進行表示時，才應使用基于光標導航的 UPDATE。

　　以下示例說明在特定情況下如何確定 T-SQL 和 CLR 將執行：

1.	考慮這樣一個過程，它執行一系列（或在最簡單的情況下僅一個）INSERT/UPDATE/DELETE/SELECT 語句，帶有幾個或者不帶返回到客戶端的行，并且不導航 SELECT 產生的行。如果將這樣的過程編寫成 T-SQL 過程可能執行得更好。
2.	考慮這樣一個過程，它執行單一的 SELECT 語句，并且使用存儲過程內的行，方法是，一次導航一行并進行一些不涉及對每行進行更多的數據訪問的處理。這個過程可能在帶有 in-proc ADO.NET 的 CLR 中執行得更好，特別是如果每行都有一些大量處理的開銷時（因為這樣的操作在 CLR 中比在 T-SQL 中更加高效）。

　　下面是一些簡單的指導原則，可以用來在 CLR 和 T-SQL 之間進行選擇：

•	盡可能使用帶有 T-SQL SELECT、INSERT、UPDATE 和 DELETE 語句的基于集的處理。只有在無法使用基于集的 DML 語句之一表示邏輯時，才應該使用過程和基于行的處理。
•	如果過程僅僅是一個通過封裝基本 INSERT/UPDATE/DELETE/SELECT 操作訪問基表的包裝，則應該用 T-SQL 進行編寫。
•	如果過程主要包括結果集中的只進、只讀行導航，以及一些涉及每行的處理，則用 CLR 編寫可能更有效。
•	如果過程包括大量的數據訪問以及計算和邏輯，則可以考慮將過程代碼分隔為 CLR 來調用 T-SQL 過程，以進行大部分的數據訪問（反之亦然）。另一個替代方法是，使用單一的 T-SQL 批處理，它包括從托管代碼執行一次的一組查詢，以減少從托管代碼提交 T-SQL 語句的往返次數。

　　后面的部分將更深入地討論在處理結果集時何時及如何適當地使用 T-SQL 和 CLR。

　　CLR 與 XP

　　在 SQL Server 以前的版本中，擴展存儲過程 (XP) 為數據庫程序開發人員提供了唯一可用的機制來編寫服務器端邏輯，這要么難于表示，要么不可能用 T-SQL 編寫。CLR 集成提供了一種更健壯的替代方法來編寫這種存儲過程。此外，使用 CLR 集成，過去以存儲過程形式編寫的邏輯通常可以更好地表示為表值函數，因為它們允許它們允許將該函數構造的結果放在 SELECT 語句中進行查詢（通過將這些結果嵌入到 FROM 子句中）。

　　以下是使用 CLR 過程或函數與 XP 相比的優勢：

•	粒度控制：很少可以控制 XP 能做什么或者不能做什么。使用代碼訪問安全模型，SQL Server 管理員可以分配三種權限之一：SAFE、EXTERNAL_ACCESS 或 UNSAFE，從而對托管代碼允許進行的操作集進行不同程序的控制。
•	可靠性：托管代碼（特別是在 SAFE 和 EXTERNAL_ACCESS 權限集中）提供了比 XP 更安全、更可靠的編程模型。可驗證的托管代碼確保了所有對對象的訪問都是通過強類型化的接口實現的，從而降低了程序訪問或破壞屬于 SQL Server 的內存緩沖的可能性。
•	數據訪問：使用 XP£¬編程人員必須向后顯式連接到數據庫（稱為回環），以訪問本地 SQL Server 數據庫。而且，必須將此回環連接顯式綁定到原來的會話事務上下文，以確保 XP 可以參與到調用它的同一個事務中。通過托管代碼，可以使用更自然和更有效的編程模型來訪問本地數據，這些模型利用當前的連接和事務上下文。
•	性能：System.Data.SqlServer API 允許托管過程將結果集發送回客戶端，其性能比 XP 使用的開放式數據服務 (ODS) API 更好。此外，System.Data.SqlServer API 支持新的數據類型（如 SQL Server 2005 中引入的 XML、(n)varchar(max)、varbinary(max)），而沒有擴展 ODS API 來支持這些新的數據類型。
•	可伸縮性：通過托管代碼，SQL Server 可以管理資源（如內存、線程和同步）的使用，因為公開這些資源的托管 API 是在 SQL Server 資源管理器上實現的。相反，SQL Server 不能查看或控制 XP 的資源使用情況。舉例來說，如果 XP 消耗了太多的 CPU 或內存資源，就沒有辦法使用 SQL Server 來檢測或控制。然而，通過托管代碼，SQL Server 可以檢測到特定線程在一段很長的時間內一直沒有退出，然后就強制該任務退出，這樣其他工作可以按計劃進行。因此，使用托管代碼提供了更好的可伸縮性和健壯性。

　　正如上面所提到的，在數據訪問和發送結果集給客戶端方面，CLR 過程比 XP 做得更好。對于不包括數據訪問和發送結果的代碼，比較 XP 和托管代碼的性能就是比較托管代碼和原生代碼的性能。一般來說，在這些情況下托管代碼比不上原生代碼的性能。而且，當在 SQL Server 內運行時，從托管代碼到原生代碼的事務處理過程有額外的開銷，因為在移動到原生代碼和從原生代碼移回時，SQL Server 需要對特定于線程的設置進行額外的登記-保留。因此，對于在托管代碼和原生代碼之間有頻繁的事務處理的情況，XP 大大地勝過在 SQL Server 內部運行的托管代碼。

　　對于大多數擴展過程，如果考慮數據訪問和結果發送的可伸縮性、可靠性和性能優勢，CLR 過程提供了更好的替代方法。對于性能主要是由處理（數據訪問和結果發送之外的）和頻繁的托管-原生轉換決定的情況，應該權衡 CLR 的可伸縮性和可靠性的優勢與 XP 的原始性能優勢。

　　四、代碼位置：數據庫與中間層

　　通過在數據庫中提供豐富的編程模型，CLR 集成提供了將邏輯從其他層移動到數據庫層的選擇。然而，這顯然并不意味著所有或大部分邏輯應該移到數據庫中。

　　將邏輯移到數據庫層可以減少網絡中的數據流量，但是增加了服務器上寶貴的 CPU 資源的負荷。因此，在應用程序中做出代碼放置的決定之前，要慎重權衡。以下注意事項適用于將數據庫層作為首選的代碼位置：

•	數據驗證：在數據層進行數據驗證的邏輯可以更好地將數據和邏輯封裝在一起。這樣避免了在不同數據接觸點（如：后端處理、批量上載和來自中間層的數據更新等）中重復驗證邏輯。
•	減少網絡流量：對于需要處理大量的數據而產生很少的數據的數據處理任務（如數據分析應用程序中的需求預測、基于需求預測的生產安排等）來說，將邏輯放在數據庫層中是合適的。

　　注即使在引入 CLR 支持之前，上面的注意事項也是有效的。數據庫層中的 CLR 支持意味著編程語言的選擇沒有妨礙代碼位置的正確選擇。

　　示例：生產安排

　　生產安排是制造企業的常見任務。在高層次上，它包括制訂何時生產多少單位數量的產品的計劃，以便能夠滿足需求、最大程度的降低庫存成本，同時將生產成本降到最低。有幾個算法將需求預測、庫存成本和生產線安裝成本作為輸入，而將制造策略作為輸出。

　　假定將來的需求預測存儲在 SQL Server 表中，則此類算法的實現有以下特征：

1.	使用大量的數據作為輸入（如需求預測）。
2.	產生小結果（如在特定的日期內生產的單位數量）。
3.	需要相當多的計算以便從輸入中派生輸出。

　　在中間層實現這樣的算法是可行的，但是在數據庫外移動輸入集方面有性能損失。在 T-SQL 中將其實現為存儲過程也是可行的，但是因為需要復雜的計算，性能損失就顯現出來了。性能特征將隨著實際的數據量和算法的復雜性的不同而不同。

　　為了驗證 CLR 集成是否適合于這樣的情況，我們舉一個特定的生產安排算法的示例 － Wagner-Whitin 算法的動態編程實現。正如所預料的，CLR 集成優于 T-SQL。對于這種情況，使用托管代碼還有其他好處。這種算法的實現需要使用大量的一維和多維數組、數據結構，而這些在 T-SQL 中是不可用的。總之，CLR 集成的性能要優于 T-SQL 實現幾個數量級。

　　假定以下簡單的數據庫架構跟蹤可以生產的產品列表。

表 1： t_products
列名	類型	是否為空	說明
PID	int	非空	產品主鍵 ID
Pname	nvarchar(256)	空	產品名稱
InventoryCost	int	非空	存儲該產品的每時段成本
StartupCost	int	非空	建立生產線來生產該產品的成本

　　下表存儲了每周每個產品的需求預測信息。

表 2： t_SalesForecast
列名	類型	是否為空	說明
PID	Int	非空	產品 ID
WeekDate	smalldatetime	非空	需求預測周
DemandQty	int	非空	特定產品和特定周的需求預測

　　給定一組產品，它們的庫存和啟動成本以及未來需求預測，我們創建了接受如下輸入參數的存儲過程：1)制訂生產進度表的日期，2)按進度表生產所需要的周數。

　　存儲過程返回帶有下表中的架構的行集。

表 3：存儲過程架構
列名	類型	說明
Product	nvarchar(256)	產品名稱
Period	datetime	進度周
Quantity	int	在指定周內制造的產品的數量

　　
將 C# 版本的代碼復制到下面的代碼中，以說明這種可以從 CLR 集成中大大獲益的情況：

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public class ProductionSchedule
{
//4-year limit on scheduling
　 public const int MAXPRODUCTS = 101;
　 public const int MAXWEEKS = 210;
　 public const int MAXNAME = 256;
　 public ProductionSchedule()
　 {
　　　　
　 }
　 public static int Schedule(SqlDateTime startDate, int numWeeks)
　 {
　　　SqlDateTime[] week = new SqlDateTime[MAXWEEKS];
　　　int[] quantity;
　　　int[][] Cij;
　　　int[] Fk;
　　　int[] minK = new int[MAXWEEKS];
　　　int product_id, current_product, product_count = 0;
　　　int startPeriod;
　　　// We'll use arrays to keep state about products and forecasts
in memory. This is only viable given that we know we have a small number
of products and weeks.
　　　// For larger data sets, we would have to consider cursors or
temporary tables.
　　　// stored as CLR types since we know they can't be null
　　　int[] h = new int[MAXPRODUCTS];
int[] K = new int[MAXPRODUCTS];
// stored as nullable SqlChars since the table schema allows for null names
　　　SqlChars[] productNames = new SqlChars[MAXPRODUCTS];
　　　bool moreProducts = true;
　　　int optimal_j;
　　　int period;
　　　int sum;
　　　SqlPipe pipe = SqlContext.GetPipe();
　　　SqlDataRecord record;
　　　object[] values = new object[3];
　　　SqlMetaData[] metadata = new SqlMetaData[3];
　　　//Initialize algorithm arrays
　　　Cij = new int[MAXWEEKS][];
　　　for( int l=0;l<MAXWEEKS;l++)
　　　　 Cij[l] = new int[MAXWEEKS];
　　　Fk = new int[MAXWEEKS];
　　　//Look up K and h for all products
　　　SqlCommand cmd = SqlContext.GetCommand();
　　　cmd.CommandText = @"SELECT pname, InventoryCost, StartupCost from dbo.t_Products ORDER BY PID";
　　　SqlDataReader reader = cmd.ExecuteReader();
　　　while(reader.Read())
　　　{
　　　　 productNames[product_count] = reader.GetSqlChars(0); //product name
　　　　 h[product_count] = reader.GetInt32(1); //holding cost
　　　　 K[product_count] = reader.GetInt32(2); //startup cost
　　　　 product_count++;
　　　　
　　　　 // if we exceeded number of expected products then bail out with an exception
　　　　 if (product_count >= MAXPRODUCTS)
　　　　 {
　　　　　　throw new Exception("Too many products");
　　　　 }
　　　}
　　　reader.Close();
　　　product_count = 0;
　　　//Get the list of product ids;
　　　cmd = SqlContext.GetCommand();
　　　cmd.CommandText = @"select PID, weekdate, DemandQty from dbo.t_SalesForecast ORDER BY PID, WeekDate";
　　　reader = cmd.ExecuteReader();
　　　moreProducts=reader.Read();
　　　　
　　　//Set up the record for returning results
　　　metadata[0] = new SqlMetaData( "Product",
SqlDbType.NVarChar,MAXNAME );
　　　metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime );
　　　metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int );
　　　record = new SqlDataRecord( metadata );
　　　while( moreProducts )
　　　{
　　　　 product_id = current_product = reader.GetInt32(0);
　　　　 int index = 1;
　　　　 quantity = new int[MAXWEEKS];
　　　　 while( current_product == product_id )
　　　　 {
　　　　　　week[index] = reader.GetSqlDateTime(1);
　　　　　　quantity[index] = reader.GetInt32(2);
　　　　　　index++;
　　　　　　moreProducts = reader.Read();
　　　　　　if( !moreProducts )
　　　　　　　 break;
　　　　　　current_product = reader.GetInt32(0);
　　　　 }
　　　　 //Determine the ordinal start week
　　　　 startPeriod = 1;
　　　　 //For each product ID calculate Cij
　　　　 for( int i = startPeriod; i < (startPeriod + numWeeks); i++ )
　　　　 {
　　　　　　for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ )
　　　　　　{
　　　　　　　 Cij[i][j] = GetCij(quantity,i,j,K [product_count],h[product_count]);
　　　　　　}
　　　　 }
　　　　 //Calculate Fk
　　　　 for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--)
　　　　 {
　　　　　　minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk);
　　　　 }
　　　　 //Send the results
　　　　 record.SetSqlChars(0,productNames[product_count]);
　　　　 pipe.SendResultsStart(record,false);
　　　　 for( int k = startPeriod; k < startPeriod + numWeeks; )
　　　　 {
　　　　　　period = k;
　　　　　　optimal_j = minK[k];
　　　　　　sum = 0;
　　　　　　while( k < optimal_j )
　　　　　　{
　　　　　　　 sum = sum + quantity[k++];
　　　　　　}
　　　　　　values[1] = week[period];
　　　　　　record.SetValue(1,values[1]);
　　　　　　values[2] = sum;
　　　　　　record.SetValue(2,values[2]);
　　　　　　pipe.SendResultsRow(record);
　　　　 }
　　　　 pipe.SendResultsEnd();
　　　　 product_count++;
　　　}
　　　reader.Close();
　　　return 0;
　 }
　 private static int GetCij(int[] quantities, int i, int j, int K, int h)
　 {
　　　if( j == i+1 )
　　　　 return K;
　　　else
　　　　 return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h);
　 }
　 private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk)
　 {
　　　int j,min;
　　　j = k+1;
　　　min = j;
　　　if ( k == n+1 )
　　　{
　　　　 Fk[k] = 0;
　　　　 return j;
　　　}
　　　Fk[k] = Cij[k][j] + Fk[j];
　　　for(; k <= n ;k++)
　　　{
　　　　 j = k + 1;
　　　　 while( j <= n+1 )
　　　　 {
　　　　　　if( Cij[k][j] + Fk[j] < Fk[k] )
　　　　　　{
　　　　　　　 min = j;
　　　　　　　 Fk[k] = Cij[k][j] + Fk[j];
　　　　　　}
　　　　　　j++;
　　　　 }
　　　}
　　　return min;
　 }
}

　　
五、處理常見數據庫編程任務和問題

　　前一節在高層次上對基于 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("http://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 repeated calls to SqlContext.GetPipe()
SqlPipe sqlpipe = SqlContext.GetPipe();
// send the metadata, do not send the values in the data record
sqlpipe.SendResultsStart(record, false);
// for each xml node returned, extract four pieces
// of information and send back each item as a row
while (i.MoveNext())
{
　 record.SetString(0, (string)
　　　　　　　　　　　 i.Current.Evaluate("string(title[1]/text())"));
　 record.SetDateTime(1, DateTime.Parse((string)
　　　　　　　　　　　 i.Current.Evaluate("string(pubDate[1]/text())")));
　 record.SetString(2, (string)
　　　　　　　　　　　 i.Current.Evaluate("string(description[1]/text())"));
　 record.SetString(3, (string)
　　　　　　　　　　　 i.Current.Evaluate("string(link[1]/text())"));
　 sqlpipe.SendResultsRow(record);
}
// signal end of results
sqlpipe.SendResultsEnd();

　　注：在 SendResultsStart 和 SendResultsEnd 調用之間，SqlPipe 被設置為繁忙狀態，調用除 SendResultsRow 之外的任何 Send 方法都會導致錯誤發生。SqlPipe 處于繁忙狀態中時，SendingResults 屬性被設置為 TRUE。

　　表值函數

　　CLR 集成也啟用了對用托管語言編寫的表值函數 (TVF) 的支持。與 T-SQL 相似，TVF 主要用于返回表結果。最顯著的不同在于，T-SQL 表值函數臨時將結果存儲在工作表中，而 CLR TVF 則能夠對產生的結果數據進行流式處理。這意味著結果在從函數返回之前不需要物化。

　　注 T-SQL 還具有內聯 TVF 的概念，即不臨時存儲結果。內聯 TVF 在大部分語義上便于指定子查詢（可能帶有參數）。

　　托管 TVF 返回 ISqlReader 接口，這是由 SqlClient 和 SqlServer (in-proc) 托管提供程序中的 SqlDataReader 實現的一種只進光標抽象。查詢處理器調用此接口上的 Read() 方法，以在每行返回 FALSE 之前獲取它。

　　將上面的示例改為返回來自 RSS 供給的信息，返回結果的代碼如下所示（不包括未實現的方法）：

[SqlFunction]
public static ISqlReader tvf1()
{
　 return (ISqlReader)new RssReader();
}
public class RssReader : ISqlReader
{
　 SqlMetaData[] rss_results = null;
　 XPathDocument doc;
　 XPathNavigator nav;
　 XPathNodeIterator i;
　 // Construct helper class, initializing metadata for the results
　 // reading from the RSS feed, creating the iterator
　 public RssReader()
　 {
　　 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);
　　 // Retrieve the RSS feed
　　 doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
　　 nav = doc.CreateNavigator();
　　 i = nav.Select("http://item");
　 }
　 // # of columns returned by the function
　 public int FieldCount { get { return rss_results.Length; } }
　 // metadata for each of the columns
　 public SqlMetaData GetSqlMetaData(int FieldNo)
　　　{ return rss_results[FieldNo]; }
　 // Read method positions the navigator iterator on next element
　 public bool Read() { return i.MoveNext(); }
　 // methods to return each column
　 public Object GetValue(int FieldNo)
　 {
　　　switch (FieldNo)
　　　{
　　　　 case 0:
　　　　　　return new SqlString((string)　　　　　　　　　
　　　　　　　　　 i.Current.Evaluate("string(title[1]/text())"));
　　　　 case 1:
　　　　　　return new SqlDateTime(DateTime.Parse(
　　　　　　　　　
(string)i.Current.Evaluate("string(pubDate[1]/text())")));
　　　　 case 2:
　　　　　　return new SqlString((string)
　　　　　　　　　 i.Current.Evaluate("string(description[1]/text())"));
　　　　 case 3:
　　　　　　return new SqlString((string)
　　　　　　　　　 i.Current.Evaluate("string(link[1]/text())"));
　　　}
　　　return null;
　 }
　 public string GetString(int i) { return (string)GetValue(i); }
　 public DateTime GetDateTime(int i) { return (DateTime)GetValue(i); }
　 public SqlChars GetSqlCharsRef(int i) {
　　　return new SqlChars((SqlString)GetValue(i)); }
　 public SqlChars GetSqlChars(int i) {
　　　return new SqlChars((SqlString)GetValue(i)); }
　 public SqlDateTime GetSqlDateTime(int i) {
　　　return (SqlDateTime)GetValue(i); }
　 ...
}

　　使用來自此表值函數的結果的一種簡單查詢如下所示：

select title, pubdate, description, link from dbo.GetRssFeed()

　　顯然可以用此數據的 TVF 形式來表示更豐富的查詢。假定函數 CanonicalURL() 會返回規范的 URL 版本。現在，可以使用規范的 URL 很容易地返回來自 RSS 供給的數據：

select title, pubdate, description, dbo.CanonicalURL(link)
from dbo.tvf1()
order by pubdate

　　
請注意，在這個示例中，我們沒有利用 TVF 的流化功能，因為我們正在使用整個 RSS 供給，在此之上建立導航器，然后在調用 Read() 時循環訪問各個項目。然而，可以想象使用流式 API 來使用 Web 資源的結果，并且使用 XmlReader 來循環訪問產生的 XML。需要重點注意的是，給出了 CLR 表值函數和 T-SQL 函數之間的執行模型的不同時，最主要的性能差異可能有利于 CLR TVF，特別是在可能將結果數據流化的情況下。

　　使用哪一個？

　　決定將存儲過程與 SqlPipe （不管是 T-SQL 中使用隱式管道還是在托管中使用顯式托管類）結合使用，還是使用表值函數，取決于以下幾個必須考慮的因素：

•	可組合性要求
•	返回的數據的源
•	對副作用操作的需要
•	強類型化和結果集的數量

　　可組合性

　　有時可能需要重用或進一步處理 TVF 或存儲過程中產生的結果。從可組合性的角度來說，表值函數更靈活。TVF 的返回類型是相關的行集，可以用在允許此類構建的任何地方。特別是，它可以用在 SELECT 語句的 FROM 子句中，因為這些產生的結果可以受益于子查詢中的 SELECT、INSERT/SELECT、派生的表和通用表表達式等的可組合性。

　　另一方面，從 T-SQL 語言中，存儲過程可以組合成 INSERT / EXEC 組合的唯一部分，這使得可以將產生的結果存儲在永久或臨時表中。INSERT 操作表示數據的實際副本，它可能會影響性能。

　　如果需要組合和重用服務器中的結果，TVF 是更好的替代方法。如果產生的結果只需要回流到客戶端/中間層，任何一種方法都可以完成這項工作。

　　數據源

　　返回的數據源是在基于 T-SQL 和基于 CLR 的實現之間做出決定的另一個重要因素。可以通過使用 in-proc 提供程序讀取本地實例中的一些數據源產生結果，也可以從 SQL Server 之外的數據源產生結果。本文前面描述的基于 Web 請求的結果構造的代碼片段便是后者的示例。另一個遠程數據源的示例是，使用 SqlClient 托管提供程序從遠程SQL Server 實例中檢索結果。對于這樣的外部源，基于 CLR 的實現是更好的選擇，因為使用它可以很容易地實現訪問外部數據的邏輯。

　　現在讓我們考慮這種情況，使用 in-proc 提供程序基于在本地實例中執行的查詢生成結果。在使用 TVF 的情況下，默認的處理可能是返回由 in-proc 提供程序產生的 SqlDataReader，或者用 ISqlReader 的自定義實現包裝這樣的閱讀器，以便在讀取結果時對其進行轉換。在使用存儲過程的情況下，根據本地實例產生的結果必須執行查詢，循環訪問讀取行，對結果執行一些操作，然后通過管道將其發送回去。

　　然而，SQL Server 2005 不允許表值函數返回時請求還處于未決狀態。在函數體可以返回之前，必須全部執行任何通過 in-proc 提供程序執行的查詢并且完全使用結果。如果執行了返回語句，而 in-proc 提供程序中的 SqlDataReader 操作還處于未決狀態，就會引發錯誤。這意味著對于從本地數據庫實例返回數據的大多數情況，無法通過 CLR TVF 流化結果。如果因為其他因素（例如可組合性）需要將此編寫為 TVF，則使用 T-SQL 編寫是唯一的選擇。另外，通過 SqlPipe 使用托管存儲過程是一個可能的選擇。

　　請注意，對于基于來自本地實例的數據從存儲過程中產生結果的情況，SendResultsXXX API 的使用只有在需要對結果進行修改或處理時才有意義。如果在未作修改的情況下將結果發送給調用者，則 SqlPipe.Execute 是更好的執行解決方案。

　　副作用操作

　　一般來說，不允許用戶定義的函數（特別是表值函數）執行副作用操作。其中包括改變數據庫狀態的操作（如 DML 語句或事務處理操作）。在產生結果前后可能需要對系統狀態作一些修改。例如，業務組件可能需要設置 SAVEPOINT 事務，執行一些 UPDATE，并且通過管道返回結果；但是如果出現錯誤，則回滾到 SAVEPOINT。

　　如果不允許從 TVF 執行副作用操作，則只有存儲過程才能實現這樣的方案，并且必須通過 SqlPipe 返回結果。

　　請注意，當 SqlPipe 忙于發送結果時，尤其不允許通過 in-proc 提供程序執行副作用操作。只有在完成結果集之前或之后允許進行這些操作。

　　強類型化和返回的結果集的數量

　　從上面的代碼示例和與 T-SQL 一致的角度來看，由存儲過程通過 SqlPipe 產生的結果的描述不同于 TVF。TVF 是強類型化的，并且作為注冊 (CREATE FUNCTION) 語句的一部分，它必須靜態地定義 TVF 產生的結果的列數和類型。

　　另一方面，存儲過程聲明并沒有聲明產生的結果 － 甚至是否返回結果。這看起來可能很方便，雖然它確實提供了更大的靈活性，但是在編寫執行存儲過程的應用程序時要更加細心，因為存儲過程可以動態地重定義產生的結果的形式。

　　因此，自然而然建議根據元數據來描述結果：如果結果的架構需要根據調用的不同而變化，則只有 SqlPipe 才能提供這種靈活性。

　　同樣地，通過存儲過程內的 SqlPipe 產生的結果的弱類型化不能將單個結果的架構擴展為可能返回可變數量的結果集。存儲過程可以自由地根據條件確定是否發送給定的行集和定義其形式。這樣的靈活性增加了使用這種可變的結果流的應用程序的復雜性開銷。

　　下表總結了如何在兩者之間做出決定的指導原則：

•	存儲過程（使用隱式 SqlPipe 或基于顯式 CLR 的方法）和 TVF
•	T-SQL 和 CLR

表 4：關于生成結果集的指導原則
	是	否
需要可組合性？	TVF	TVF 過程或 TVF
外部數據源（與只訪問本地數據）？	CLR TVF 或 CLR 過程	（只訪問本地數據）T-SQL TVF 或過程
需要副作用？	過程	過程或 TVF
固定的結果架構？	過程或 TVF	過程
多個結果集？	過程	過程或 TVF
流化結果的能力？	CLR TVF	T-SQL TVF

　　對于本節的大部分內容，通過 SqlPipe 發送結果是與過程緊密相關的。即使在 CLR 觸發器主體中 SqlPipe 是可用的并且返回結果是可能的，也很不提倡這種做法，因為使用在目標對象中定義的觸發器發出數據處理語言 (DML) 或數據定義語言 (DDL) 語句可能會導致意外的結果。

　　將標量分解為行

　　經常需要在應用程序中傳送多值參數。例如，在定單處理系統中，可能需要編寫存儲過程來將定單插入到 Orders 表中。存儲過程中的參數之一可能是定單中的行項目。在這種情況下，您會遇到 T-SQL 限制，它不支持表值參數或缺乏集合數據類型（如數組）。解決這個問題的一種方法是，將集合編碼為一個標量值（如 nvarchar 或 xml），然后將其作為參數傳遞給存儲過程。在存儲過程內，可以使用表值函數來接受標量輸入，并將其轉換成一組行，然后將這些行插入到 LineItems 表中。

　　雖然可以用 T-SQL 編寫表值函數，但是用 CLR 實現它有兩個好處：

•	System.Text 命名空間中的字符串處理函數使得編寫表值函數更加容易。
•	CLR TVF 提供了更有效的流實現，這避免了將結果加載到工作表中。

　　下面的代碼片段顯示了如何實現一個表值函數，它接受以‘;’分隔的一組值作為輸入字符串，并且以一組行（字符串中的每個值一行）的形式返回該字符串。請注意，MySqlReader 類的構造函數實現了大部分工作，它使用 System.String.Split 方法將輸入字符串分解為數組。

// TVF that cracks a ';' separated list of strings into a result
　　// set of 1 nvarchar(60)column called Value
　　　　 public static ISqlReader GetStrings(SqlString str)
　　　　{
　　　　　　return (ISqlReader)new MySqlReader(str);
　　　　}
　　　　public class MySqlReader : ISqlReader
　　　　{
　　　　　　private string[]　m_strlist;
　　　　　　private int m_iRow = -1; // # rows read
　　　　　　
　　　　　　//The core methods
　　　　　　//Initialize list
　　　　　　public MySqlReader(SqlString str)
　　　　　　{
　　　　　　　　//Split input string if not database NULL;
　　//else m_strlist remains NULL
　　　　 if (!str.IsNull)
　　　　 {
　　　　　　m_strlist = str.Value.Split(';');
　　　　 }
　　　　　　}
　　　　　　// SECTION: Metadata related: Provide #, names, types of
　　　　　　// result columns
　　　　　　public int FieldCount { get { return 1; } }
　　　　　　public SqlMetaData GetSqlMetaData(int FieldNo)
　　　　　　{
　　　　　　　　if (FieldNo==0)
　　　　　　　　　　return new SqlMetaData("Value", SqlDbType.NVarChar, 60);
　　　　　　　　else throw new NotImplementedException();
　　　　　　}
　　　　　　// SECTION: Row navigation. Read is called until it returns
　　　　　　// false. After each Read call, Get<TypeName> for each
　　　　　　// column is called.
　　　　　　public bool Read()
　　　　　　{
　　　　　　　　//Return empty result set if input is DB NULL
　　//and hence m_strlist is uninitialized
　　　　　if (m_strlist==null) return false;
　　
　　m_iRow++;
　　　　　　　　if (m_iRow == m_strlist.Length)
　　　　　　　　　　return false;
　　　　　　　　return true;
　　　　　　}
　　　　　　//Column getters
　　　　　　//Implement Get<SqlTypeName> for each column produced by
　　　　　　//the TVF; in this case just one.
　　　　　　public SqlChars GetSqlChars(int i)
　　　　　　{
　　　　　　　　if (i == 0)
　　　　　　　　　　return new SqlChars(m_strlist[m_iRow]);
　　　　　　　　else
　　　　　　　　　　throw new NotImplementedException();
　　　　　　}
　　　　　　
　　　　　　//Methods not used by SqlServer omitted;
　　　//Actual implementation should provide an empty
　　　//implementation.
　　　　　　...
　　　　
　　　　} // public class MySqlReader
　　 } // class StringFunctions;

　　假定 GetStrings 方法注冊為具有相同名稱的 TVF。下面是存儲過程的代碼片段，它使用此 TVF 從定單中提取表形式的行項目。

　　CREATE PROCEDURE Insert_Order @cust_id int, @lineitems
　　　　　　　　　　　　　　　　　　　　　　　　　　nvarchar(8000)
AS
BEGIN
　　　 ...
　　　 INSERT LineItems
　　　 SELECT * FROM dbo.GetStrings(@lineitems)
...
END

　　
對數據進行自定義聚合

　　在許多情況下，您可能需要對數據進行聚合。這包括執行統計計算（如 avg、stddev 等等）。如果所需的聚合函數不是作為內置聚合函數直接支持的，SQL Server 2005 中有三種方法可以進行這樣的自定義聚合：

•	將聚合編寫為用戶定義的聚合 (UDA)。
•	使用 CLR 存儲過程編寫聚合。
•	使用服務器端光標。

　　讓我們在一個稱為 PRODUCT(int) 的簡單聚合函數的上下文中檢查這三種替代方法，該聚合函數計算一組給定值的乘積。

　　作為用戶定義的聚合函數實現的 PRODUCT

　　下面是此函數的主干 C# 代碼示例。所有的積累邏輯都在 Accumulate 函數中（為了簡單起見，其他函數顯示為 {...}）。

[SqlUserDefinedAggregate(Format.Native)]
public struct Product
{　
　 public void Accumulate(SqlInt32 Value)
　 {
　　　　 m_value *= Value;
　 }
　 public void Init() {...}
　 public void Merge(Product Group) {...}
　 public SqlInt32 Terminate() {...}
}

　　在定義類型、創建程序集和注冊到 SQL Server 之后，就可以通過以下方式使用 T-SQL中的聚合函數：

SELECT dbo.Product(intcol)
FROM tbl
GROUP BY col

　　作為使用 SqlDataReader 的托管存儲過程實現的 PRODUCT

　　可以創建存儲過程來執行查詢和循環訪問結果，以執行計算。這種循環訪問是通過使用 SqlDataReader 類完成的。

[SqlProcedure]
　 public static void Product(out SqlInt32 value)
　 {
　　　SqlCommand cmd = SqlContext.GetCommand();
　　　cmd.CommandText = "select intcolumn from tbl";
　　　SqlDataReader r = cmd.ExecuteReader();
　　　bool first = true;
　　　using (r)
　　　{
　　　　 while (r.Read()) //skip to the next row
　　　　 {
　　　　　　if (first)
　　　　　　{
　　　　　　　 value = r.GetSqlInt32(0);
　　　　　　　 first = false;
　　　　　　}
　　　　　　else
　　　　　　{
　　　　　　　 value *= r.GetSqlInt32(0);
　　　　　　}
　　　　 }
　　　}
　 }

　　可以使用 EXEC 語句來調用這一過程

EXEC Product @p OUTPUT

　　作為使用光標的 T-SQL 存儲過程實現的 PRODUCT

　　可以創建 T-SQL 存儲過程來執行查詢和通過使用 T-SQL 光標循環訪問結果，以執行計算。

create procedure TSQL_ProductProc (@product int output)
as
begin
　 declare @sales int
　 declare c insensitive cursor for select intcolumn from tbl
　 open c
　 fetch next from c into @sales
　
　 if @@FETCH_STATUS = 0　
　 set @product = @sales　
　
　 while @@FETCH_STATUS = 0
　 begin
　 fetch next from c into @sales
　 set @product = @product * @sales
　 end
　
　 close c
　 deallocate c
end

　　決定是使用 UDA 還是使用其他某種解決方案來產生結果取決于幾個因素：

•	可組合性要求。UDA 實際上是獨立的對象，可以用于任何 T-SQL 查詢，通常用在可以使用系統聚合函數的任何相同的地方。不需要假定它所操作的查詢。例如，可以將其包括在視圖定義（不過，索引視圖中不支持 UDA）和標量子查詢中。
•	聚合算法細節。在 Order By 子句（如果查詢中有）之前可能對 UDA 進行求值，因此不能保證傳遞給聚合函數的值的順序。如果聚合算法需要按照特定的順序使用值，則不能使用 UDA。同樣地，UDA 從整組中使用值并且返回單一值。如果需要必須為組中的每個值返回值的聚合函數，則應該考慮使用存儲過程或流表值函數來編寫您的函數。詳細信息請參見本文中的“產生結果”一節。
•	對副作用和數據訪問的需要。不允許 UDA 進行數據訪問或有副作用。如果您的函數需要保留大量的數據作為聚合的中間狀態，或因為其他某種原因需要進行數據訪問，則必須使用過程。

　　使用 UDA 的第一種方法在這三個選擇中可能提供最好的性能。通常，如果沒有碰到上面所列的限制，就應該嘗試將聚合函數編寫為 UDA。如果無法使用 UDA 方法，則使用 SqlReader 的托管代碼方法可能比 T-SQL 光標方法執行得更好。

　　可以用 UDA 方法編寫的有用的聚合的示例還包括：找到每組中第 N 大（或第 N 小）值，找到每組中前 N 個最大值的平均值或總和，等等。

　　六 用戶定義的類型 (UDT)

　　現在，我們來講 SQL Server 2005 中功能更強大但是經常被錯誤理解的一個功能。使用用戶定義的類型 (UDT)，可以擴展數據庫的標量類型系統（不僅僅為系統類型定義您自己的別名，這在 SQL Server 以前的版本中一直可用）。定義 UDT 就像用托管代碼編寫類，創建程序集，然后使用“create type”語句在 SQL Server 中注冊該類型一樣簡單。下面是實現 UDT 的主干代碼：

[SqlUserDefinedTypeAttribute(Format.Native)]
　 public struct SimpleUdt: INullable
　 {
　　　public override string ToString() {...}
　　　public bool IsNull { get; }
　　　public static SimpleUdt Null { get; }
　　　public static SimpleUdt Parse(SqlString s) {...}
　　　...
　 }
create type simpleudt from [myassembly].[SimpleUdt]
create table t (mycolumn simpleudt)

　　何時創建 UDT

　　SQL Server 2005 中的 UDT 不是對象相關的擴展性機制。它們是擴展數據庫的標量類型系統的一種方法。標量類型系統包括 SQLServer 附帶的列類型（如 int、nvarchar 和 uniqueidentifier 等類型）。例如，使用 UDT，可以定義您自己的、用于列定義的類型。如果您的類型確實是一個適合建模為列的原子值，請創建 UDT。

　　如果需要定義您自己的標量類型，請使用 UDT。這種類型的示例情況包括各種日歷中的自定義日期/時間數據類型和貨幣數據類型。使用 UDT，可以創建單個對象來公開類型上可用的所有行為，并且封裝或隱藏該類型所存儲的基礎數據。需要訪問數據的每個人都必須使用 UDT 的編程接口。如果能夠利用 .NET Framework 中現有的功能（如國際化或日歷功能），這實際上又是考慮將類型實現為 UDT 的一個很好的理由。

　　何時不創建 UDT

　　不要使用 UDT 來對復雜的業務對象（如雇員、聯系人或客戶）進行建模。您將會陷入 UDT 的所有列限制（如，8KB 大小限制、索引限制）和在更新 UDT 值時更新整個值的不利方面。對于復雜類型，UDT 不是合適的數據建模抽象；因此對于這種情況，最好使用中間層對象相關映射技術。

　　設計 UDT 時需要考慮的因素

　　因為它們是列形式的，所以可以定義整個 UDT 值的索引，并且創建參考完整性約束（如 UDT 列的唯一性）。還可以在比較和排序方案中使用 UDT。

　　比較 UDT 值是通過比較類型的基礎二進制表示完成的。如果使用 Format.Native 作為持久性機制，則會按照同在該類型中定義的一樣的字段順序創建永久形式；因此，請確保這些字段是按照類型的正確順序排列的。

　　UDT 上的每個操作（除了比較）都要求 UDT 的值反序列化，接著進行方法調用。這種模式有與之相關的固定開銷。如果要將類型建模為 UDT（相對于表中的列），則在訪問類型屬性（相對于表中的列）時應該考慮這種差別。如果類型上的行為非常復雜，則應該考慮使用 UDT。如果類型沒有任何與之相關的行為，則應該考慮將數據存儲為表中的列。

　　如果發現需要實現相關函數的庫，則 UDT 中的靜態方法是一種可以方便地創建這種庫的封裝機制。可以通過使用 :: 語法來調用T-SQL 中的靜態方法，如下所示：

select imagetype::MyFunction(@arg1)

　　實際方案

　　客戶希望在 UmAlQuraCalendar 中存儲日期時間值，這與 SQL Server 日期時間數據類型使用的 Gregorian 日歷不同。他們想讓這個日期類型具有相同的基本行為集，即字符串轉換、日期部分、日期算法和 GetDate()。

　　下面是這一數據類型的代碼片段。它使用 UmAlQuraCalendar 2.0 版，這是 .NET Framework 中的新類型。

[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct UmAlQuraDateTime : INullable
{
　 //Private state
private long dtTicks;
　 //Calendar object used for all calendar-specific operations
private static readonly UmAlQuraCalendar s_calendar = new UmAlQuraCalendar();
public static UmAlQuraDateTime Null
　 {
　　　get
　　　{
　　　　 UmAlQuraDateTime dt = new UmAlQuraDateTime();
　　　　 dt.isNull = true;
　　　　 return dt;
　　　}
　 }
　 public bool IsNull
　 {
　　　get
　　　{
　　　　 return this.isNull;
　　　}
　 }
　 //Convert into CLR DateTime type
　 public DateTime DateTime
　 {
　　　get { return new DateTime(this.dtTicks); }
　 }

　　字符串轉換和創建新值：

public static UmAlQuraDateTime Parse(SqlString s)
　 ...
　 public override string ToString()
　　　 ...
　 public static UmAlQuraDateTime ParseUsingFormat(string data, string fmt)
　 ...
　 public string ToStringUsingFormat(string format)
　 ...
　 public static UmAlQuraDateTime Now
　 {
　　　get
　　　{
　　　　 return new UmAlQuraDateTime(DateTime.Now);
　　　}
　 }
　 public static UmAlQuraDateTime FromSqlDateTime(SqlDateTime d)
　 {
　　　if (d.IsNull) return Null;
　　　return new UmAlQuraDateTime(d.Value);
　 }

　　
日期部分類似功能：

public int Year
　 {
　　　get
　　　{
　　　　 return s_calendar.GetYear(this.DateTime);
　　　}
　 }
　 public int Month
　 ...
　 public int Hour
　 ...
　 public int Minute
　 ...
　 public int Second
　 ...
　 public double Milliseconds
　 ...
　 public long Ticks
　 ...
　 public SqlDateTime ToSqlDateTime()
　 {
　　　return new SqlDateTime(this.DateTime);
　 }

　　DateAdd/DateDiff:

public UmAlQuraDateTime AddYears(int years)
　 {
　　　return new UmAlQuraDateTime(s_calendar.AddYears(this.DateTime, years));
　 }
　 public UmAlQuraDateTime AddDays(int days)
　 ...
　 public UmAlQuraDateTime AddMonths(int months)
　 ...
　 public UmAlQuraDateTime AddHours(int hours)
　 ...
　 public UmAlQuraDateTime AddMinutes(int minutes)
　 ...
　 public double DiffDays(UmAlQuraDateTime other)
　 ...

　　下面是一些處理 T-SQL 中的類型的示例操作：

-- convert it to arabic (for testing purposes)
declare @h hijridatetime
set @h = hijridatetime::ParseArabicString('01/02/1400')
select @h.ToArabicString(), @h.ToArabicStringUsingFormat('F')
-- convert sql datetime to hijri and back
declare @h hijridatetime
set @h = hijridatetime::FromSqlDateTime(GetDate())
select @h.ToArabicString(), @h.ToSqlDateTime()
-- get the current hijri datetime, in two ways
select　 hijridatetime::Now.ToString(),
　 hijridatetime::FromSqlDateTime(GetDate()).ToString()
-- do some arithmetic:
declare @h hijridatetime, @d datetime
set @h = hijridatetime::Now -- get the current hijri datetime
set @h = @h.AddDays(10) -- add ten days to it
set @d = GetDate() -- current sql date
set @d = DateAdd(day, 10, @d) -- add ten days to the sql datetime
-- print 'em both, should be the same
select @h.ToSqlDateTime(), @d
-- datepart
declare @h hijridatetime
set @h = hijridatetime::Now -- get the current hijri datetime
select @h.Year as year, @h.Month as month, @h.Day as month

　　小結

　　本文介紹了使用 SQL Server 2005 中的 CLR 集成功能的指導原則、具體使用情況和示例。數據庫應用程序開發人員和架構師應該結合集中討論 SQL Server 2005 中的其他功能區（如 TransacT-SQL、XML 和 Service Broker）的指導原則的其他文章來閱讀本文。

　　將來，我們計劃在這一領域提供更多白皮書，講解 CLR 集成的可管理性、監控和故障排除方面的指導原則。

　　版權

　　這是一個預備文檔，在此處描述的軟件的最終商業版本發布之前可能對該文檔進行補充性更改。本文檔中包含的信息代表 Microsoft Corporation 在發布時對所討論問題的最新觀點。由于 Microsoft 必須適應不斷變化的市場情況，因此，這些信息并非 方面所作的承諾，Microsoft 也不能保證出版日之后提供的任何信息都完全正確。

　　本白皮書僅供參考。MICROSOFT 對本文檔中的有關信息不作任何明示或暗示的擔保。

　　用戶有責任遵從所有適用的版權法。除了版權法所賦予的權利以外，未經 Microsoft Corporation 明確書面許可，不得擅自將本文檔的任何部分進行復制、存儲在或輸入可檢索系統，或以任何形式或方式（電子、機械、影印、錄制或其他方式）進行傳播，或用作其他目的。

　　Microsoft 對本文檔中的主題持有專利權、專利申請權、商標權、版權或其他相關的知識產權。Microsoft 只提供在任何書面許可協議中明確規定的權利，而不授予您本文檔的上述專利權、商標權、版權或其他知識產權。

　　除非另有聲明，否則本文中提到的示例公司、組織、產品、域名、電子郵件地址、徽標、人物、地點和事件都是虛構的，與任何真實的公司、組織、產品、域名、電子郵件地址、徽標、人物、地點或事件無任何關聯，也不應該被推斷為有任何關聯。

　　© 2004 Microsoft Corporation. All rights reserved.

　　Microsoft、Visual C++ 和 Visual C# 是 Microsoft Corporation 在美國和/或其他國家/地區的注冊商標或商標。

　　此處提到的真實的公司和產品名稱是其各自所有者的商標。

關鍵字：SQL Server 、CLR、商標

【 頂部 】 【 關閉 】