SQL Server CLR 實戰指南（二）：CLR 預存程序開發與部署全流程

CLR 預存程序核心類別與方法

七大 CLR 預存程序實作範例

1. 無參無回傳：簡單訊息輸出

[Microsoft.SqlServer.Server.SqlProcedure]
public static void USP_SayHello()
{
    SqlContext.Pipe.Send("USP: Hello from CLR!");
}

2. 有參無回傳：參數透傳

[Microsoft.SqlServer.Server.SqlProcedure]
public static void USP_SayHelloByParameter(SqlString msg)
{
    SqlContext.Pipe.Send(msg.ToString());
}

3. 有參有回傳：純量返回值

[Microsoft.SqlServer.Server.SqlProcedure]
public static SqlInt32 USP_SayHelloByReturn(SqlString msg)
{
    return msg.ToString().Length;  // 返回字串長度
}

4. SqlCommand 執行 DML（傳統方式）

[Microsoft.SqlServer.Server.SqlProcedure]
public static void USP_ExecuteBySqlCommand(SqlString stor_id, SqlString stor_name)
{
    using (SqlConnection con = new SqlConnection("context connection=true"))
    {
        con.Open();
        SqlCommand com = new SqlCommand(
            string.Format("INSERT INTO stores VALUES('{0}','{1}')", stor_id, stor_name), con);
        com.ExecuteNonQuery();
    }
}

5. ExecuteAndSend 高效執行（推薦）

[Microsoft.SqlServer.Server.SqlProcedure]
public static void USP_ExecuteByExecuteAndSend(SqlString stor_id, SqlString stor_name)
{
    using (SqlConnection con = new SqlConnection("context connection=true"))
    {
        con.Open();
        SqlCommand com = new SqlCommand(
            string.Format("INSERT INTO stores VALUES('{0}','{1}')", stor_id, stor_name), con);
        SqlContext.Pipe.ExecuteAndSend(com);  // 結果直接串流回用戶端，不經托管記憶體
    }
}

效能差異：ExecuteAndSend 避免資料複製到 .NET 記憶體再封包，直接由 SQLOS 串流至 TDS 協議層，大量資料回傳時效能優勢明顯。

6. Send 逐筆傳送多個結果集

[Microsoft.SqlServer.Server.SqlProcedure]
public static void UPS_PipeSendSqlDataRecord()
{
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
        new SqlMetaData("Col1", SqlDbType.NVarChar, 100),
        new SqlMetaData("Col2", SqlDbType.Int)
    });

    for (int i = 1; i < 5; i++)
    {
        record.SetString(0, i.ToString());
        record.SetInt32(1, i);
        SqlContext.Pipe.Send(record);  // 每次 Send 產生一個獨立結果集
    }
}

7. SendResults 傳送單一結果集多行（標準模式）

[Microsoft.SqlServer.Server.SqlProcedure]
public static void UPS_PipeSendResultSqlDataRecord()
{
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
        new SqlMetaData("Col1", SqlDbType.NVarChar, 100),
        new SqlMetaData("Col2", SqlDbType.Int)
    });

    SqlContext.Pipe.SendResultsStart(record);  // 定義結構描述

    for (int i = 0; i < 5; i++)
    {
        record.SetString(0, i.ToString());
        record.SetInt32(1, i);
        SqlContext.Pipe.SendResultsRow(record);  // 填充資料列
    }

    SqlContext.Pipe.SendResultsEnd();  // 結束並回傳單一結果集
}

差異：範例 6 回傳 5 個單行結果集；範例 7 回傳 1 個 5 行結果集。應用端處理方式完全不同。

CLR 組件部署 T-SQL 腳本

USE pubs;
GO

-- 1. 建立組件（假設 DLL 位於 C:\CLRDemo.dll）
CREATE ASSEMBLY CLRDemoAssembly
FROM 'C:\CLRDemo.dll'
WITH PERMISSION_SET = SAFE;  -- 或 EXTERNAL_ACCESS/UNSAFE 視權限需求
GO

-- 2. 建立預存程序對應進入點
-- 格式：EXTERNAL NAME AssemblyName.[Namespace.]ClassName.MethodName
CREATE PROC USP_SayHello 
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.USP_SayHello;
GO

CREATE PROC USP_SayHelloByParameter (@Msg NVARCHAR(128))
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.USP_SayHelloByParameter;
GO

CREATE PROC USP_SayHelloByReturn (@Msg NVARCHAR(128))
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.USP_SayHelloByReturn;
GO

CREATE PROC USP_ExecuteBySqlCommand (@Id NVARCHAR(4), @Name NVARCHAR(32))
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.USP_ExecuteBySqlCommand;
GO

CREATE PROC USP_ExecuteByExecuteAndSend (@Id NVARCHAR(4), @Name NVARCHAR(32))
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.USP_ExecuteByExecuteAndSend;
GO

CREATE PROC UPS_PipeSendSqlDataRecord
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.UPS_PipeSendSqlDataRecord;
GO

CREATE PROC UPS_PipeSendResultSqlDataRecord
AS EXTERNAL NAME CLRDemoAssembly.StoredProcedures.UPS_PipeSendResultSqlDataRecord;
GO

呼叫與移除

-- 呼叫
EXEC USP_SayHello;
EXEC USP_SayHelloByParameter 'Hello CLR';
DECLARE @Len INT; EXEC @Len = USP_SayHelloByReturn 'Test'; SELECT @Len;

-- 移除（順序：先程序後組件）
DROP PROC USP_SayHello;
DROP PROC USP_SayHelloByParameter;
DROP PROC USP_SayHelloByReturn;
DROP PROC USP_ExecuteBySqlCommand;
DROP PROC USP_ExecuteByExecuteAndSend;
DROP PROC UPS_PipeSendSqlDataRecord;
DROP PROC UPS_PipeSendResultSqlDataRecord;
GO
DROP ASSEMBLY CLRDemoAssembly;
GO

命名空間處理

若 C# 程式碼有命名空間（如 namespace MyCLR { class StoredProcedures { ... } }）：

CREATE PROC USP_SayHello 
AS EXTERNAL NAME CLRDemoAssembly.[MyCLR.StoredProcedures].USP_SayHello;
-- 類別全名需用方括號包裹

權限集合選擇指南