SQL Server 2005 CLR 整合開發：Visual Studio 建立與部署 CLR 預存程序實戰

SQL Server 2005 CLR 整合：Visual Studio 專案建立、編碼、編譯與部署完整流程

開發環境與工具

• Visual Studio 2005
• SQL Server 2005（啟用 CLR 整合）
• .NET Framework 2.0+

建立 CLR 預存程序專案步驟

1. 新增專案項目

• 「加入新項目」對話框 → 範本選擇 **Stored Procedure**
• 「名稱」欄位輸入預存程序名稱（如 usp_ReadTextFile）
• 點擊「加入」

2. 自動產生的專案結構

• 類別檔案命名與預存程序名稱相同（usp_ReadTextFile.cs）
• 預設類別名稱：**StoredProcedures**
• 方法結構已建構，附加 [SqlProcedure] 屬性
• 自動包含必要的 using 陳述式（Microsoft.SqlServer.Server, System.Data.SqlClient 等）

圖 11 - 自動產生的 SQL Server CLR 預存程序類別

3. 編碼實作邏輯

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

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void usp_ReadTextFile(string filePath)
    {
        // 實作檔案讀取邏輯
        using (StreamReader sr = new StreamReader(filePath))
        {
            string line;
            while ((line = sr.ReadLine()) != null)
            {
                SqlContext.Pipe.Send(line);  // 直接輸出至用戶端
            }
        }
    }
}

4. 編譯專案

• Visual Studio 選單：「建置」→「建置方案」
• 產出組件（.dll）檔案

圖 12 - 建置 SQL Server CLR 預存程序

部署至 SQL Server

1. 啟用 CLR 整合：EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
2. 建立組件：CREATE ASSEMBLY MyAssembly FROM 'C:\Path\MyAssembly.dll' WITH PERMISSION_SET = SAFE;
3. 建立預存程序：CREATE PROCEDURE usp_ReadTextFile @filePath NVARCHAR(260) AS EXTERNAL NAME MyAssembly.[StoredProcedures].usp_ReadTextFile
4. 授權執行權限：GRANT EXECUTE ON usp_ReadTextFile TO [User/Role];

CLR 整合優勢與適用場景

權限集合選擇指南

最佳實務建議

1. **僅在必要時使用 CLR**：T-SQL 能解決的集合操作優先用 T-SQL
2. **嚴格限制權限集**：預設 SAFE，確有外部存取需求才升級 EXTERNAL_ACCESS
3. **完善錯誤處理**：CLR 例外會中斷批次，需 try-catch 包裹並轉換為 SqlError
4. **效能基準測試**：對比 T-SQL 同邏輯實作，確認 CLR 確有優勢才採用
5. **版本管理**：組件版本號與資料庫物件版本同步，支援回滾

版本演進提示

• SQL Server 2005：首度引入 CLR 整合（.NET 2.0）
• SQL Server 2012+：支援 .NET 4.0，效能提升
• SQL Server 2019+：支援 .NET Core / .NET 5+（預覽），跨平台一致性
• Azure SQL Database：支援 SAFE/EXTERNAL_ACCESS，UNSAFE 受限