在SQL Server中使用CLR調用.NET方法

我們一起來做個樣本，在.NET中建立一個類，並在這個類裡建立一個方法，然後在SQL Server中調用這個方法。按照微軟所述，通過宿主 Microsoft .NET Framework 2.0 公用語言運行庫 (CLR)，SQL Server 2005顯著地增強了資料庫編程模型。 這使得開發人員可以用任何CLR語言(如C#、VB.NET或C++等)來寫預存程序、觸發器和使用者自訂函數。

我們如何?這些功能呢?

為了使用CLR，我們需要做如下幾步：

1、在.NET中建立一個類，並在這個類裡建立一個public方法。

2、編譯這個類為一個DLL。

3、在SQL Server中註冊這個DLL。

4、建立一個SQL Server函數來訪問指定的.NET方法。

接下來，我們一起來完成一個樣本

首先，在Visual Studio中建立一個名為“SQLServerCLRTest”的類庫項目。 然後，建立一個名為“CLRFunctions”的類，並在其內添加一個名為“HelloWold”的方法，代碼如下：

以下為引用的內容： public class CLRFunctions { public static string HelloWorld(string Name) { return ("Hello " + Name); } }

這是一個非常簡單的方法(為了讓SQL Server可以調用它，它必須要是public和static的)，這個方法有一個string類型的參數，返回資訊為“Hello”加上你傳入的參數。

現在，我們需要編譯這個項目為一個DLL，並在SQL Server中註冊它。 這也是比較簡單的，在VS中按右鍵項目，選擇“產生”後程式就會產生一個DLL。 如果你的項目是偵錯模式的話，那麼就可以在如下所示那樣的路徑裡找到編譯好的DLL。

C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\SQLServerCLRTest\SQLServerCLRTest\bin\Debug\SQLServerCLRTest.dll

找到這個DLL後，我們就可以把它拷貝到我們的SQL Server機器上了，如果是相同機器的話我們只要記住這個路徑即可。

啟用CLR功能

預設情況下，SQL Server中的CLR是關閉的，所以我們需要執行如下命令開啟CLR：

以下為引用的內容： exec sp_configure 'clr enabled',1 reconfigure go

註冊DLL

為了調用我們寫的那個方法，需要在SQL Server中註冊我們剛剛編譯好的那個DLL。 我們可以在資料庫中使用如下命令來註冊DLL(路徑為你的DLL檔案的路徑)

CREATE ASSEMBLY asmHelloWorld FROM 'C:\SQLServerCLRTest.dll'

在SQL Server中調用我們的.NET方法

為了調用.NET方法，我們可以寫一個SQL Server自訂函數，並在其內使用“EXTERNAL NAME”來通知SQL Server使用CLR功能。 代碼如下：

以下為引用的內容： CREATE FUNCTION dbo.clrHelloWorld ( @name as nvarchar(200) ) RETURNS nvarchar(200) AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld

上面的自訂函數做了兩項工作。 首先是聲明了一個nvarchar參數，它等同於.NET裡的string類型(如果將其設定為varchar並且後面使用了“EXTERNAL NAME”的話就會報錯)。然後使用“EXTERNAL NAME”來調用.NET方法。 文法如下：

程式集名.類名.方法名

但是，當我使用這個文法調用.NET方法的時候，SQL Server就會報錯，所以為了讓它正常工作，我使用了如下文法：

程式集名.[類名].方法名

現在我們就可以通過如下語句調用.NET方法了：

SELECT dbo.clrHelloWorld('Mark')

當你運行這段代碼的時候，就會得到一個返回結果“Hello Mark”。