摘要:SQL Server 2005 - 如何從 CLR 預存程序傳回表格結果與訊息（上）
親愛的朋友們，閱讀過「實作CLR預存程序」一文之後，相信大家對於如何建立 CLR 預存程序已經有了清楚的認識。在本文中，我們要繼續討論一個非常重要的課題，那就是如何從 CLR 預存程序傳回表格結果與訊息。
除了透過輸出參數傳回資料之外，我們還可以使用 SqlPipe 物件將表格結果與訊息傳回給用戶端。一般來說，我們是透過 SqlContext 類別的 Pipe 屬性取得 SqlPipe 物件，然後呼叫 SqlPipe 物件的 Send 方法將表格結果或訊息傳送給用戶端。
SqlPipe 物件的 Send 方法擁有下列三個多載版本：
q SqlPipe.Send(message As String)
此多載版本的 Send 方法能夠將字串訊息直接傳送至用戶端。文字訊息最長不可超過 8000 個字元，超出 8000 個字元的部分將會被截掉。
訊息字串會被當作一個資訊訊息傳送給用戶端，至於用戶端要透過哪一種機制來聆聽此訊息，則視用戶端所採用的資料存取應用程式介面（API）而定。比方說，假設用戶端使用 ADO.NET，則訊息會透過事件 InfoMessage 來傳送。
q SqlPipe.Send(record As SqlDataRecord)
此多載版本的 Send 方法能夠將單一資料列結果集（也就是一個 SqlDataRecord 物件）直接傳送至用戶端。如果您於 Send 方法中所使用的 SqlDataReader 物件擁有隱藏欄位，這些欄位將不會填入傳送給用戶端的結果集當中。
以前一篇文章所建立的CLR預存程序 SayHello 而言，它除了使用輸出參數來傳回文字資料之外，還呼叫此多載版本的 Send 方法將單一資料列結果集直接傳送至用戶端，如下所示：
SqlContext.Pipe.Send(greetingRecord)
q SqlPipe.Send(reader As SqlDataReader)
此多載版本的 Send 方法能夠將一個多資料列結果集（也就是一個 SqlDataReader 物件）直接傳送至用戶端。
如果您要傳送先前透過同處理序（In-Process）提供者所執行的查詢結果，或是想要使用 SqlDataReader 的自訂實作來預先處理資料，請使此多載版本的 Send 方法來接收一個 SqlDataReader 物件並將其直接傳送給用戶端。雖然 Send 方法的執行效率稍稍慢於ExecuteAndSend 方法，不過在資料被傳送至用戶端之前，它提供了最大的彈性來處理資料。
請大家注意，除了使用 Send 方法之外，CLR 預存程序還可以使用 SqlPipe 物件的 ExecuteAndSend 方法將查詢結果傳送給用戶端。ExecuteAndSend 方法的最大好處，就是它提供了一種最高效率的方式將查詢結果傳送給用戶端，之所以如此，是因為資料是透過網路緩衝區來傳送，而不需被複製到受管理的記憶體中。
以下是 ExecuteAndSend 方法的語法：
SqlPipi.ExecuteAndSend (command As SqlCommand)
從語法可以看出，ExecuteAndSend 方法其實是以一個 SqlCommand物件作為其參數，它會執行 SqlCommand 物件並將結果傳送至用戶端。大家必須瞭解，除了所有實際的執行結果之外，其他的訊息與錯誤也會直接傳送給用戶端。
程式範例
以下我們示範如何使用 SqlPipe 物件的 Send 方法將文字串直接傳送至用戶端：
q 下面這一個 CLR 預存程序存放於類別庫專案 GiveYouString 中，它示範如何使用 SqlPipe 物件的 Send 方法將文字串直接傳送至用戶端：
namespace GiveYouString
{
public class GiveYouStringClass
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void GiveYouString()
{
// 呼叫 Pipe 物件的 Send 方法將文字串直接傳送給用戶端。
SqlContext.Pipe.Send("章立民研究室");
}
}
}
請注意，這一個CLR預存程序並沒有任何參數。
q 請執行如下所示之 SQL 指令碼，登錄組件 GiveYouString.dll、建立一個參考所登錄之組件 GiveYouString.dll 的預存程序、然後執行此 CLR 預存程序：
USE 北風貿易;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] =
'GiveYouString')
DROP PROCEDURE GiveYouString;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] =
'GiveYouString')
DROP ASSEMBLY GiveYouString;
GO
-- 登錄組件GiveYouString.dll
CREATE ASSEMBLY GiveYouString
FROM 'C:SQL2005DemoCH13GiveYouStringGiveYouStringbinGiveYouString.dll'
WITH permission_set = Safe;
GO
-- 建立一個會參考所登錄之組件GiveYouString.dll的預存程序
CREATE PROCEDURE dbo.GiveYouString
AS EXTERNAL NAME GiveYouString.[GiveYouString.GiveYouStringClass].GiveYouString;
GO
EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO
DECLARE @return_value int
-- 執行CLR預存程序
EXECUTE @return_value = dbo.GiveYouString;
q 在您執行了上述 SQL 指令後將會發現， 執行CLR 預存程序 GiveYouString 之後並無法取得所傳回的文字串。事實上我們之前就曾經提及，SqlPipe 物件之 Send 方法所傳回的訊息字串會被當作一個資訊訊息傳送給用戶端，至於用戶端要透過哪一種機制來聆聽此訊息，則視用戶端所採用的資料存取應用程式介面（API）而定。如果用戶端使用 ADO.NET，則訊息會透過事件 InfoMessage來傳送。
為了示範如何在前端應用程式中透過 ADO.NET 取得 SqlPipe 物件之 Send 方法所傳回的訊息字串，我們特別設計了一個Windows應用程式 TakeReturnString 來加以實作。從圖表1的執行畫面可以看出，我們果然能夠順利取得 CLR 預存程序使用 SqlPipe 物件之 Send 方法所傳回的文字串。
相關的前端程式碼列示如下：
private void btnRun_Click(object sender, EventArgs e)
{
// 利用 SqlConnectionStringBuilder 物件來構建連接字串。
SqlConnectionStringBuilder connectStringBuilder = new
SqlConnectionStringBuilder();
connectStringBuilder.DataSource = @"(local)";
connectStringBuilder.InitialCatalog = "北風貿易";
connectStringBuilder.IntegratedSecurity = true;
try
{
using(SqlConnection northwindConnection = new
SqlConnection(connectStringBuilder.ConnectionString))
{
northwindConnection.InfoMessage += new
SqlInfoMessageEventHandler(OnInfoMessage);
northwindConnection.Open();
SqlCommand cmdLiming = new SqlCommand("dbo.GiveYouString",
northwindConnection);
cmdLiming.CommandType = CommandType.StoredProcedure;
cmdLiming.ExecuteNonQuery();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private static void OnInfoMessage(object sender,
SqlInfoMessageEventArgs args)
{
// 顯示 CLR 預存程序利用 SqlPipe.Send 方法所傳回的文字串。
MessageBox.Show(args.Message);
}
圖表 1
本文節錄並修改自「SQL Server 2005 完全實戰」
一書。
參考書籍：「Visual C# 2005 檔案 IO 與資料存取秘訣」
章立民研究室