Getting Filtered Data Across the Web

The advent of simple ways of retrieving data across the Internet has changed what we're able to offer clients . It's easy to build Web services in either Visual FoxPro or Visual Basic .NET, and it's easy to return and display the data.

Building a Parameterized Web Service in Visual FoxPro

Visual FoxPro 7 made it possible to build XML Web Services; Visual FoxPro 8 made it easy. In this example, I'll use only Visual FoxPro 8 or above.

Create a project in Visual FoxPro 8 called XMLProject. Add one program called XMLClass.PRG . Type the contents of Listing 9.26 into that program.

Listing 9.26. Three Methods for a Simple XML Web Service

 DEFINE CLASS XMLCLASS AS CUSTOM OLEPUBLIC FUNCTION CustsByRegion (pRegion AS String ) AS String S = "Driver={SQL Server};Server=(local);Database=Pinter;UID=sa;PWD=;" Handle = SQLSTRINGCONNECT(  s ) FieldList = "CustomerID,CompanyName,ContactName,Region" Cmd = "SELECT " + FieldList + " FROM CUSTOMERS WHERE Region='" + pRegion + "'" SQLEXEC( Handle, Cmd ) CURSORTOXML("SQLResult","lcXML") USE SQLDISCONNECT(0) RETURN lcXML ENDFUNC FUNCTION CustsByCountry (pCountry AS String ) AS String S = "Driver={SQL Server};Server=(local);Database=Pinter;UID=sa;PWD=;" Handle = SQLSTRINGCONNECT(  S ) FieldList = "CustomerID,CompanyName,ContactName,Country" Cmd = "SELECT " + FieldList + " FROM CUSTOMERS WHERE Country='" + pCountry + "'" SQLEXEC( Handle, Cmd ) CURSORTOXML("SQLResult","lcXML") USE SQLDISCONNECT(0) RETURN lcXML ENDFUNC FUNCTION OneCustomer (pCustID AS String ) AS String S = "Driver={SQL Server};Server=(local);Database=Pinter;UID=sa;PWD=;" Handle = SQLSTRINGCONNECT(  S ) SQLEXEC( Handle, "SELECT * FROM CUSTOMERS WHERE CustomerID='" + pCustID + "'" ) CURSORTOXML("SQLResult","lcXML") USE SQLDISCONNECT(0) RETURN lcXML ENDFUNC ENDDEFINE 

This Web service published three methods: One lists customers by state, another lists customers by country, and the last one returns a customer record. You can present users with a filtered list either by Country or by Region, give them a grid or list box to pick a customer, then return the customer.

Compile this using the Build Multi-Threaded DLL option. If you're compiling again after having tried to use the DLL, it will be in the clutches of IIS, and you'll need to open a DOS window and type

 IISRESET 

to stop and restart IIS, thus freeing your DLL.

Using the DLL

Now, if you type

 ! regsvr32 "XMLProject.dll" 

you'll be able to test this by typing

 o = CREATEOBJECT ( "XMLProject.XMLClass" ) ? o.CustsByRegion("SP") 

However, that's not how it's supposed to be used. Open the Task Pane and change to the XML Web Services page if it's not already visible. Select Publish Your XML Web Service. Click on the Advanced command button and make sure that only the three functions that you coded are checked. Pick a new virtual directory (which will be created under C:\InetPub\WWWRoot\ ), and name it FoxWebServices . Click Generate, and you should see the dialog shown in Figure 9.14.

Figure 9.14. Generating the WSDL file.

WSDL stands for Web Services Discovery Language, and describes what's available. It's used by IntelliSense to tell prospective users what your Web service can do.

Next, select Register an XML Web Service, and fill in the name of the generated WSDL file, as shown in Figure 9.15.

Figure 9.15. Registering the WSDL file and the Web service.

The message "Finished generating IntelliSense script" tells you that it worked. Now the fun begins.

Using the Web Service in a Smart Client Application

Now, type MODIFY COMMAND WSTest . Open the toolbox and open up the My XML Web Services tab. Click on your Web service entry (and there will probably be two identical entries, so pick either one), hold the left mouse button down, and drag and drop in the open code window. Voil  , you've got code (see Listing 9.27)!

Listing 9.27. Quick Demonstration of the Use of the Web Service DLL

 LOCAL loXMLCLASS AS "XML Web Service" * LOCAL loXMLCLASS AS "MSSOAP.SoapClient30" * Do not remove or alter following line. * It is used to support IntelliSense for your XML Web service. * Note that in our example, I've used Name instead of XMLCLASS, * the name in our example; the string "Name" will be replaced * with your class name in the generated code. * Visual FoxProWSDef__:loName=http://localhost/WSDIR/Name.wsdl,Name,NameSoapPort LOCAL loException, lcErrorMsg, loWSHandler TRY     loWSHandler = NEWOBJECT("WSHandler",;      IIF(VERSION(2)=0,"",HOME()+"FFC\")+"_ws3client.vcx")     loXMLCLASS = loWSHandler.SetupClient( ;       "http://FRYS21/FoxWebServices/XMLCLASS.wsdl", "XMLCLASS", ;          "XMLCLASSSoapPort")    * Call your XML Web service here.  ex: leResult = loXMLCLASS.SomeMethod() CATCH TO loException     lcErrorMsg="Error: "+TRANSFORM(loException.Errorno)+" - "+loException.Message     DO CASE     CASE VARTYPE(loXMLCLASS)#"O"         * Handle SOAP error connecting to web service     CASE !EMPTY(loXMLCLASS.FaultCode)         * Handle SOAP error calling method         lcErrorMsg=lcErrorMsg+CHR(13)+loXMLCLASS.Detail     OTHERWISE         * Handle other error     ENDCASE     * Use for debugging purposes     MESSAGEBOX(lcErrorMsg) FINALLY ENDTRY 

