Importing XML into Visual Basic .NET

Just as the DBF is the native data container for Visual FoxPro, XML is the native data container for .NET. In fact, datasets are XML. For example, if you have an XML Web Service, the command to create a dataset (the SQLAdapter , the SQLConnection , and the Authors1 dataset have already been defined) and return it as XML is this:

 <WebMethod> Public Function  GetAuthors  () As authors1    Dim authors As New authors1()    SqlDataAdapter1.Fill(authors)    Return authors End Function 

And the code to receive the output from the Web service and convert it back into a recordset is this:

 Dim ws As New AuthorsWinClient.localhost.AuthorsService() ws.Credentials = System.Net.CredentialCache.DefaultCredentials AuthorData.Merge(ws.  GetAuthors  ()) 

( AuthorData is a dataset on the client.) But don't XML Web Services return XML? Yes, they do. And yet all you see here is references to datasets. They're the same thing.

Direct Access to SQL Server with XML

In FoxPro, if a program running on a workstation has access to a server running SQL Server, to pass the result of a query back to a cursor, just call the SQLExec function:

 Con = SQLStringConnect (; "Driver={SQL Server};server=(local);UID=sa;PWD=;database=pubs;)" SQLExec (Con, "select * from authors where state = 'CA'", "SQLResult") 

This puts the results of the query in a cursor named SQLResult . (In fact, that's the default cursor name if you don't supply that third parameter.)

In the past, it was much more complicated to return data from a remote server. Not any longer. You can configure SQL Server in such a way that query output can be sent directly to your program using HTTP, without any third-party software at all, using just a few lines of code.

In the example in Listing 7.17, the plus signs in the URL are called encoding. URLs can't contain blanks, so either you replace blanks with something like a plus and let IIS remove them, or you call an encoding routine. You can use CHRTRAN(url, " ", "+") to replace blanks with plus signs.

Listing 7.17. Using the XMLDOM Object to Return XML

 oXMLDOM = CREATEOBJECT ("Microsoft.XMLDOM") WITH oXMLDOM .Async = .F.    && might not work without this .Load ("http://localhost/xmldir?sql=SELECT ;  + *+FROM+authors+FOR+XML+AUTO&root=root") XMLTOCURSOR (.XML, "Authors") ENDWITH 

Before you try this out on your own computer, you'll need to select Start, Programs, SQL Server, Configure SQL XML Support in IIS and specify the name of a directory to be used to hold the results of this type of query.

Figure 7.5 shows the first page of the dialog that you use to configure SQL Server via HTTP. The Virtual Directory name is the name that appears in the URL string; the Actual Directory name is the fully qualified name of the directory on your hard drive. It's easiest if you give them both the same name. If the new physical directory doesn't yet exist, the wizard will create it for you when you close the dialog.

Figure 7.5. General settings for XML access to SQL via HTTP.

The password and user ID should be the ones that are used with your database. I've used sa as the username with a blank password as shown in Figure 7.6 because that's what ships with SQL Server and MSDE. You're supposed to remove that combination as soon as you add some other user and password, so use whatever works. Also, my server on my installation of SQL Server on my laptop is called VAIO\VAIO , not (local) , so you might have to change your server name as well depending on how you installed SQL or MSDE.

Figure 7.6. Security settings for HTTP access to SQL.

The data source is the name of the server ”usually (local) or "localhost" ”and the name of the database (see Figure 7.7). Note that only one database is supported per virtual SQL directory.

Figure 7.7. Data source settings for HTTP access to SQL.

The last page that you have to configure determines which options you want enabled; enable all of them so that you can experiment, as shown in Figure 7.8. You can always turn them off later.

Figure 7.8. Miscellaneous settings for HTTP access to SQL.

Using the XML DOM to Call a Web Service

Similarly, you can call a Web service written in either FoxPro or in one of the .NET languages. As we'll see below, you can create a Web service in a minute or two. Web services can be of arbitrary complexity, so don't think that there are things you can do in SQL but not with Web services ”all it takes is time. And you can use either DBFs or SQL Server (except when using HTTP SQL as described in the preceding section, which obviously only works with SQL).

But there are other MSXML2 components that are also useful. Above we used the MXSML2.DOMDocument class to send a query to SQL Server. But we can also use the MSXML2.XMLHTTP class to return XML from a URL, as shown in Listing 7.18.

Listing 7.18. Using the MSXML DOM to Return XML from a URL

 LOCAL loXMLHTTP         AS "MSXML2.XMLHTTP" loXMLHTTP = CREATEOBJECT ("MSXML2.XMLHTTP.4.0") loXMLHTTP.Open("GET", "http://localhost/AppName/SendMeSomeXML.ASP") loXMLHTTP.Send() WAIT WINDOW [Waiting for data] TIMEOUT 1  && Your response may vary XMLTOCURSOR (loXMLHTTP.ResponseBody) 

This will return anything, including a Web page. But because you'd need a browser control to display a Web page, it's not very useful unless you need to do some screen-scraping. Actually, screen-scraping was how we got output from Web sites before XML Web Services were the standard; now you only have to screen-scrape if they don't want you to have their page content.

Using the XML DOM to Validate Documents

We can use the CursorToXML() function to create an XSD schema that can be subsequently used to validate the XML that is sent to us. The following code creates an XSD file:

 USE CLIENTS CursorToXML (SELECT(), "lcXML", 1, 0, 0, "clients.xsd") MODIFY FILE clients.xsd       && to see what was produced 

Now we can use the code shown in Listing 7.19 to read XML from a remote source and validate it using the structure stored in CLIENTS.XSD .

Listing 7.19. Using an XSD Schema to Validate XML As It Is Received

 LOCAL loXMLSchema         AS "MSXML2.XMLSchemaCache.4.0 loXMLSchema = CREATEOBJECT ("MSXML2.XMLSchemaCache.4.0") loXMLSchema.Add ("", "clients.xsd") && File we created above LOCAL loXML AS "MSXML2.XMLDOMDocument.4.0" loXML = CREATEOBJECT ("MSXML2. XMLDOMDocument.4.0") loXML.Schemas = loXMLSchema loXML.Asynch = .F. loXML.Load (http://www.lespinter.com/SvcsWebXML/Clients.aspx) IF loXML.ParseError.ErrorCode <> 0    MessageBox (;    [Error: The XML does not match the schema]   + CHR(13) ;  + [Line:  ] + TRANSFORM(loXML.ParseError.Line) + CHR(13) ;  + [Cause: ] + loXML.ParseError.Reason) ENDIF 

Similarly, the Visual Studio XML Editor can be used to create an XML schema (see Figure 7.9). Create a Windows Forms project, add a SQLDataAdapter and configure its connection and SELECT statement. Then right-click on the DataAdapter and select Generate Dataset, and an XSD file will be added to your project.

Figure 7.9. Using the Visual Studio .NET XML Editor to create a schema file.

XMLUpdateGram s

In FoxPro, we change our tables directly, in a way that is so transparent that we aren't even aware of all of the things that are happening under the surface. It's like a little miracle .

For the rest of the programming world, there's no miracle. It's just code ”often lots of code .

One way to change records in a table is to send SQL UPDATE commands to a server, and let the server engine do what needs to be done. For example, we could copy a record to our workstation application, make some changes in our local cursor, and then generate a SQL UPDATE statement to make the same changes to the remote data store that we already made in our FoxPro cursor. But why is this so complicated?

Fortunately, there's something better. When you change a record in a FoxPro app while buffering mode 3 (optimistic table buffering) is in effect, Visual FoxPro remembers the state of all records before and after any changes. (That's why the CurVal() , OldVal() , and GetFldState() functions work.) Using the XMLUpdateGram() function, we can get a "snapshot" of the data before and after changes. The server can then use this UpdateGram to decide what action to take ” sort of.

