VS2005 + C# + SQL Server 2005 打造 CLR 存儲過程完整教學

SQL Server 2005 整合 .NET CLR，允許以 C#/VB.NET 撰寫存儲過程、函數、觸發器、類型與聚合。本文系統梳理 CLR 存儲過程的需求、建立流程、結果返回方式與部署實戰，助你快速上手。

核心需求

• 類別必須 public，方法為 public static
• 回傳型別：void (回傳碼 0) 或 int (自訂回傳碼)
• 參數數量、型別需與 T-SQL 宣告一致，對應 SqlTypes 命名空間
• 組件需註冊為 SAFE / EXTERNAL_ACCESS / UNSAFE 權限集

三種結果返回模式

1. OUTPUT 參數

// C#
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PriceSum(out SqlInt32 value)
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT Price FROM Products", conn))
        using (SqlDataReader rd = cmd.ExecuteReader())
        {
            value = 0;
            while (rd.Read()) value += rd.GetSqlInt32(0);
        }
    }
}

// T-SQL 部署
CREATE ASSEMBLY TestStoredProc FROM 'C:\TestStoredProc.dll';
CREATE PROCEDURE PriceSum @sum INT OUTPUT
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum;

2. 訊息與表格結果 (SqlPipe)

透過 SqlContext.Pipe 發送：

• Send(string)：回傳訊息 (上限 8000 字元)
• ExecuteAndSend(SqlCommand)：直接將查詢結果流向客戶端，效能最佳
• Send(SqlDataReader)：預先處理資料再送出，彈性較高
• Send(SqlDataRecord)：動態建立結果集逐列送出

// 動態結果集範例
[SqlProcedure]
public static void SendTransientResultSet()
{
    var record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
    record.SetSqlString(0, "Hello World!");
    SqlContext.Pipe.Send(record);
}

3. 綜合範例：訊息 + 查詢結果

[SqlProcedure]
public static void HelloWorld()
{
    SqlContext.Pipe.Send("Hello world! It's now " + DateTime.Now + "\n");
    using (var conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        var cmd = new SqlCommand("SELECT ProductNumber FROM ProductMaster", conn);
        SqlContext.Pipe.ExecuteAndSend(cmd); // 最高效
    }
}

部署步驟總覽

1. 編譯 DLL：csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs
2. 註冊組件：CREATE ASSEMBLY MyFirstUdp FROM 'C:\Path\MyFirstUdp.dll' WITH PERMISSION_SET = SAFE;
3. 建立程序：CREATE PROCEDURE HelloWorld AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;
4. 測試執行：EXEC HelloWorld;

避坑指南

• ❌ 不要用 Console.WriteLine，必須用 SqlContext.Pipe.Send
• ✅ 類別與方法皆為 public static
• ✅ 命名空間非必要，但建議保留結構清晰
• ✅ 連接字串固定用 context connection=true 走進程內通道
• ✅ 大量資料搬移優先 ExecuteAndSend，避免記憶體壓力

CLR 存儲過程適用於複雜運算、正則表達式、外部資源存取等 T-SQL 不擅長的場景；單純 CRUD 仍以純 T-SQL 為宜。善用兩者優勢互補，打造高效能資料庫層。