Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 17.5 Work with a Web Service that Returns a DataSet

17.5.1 Problem

Some web services return complex objects
that are not readily understood by Access. For example, you might
wish to call a web service that returns a .NET DataSet. Is it
possible to call such a web service from Access?

17.5.2 Solution

Web services communicate using the text-based protocols HTTP and
SOAP. Thus, any complex objects must be converted from the normal
binary format into text. This process is known as
serialization.
.NET automatically serializes many of its built-in objects, including
the DataSet, into XML. Thus, a .NET-based web service that returns a
DataSet, in reality returns an XML document that represents the
DataSet.

When possible, the Microsoft Office 2003 Web Services
Toolkit maps complex object return values into compatible types. The
serialized XML representation of a DataSet returned by a web service
is mapped by the toolkit into an MSXML2.IXMLDOMNodeList object. This
object is part of the MSXML component that you can use to navigate
through XML documents from Access.

The RunningCalculator
web service introduced in the Solution in Recipe 17.4 contains the GetMileSplits method which
returns a DataSet filled with mile splits for a given distance and
total time. Follow these steps to create an Access form that calls
the GetMileSplits method, navigates through the XML returned by the
web service, and populates an unbound listbox control on the form
with the mile splits:

1. 
   If you haven't yet done so, download and install the
   Microsoft Office 2003 Web Services Toolkit.

   
   

2. 
   Start Access 2003 and create an unbound form named
   frmSplitCalculator.

   
   

3. 
   Add the controls listed in Table 17-2 to the form.
   Size the controls to your liking.

   
   

4. 
   From the VBA editor, select Tools Web Service
   References.... This menu item is added to the VBA editor by the
   Microsoft Office 2003 Web Services Toolkit.

   
   

If you've already completed the Solution in Recipe 17.4 and are working within the same database, you can skip Steps 4-7.

5. 
   At the Microsoft Office 2003 Web Services Toolkit dialog box, select
   the Web Service URL radio button and enter the following URL into the
   URL textbox:

   
   

   www.deeptraining.com/webservices/runnercalculator.asmx

6. 
   The RunnerCalculator service and its methods should be displayed in
   the SearchResults box. Check the checkbox to the left of
   RunnerCalculator and click the Add button at the bottom of the dialog
   box to add a reference to the RunnerCalculator service.

   
   

7. 
   The toolkit adds a new class module to the project with the name
   clsws_RunnerCalculator. This class serves as a proxy for making calls
   to the web service. The code in this class will take care of speaking
   to the web service using the SOAP protocol.

   
   

8. 
   Attach the following code to the cmdCalculateSplits
   button's Click event to use the
   clsws_RunnerCalculator proxy class to call the GetMileSplits method:

   
   

   Private Sub cmdCalculateSplits_Click( )
   Dim prxRunnerCalc As clsws_RunnerCalculator
   Dim nlDS As MSXML2.IXMLDOMNodeList
   Dim i As Integer
   ' Clear any existing items from the listbox
   For i = lstSplits.ListCount - 1 To 0 Step -1
   lstSplits.RemoveItem (i)
   Next
   ' Add the headings to the list
   lstSplits.ColumnWidths = "0.35";1""
   lstSplits.ColumnHeads = True
   lstSplits.AddItem ("Mile;Split")
   On Error GoTo HandleErr
   DoCmd.Hourglass True
   ' Instantiate proxy class
   Set prxRunnerCalc = New clsws_RunnerCalculator
   If Len(txtDistance) > 0 And Len(txtHours) > 0 And _
   Len(txtMinutes) > 0 And Len(txtSeconds) > 0 Then
   ' Call GetMileSpilts method via proxy class
   ' This method returns a .NET DataSet which gets
   ' serializedd into XML.
   ' XML is returned by the proxy as the type
   ' MSXML2.IXMLDOMNodeList.
   Set nlDS = prxRunnerCalc.wsm_GetMileSplits(txtDistance, _
   txtHours, txtMinutes, txtSeconds)
   Call ProcessSplits(nlDS)
   Else
   MsgBox "You must enter values for each text box.", _
   vbOKOnly + vbCritical, "Splits Calculator"
   End If
   ExitHere:
   On Error GoTo 0
   DoCmd.Hourglass False
   Exit Sub
   HandleErr:
   MsgBox "Error " & Err.Number & ": " & Err.Description, _
   vbOKOnly + vbCritical, "Splits Calculator"
   Resume ExitHere
   End Sub