There's only one small task left. About nine lines down in the code, you'll see a comment that says "Call your XML Web Service here" or words to that effect. Start a new line, then type the name of the proxy object, loXMLClass , and press the period key (see Figure 9.16).

Figure 9.16. IntelliSense shows you the available methods.

This lets you know what's available. If you select one of the three choices, just include the proper parameter in parentheses and quotes (because the Customer key is a character field), and you'll get immediate gratification. For example, I included the following code. Try it and see what it does:

 lcXML = loXMLClass.CustsByCountry("Argentina") XMLTOCURSOR(lcXML,"TEST") BROWSE 

The really remarkable thing about this is that the parameter of the call to the loWSHandler.SetupClient() is LOCALHOST here because you're working on your own computer. But if you register this DLL on your Web server, you can change LOCALHOST to 64.86.225.101 and get the data from your Web server.

Note that Internet Information Server is an essential part of this conversation.

Building a Parameterized Web Service in Visual Basic .NET

Open the Visual Studio .NET IDE and create a new VISUAL BASIC .NET project. Select File, New, ASP.NET Web Service. Name it FilteredWebServices . Double-click on the design surface to reveal the example code, and type in the code from Listing 9.28.

Listing 9.28. XML Web Service Code in Visual Basic .NET

 <WebMethod()> Public Function CustsByRegion( _   ByVal pRegion As String) As dsByRegion     Dim data As New dsByRegion     SqlDataAdapter1.SelectCommand.Parameters(0).Value = pRegion     SqlDataAdapter1.Fill(data)     Return data End Function <WebMethod()> Public Function CustsByCountry( _   ByVal pCountry As String) As dsByCountry     Dim data As New dsByCountry     SqlDataAdapter2.SelectCommand.Parameters(0).Value = pCountry     SqlDataAdapter2.Fill(data)     Return data End Function <WebMethod()> Public Function OneCust(ByVal pCustID As String) As dsOne     Dim data As New dsOne     SqlDataAdapter3.SelectCommand.Parameters(0).Value = pCustID     SqlDataAdapter3.Fill(data)     Return data End Function 

I also returned to the design surface and pressed F4, then changed the service name from Service1 to MyWebService . Then I pressed F5, and I got the screen shown in Figure 9.17.

Figure 9.17. Automatically generated test bed for the Web service.

The form I've designed to use these Web services is shown in Figure 9.18. I used a Fixed3D border, CenterScreen StartPosition , Max and Min buttons turned off, and I docked the DataGrid at the bottom of the screen.

Figure 9.18. The form to use the Web service.

The combo boxes are named according to their contents. Although you would probably write code to populate them in a production application, I've done so manually here for simplicity. I opened the SQL Query Analyzer, pressed Ctrl+T to change to Text mode, did a SELECT DISTINCT of each of the fields I needed ( Country , Region , and CustomerID ) ordered by the selected field in each case, and then pasted them into the Items collection of the respective combo box.

I created a different dataset for each of the three methods available from my Web service. I probably should be changing the SQLDataAdapter names to make it easier to sync up data adapters and datasets, but it's not that hard to keep three methods straight (see Listing 9.29).

Listing 9.29. Using My XML Web Service Code in a Visual Basic .NET Form

 Private Sub CountryCombo_SelectedIndexChanged( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _ Handles CountryCombo.SelectedIndexChanged     Dim ws As New UseMyWebServices.localhost.MyWebService     DsByCountry1.Clear()     DsByCountry1.Merge(ws.CustsByCountry(CountryCombo.Text))     DataGrid1.DataSource = DsByCountry1.Customers End Sub Private Sub RegionCombo_SelectedIndexChanged( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _ Handles RegionCombo.SelectedIndexChanged     Dim ws As New UseMyWebServices.localhost.MyWebService     DsByRegion1.Clear()     DsByRegion1.Merge(ws.CustsByRegion(RegionCombo.Text))     DataGrid1.DataSource = DsByRegion1.Customers End Sub Private Sub CustCombo_SelectedIndexChanged( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _ Handles CustCombo.SelectedIndexChanged     Dim ws As New UseMyWebServices.localhost.MyWebService     DsOne1.Clear()     DsOne1.Merge(ws.OneCust(CustCombo.Text))     DataGrid1.DataSource = DsOne1.Customers End Sub 

I've put the code in the SelectedIndexChanged event of the respective combo boxes, so as you scroll through the list of available choices, the grid is populated with the matching records.