如何在SQL Server中加入
C# CLR Function

Microsoft SQL Server除了提供豐富的日期時間、數值、字串以及各種類型的函數外，可以自訂使用者函數。但是，寫過Microsoft SQL Server自訂函數就會知道，SQL Server Transact-SQL提供的流程控制語法較少(僅有IF, WHILE, GOTO)，缺少.NET BCL(Base Class Library)數以萬計的函式庫支援，寫一些較為複雜的功能時，就顯得有些捉襟見肘了。為增加這個擴充能力，SQL Server 2005(及之後的版本)提出了一個解決方案—CLR Function。

CLR Function可以在Visual Sutdio中，利用.NET的程式語言開發SQL Server所需的函式(Function)、預存程序(Stored Procedure)甚至是觸發程序(Trigger)等物件，連結至SQL Server資料庫中，在SQL語法中執行。例如，在C#中寫一個MD5加密功能，對一個具備C#程式設計基礎的人不是太困難的一件事情(提示：使用Google，鍵入關鍵字”MD5 C#”，會有相當多的程式範例可參考)，但是在SQL Server中要寫出這樣的函式，可就不是那麼簡單了。以下就以這個在SQL Server中加入MD5加密功能為例，來介紹『如何在SQL Server中加入C# CLR Function』。

要完成這個程式之前，請先準備以下的應用程式：

XX【MAC address的第四碼】的產生原則，是vCenter Server在安裝或是deploy時，會從 0-63，隨機挑一個數字。

• Microsoft SQL Server 2016 Developer Edition
  (也可以使用SQL Server Express版本，但必須做過測試，因為在一些彙總函數的CLR Function不支援，都可以在微軟官方網站免費下載)。
• SQL Server Management Studio 2016
  (可以在微軟官方網站免費下載)。
• Microsoft Visual Studio Community 2015
  (可以在微軟官方網站免費下載)
• SQL Server Data Tools
  (SSDT: SQL Server的附加套件，可以在微軟官方網站下載)。

以下有幾個步驟：

步驟一、在Visual Studio Community 2015中完成CLR_Function.DLL組件。進入Visual Studio，開啟新專案，使用 SQL Server範本，選擇SQL Server資料庫專案，方案名稱：CLR_Solution，名稱：CLR_Function。

按下確定後，在方案總管中，CLR_Function上右鍵，選擇加入，選擇新增項目。

跳出的視窗中，選擇 SQL CLR C#以及SQL CLR C#使用者定義函式，名稱中輸入：MD5_Encrypt.cs，按新增。

完成以下的MD5加密程式碼。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Security.Cryptography;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString MD5_Encrypt(SqlString s)
    {
        // 將程式碼放在此處
        string hash = "";
        MD5CryptoServiceProvider md5provider = new MD5CryptoServiceProvider();
        byte[] bytes = md5provider.ComputeHash(new UTF8Encoding()
                                                .GetBytes(s.ToString()));
        for (int i = 0; i < bytes.Length; i++)
        {
            hash += bytes[i].ToString("x2");
        }        
        return new SqlString (hash.ToString());
    }
}

在方案總管中，編譯成 CLR_Function.DLL。

步驟二、在SQL Server Managemant Studio中將CLR_Function.DLL加入Database的Assembly。

進入SSMS中，建立資料庫 db01，在物件瀏覽器中，展開databases=>db01=>Programmability=>Assembly上按右鍵，New Assembly，建立新的組件。

在開啟的視窗中，選取Browse選擇在前一步驟完成的 CLR_Function.DLL。

此時在物件瀏覽器中會新增一個組件CLR_Function。

執行以下的SQL語法：

USE db01;
GO
CREATE FUNCTION dbo.MD5Encrypt(@src nvarchar(100))
RETURNS nvarchar(1000)
EXTERNAL NAME CLR_Function.UserDefinedFunctions.MD5_Encrypt
GO

建立CLR Function的語法：

CREATE FUNCTION dbo.MD5Encrypt(@src nvarchar(100))
RETURNS nvarchar(1000)

dbo.MD5Encrypt是指在SQL中建立的函式名稱，其回傳值、參數值的型別與個數必須與C#中定義的函式簽章(Signature)一致。

EXTERNAL NAME CLR_Function.UserDefinedFunctions.MD5_Encrypt

EXTERNAL NAME的定義為 [組件名稱].[類別名稱].[方法名稱]。

步驟三、測試CLR Function。

USE db01;
GO
SELECT dbo.MD5Encrypt(N’Hello world!’);

執行結果：