Transact-SQL 利用 CLR Stored Procedure 呼叫 Web Service (CLR Stored Procedure Call Web Service)

本文將介紹如何利用 SQL Server CLR Stored Procedure 呼叫 Web Service，並將回傳的 XML 資料寫入資料庫，以 SQL 指令進行讀取作業。

測試環境

Visual Studio 2005, SQL Server 2005 Express (二者安裝於同一台 PC)


前置作業

於 SQL Server 中，建立名為 "TEST" 的資料庫及 "My_CLR_XML" Table。

create database TEST
go

use TEST
go

create table My_CLR_XML (XML_Data   xml)
go

利用 Visual Studio 2005 建立 CLR Stored Procedure (使用 C#)

1) 於 Visual Studio 2005 建立資料庫專案 (此處命名為 My_CLR_SP): 選取工具列 "檔案" --> "新增" --> "專案"

2) 出現 "新增專案" 視窗，請依下圖進行操作。

3) 此時將出現 "加入資料庫參考" 視窗，選擇一個 SQL Server 資料庫後，再點選 "確定"。

4) 將 http://www.webservicex.net/globalweather.asmx 加入該方案的 "參考" (CLR Stored Procedure 將呼叫此 Web Service):
4-1) 點選方案總管的 "參考" --> 按下滑鼠右鍵 --> "加入 Web 參考" 。

4-2) 於出現的 "加入 Web 參考" 視窗中，依下圖進行操作。

5) 此時會發現已將該 Web Service 加入此方案的 "Web References"。

6) 於建立的專案中，新增 CLR Stored Procedure: 點選方案總管的 "My_CLR_SP" --> 按下滑鼠右鍵 --> "加入" --> "新增項目" 。

7) 此時將出現 "加入新項目 - My_CLR_SP" 視窗，請依下圖進行操作 (將 Stored Procedure 命名為 usp_My_CLR_SP)。

8) 此時方案總管會出現 usp_My_CLR_SP.cs 。

9) 開啟 "usp_My_CLR_SP.cs"，並輸入以下內容。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using My_CLR_SP.net.webservicex.www;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void usp_My_CLR_SP()
    {
        // Call Web Service -------------------------------------------------------------------------------------------
        My_CLR_SP.net.webservicex.www.GlobalWeather ws = new My_CLR_SP.net.webservicex.www.GlobalWeather();

        string prm_counter_name = "Taiwan";

        string rtn_xml = ws.GetCitiesByCountry(prm_counter_name);


        // Write To DB ------------------------------------------------------------------------------------------------
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "Data Source=127.0.0.1\\SQLEXPRESS; Initial Catalog=TEST; User ID=sa; PWD=Your_Password; Pooling=True;";

        cn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into My_CLR_XML (XML_Data) values (" + "'" + rtn_xml + "'" + ")";
        cmd.ExecuteScalar();

        cn.Close();
    }
};

10) 點選工具列 "專案" --> "My_CLR_SP 屬性"，依以下附圖進行設定後，儲存該方案。

11) 點選工具列 "建置" --> "建置方案"，待畫面下方訊息列出現 "建置成功" 即可。

12) 於 C:\ 建立 "TEST" 目錄，並將 My_CLR_SP.dll 及 My_CLR_SP.XmlSerializers.dll 複製到 C:\TEST 。


編譯 CLR Stored Procedure

於 SQL Server 2005 中，執行以下指令以產生 CLR Stored Procedure: usp_My_CLR_SP

use TEST
go

exec sp_configure 'CLR Enabled' , '1'

reconfigure with override

alter database TEST set trustworthy on

exec sp_changedbowner 'sa'
go


-- Drop Existing Assemblies, Stored Procedure -------------------------------------------------------------------------
if exists (select * from sys.assemblies where name = N'My_CLR_SP.XmlSerializers') 
   drop assembly [My_CLR_SP.XmlSerializers]

if exists (select * from sys.procedures where name = 'usp_My_CLR_SP')
   drop procedure usp_My_CLR_SP;

if exists (select * from sys.assemblies where name = 'My_CLR_SP')
   drop assembly [My_CLR_SP];
go


-- Create Assembly: My_CLR_SP -----------------------------------------------------------------------------------------
create assembly [My_CLR_SP]
  from 'C:\TEST\My_CLR_SP.dll' with permission_set = external_access;
go


-- Create Assembly: My_CLR_SP.XmlSerializers Assembly -----------------------------------------------------------------
create assembly [My_CLR_SP.XmlSerializers] 
  from 'C:\TEST\My_CLR_SP.XmlSerializers.dll' with permission_set = external_access
go


-- Create CLR Stored Procedure: usp_My_CLR_SP -------------------------------------------------------------------------
create procedure usp_My_CLR_SP as external name My_CLR_SP.StoredProcedures.usp_My_CLR_SP;
go     


exec sp_configure 'CLR Enabled' , '0'

reconfigure with override
go

驗證結果 (執行環境: SQL Server 2005)

1) 執行以下指令，此舉將透過 CLR Stored Procedure (usp_My_CLS_SP) 呼叫 Web Service，並將回傳的 XML 資料寫入 TEST.dbo.My_SP_XML。

use TEST
go

exec sp_configure 'CLR Enabled' , '1'
reconfigure with override
go

exec usp_My_CLR_SP
go

exec sp_configure 'CLR Enabled' , '0'
reconfigure with override
go

2) 於 TEST 資料庫執行以下指令，並點選一筆查詢結果資料 (下圖紅框部分)。

3) 此時將可看到 Web Service 回傳 XML 架構及內容。

4) 利用以下指令查詢結果

use TEST
go

declare @xml_contents   xml

select top 1 @xml_contents = XML_Data
  from My_CLR_XML

select x.y.value('Country[1]','nvarchar(100)') as Country,
    x.y.value('City[1]','nvarchar(100)')    as City
  from @xml_contents.nodes('NewDataSet/Table') x(y)
go