9. 
   Add the following code to the module to use the MSXML component to
   process the returned XML data and add the split values to the
   lstSplits listbox:

   
   

   Private Sub ProcessSplits(nlDS As MSXML2.IXMLDOMNodeList)
   Dim nlPace As MSXML2.IXMLDOMNodeList
   Dim nodData As MSXML2.IXMLDOMNode
   Dim nodRow As MSXML2.IXMLDOMNode
   Dim nodField As MSXML2.IXMLDOMNode
   Dim strItem As String
   On Error GoTo HandleErr
   ' Grab the second node -- the data -- from the
   ' returned node list
   Set nodData = nlDS.Item(1)
   ' Get the Pace nodes (rows)
   Set nlPace = nodData.selectNodes("//MileSplits/Pace")
   ' For each Pace node
   For Each nodRow In nlPace
   ' Get the child nodes of Pace, i.e., the fields
   For Each nodField In nodRow.childNodes
   Select Case nodField.nodeName
   Case "Mile"
   ' Grab the Mile value
   strItem = nodField.nodeTypedValue
   Case "SplitString"
   ' Grab the SplitString value
   strItem = strItem & ";" & nodField.nodeTypedValue
   ' Add the strItem value to the listbox
   lstSplits.AddItem strItem
   End Select
   Next
   Next
   ExitHere:
   On Error GoTo 0
   DoCmd.Hourglass False
   Exit Sub
   HandleErr:
   MsgBox "Error " & Err.Number & ": " & Err.Description, _
   vbOKOnly + vbCritical, "Process Splits"
   Resume ExitHere
   End Sub

10. 
    Save and open the form to test it out. Enter values into each of the
    textboxes and click on the Calculate Splits button. The form should
    look similar to the one shown in Figure 17-8.

    
    

Figure 17-8. The code behind the Calculate Splits button calls the RunningCalculator service's GetMileSplits method, processes the returned serialized DataSet, and adds the splits to the listbox

17.5.3 Discussion

When you establish a reference to a web service using the Microsoft
Office 2003 Web Services Toolkit, the toolkit, among other things,
sets a reference to the Microsoft XML v 5.0 type library, which
allows you to use
MSXML without having
to manually set a reference to the type library.

17.5.3.1 Processing the returned XML

The MSXML component contains a number of objects, properties, and
methods for working with XML documents. You can find online
documentation for MSXML at the following URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm/xml_obj_
ixmldomnodelist_4kvo.asp

In order to create the code that processes a
serialized DataSet using MSXML you need to
understand the layout of the XML returned by the web service method.
For .NET web services, you can obtain basic documentation about the
web service and its methods by directly navigating to the web service
(the asmx file) using Internet Explorer. Thus, for the
RunnerCalculator service, you could obtain information about the web
service at this address:

www.deeptraining.com/webservices/runnercalculator.asmx

When you do this you should see a screen that looks similar to the
one shown in Figure 17-9.

Figure 17-9. .NET web services supply a basic set of documentation when you navigate to them

You may find it helpful to take a look at the web
services' Web
Services Description Language (WSDL) document, which you can get to
by clicking on the Service Description link (see Figure 17-9). You can think of the WSDL as the equivalent
of a type library for a web service.

If the web service was
created with Microsoft .NET 1.0 you can also use a special
automatically-generated test form to call a web service method
interactively from Internet Explorer. This test form is available by
clicking on the name of a method you wish to test (see Figure 17-9). In Microsoft .NET 1.1 (Visual Studio .NET
2003), by default, you no longer get the test form when calling the
web service remotely. If you're using a .NET 1.1 web
service, the test form is disabled when used from a remote client
(anything other than localhost), so you can't depend
on the test form for help.

Of course, neither the .NET 1.0 test form nor the WSDL for a web
service takes the place of good documentation. If you are using a web
service in a production environment, you're going to
need for the web service's creator to supply you
with documentation that should include a thorough discussion of the
web services input parameters and return value.

17.5.4 See Also

Working with ADO.NET Datasets in Microsoft Office (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office08012002.asp).

MSXML documentation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm/ xml_obj_ixmldomnodelist_4kvo.asp).