ASP 呼叫Web Services

(XMLObjects.asp)

<%

'設定 Web Service WSDL 網址 

Const WSDL_URL = "http://localhost/NorthWind/sqlxml?wsdl"

'設定 Web Service 名稱 

Const WB_NAME = "sqlAsXml"

dim soapClient, NodeList, ItemLength

'建立 soapClient 物件 

Set soapClient = Server.CreateObject("MSSOAP.soapClient")

soapClient.ClientProperty("ServerHTTPRequest") = True

'初始化 soapClient

soapClient.mssoapinit WSDL_URL,WB_NAME

Set NodeList = soapClient.GetCategories(0)

'算出節點 CategoryName 的數目 

ItemLength = NodeList.Item(0).selectNodes("//CategoryName").length

 

Response.Write "CategoryName"

'取出所有 CategoryName 節點的內容 

FOR i = 0 To ItemLength - 1

           Response.Write(NodeList.Item(0).selectNodes("//CategoryName").item(i).text)

           Response.Write(", ")

NEXT

 

Response.Write "<p></p>"

Response.Write "SQL Return Code"

'取得 SQL Return Code 

Response.Write NodeList.Item(1).xml

%>

經由 MSSOAP.soapClient 透過 WSDL Web Services 名稱去存取 Web Services,然後使用 selectNodes( ) 方法在 Web Services 回應的 XML 中去找尋所要的節點,這裡是要列出所有的CategoryName 內容。

結果如下:

 MSSOAP.soapClient 存取 Web Services

12

這裡先探討一下剛才設定 Web 方法輸出為 XML 物件,那回應訊息是什麼呢?從程式中可以了解,NodeList.Item(0).xml的內容是 store procedure 查詢得到的結果,NodeList.Item(1).xml的內容則是 SQL Server return code。擷取其中內容可以得到如下的訊息:

NodeList.Item(0).xml 

….

<SqlXml>

<row>

<CategoryID>1</CategoryID>

<CategoryName>Beverages</CategoryName>

<Description>Soft drinks, coffees, teas, beers, and ales</Description>

</row>

<row>

<CategoryID>2</CategoryID>

<CategoryName>Condiments</CategoryName>

<Description>Sweet and savory sauces, relishes, spreads, and seasonings</Description>

</row>

….

</SqlXml>

……….

 

NodeList.Item().xml 

<sqlresultstream:SqlResultCode …. xsi:type=""sqltypes:SqlResultCode"">

0

</sqlresultstream:SqlResultCode>

 

所謂的 XML 物件,其實就是序列化至 Object  Array XML 物件,但XML Objects 所回傳的 XML 訊息並沒有包含 XML Schema,且回傳的格式在 .NET 中不能直接做 Data Bind。初學者建議設定為DataSet

下面就以 Store Procedure Templates 兩種型態設定的 Web 方法,搭配回傳格式為DataSet 的範例來探討。

 

 

 

 

 

 

資料島 ( DataIsland ) 存取技術 

 

首先準備一些簡單的 store procedure

查詢         

 

名稱:queryEmployees

CREATE PROCEDURE queryEmployees @EmployeeID int  AS

SELECT         EmployeeID, LastName, FirstName,Title,TitleOfCourtesy, Address, City,Country

FROM             Employees

where EmployeeID >= @EmployeeID

GO

 

名稱:querySingleEmployee

CREATE PROCEDURE querySingleEmployee @EmployeeID int  AS

SELECT         EmployeeID, LastName, FirstName,Title,TitleOfCourtesy, Address, City,Country

FROM             Employees

where EmployeeID = @EmployeeID

GO

 

 

 

新增         

 

名稱:InsertSample

CREATE PROCEDURE InsertSample @LastName nvarchar(20),@FirstName nvarchar(10),@Title nvarchar(30),@TitleOfCourtesy nvarchar(25),@Address nvarchar(60),@City nvarchar(15),@Country nvarchar(15)  AS

           INSERT INTO Employees (LastName, FirstName, Title, TitleOfCourtesy,  Address, City,Country)

           VALUES         (@LastName,@FirstName,@Title,@TitleOfCourtesy,@Address,@City,@Country)

GO

 

修改         

 

名稱:UpdateSample

CREATE PROCEDURE UpdateSample @EmployeeId int,@LastName nvarchar(20),@FirstName nvarchar(10),@Title nvarchar(30),@TitleOfCourtesy nvarchar(25),@Address nvarchar(60),@City nvarchar(15),@Country nvarchar(15) AS

           UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Title=@Title, TitleOfCourtesy=@TitleOfCourtesy, Address=@Address, City=@City,Country=@Country

           WHERE EmployeeId=@EmployeeId

GO

 

刪除         

 

名稱:DeleteSample

CREATE PROCEDURE DeleteSample @EmployeeId int  AS

           DELETE FROM Employees

           WHERE EmployeeId=@EmployeeId

