寫在前面
本系列文章一共分為四部分：
1. CLR概述。
2. 在Visual Studio中進行CLR集成編程並部署到SQL Server，包括存儲過程 、觸發器、自定義函數、自定義類型和聚合。
3. CLR集成編程的調試和所遇到的問題。
4. 利用CLR同步SQL Server表和Sharepoint List（來源於實際項目應用）。
本系列文章建立在以下軟件環境的基礎上：
Windows Server 2003 Enterprise Edition Service Pack 2
Microsoft Visual Studio Team System 2008
Microsoft SQL Server 2008
Microsoft Office Sharepoint Server 2007
准備工作
默認情況下SQL Server對CLR的允許狀態是關閉的，我們需要在查詢分析器中 打開它，將CLR設置為允許，這樣該服務器將對所有數據庫實例開放CLR執行條件 。可以按照下面的步驟進行：
Exec sp_configure 'clr enabled'
該系統存儲過程用於查看當前CLR的狀態，返回的結果中如果config_value字 段的值為0則表示禁止CLR在該服務器上運行，值為1則為允許。增加第二個參數到 該存儲過程，然後再執行。
Exec sp_configure 'clr enabled', 1--1 enabled, 0 disabled
查詢分析器的消息窗口中給出提示：Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install. 按提示運 行reconfigure命令進行安裝。
reconfigure with override
現在SQL Server服務器已經允許執行CLR托管代碼了，接下來我們只需要在 Visual Studio中編寫代碼，將生成的dll部署到SQL Server中即可。按下圖，創 建SQL Server Project並設置好數據庫連接，然後就可以編寫用戶自定義類型、 存儲過程、用戶自定義函數、聚合和觸發器了。
開始
一個簡單的自定義函數
現在我們已經可以在Visual Studio中開始CLR項目了，在Solution Explorer 中右鍵單擊項目文件，點擊Add，在其中選擇你所要添加的類型。我們從最簡單的 類型開始，選中User-Defined Function…，取名為DateTimePre.cs，該自定義函 數主要實現在用戶給定的字符串數據前加上系統當前時間前綴，編寫代碼如下。
1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Data.SqlTypes;
5 using Microsoft.SqlServer.Server;
6
7 public partial class UserDefinedFunctions
8 {
9 [Microsoft.SqlServer.Server.SqlFunction]
10 public static SqlString DateTimePre(string input, string format)
11 {
12 string sRst = string.Format("{0}:{1}", DateTime.Now.ToString(format), input);
13 return new SqlString(sRst);
14 }
15 };
代碼很簡單，就是在用戶給定的文本前加上當前時間前綴，第二個參數用於指 定時間顯示的樣式。先說明一下代碼的結構。SQLCLR中定義的類（用戶自定義的 類除外）都以partial關鍵字開頭，表示它是一個分部類，這個我們不需要去改它 ，默認的類名可以修改，同一個類中可以有多個自定義的類型（自定義函數、觸 發器、存儲過程等），但都必須用特征屬性顯示指明類型，如SqlFunction、 SqlProcedure、SqlTrigger、SqlUserDefinedType等，這些類型必須定義為 public類型的而且必須為static，以向SQL Server完全公開，有些類型必須要有 返回值，如自定義函數，如果不需要類型返回值，則可以定義為存儲過程類型， 這個後面再舉例。
編譯代碼，部署到SQL Server服務器，這個服務器的地址取決於你在Visual Studio中所指定的數據庫服務器，就是我們在創建項目前所指定的那個數據庫服 務器。在Solution Explorer中右鍵單擊項目文件，點擊Deploy，Visual Studio 會自動為你進行程序集的部署。因為我們的這個示例很簡單，也沒有涉及到訪問 外部資源的代碼，所以不用考慮程序集的訪問級別和安全性，讓Visual Studio按 照默認的情況自動進行就可以了。我會在後面專門介紹如何設置程序集的訪問級 別以及其中遇到的問題。
部署成功後轉到SQL Server Management Studio，打開你所連接的數據庫，依 次選擇Programmability—Functions—Scalar-valued Functions，在下面可以找 到我們剛創建的這個類型，表示一切順利！
接下來我們在查詢分析器中執行它，看一下執行結果，OK。一個簡單的自定義 函數就完成了，因為有Visual Studio的幫助，使得SQLCLR的編寫變得非常簡單， 否則，你需要在命令行方式下手動進行部署，這個過程比較復雜，也不是本文的 重點。
Select dbo.DateTimePre('Begin', '')
流式表值函數（TVF）的實現
前面我在介紹CLR集成性能的時候提到了流式表值函數（TVF），它返回 IEnumerable接口的托管函數，通過特征屬性指定返回結果的樣式和定義方法，將 結果以數據表的形式在SQL Server的查詢分析器中輸出。它的性能將優於在SQL Server查詢分析器中使用擴展存儲過程的性能。
在剛才創建的class文件中再寫一個方法，用於實現流式表值函數。示例給出 的是在用戶指定的位置搜索出用戶指定類型的所有文件，然後以規定的表格樣式 將結果在SQL Server的查詢分析器中輸出。
1 [SqlFunction(FillRowMethodName = "BuildRow",
2 TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]
3 public static IEnumerable FileListCs(string directoryName, string pattern)
4 {
5 FileInfo[] files;
6 //模擬當前SQL安全上下文
7 WindowsImpersonationContext contect = SqlContext.WindowsIdentity.Impersonate();
8 try
9 {
10 DirectoryInfo di = new DirectoryInfo (directoryName);
11 files = di.GetFiles(pattern);
12 }
13 finally
14 {
15 if (contect != null)
16 {
17 contect.Undo();
18 }
19 }
20 return files;
21 }
22
23 private static void BuildRow(object Obj,
24 ref SqlString fileName,
25 ref SqlInt64 fileLength,
26 ref SqlDateTime fileModified)
27 {
28 if (Obj != null)
29 {
30 FileInfo file = (FileInfo)Obj;
31 fileName = file.Name;
32 fileLength = file.Length;
33 fileModified = file.LastWriteTime;
34 }
35 else
36 {
37 fileName = SqlString.Null;
38 fileLength = SqlInt64.Null;
39 fileModified = SqlDateTime.Null;
40 }
41 }
對代碼的說明。特征屬性中的FillRowMethodName用來告訴該函數，輸出的結 果需要用它指定的函數進行格式化，而格式化的樣式（即Table的定義）由 TableDefinition的值指定。這也就是說，我們需要自己編寫FillRowMethodName 所指定的函數，即代碼中的BuildRow方法。該方法有一個輸入型參數Obj，用於接 收流式表值函數的返回值，另外幾個ref型的參數必須與TableDefinition所規定 的內容一致，並且必須是ref類型的參數。經過這樣的規定，流式表值函數就會按 照我們定義好的表結構在SQL Server的查詢分析器中輸出結果。 WindowsImpersonationContext對象用於在SQL Server中模擬當前安全上下文，以 SQL Server進程的身份執行程序代碼，在訪問外部資源時（尤其是網絡資源）， 如果當前SQL Server沒有訪問權限，則依附於它的CLR程序集的訪問也會失敗。注 意在對CLR進行安全上下文模擬之後必須進行Undo操作！
有一個前提條件需要說明一下。前面我在CLR集成安全性中提到，當CLR的程序 集需要訪問外部資源時（例如我們這裡所訪問的磁盤文件），需要設置程序集的 訪問級別為External，而且需要將宿主數據庫的權限級別設置為EXTERNAL_ACCESS ，否則SQL Server的查詢分析器會提示錯誤。
Use Test
Go
Alter Database Test Set trustworthy On
Go
編譯、部署，轉到SQL Server查詢分析器中，在Programmability—Functions —Table valued Functions下可以看到我們剛創建的流式表值函數。執行它！
Select * From Test.dbo.FileListCs('D:\TreeView', '*.*')
出現了錯誤！原來我們在執行流式表值函數的特征屬性時少了一個DataAccess ，下面補上。再運行，函數給出了正確的結果。
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FileListCs":
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode (SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext (SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext (SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext ()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_WindowsIdentity()
at UserDefinedFunctions.FileListCs(String directoryName, String pattern)
.
[SqlFunction(DataAccess = DataAccessKind.Read,
FillRowMethodName = "BuildRow",
TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]
存儲過程的實現
在Visual Studio中重新創建一個類型為Stored Procedure的class，編寫代碼 如下。
1 [Microsoft.SqlServer.Server.SqlProcedure]
2 public static void GetData(string tbName)
3 {
4 using (SqlConnection cn = new SqlConnection("context connection=true"))
5 {
6 using (SqlCommand cmd = cn.CreateCommand())
7 {
8 cmd.CommandText = string.Format ("Select * from {0}", tbName);
9 cn.Open();
10 SqlContext.Pipe.Send (cmd.ExecuteReader());
11 }
12 }
13 }
按照我在前面CLR集成安全性中介紹的，CLR在EXTERNAL_ACCESS訪問模式下默 認以SQL Server當前的服務賬戶運行，所以我們可以利用當前上下文來獲取數據 庫連接字符串，並進行相關數據處理（如輸出Message，訪問站點等）。 SqlContext.Pipe.Send方法用於在當前上下文中輸出結果到SQL Server查詢分析 器的Results窗口中，它可以接收SqlDataReader、SqlDataRecord和string類型的 參數，同時也可以SqlContext.Pipe.ExecuteAndSend(SqlCommand)這樣來用。
編譯、部署，轉到SQL Server查詢分析器中，剛剛編寫的存儲過程出現在 Programmability—Stored Procedure下，直接調用該存儲過程，得到結果。
觸發器的實現
觸發器的實現比較簡單，主要還是方法前的特征屬性需要描述清楚，這裡給出 一個示例，當對表Area進行更新操作的時候會在表Region中更新相應的值，相關 截圖和代碼如下。
表Area（修改前）
表Region（修改前）
1 [Microsoft.SqlServer.Server.SqlTrigger(Name = "Triggers", Target = "Area", Event = "FOR UPDATE")]
2 public static void TriggersTest()
3 {
4 string comText = @" Declare @oldTitle varchar(50)
5 Declare @newTitle varchar(50)
6
7 Select @oldTitle = Title From Deleted
8 Select @newTitle = Title From Inserted
9
10 Update Region Set Area = @newTitle Where Area = @oldTitle";
11
12 using (SqlConnection cn = new SqlConnection())
13 {
14 cn.ConnectionString = "context connection=true";
15 cn.Open();
16 using (SqlCommand cmd = cn.CreateCommand())
17 {
18 cmd.CommandText = comText;
19 SqlContext.Pipe.ExecuteAndSend (cmd);
20 }
21 }
22 }
編譯並部署，觸發器出現在相關表下面的Triggers目錄下，並且該觸發器的圖 標上有一個小鎖，表示該觸發器是由CLR生成的，並且不能被修改。現在update表 Area的一條數據，會發現Region表中相關的記錄也發生了變化，表示觸發器已經 生效了。
Update Area Set Title = 'APAC' Where Title = 'APAC1'
表Area（修改後）
表Region（修改後）
用戶自定義類型的實現
用戶自定義類型理解起來可能稍微有點復雜，在實際應用當中可能也很少用到 ，我在這裡直接引用了MSDN上的一個例子，了解一下。實際上，當我們在Visual Studio上創建一個UserDefinedType時，IDE已經為我們做了很多事情了，剩下的 工作只需要以填空的方式完善代碼即可，這樣說來，實際上也不是那麼復雜啊， 至少Visual Studio在為我們搭建好的代碼結構中已經有了不少注釋，我們應該知 道怎麼去做。
這個示例中定義了一個UserDefinedType類型，用戶使用的時候可以給定一個 字符串值，然後通過內部的轉換顯示出相應的信息。如給定1:2，則表示right top，給定-1:3，則表示left top等，內部的轉換規則需要自己去實現，當然，你 也可以實現更加復雜的結構。
1 [Serializable]
2 [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
3 public struct UserDefinedType : INullable
4 {
5 public override string ToString()
6 {
7 if (this.IsNull)
8 {
9 return "NULL";
10 }
11 else
12 {
13 return this.m_x + ":" + this.m_y;
14 }
15 }
16
17 public bool IsNull
18 {
19 get { return this.m_Null; }
20 set { this.m_Null = value; }
21 }
22
23 public static UserDefinedType Null
24 {
25 get
26 {
27 UserDefinedType h = new UserDefinedType ();
28 h.m_Null = true;
29 return h;
30 }
31 }
32
33 public static UserDefinedType Parse(SqlString s)
34 {
35 if (s.IsNull)
36 return Null;
37
38 string str = Convert.ToString(s);
39 string[] xy = str.Split(':');
40
41 UserDefinedType u = new UserDefinedType();
42 u.X = Convert.ToInt32(xy[0]);
43 u.Y = Convert.ToInt32(xy[1]);
44
45 return u;
46 }
47
48 public SqlString Quadrant()
49 {
50 if (m_x == 0 && m_y == 0)
51 {
52 return "centered";
53 }
54
55 SqlString stringReturn = "";
56
57 if (m_x == 0)
58 {
59 stringReturn = "center";
60 }
61 else if (m_x > 0)
62 {
63 stringReturn = "right";
64 }
65 else if (m_x < 0)
66 {
67 stringReturn = "left";
68 }
69
70 if (m_y == 0)
71 {
72 stringReturn = stringReturn + " center";
73 }
74 else if (m_y > 0)
75 {
76 stringReturn = stringReturn + " top";
77 }
78 else if (m_y < 0)
79 {
80 stringReturn = stringReturn + " bottom";
81 }
82
83 return stringReturn;
84 }
85
86 // This is a place-holder field member
87 public int X
88 {
89 get { return this.m_x; }
90 set { this.m_x = value; }
91 }
92
93 public int Y
94 {
95 get { return this.m_y; }
96 set { this.m_y = value; }
97 }
98
99 // Private member
100 private int m_x;
101 private int m_y;
102 private bool m_Null;
103 }
編譯部署，在SQL Server的查詢分析器中打開Programmability—Types— User-Defined Types，可以看到剛創建的類型，執行後可以看到結果。
CREATE TABLE test_table (column1 UserDefinedType)
go
INSERT INTO test_table (column1) VALUES ('1:2')
INSERT INTO test_table (column1) VALUES ('-2:3')
INSERT INTO test_table (column1) VALUES ('-3:-4')
Select column1.Quadrant() From test_table
聚合的實現
與自定義類型類似，在Visual Studio中創建聚合（Aggreagte），IDE同樣已 經為我們做好了准備。這裡我也給出一個MSDN上的例子（不過對於嵌套循環稍做 了修改），用來在聚合中計算字符串數據類型中的原音字符的個數。
1 [Serializable]
2 [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
3 public struct Aggregate
4 {
5 public void Init()
6 {
7 countOfVowels = 0;
8 }
9
10 public void Accumulate(SqlString value)
11 {
12 // list of vowels to look for
13 List<string> vowels = new List<string>();
14 vowels.Add("a");
15 vowels.Add("e");
16 vowels.Add ("i");
17 vowels.Add("o");
18 vowels.Add("u");
19
20 // for each character in the given parameter
21 for (int i = 0; i < value.ToString().Length; i++)
22 {
23 if (vowels.Contains(value.Value.Substring(i, 1).ToLower()))
24 {
25 // it is a vowel, increment the count
26 countOfVowels += 1;
27 }
28 }
29 }
30
31 public void Merge(Aggregate value)
32 {
33 Accumulate(value.Terminate());
34 }
35
36 public SqlString Terminate()
37 {
38 return countOfVowels.ToString();
39 }
40
41 // This is a place-holder member field
42 private SqlInt32 countOfVowels;
43 }
編譯部署，在SQL Server的查詢分析器中打開 Programmability—Functions—Aggreate Functions，可以看到剛創建的聚合函 數，下面是執行後的結果。
結語
CLR創建SQL Server對象應該還不止上面提到的這幾種類型，但常用的基本都 在這裡了，尤其是存儲過程、函數和觸發器。利用C#來編寫這些類型，靈活性更 大，可操控性也更強了。下一篇我將會介紹如何在Visual Studio中進行CLR調試 ，對程序集的分發和手動部署，以及常見問題的解決辦法。
本文配套源碼
