初探SQL Server CLR 整合

sqlserver
2005中，加入了很多新特性。對於開發人員來講，最值得一提的是對XML的支援和CLR整合。當然，還有一些關於“高可用“（資料庫叢集/資料庫鏡像/記錄傳送等）的新特性，比較適合DBA，感興趣的朋友可以瞭解一下。

相信很多人和我一樣，一直很希望在sqlserver中非常靈活地編程，實現各種功能（能像在VS中使用C#編程一樣的爽）。但是未能如願，暫不說開發環境的問題（代碼補全，配色等）。最主要的是，T-SQL這東西確實不適合做複雜的運算（對我這種菜鳥來講確實有難度。對那些大牛來說估計不難），也缺乏物件導向編程的靈活性。連一些基本的資料結構也沒有辦法實現，更別提什麼物件導向了。

有了sqlserver
CLR整合後，這一切似乎就變了。什麼是CLR ，到底什麼是CLR整合？先看看MSDN的解釋吧。

通過在 Microsoft SQL Server 中託管 CLR（稱為 CLR 整合），可以在Managed 程式碼中編寫預存程序、觸發器、使用者定義函數、使用者定義型別和使用者定義彙總函式。 因為Managed 程式碼在執行之前會編譯為機器碼，所以，在有些方案中可以大大提高效能。

通俗點講，就是sqlserver 2005 版本之後，資料庫引擎中加入了 .NET Framework 的通用語言執行平台 (CLR) 組件，可以更方便和.NET應用程式互動。

首先，建立一個sqlserver
CLR 資料庫專案，

項目建立好之後，可以開啟試圖，看看結構，裡面只包含一些測試樣本的sql指令碼。

接下來，建立一個普通的C#類，在類中定義一個方法。如下：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.SqlServer.Server;namespace SqlServerProject1{   public  class demo    {       [Microsoft.SqlServer.Server.SqlProcedure]       public static void ClrProc() {           SqlContext.Pipe.Send("Hello World");       }    }}

記得要引入using Microsoft.SqlServer.Server這個命名空間。在方法體中，使用了如下代碼

SqlContext.Pipe.Send("Hello World");

可以理解為像用戶端發送一條結果，就像Response.Write輸出一樣。

給這個普通的方法，加上[Microsoft.SqlServer.Server.SqlProcedure]特性。其意義就是標記自己是個CLR
預存程序。

註：Attribute，有的人也稱作屬性，為了避免不和物件導向中的get/set混淆，故本人習慣稱之為”特性“。和java中的註解差不多。

編譯通過後，可以選擇直接在VS的產生菜單下選擇”部署“，也可以去資料庫中手動建立。

開啟指定資料庫的查詢時段，開始寫指令碼，檢查資料庫配置，看看是否開啟了clr支援，沒有的話則手動去開啟：

--查看系統配置SELECT * FROM sys.configurationsORDER BY nameGO--啟用clrsp_configure 'clr enabled',1goRECONFIGURE;GO

準備完畢後，開始建立程式集和CLR預存程序：

--建立程式集CREATE ASSEMBLY SqlServerProject1 FROM N'C:\Users\Administrator\Documents\visual studio 2010\Projects\Database1\SqlServerProject1\bin\Debug\SqlServerProject1.DLL'WITH permission_set=SAFEGO----DROP ASSEMBLY SqlServerProject1--go--建立CLR預存程序 (程式集.命名空間.類型.方法名)CREATE PROC dbo.helloworldAS EXTERNAL NAME SqlServerProject1.[SqlServerProject1.demo].ClrProcgo--執行Clr預存程序EXEC helloworldgo

注意，之前的項目中編譯後的DLL檔案的路徑（推薦使用絕對路徑），許可權直接給safe。建立CLR預存程序，其實和普通的proc差不多，只不過多了一句（大致意思就是指定程式集中指定的命名空間下的類和對應的方法）

可以像調用普通的預存程序一樣去執行它。看看結果，果然有了”HelloWorld“。呵呵。當然，這隻是最簡單的，目的是教大家如何建立。

當然，也可以建立複雜一點的預存程序/函數/觸發器等。我就拿預存程序為例，做一個稍微複雜的，返回一個SqlDataReader對象。

直接在項目上右鍵，建立，選擇預存程序即可（剛才建立一個普通類，是為了讓大家理解其基本的實現和對應關係）。

建好預存程序後，可以往方法裡面添加一些簡單的代碼，執行一個基本的單表查詢，然後返回給用戶端一個SqlDataReader對象。代碼如下：

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class StoredProcedures{    [Microsoft.SqlServer.Server.SqlProcedure]    public static void StoredProcedure1()    {        // 在此處放置代碼        SqlConnection conn = new SqlConnection("context connection=true");        try        {            conn.Open();            SqlCommand cmd = new SqlCommand("SELECT count(*) FROM dbo.TempCatalog", conn);            SqlDataReader dr = cmd.ExecuteReader();            SqlContext.Pipe.Send(dr);        }        catch (Exception)        {            conn.Close();        }    }};

編譯完成後，可以在VS的產生菜單下選擇”部署“（會自動部署到sqlserver中，並覆蓋原來的）。當然，也可以手動去重新載入程式集並建立需要的預存程序。

在sqlserver中建立好並執行預存程序後，檢查一下結果，看是不是你想要的？

提示：手動重複載入、建立，可能會衝突。需要先刪除後再重新建立。可以在sqlserver物件瀏覽器中檢查，也可以使用如下sql語句查詢相關資訊。

SELECT * FROM sys.assembliesSELECT * FROM sys.assembly_files

簡單的例子做完了，優點是可想而知的。那到底啥時候應用CLR，啥時候寫T-sql呢？我覺得具體要根據實際業務需求和應用情境去判斷了。

根據有些微軟方面的專家提示，在下面幾種情況下，應該考慮使用CLR： 
SQL中涉及大量的邏輯判斷和邏輯運算。比如需要在資料庫層級自訂密碼編譯演算法，解密演算法等。 
T-SQL無法處理需求。比如需要在SQL中進行Regex的判斷等。 
邏輯判斷或者迴圈分支過於複雜，有時需要使用大量遊標進行處理（也不一定使用遊標就會變慢，關鍵看敲代碼的人）。

本文出自http://blog.csdn.net/dinglang_2009，轉載請註明出處。