SQL Server中的CLR程式設計——用.NET為SQL Server編寫存儲過程和函數

很早就知道可以用.NET為SQL Server2005及以上版本編寫存儲過程、觸發器和存儲過程的，不過之前開發的系統要麼因為曆史原因用的是SQL2000要麼根本用不着在SQL Server中啟用CLR，是以一直沒有嘗試。最近因為項目的原因，在這方面做了一個調研，現在在這裡分享一下心得。

首先要說明的是要在SQL Server中啟用CLR必須是在SQL Server2005及以上版本，其次在預設情況下是沒有啟用CLR的，必須要顯示設定為啟用。比如我們要在ArticleCollectorDB資料庫中運作用.NET編寫的函數或者存儲過程，至少先要進行下面的SQL語句：

這時可能會得到提示要重新啟動SQL Server，如果有此提示則重新啟動一下。

接着我們在VS中進行編碼，在這裡我們将分别編寫一個名為IsMatch的函數和一個名為SendMail存儲過程。在VS中建立一個名為NetSkycn.Data的類庫項目，添加一個SqlCLR的類，代碼如下：

編譯通過之後，記住類庫的實體全路徑，比如：F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll，在這裡要強調幾點：一、對于将來提供給SQL Server調用的函數或者存儲過程必須是靜态方法，并且還必須帶有SqlFunction或者SqlProcedure屬性；二、對于一些需要通路外部網絡資源和安全屬性的還必須添加響應的屬性（如本例中的SendMail方法，如果沒有添加響應的屬性在建立SQL Function/Procedure時會出現錯誤提示）。

現在我們開始遵循先為SQL Server建立程式集、後建立函數或者存儲過程的順序來操作，在操作過程中用到的SQL語句如下：

如果沒有得到任何錯誤提示，則表示建立函數和存儲過程成功。至此我們會看到如下情形：

這表示建立成功。

測試建立函數的SQL語句（查找article表中title字段是3至5個字段的資料）：

測試建立存儲過程的SQL語句：

以上代碼在SQL Server 2005中文企業版、SQL Server 2008英文企業版測試通過。

可以看出在一些SQL語句不夠靈活的情況下，可以使用.NET來編寫存儲過程和函數，通過以上步驟之後和調用SQL語句寫的存儲過程和函數沒有差別，極大地友善了程式設計。

周公

2012-05-12