Do the experiment shown in Listing 7.20.

Listing 7.20. How to Create an UpdateGram Showing an INSERT

 CREATE TABLE TEST (ContactID Integer, FullName Char(20), Phone Char(15)) SET MULTILOCKS ON SELECT TEST CursorSetProp([Buffering], 5) INSERT  INTO TEST VALUES (1, [Les Pinter], [324-4321]) X = XMLUpdateGram() STRTOFILE(x, "UpdateGram.xml") MODIFY FILE UPDATEGRAM.xml 

Listing 7.21 shows the resulting XML.

Listing 7.21. UpdateGram Resulting from an INSERT

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before/>         <updg:after>             <test>                 <contactid>1</contactid>                 <fullname>Les Pinter</fullname>                 <phone>324-4321</phone>             </test>         </updg:after>     </updg:sync> </root> 

<Updg:Before/> means that the "before" version of the record is empty. Because the "after" has a record, this is an add. Similarly, if you have both a "before" and an "after," it's an update, and if there's a "before" and no "after," it's a delete.

Changes are documented down to the field in an UpdateGram , as shown in Listing 7.22.

Listing 7.22. An UpdateGram Showing an UPDATE

 CREATE TABLE TEST (ContactID Integer, FullName Char(20), Phone Char(15)) SET MULTILOCKS ON SELECT TEST INSERT  INTO TEST VALUES (1, [Les Pinter], [324-4321]) CursorSetProp([Buffering], 5) CursorSetProp([KeyFieldList],[ContactID]) REPLACE FullName WITH [Juan Carral] X = XMLUpdateGram() STRTOFILE(x, "UpdateGram.xml") MODIFY FILE UPDATEGRAM.xml 

