通過CLR同步SQL Server和Sharepoint List資料（一）

寫在前面

    本系列文章一共分為四部分：

        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

 

前言

    CLR（Common Language Runtime）通用語言執行平台是Microsoft在.NET出來之後創造出來的一個概念，它是.NET架構中重要的組成部分，為所有.NET Framework代碼提供執行環境。在CLR中啟動並執行代碼稱為Managed 程式碼。CLR提供執行程式所需的各種函數和服務，包括即時（JIT）編譯、分配和管理記憶體、強制型別安全、異常處理、線程管理和安全性等。相信讀者已經在任何一本介紹.NET的書中對它進行了瞭解，並且深知CLR的工作原理。本文要介紹的不僅僅是.NET架構中的CLR，更多的則是有關CLR的整合編程。事實上，Microsoft在通用語言執行平台（CLR）整合編程上已經做了很多準備了，以至於使用者現在可以用.NET的任何一種語言將自己編寫好的功能安插在微軟的任何一款產品上（可能有些誇張了），例如SQL Server、Office產品等，本文正是針對CLR在SQL Server上的應用進行介紹。有關CLR在其它產品上的應用，我將在其它系列文章中再做介紹（如VSTO等）。

    從SQL Server 2005開始，Microsoft就已經在其中整合了通用語言執行平台（CLR）組件，只不過當時的應用可能還不太廣泛（也許我當時還並沒有怎麼聽說），使用的使用者不多。但是，這也就意味著使用者已經可以使用.NET的任何一種語言（如VB.NET和C#.NET等）來為資料庫編寫預存程序、觸發器、使用者定義型別、使用者定義函數、使用者定義彙總和流式表函數等等資料庫物件了。這個訊息聽起來著實讓人很興奮，這讓那些許多年來都十分保守的DBA、DEV（資料庫開發人員）們有了更多的選擇，同時也讓許多單純的SDE（軟體開發人員）可以嘗試資料庫開發，從而讓程式和背景資料庫結合得更加緊密，開發人員之間的協作更加順暢。

 

通用語言執行平台（CLR）整合概述

    Microsoft SQL Server已經具備了與.NET Framework的通用語言執行平台（CLR）組件整合的功能。CLR為Managed 程式碼提供服務，例如跨語言整合、程式碼存取安全性、物件存留期管理以及調試和分析支援，它具有以下一些特點：

• 更好的編程模型。.NET Framework語言在許多方面都比 Transact-SQL豐富，它為SQL Server開發人員提供了以前沒有的構造和功能。開發人員還可以利用 .NET Framework 庫的功能，它提供了大量可用於快速有效地解決編程問題的類。
• 改進了安全和安全性。Managed 程式碼在資料庫引擎承載的通用語言執行平台環境中運行。SQL Server利用這一特點為在 SQL Server 早期版本中提供的擴充預存程序提供更安全更可靠的替代方法。
• 能夠定義資料類型和彙總函式。 使用者定義型別和使用者定義彙總是兩個新的管理的資料庫對象，這兩個對象擴充了SQL Server的儲存和查詢功能。
• 通過標準化環境簡化了開發。資料庫開發整合到將來版本的Microsoft Visual Studio .NET開發環境中。開發人員在開發和調試資料庫物件和指令碼時所使用的工具與他們編寫中介層或用戶端層的 .NET Framework組件和服務時所使用的工具相同。
• 具備改善效能和可擴充性的潛力。在多數情況下，.NET Framework語言編譯和執行模型通過Transact-SQL提高效能。

    Managed 程式碼使用程式碼存取安全性（CAS）來使程式集無法執行某些操作。SQL Server使用CAS來協助保護Managed 程式碼，並阻止對作業系統或資料庫伺服器的侵害。

CLR整合的優點

    Transact-SQL是為了在資料庫中直接進行資料訪問和操縱而專門設計的。雖然Transact-SQL在資料訪問和管理方面表現很好，但它不是完整的程式設計語言。例如，Transact-SQL不支援數組、集合、for-each迴圈、移位或類。雖然可以在Transact-SQL中類比某些這樣的構造，但Managed 程式碼已經整合了對這些構造的支援。根據具體情況，這些功能足以為在Managed 程式碼中實現某些資料庫功能提供充分理由。

選擇Transact-SQL還是Managed 程式碼

    如果代碼主要執行沒有或只有很少過程邏輯的資料訪問，請使用Transact-SQL。如果要編寫有複雜邏輯並且CPU佔用量大的函數和過程，或者想使用.NET Framework的BCL，則使用Managed 程式碼。

選擇在伺服器中執行還是在用戶端中執行

    影響使用Transact-SQL還是Managed 程式碼的另一個因素是您想將代碼駐留在伺服器電腦上，還是在用戶端電腦上。Transact-SQL和Managed 程式碼都可以在伺服器上運行。這種方式可以將代碼和資料靠近放在一起，並允許您利用伺服器的強大處理能力。另一方面，您可能希望避免將處理器佔用量大的任務放在資料庫伺服器上。目前大多數用戶端電腦都有非常強大的功能，因此您可能希望通過將儘可能多的代碼放在用戶端上，來利用這種處理能力。Managed 程式碼可以在用戶端電腦上運行，而Transact-SQL不能。

