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 內容。
結果如下：
圖 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>
結果如下：
圖 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>
得到的網頁：
圖 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 版權所有，歡迎轉載，但請註明出處及連結！
留言列表