GO

 

SQLXML 中再設定一個『虛擬名稱』,在『名稱』裡輸入 sqlwb,『型別』選擇 soap,『Web 服務名稱』輸入 SqlAsDataSet,『儲存』後點選『設定』按鈕,設定 Web 方法,型別選擇『SP』,輸出的格式,在『輸出為』選項全部選擇『單一資料集』,Web 方法名稱和Store Procedure 的對應如下表:

 

Web 方法名稱

對應的Store Procedure

queryEmployees

queryEmployees

querySingleEmployee

querySingleEmployee

SPInsertSample

InsertSample

SPUpdateSample

UpdateSample

SPDeleteSample

DeleteSample

 

先來查詢 Northwind 資料庫裡的 Employees 資料表中的幾個欄位:

(DataIsland_Sample.asp)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%>

<HTML>

  <HEAD>

                     <meta http-equiv="Content-Type" content="text/html; charset=big5">

                     <%

                                Const WSDL_URL = "http://localhost/NorthWind/sqlwb?wsdl"

                                Const WB_NAME = "SqlAsDataSet"

 

                                Dim soapClient

                                Set soapClient = Server.CreateObject("MSSOAP.soapClient")

                                soapClient.ClientProperty("ServerHTTPRequest") = True

                                soapClient.mssoapinit WSDL_URL,WB_NAME

 

                                Set NodeList = soapClient.queryEmployees(0,True)

                     %>

                     <xml>

                                <?xml version="1.0"?>

                                <%=NodeList.Item(0).selectNodes("//rowset").item(0).xml%>

                     </xml>

                     <script>

                     <!--

                                Sub button1_onclick

                                           Table1.previousPage

                                End Sub

                                Sub button2_onclick

                                           Table1.nextPage

                                End Sub

                     -->

</script>

</HEAD>

           <body>

                     <font><b>使用資料島 (XML DataIslands) 呈現範例:</b></font>

 

                     <table cellSpacing="0" cellPadding="0"  border="1" datasrc="#xmldso" datapagesize="5">

                                <thead>

                                           <td >EmployeeID</td>

                                           <td >LastName</td>

                                           <td >FirstName</td>

                                           <td >Title</td>

                                           <td >TitleOfCourtesy</td>

                                           <td >Address</td>

                                           <td >City</td>

                                           <td >Country</td>

                                </thead>

                                <tr>

                                           <td ><span datafld="EmployeeID"></span></td>

                                           <td ><span datafld="LastName"></span></td>

                                           <td ><span datafld="FirstName"></span></td>

                                           <td ><span datafld="Title"></span></td>

                                           <td ><span datafld="TitleOfCourtesy"></span></td>

                                           <td ><span datafld="Address"></span></td>

                                           <td ><span datafld="City"></span></td>

                                           <td ><span datafld="Country"></span></td>

                                </tr>

                                </table>

                                <P>

                                           <input value="上一頁">

                                           <input value="下一頁">

                                </P>

           </body>

</HTML>

 

結果如下:

 DataIsland 呈現結果

13

 

DataIsland_Sample.asp仍是透過soapClient Web Services 溝通,所得到的資料放置於 XML DataIsland 中,在前端利用資料島的技術做資料繫結,這種方式相當直覺化。美中不足的就是除了 Internet Explorer 5.0 以上版本,其他 Browser的支援度很低,若開發給 Internet 上的客戶端使用,須將這個因素考慮進去。

 

Recordset存取 Web Services

 

那如何在 Server 端呈現資料呢?筆者寫了將 XML 內容轉換為 RecordSet的函式 fnXML2RecSet( ) ASP 網頁呈現資料:

(SP_Sample.asp)

<HTML>

<HEAD>

<meta http-equiv="Content-Type" content="text/html; charset=big5">

<script>

<!--

function Button1_onclick(index) {

           location.href = "SP_Update_Sample.asp?EmployeeID=" + index;

}

 

function Button2_onclick(index) {

           location.href = "SP_Delete_Exec.asp?EmployeeID=" + index;

}

 

//-->

</script> 

</HEAD>

<body>

<font><b>ASP 使用 SQLXML 預存程序 (Store Procedure) 範例:</b></font>

<%

Const WSDL_URL = "http://localhost/NorthWind/sqlwb?wsdl"

Const WB_NAME = "SqlAsDataSet"

Dim soapClient

Set soapClient = Server.CreateObject("MSSOAP.soapClient")

soapClient.ClientProperty("ServerHTTPRequest") = True

soapClient.mssoapinit WSDL_URL,WB_NAME

Set NodeList1 = soapClient1.queryEmployees(0,True)

' Web Service 取回的 xml rowset 節點開始擷取,傳入 fnXML2RecSet 中轉為 recordset

