SQL Server 2005+ 整合 **Common Language Runtime (CLR)**，允許使用 C#/VB.NET 撰寫預存程序、觸發器、使用者定義函數/彙總/類型。典型場景：**檔案系統存取、正則表達式、複雜數學運算、外部 API 呼叫、加密/壓縮** 等 T-SQL 難以或無法高效完成的工作。本文示範建立 CLR 預存程序回傳標準結果集 (資料列/欄位)。
-- 方法 1: sp_configure (需 sysadmin)
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
-- 方法 2: 外圍應用配置工具 (Surface Area Configuration)
-- 啟動 → 功能 → CLR 整合 → 勾選「啟用 CLR 整合」
using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void ListDirectoryFiles(string directoryPath)
{
// 1. 定義結果集結構 (SqlMetaData 陣列)
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("FileName", SqlDbType.NVarChar, 255),
new SqlMetaData("FileSize", SqlDbType.BigInt),
new SqlMetaData("CreationTime", SqlDbType.DateTime),
new SqlMetaData("LastWriteTime", SqlDbType.DateTime),
new SqlMetaData("Extension", SqlDbType.NVarChar, 50)
};
// 2. 建立 SqlDataRecord 可重複使用
SqlDataRecord record = new SqlDataRecord(metaData);
// 3. 宣告結果集開始
SqlContext.Pipe.SendResultsStart(record);
try
{
// 4. 驗證路徑
if (!Directory.Exists(directoryPath))
{
SqlContext.Pipe.Send($"Directory not found: {directoryPath}");
return;
}
// 5. 列舉檔案並逐筆送出
foreach (string file in Directory.GetFiles(directoryPath))
{
FileInfo fi = new FileInfo(file);
record.SetString(0, fi.Name);
record.SetInt64(1, fi.Length);
record.SetDateTime(2, fi.CreationTime);
record.SetDateTime(3, fi.LastWriteTime);
record.SetString(4, fi.Extension);
SqlContext.Pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send($"Error: {ex.Message}");
}
finally
{
// 6. 宣告結果集結束 (必要！)
SqlContext.Pipe.SendResultsEnd();
}
}
}
| API | 用途 |
|---|---|
SqlContext.Pipe.SendResultsStart(SqlDataRecord) | 宣告結果集結構定義，開始串流模式 |
SqlDataRecord.SetXxx(ordinal, value) | 設定單一資料列的欄位值 (可重複使用同一 Record 物件) |
SqlContext.Pipe.SendResultsRow(SqlDataRecord) | 送出單一資料列給用戶端 |
SqlContext.Pipe.SendResultsEnd() | 結束結果集串流，**必須呼叫**，否則用戶端掛起 |
SqlContext.Pipe.Send(string) | 送出訊息/錯誤 (對應 PRINT/RAISERROR)，不影響結果集 |
-- 1. 建立組件 (PERMISSION_SET = EXTERNAL_ACCESS 需資料庫 TRUSTWORTHY 或簽署非對稱金鑰)
CREATE ASSEMBLY FileUtils
FROM 'C:\Deploy\FileUtils.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
-- 2. 建立預存程序包裝器
CREATE PROCEDURE usp_ListDirectoryFiles
@DirectoryPath NVARCHAR(260)
AS EXTERNAL NAME FileUtils.[StoredProcedures].ListDirectoryFiles;
GO
EXEC usp_ListDirectoryFiles 'C:\Windows\Temp';
輸出標準結果集：
FileName FileSize CreationTime LastWriteTime Extension
----------------- --------- --------------------- --------------------- ---------
test.txt 1024 2024-01-15 10:30:00 2024-01-15 10:30:00 .txt
log_20240115.log 4096 2024-01-15 08:00:00 2024-01-15 12:00:00 .log
...
CLR 預存程序透過 `SqlContext.Pipe.SendResultsStart/Row/End` 模式，能以 **標準結果集** 形式將 .NET 強大功能 (檔案系統、網路、運算) 無縫整合進 SQL Server。**關鍵在於：權限集合最小化 (EXTERNAL_ACCESS)、資料流式回傳避免記憶體壓力、嚴格參數驗證防禦注入、自動化部署管線保障品質。** 現代架構中，此類跨界操作亦可考慮移至應用層微服務，視延遲容忍度與治理策略決策。