The UpdateGram reveals only the changed fields, as shown in Listing 7.23.

Listing 7.23. UpdateGram Resulting from an UPDATE

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>             <test>                 <contactid>1</contactid>                 <fullname>Les Pinter</fullname>             </test>         </updg:before>         <updg:after>             <test>                 <contactid>1</contactid>                 <fullname>Juan Carral</fullname>             </test>         </updg:after>     </updg:sync> </root> 

Finally, Listing 7.24 shows what a DELETE UpdateGram looks like.

Listing 7.24. Code to Produce an UpdateGram Showing a DELETE

 CREATE TABLE TEST (ContactID Integer, FullName Char(20), Phone Char(15)) SET MULTILOCKS ON SET DELETED ON SELECT TEST INSERT  INTO TEST VALUES (1, [Les Pinter], [324-4321]) CursorSetProp([Buffering], 5) DELETE NEXT 1 X = XMLUpdateGram() STRTOFILE(x, "UpdateGram.xml") MODIFY FILE UPDATEGRAM.xml 

The result is shown in Listing 7.25.

Listing 7.25. An UpdateGram Showing a DELETE

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>             <test>                 <contactid>1</contactid>                 <fullname>Les Pinter</fullname>                 <phone>324-4321</phone>             </test>         </updg:before>         <updg:after/>     </updg:sync> </root> 

So updategrams are easy to understand and easy to produce. That's the good news.

The bad news is that although SQL Server can read diffgrams, FoxPro 7 can't. And anyway, neither can read updategrams. Visual FoxPro 8 has diffgrams , which are different from updategrams, and Visual FoxPro 8 can read and apply them to add, update, and delete records.

So, unless you write your own UpdateGram parser for receiving and processing these animals, they're interesting but not relevant with working with DBFs.

DiffGram s in Visual FoxPro 8

Visual FoxPro 8 adds the CursorAdapter and the XMLAdapter , the two missing pieces that finally make XML useful to FoxPro developers. Listing 7.26 shows how to create a DiffGram using an XMLAdapter , and how to send it to a Web service that knows what to do with it. You'll have to imagine the search form.

