SQL Server 2005 - 如何從 CLR 預存程序傳回表格結果與訊息（下） | 章立民研究室 - 點部落

2009-11-13

SQL Server 2005 - 如何從 CLR 預存程序傳回表格結果與訊息（下）

摘要:SQL Server 2005 - 如何從 CLR 預存程序傳回表格結果與訊息（下）

親愛的朋友們，閱讀過上一篇文章「如何從 CLR 預存程序傳回表格結果與訊息（上）」之後，相信大家已經瞭解如何使用 SqlPipe 物件的 Send 方法將文字串直接傳送至用戶端，接下來我們將示範如何使用 SqlPipe 物件的 ExecuteAndSend 方法來執行 SqlCommand 並將查詢結果直接傳送至用戶端：

q  類別庫專案 SendYouResultSet 實作 CLR 預存程序，示範如何使用 SqlPipe 物件的 ExecuteAndSend 方法將查詢結果直接傳送至用戶端。事實上，它會接收兩個數值參數，以便查詢出「章立民研究室」資料表中介於該薪資範圍的員工資料：

using Microsoft.SqlServer.Server;

namespace SendYouResultSet
{
public class SendYouResultSetClass
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SendYouResultSet(
    decimal LowerSalary, decimal HigherSalary)
{
   SqlCommand command;

   // 使用內容連接來進行連接。
   using (SqlConnection connection =
     new SqlConnection("context connection=true"))
   {
    connection.Open();

    command = new SqlCommand(
      "SELECT 員工編號,姓名,目前薪資 FROM dbo.章立民研究室 " +
      "WHERE 目前薪資 BETWEEN @LowerSalary AND @HigherSalary",
      connection);
    command.Parameters.AddWithValue("@LowerSalary", LowerSalary);
    command.Parameters.AddWithValue("@HigherSalary", HigherSalary);

    // 呼叫 ExecuteAndSend 方法執行 SqlCommand
    // 並查詢結果直接傳送給用戶端
    SqlContext.Pipe.ExecuteAndSend(command);
   }
}
}
}

請大家注意，此 CLR 預存程序的兩個參數都是輸入參數，因此使用傳值方式來傳遞。另外請特別注意，CLR 預存程序是使用「內容連接」來連接至 SQL Server（亦即程式中的 SqlConnection("context connection=true")）。

q  請繼續執行如下所列之 SQL 指令碼，登錄組件 SendYouResultSet.dll、建立一個參考所登錄之組件 SendYouResultSet.dll 的預存程序，最後啟動此 CLR 預存程序。從圖表 1 的執行畫面可以看出，SQL Server 2005 成功利用組件所建構之 CLR 預存程序，透過 SqlPipe 物件來執行 SqlCommand，並將查詢結果直接傳送至前端：

USE 北風貿易;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'SendYouResultSet')
DROP PROCEDURE SendYouResultSet;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'SendYouResultSet')
DROP ASSEMBLY SendYouResultSet;
GO

-- 登錄組件SendYouResultSet.dll
CREATE ASSEMBLY SendYouResultSet
FROM
'C:SQL2005DemoCH13SendYouResultSetSendYouResultSetbinSendYouResultSet.dll'
WITH permission_set = Safe;
GO

-- 建立一個會參考所登錄之組件SendYouResultSet.dll 的預存程序
CREATE PROCEDURE dbo.SendYouResultSet
(
@LowerSalary money, @HigherSalary money
)
AS EXTERNAL NAME
SendYouResultSet.[SendYouResultSet.SendYouResultSetClass].SendYouResultSet;
GO

EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

DECLARE @return_value int

-- 執行CLR 預存程序
EXECUTE @return_value = dbo.SendYouResultSet
        @LowerSalary = 50000,
        @HigherSalary = 70000;

圖表 1

本文節錄並修改自「SQL Server 2005 完全實戰」一書。

參考書籍：「Visual C# 2005 檔案 IO 與資料存取秘訣」

章立民研究室 2007/1/12

回首頁