Set rs = fnXML2RecSet(NodeList1.Item(0).selectNodes("//rowset").item(0).xml)

 

' XML 內容轉換為 Record Function

Function fnXML2RecSet(XmlContent)

           '建立 XML DOM 物件 

           Set Xmldoc = Server.CreateObject("Msxml2.DOMDocument.4.0")

           XmlDoc.async = false

           '載入 xml 內容 

           Xmldoc.loadXML(XmlContent)

 

           '對應 SQL 參數的資料型別設定值,這裡只要顯示資料,所以欄位設定成文字即可

           Const adVarChar = 200                                '文字(Text)

                     

           '建立 Recordset 物件

           Set rsReturn = Server.CreateObject("ADODB.Recordset")

          

          

           For i = 0 To Xmldoc.documentElement.childNodes(0).childNodes.length - 1               '以每一列的欄數為迴圈

                     '以每一欄的節點名稱,新增 Recordset Fields

                     rsReturn.Fields.Append Xmldoc.documentElement.childNodes(0).childNodes(i).nodeName, adVarChar, 300, adFldIsNullable

           Next

          

           '開啟 Recordset 物件

           rsReturn.Open()

          

           '算出總共共幾列 record

           For j = 0 To Xmldoc.documentElement.childNodes.length - 1

                     '新增一筆 record

                     rsReturn.AddNew()

                    

                     '將所有的值填入相對應的欄位名稱

                     For i = 0 To Xmldoc.documentElement.childNodes(j).childNodes.length - 1

                                rsReturn(Xmldoc.documentElement.childNodes(j).childNodes(i).nodeName) = Xmldoc.documentElement.childNodes(j).childNodes(i).Text

                     Next

           Next

          

           '移到 Recordset 的第一筆

           rsReturn.MoveFirst

          

           '回傳 Recordset

           Set fnXML2RecSet = rsReturn

End Function

%>

<table cellSpacing="0" cellPadding="0" border="1">

           <tr>

                     <td>EmployeeID</td>

                     <td>LastName</td>

                     <td>FirstName</td>

                     <td>Title</td>

                     <td>TitleOfCourtesy</td>

                      <td>Address</td>

                     <td>City</td>

                     <td>Country</td>

           </tr>

<%

  WHILE NOT rs.EOF

%>

           <tr>

                     <td>

<INPUT name=”Button1” onclick="return Button1_onclick(<%=rs(0)%>)" value=”修改”>

</td>

                     <td>

<INPUT name=”Button2” onclick="return Button2_onclick(<%=rs(0)%>)" type=button value=”刪除”>

</td>

                     <td ><%=rs(0)%></td>

                     <td ><%=rs(1)%></td>

                     <td ><%=rs(2) %></td>

                     <td ><%=rs(3) %></td>

                     <td ><%=rs(4)%></td>

                     <td ><%=rs(5)%></td>

                     <td ><%=rs(6)%></td>

                     <td ><%=rs(7)%></td>

           </tr>

<%

rs.MoveNext

WEND

%>

</table>

<P><a href="SP_Insert_Sample.asp" target="_self">我要新增一筆資料</a></P>

</body>

</HTML>

 

得到的網頁:

 SQL XML 使用ASP Recordset 存取資料

14

 

 

雖然得到的 XML 文件轉換成RecordSet,但不是直接和 SQL Server 連線,所以不能使用 RecordSet 來做資料的異動。變更資料只要透過 soapClient 去執行對應的 Web 方法即可。

 

新增:

 

'執行新增的 Web Method

LastName = Request("txtLastName")

FirstName = Request("txtFirstName")

Title = Request("txtTitle")

TitleOfCourtesy = Request("txtTitleOfCourtesy")

Address = Request("txtAddress")

City = Request("txtCity")

Country = Request("txtCountry")

Set NodeList = soapClient.SPInsertSample(LastName, FirstName, Title, TitleOfCourtesy, Address, City, Country,True)

 

修改:

 

EmployeeID = Request("txtEmployeeID")

LastName = Request("txtLastName")

FirstName = Request("txtFirstName")

Title = Request("txtTitle")

TitleOfCourtesy = Request("txtTitleOfCourtesy")

Address = Request("txtAddress")

City = Request("txtCity")

Country = Request("txtCountry")

'執行修改的 Web Method

Set NodeList = soapClient.SPUpdateSample(EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, Address, City, Country,True)

 

刪除:

 

'取得預刪除的 record 索引 CategoryID EmployeeID = Request("EmployeeID")

'執行刪除的 Web Method

Set NodeList = soapClient.SPDeleteSample(EmployeeID,True)

 

Mr. Zabo 版權所有,歡迎轉載,但請註明出處及連結!

創作者介紹

Mr. Zabo - 網路、趨勢、技術、新概念

Zabo 發表在 痞客邦 PIXNET 留言(0) 人氣()