Listing 7.26. Using Visual FoxPro 8 DiffGram s to Update a Record on a Web Server

 DO SearchForm to lcSelectedRecordKey LOCAL loXMLHTTP AS       [ MSXML2.IXMLHTTPRequest] loXMLHTTP = CREATEOBJECT ([MSXML2.XMLHTTP.4.0]) lcCmd = [SELECT * FROM CLIENTES WHERE ClID = ] + lcSelectedRecordKey lcCmd =  CHRTRAN (Cmd, [ ], [+]) *  Encode for HTTP, which like nature, abhors a vacuum loXMLHTTP.AddPostVar (lcCmd) loXMLHTTP.Open([POST], [http://localhost/Fox/GetClientRecord) loXMLHTTP.Send() XMLTOCURSOR (loXMLHTTP.ResponseBody) 

The user clicks on the Edit button:

 THISFORM.EnableFieldsAndTurnOnSaveAndCancelButtons() CursorSetProp([Buffering],5) 

The user modifies the record in the current screen, then clicks on the Save button. This is where we create the DiffGram and send it to the server, as shown in Listing 7.27.

Listing 7.27. The Save Button Click Event Code

 LOCAL oXML  AS XMLAdapter oXML = CREA ("XMLAdapter") oXML.AddTableSchema ("Clientes") oXML.IsDiffGram  = .T. oXML.UTF8Encoded = .T. oXML.ToXML  ("lcXML", "", .F., .T., .T.) LOCAL loXMLHTTP AS       [MSXML2.IXMLHTTPRequest] loXMLHTTP = CREATEOBJECT ([MSXML2.XMLHTTP.4.0]) loXMLHTTP.AddPostVar (lcXML) loXMLHTTP.Open([POST], [http://localhost/Fox/UpdateClientsTable]) loXMLHTTP.Send() 

When the Server program UpdateClientsTable runs, it extracts the diffgram from the post buffer and uses it to update the table, as shown in Listing 7.28.

Listing 7.28. Updating the Table on the Server

 oXML.LoadXML (lcXML) MESSAGEBOX("Diffgram loaded", 48, ;  "Simulating a DiffGram being received on the server") LOCAL oCA AS CursorAdapter oCA = CREATEOBJECT ([CursorAdapter]) oCA.DataSource =   "Native" oCA.CursorAttach ("Clients") LOCAL oTable AS XMLTable oTable = CREATEOBJECT ([XMLTable]) oTable = oXML.Tables.Item(1) oField = oTable.Fields.Item(1) oField.KeyField = .T. oTable.ApplyDiffGram ("Clients", oCA, .T.) CLOSE TABLES CLOSE DATABASE 

It's not as simple as updating a FoxPro table on a workstation, and it's not as easy as constructing a SQL UPDATE statement and calling SQLEXEC to execute it. But after you've made it work one time, it's not too difficult the next time.

The Visual FoxPro CursorAdapter

In FoxPro 8, XML has finally received the support that we lacked for communicating with the rest of the world. The new base class CursorAdapter replaces what we used to do with remote views, and to a degree what we did with SQL Pass-Through. And it does so in a homogeneous way, so that only one syntax has to be learned regardless of the data store. It automates the tasks of inserting, updating, and deleting records on remote tables when we make changes locally. What's new is that it also works with XML.

To put this technology to use, you add a CursorAdapter to the form's Data Environment. You set a few properties and call methods to retrieve your data. You can then use the CursorAdapter in conjunction with the new XMLAdapter to construct and send updates. The Server can also use the CursorAdapter and XMLAdapter to apply them.

Let's create a new form called XMLForm1 . Open the Data Environment and right-click. You'll see several options, among them Add CursorAdapter and Builder. The first lets you construct the CursorAdapter manually, but the Builder is helpful for learning how it works. For now, we'll use the Builder.

I have a file called XML1.XML , shown in Listing 7.29, which includes an XSD. This is important because the CursorAdapter Wizard in the Data Environment will use it to construct the cursor.

Listing 7.29. XML1.XML File to Demonstrate Loading XML to a CursorAdapter

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <Root>   <xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn  :schemas-microsoft-com:xml-msdata">     <xsd:element name="VFPData" msdata:IsDataSet="true">       <xsd:complexType>         <xsd:choice maxOccurs="unbounded">           <xsd:element name="clientes" minOccurs="0" maxOccurs="unbounded">             <xsd:complexType>               <xsd:sequence>                 <xsd:element name="clave" type="xsd:int"/>                 <xsd:element name="nombre">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="20"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>                 <xsd:element name="apellidos">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="30"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>                 <xsd:element name="telefono">                   <xsd:simpleType>                     <xsd:restriction base="xsd:string">                       <xsd:maxLength value="15"/>                     </xsd:restriction>                   </xsd:simpleType>                 </xsd:element>               </xsd:sequence>             </xsd:complexType>           </xsd:element>         </xsd:choice>         <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"  processContents="lax"/>       </xsd:complexType>     </xsd:element>   </xsd:schema>   <clientes>     <clave>1</clave>     <nombre>Juan</nombre>     <apellidos>del Pueblo</apellidos>     <telefono>3312-1234</telefono>   </clientes>   <clientes>     <clave>2</clave>     <nombre>Carlos</nombre>     <apellidos>Estrada</apellidos>     <telefono>4343-3323</telefono>   </clientes>   <clientes>     <clave>3</clave>     <nombre>Jimena</nombre>     <apellidos>Snchez</apellidos>     <telefono>2014-1914</telefono>   </clientes> </Root> 

Creating a CursorAdapter to Read XML

Open the Data Environment and right-click somewhere inside it. Select Builder from the context menu. You'll see the screen shown in Figure 7.10.

Figure 7.10. The DataEnvironment Builder.

The Builder will create almost everything we need to convert this XML file into a cursor, which can be manipulated as if it had come from a DBF, or from SQL Server. Of course we could also select Add CursorAdapter and specify all of the properties; but as is always the case with a new tool, the Builder does it right the first time and we don't. So we'll get trained, and get a working CursorAdapter in the bargain.

Since Visual FoxPro 3, the remote view has been the mechanism that permitted us to build a cursor containing the contents of a table from a remote source such as SQL Server, an Access MDB, or any other data source. The CursorAdapter lets us use a single syntax for any data source. This lets us migrate the application from DBF to some other data source at any time without changing any code; we simply change the data source from Native (that is, FoxPro DBF) to something else. We also get to practice the use of disconnected recordsets, which are the gospel according to Microsoft.

The first page of the DataEnvironment Builder (see Figure 7.11) lets us select the data source: ADO, Native (DBF), ODBC, and XML. Choose XML for this example. Click on the second tab of the DataEnvironment Builder, "2. Cursors", to continue.

Figure 7.11. Select the data source for the DataEnvironment Builder.

After you've selected XML, the Cursors page will launch the CursorAdapter Builder to create a CursorAdapter , which will open the XML file, read the XSD, and create the cursor's structure. Click on New to launch the CursorAdapter Builder shown in Figure 7.12.

Figure 7.12. The Cursors Screen in the DataEnvironment Builder.

FoxPro will immediately add a little cursor with a single field named F1. It's a placeholder, and it needs a little more information. Enter XML again as the data source type. (I know we just told it we were going to use XML, but I guess it forgot .) Select page 2, the Data Access tab of the page frame, and click on the Build button to the right of the label caption Schema, as shown in Figure 7.13. The resulting dialog will let you pick the name of the XML file from which you want the schema to be constructed .

Figure 7.13. Setting the Data Source Type in the CursorAdapter Builder.

Double-click the name of the file containing the XML. The builder will read the XML file and create the schema that appears in Figure 7.14.

Figure 7.14. Creating the schema for the cursor from the XML.

Click OK to close the CursorAdapter Builder and click OK again to close the Data Environment Builder.

Two steps remain to be done in code. First, open the Data Environment, and right-click on Cursor1, select Code. Select the AutoOpen procedure, and you'll see that the CursorAdapter Wizard has added several lines of generated code. At the end of this generated code, add these two lines:

 THIS.SelectCmd = FILETOSTR("XML1.XML") THIS.CursorFill() 

Next, add a grid on the form, open the Properties Sheet, and enter the name of the cursor (Cursor1) as the RecordSource . Press Ctrl+E to save the form and run it. The result appears in Figure 7.15.

Figure 7.15. A Grid Populated with the Contents of an XML File.