SQL Server 2005 - 實作 CLR 預存程序 | 章立民研究室 - 點部落

2009-11-13

SQL Server 2005 - 實作 CLR 預存程序

摘要:SQL Server 2005 - 實作 CLR 預存程序

「預存程序」（Stored Procedures）是SQL Server 2005程式設計中最重要的一環，您將會發現，應用系統中的許多作業都會交由預存程序來完成。在以下的操作步驟中，我們將帶領大家利用Visual Studio 2005整合式開發環境，實際建立一個CLR預存程序（採用Visual C#），並示範如何使用它：

1. 如圖表1所示，選取Microsoft Visual Studio 2005功能表指令來開啟Visual Studio 2005的整合式開發環境。

圖表 1

2. 從「專案」功能表中選取「新增／專案」指令。

3. 如圖表2所示，選取「類別庫」範本，然後依序指定專案名稱（SayHello）、存放位置、以及方案名稱（SayHello）之後，按一下「確定」按鈕。

圖表 2

4. 於Visual Studio 2005整合式開發環境的「方案總管」中，使用滑鼠右鍵按一下預設建立的 Class1.cs 並從快顯功能表中選取「刪除」指令以便將它刪除。

5. 從「專案」功能表中選取「加入新項目」指令。

6. 如圖表3所示，於「加入新項目」對話方塊中，選取「類別」範本，然後以 SayHelloClass.cs 作為類別檔的檔案名稱，然後按一下「加入」按鈕。

圖表 3

7.         於程式碼編輯器中，將SayHelloClass類別的程式碼改寫如下：

using Microsoft.SqlServer.Server;

namespace SayHello
{
public class SayHelloClass
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SayHello(ref string greeting)
{
   SqlMetaData columnInfo = new SqlMetaData("問候", SqlDbType.NVarChar, 12);

   SqlDataRecord greetingRecord = new SqlDataRecord(new SqlMetaData[] { columnInfo });

   greetingRecord.SetString(0, "大家好，我是章立民！");

   // 呼叫 Pipe 物件的 Send 方法將單一資料列結果集（也就是 SqlDataRecord 物件）
   // 直接傳送給用戶端。
   SqlContext.Pipe.Send(greetingRecord);

   // 將文字串指派給輸出參數。
   greeting = "現在時間是 " + DateTime.Now.ToString() + " --- 章立民研究室向您問好！";
}
}
}

上述程式碼中的SayHello程序是一個公用靜態方法，而它也就是CLR預存程序所實際呼叫的對象。

8. 按一下工具列中的「儲存」按鈕。

9. 如圖表4所示，使用滑鼠連按兩下「方案總管」中的Properties節點，一開始會顯示出「應用程式」索引標籤頁面，您可以去設定組件名稱與跟命名空間。不過以本實作演練而言，請採用預設值，不要更改。

圖表 4

10. 按一下左側的「編譯」索引標籤來切換至其頁面，並且將「建置輸出路徑」設定成 bin（如圖表5所示），然後按一下工具列中的「儲存」按鈕。

圖表 5

11. 從「建置」功能表中選取「建置SayHello」指令。

12. 好的，我們現在已經完成組件的建置作業，現在必須回到SQL Server Management Studio中，以便登錄組件並建立指向該組件的預存程序。

13.     請您於SQL Server Management Studio中執行以下的程式碼，以便使用CREATE ASSEMBLY陳述式來登錄組件SayHello.dll，然後使用CREATE PROCEDURE陳述式建立一個會參考所登錄之組件SayHello.dll的預存程序：

USE 北風貿易;
GO

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

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

-- 登錄組件 SayHello.dll（請務必確認組件所在路徑正確）
CREATE ASSEMBLY SayHello
FROM 'C:SQL2005DemoCH13SayHelloSayHellobinSayHello.dll'
WITH permission_set = Safe;    ｜
GO                                                      ﹂──＞組件的位置路徑

-- 建立一個會參考所登錄之組件 SayHello.dll 的預存程序
CREATE PROCEDURE dbo.SayHello
(
    @Greeting nvarchar(80) OUTPUT
)
AS EXTERNAL NAME SayHello.[SayHello.SayHelloClass].SayHello;
GO                                     ↓
                                 [命名空間.類別名稱]

14.     請您繼續於SQL Server Management Studio中執行以下的程式碼，以便嘗試執行我們所建立的CLR預存程序（執行結果如圖表6所示）：

USE 北風貿易;
GO

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

DECLARE @return_value int, @Greeting nvarchar(80);

-- 執行CLR預存程序並取得傳回值與輸出變數的值
EXECUTE @return_value = dbo.SayHello
        @Greeting = @Greeting OUTPUT;

-- 檢視傳回值與輸出變數的值
SELECT @return_value AS 傳回值,
               @Greeting AS "輸出參數 @Greeting 之值";

圖表 6

如果有朋友希望瞭解 Visual Basic 語法的 CLR 預存程序之撰寫與使用方法，則請參考章立民研究室出版的資料庫最佳書籍－「SQL Server 2005 完全實戰」，將有完整的章節詳述內容。

章立民研究室 2007/1/10

回首頁