測試環境
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