選擇擴充預存程序還是Managed 程式碼

    可以產生擴充預存程序來執行使用Transact-SQL預存程序無法實現的功能。但是，擴充預存程序可能有損於SQL Server進程的完整性，而經過驗證確定為型別安全的Managed 程式碼則不會。進一步來說，在CLR的Managed 程式碼與SQL Server之間更深入地整合了記憶體管理、線程及纖程的調度以及同步服務。如果所編寫的預存程序需要執行在Transact-SQL中不可能完成的任務，則CLR整合有比擴充預存程序更安全的方式來實現它。

 

通用語言執行平台（CLR）整合效能

    現在開發人員可以自由選擇Transact-SQL或者CLR進行SQL Server資料庫開發，但是兩者在效能方面各有優缺點，針對於不同類型的運算和資料庫物件，下表給出了兩者之間的區別。

類型	Transact_SQL	CLR
使用者定義函數	執行資料訪問時更有效。	適用於過程代碼、計算和字串操作以及需要大量計算和不執行資料訪問的部分。
使用者定義彙總	非基於遊標的本機內建彙總函式的效能高於CLR方式。	效能高於基於遊標的彙總，但執行速度較慢。
流式資料表值函式（TVF函數）	—	託管TVF的效能優於可比擴充預存程序實現的效能，它返回IEnumerable介面的託管函數。
數組與遊標	遊標的效能低於CLR中的數組	當Transact-SQL遊標必須遍曆更容易表示為數組的資料時，使用Managed 程式碼可以顯著提高效能。
字串資料	char或varchar資料類型。	託管函數中可以是SqlString或 SqlChars類型。
擴充預存程序	無法查看或控制擴充預存程序的資源使用方式。	可以使用Managed 程式碼對給定的線程進行檢測。

 

通用語言執行平台（CLR）整合式安全性

    與.NET Framework通用語言執行平台 (CLR) 整合的SQL Server的安全模式用於管理和保護SQL Server內啟動並執行不同類型CLR對象和非CLR對象之間的訪問。這些對象可能由Transact-SQL語句或伺服器上啟動並執行其他CLR對象調用。對象之間的調用稱為連結。對這些對象執行的安全檢查類型取決於相關的連結類型。CLR整合安全模式可實現以下目的：

• 預設情況下，在SQL Server中運行託管使用者代碼不應當損害SQL Server的完整性和穩定性。如果執行有可能損害 SQL Server可靠性的操作，則應當受到適當的進階許可權的保護。
• 託管使用者代碼不應當獲得對資料庫中使用者資料或其他使用者代碼的未經授權訪問。使用者定義代碼應當在調用該代碼的使用者會話的安全上下文中運行，且擁有該安全內容相關的正確特權。
• 應當有控制來限制使用者代碼不得訪問伺服器以外的任何資源，而只能用於本機資料訪問和計算。
• 使用者定義代碼不應能通過在SQL Server進程中運行而獲得對系統資源的未經授權訪問。

    SQL Server已經整合了SQL Server基於使用者的安全模式和CLR基於代碼訪問的安全模式。SQL Server主機策略層級授予程式集的程式碼存取安全性許可權集由建立該程式集時指定的許可權集決定。有三個許可權集：SAFE、EXTERNAL_ACCESS和UNSAFE。

SAFE	最具限制性的許可權集，只允許內部計算和本機資料訪問，無法訪問外部系統資源，如檔案、網路、環境變數或註冊表。並且只能使用上下文連接字串指定資料庫連接，即context connection=true或context connection=yes。
EXTERNAL_ACCESS	與SAFE具有相同的許可權，但允許訪問外部系統資源。
UNSAFE	允許程式集不受限制地訪問SQL Server內部和外部的資源，此時程式集被授予FullTrust。

    一般情況下，在不使用SQL Server外部系統資源時建議採用SAFE方式的程式集，如果在程式集中需要訪問外部系統資源，推薦使用EXTERNAL_ACCESS，而不是UNSAFE，後者將允許程式集中的代碼對SQL Server進程空間進行非法操作，可能會損害SQL Server的健壯性和可靠性。EXTERNAL_ACCESS程式集預設情況下將以SQL Server的當前服務賬戶運行，它可以顯示類比調用方的Windows身分識別驗證安全上下文，這也就是我在後面使用SQL CLR串連Sharepoint List時為什麼要將程式集設定為EXTERNAL_ACCESS！

 

結語

    總之，通用語言執行平台（CLR）為使用C# Custom程式集串連SQL Server和外部資源（諸如Sharepoint List、網路資源、檔案系統等），以及SQL Server本身資料運算提供了良好的基礎和更好的便利性。在下一篇文章中我將介紹如何在SQL Server 2008中開啟CLR並編寫CLR使之在SQL Server中成功運行，當中可能會遇到很多小的問題，到時我會一一給出解決辦法。

1

2

3

4