SQL Server 2005 CLR 整合程式設計入門：從建立資料庫到部署組件

前置作業：建立範例資料庫與資料表

1. SSMS 連線實例 → 右鍵 Databases → New Database → 命名 Sample。
2. 展開 Sample → 右鍵 Tables → New Table → 建立 emp 表：

CREATE TABLE emp (
    empno INT PRIMARY KEY,
    ename NVARCHAR(50),
    sal DECIMAL(10,2),
    deptno INT
);

建立 CLR 專案與類別

1. Visual Studio → SQL Server 專案類型 → 「SQL Server Database Project」或「Class Library」。
2. 加入類別 EmployeeClr，撰寫靜態方法並標記 [SqlFunction]/[SqlProcedure]：

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class EmployeeClr
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDecimal GetAvgSalary(SqlInt32 deptNo)
    {
        using (var conn = new System.Data.SqlClient.SqlConnection("context connection=true"))
        {
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT AVG(sal) FROM emp WHERE deptno = @deptno";
            cmd.Parameters.AddWithValue("@deptno", deptNo.Value);
            return new SqlDecimal((decimal)cmd.ExecuteScalar());
        }
    }

    [SqlProcedure]
    public static void InsertEmployee(SqlInt32 empno, SqlString ename, SqlDecimal sal, SqlInt32 deptno)
    {
        using (var conn = new System.Data.SqlClient.SqlConnection("context connection=true"))
        {
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO emp VALUES (@empno, @ename, @sal, @deptno)";
            cmd.Parameters.AddWithValue("@empno", empno.Value);
            cmd.Parameters.AddWithValue("@ename", ename.Value);
            cmd.Parameters.AddWithValue("@sal", sal.Value);
            cmd.Parameters.AddWithValue("@deptno", deptno.Value);
            cmd.ExecuteNonQuery();
        }
    }
}

部署與測試

1. 專案屬性 → Database → 設定目標連線字串 (Sample)。
2. Build → Deploy，VS 自動註冊組件、建立 T-SQL 包裝物件。
3. SSMS 測試：

-- 測試函數
SELECT dbo.GetAvgSalary(10);

-- 測試預存程序
EXEC dbo.InsertEmployee 999, N'測試員工', 50000, 20;
SELECT * FROM emp WHERE empno = 999;

關鍵設定說明

• context connection=true：使用目前工作階段的內部連線，效能最佳、自動參與交易。
• DataAccessKind.Read / ReadWrite：宣告函數/程序是否存取資料，影響部署權限檢查。
• 組件權限集：SAFE (預設) 僅允許內部運算；需跨資料庫/外部資源時設為 EXTERNAL_ACCESS 並簽署強名稱。
• 除錯：VS 可直接對 SQLCLR 程式碼設定中斷點，支援單步執行、變數監看。

適用場景

• 複雜數學/統計運算 (T-SQL 難以表達)。
• 字串處理、正則表達式、XML/JSON 剖析。
• 自訂彙總函數、資料表值函數。
• 封裝 .NET Framework/Bcl 強大功能 (加密、壓縮、編碼)。