Local tables with the extension .dbf are the best-known identifying characteristic of FoxPro applications. The DBF, described in an earlier chapter, is a disk file with a header of 512 bytes if part of a database or 256 bytes if a free table, followed by 32 bytes to describe each field in the table, followed by fixed-length records preceded by a delete byte, which contains an asterisk if it has been marked for deletion with the DELETE command. (Tables that are members of a database have the name of the database container file [DBC] stored in the additional 256 bytes of the header.) Cursors (described later in this chapter) have the same format as free tables because they can't be created as members of a database. Part of the header information is stored in hexadecimal format, including LSB (least-significant byte first) format, so that reading cursors is a skill in itself. However, it is almost never necessary to do so, unless you're Paul Heiser and you sell a tool to fix them when they get damaged.

You can create a table either interactively or in code. Usually, when I'm building a system, I simply type

CREATE CUSTOMER

A Table Designer window opens up and allows me to begin describing the table, as shown in Figure 6.1.

Figure 6.1. The MODIFY STRUCTURE dialog.

Field names are limited to 10 characters if the table is not part of a database container. If a database container is open, the table is automatically added to it and can have long field names.

Tables can also be created using the CREATE TABLE command, which has this syntax:

CREATE TABLE SUBS (  ;
 SubNumber Integer,  ;
 StartDate Date,     ;
 Cost Numeric(6,2),  ;
 Expires Date,       ;
 CustomerID Char(10) )

Typically, you would use some data source, perhaps a table of possible fields to be included in a particular survey, and then generate a string containing the command. Then you would use macro expansion, (for example, &Cmd) to execute the command and create the table.

To use a DBF, you, well, USE a DBF:

USE ( TableName )

This command opens the named DBF in the current work area and moves the record pointer to the first record. In VB, there is no current select area as there is in FoxPro, and the position of the record pointer within a dataset serves as a surrogate for RECNO() (record number). In FoxPro, the current select area (SELECT()) and the current record number (RECNO()) are managed by the FoxPro environment, so that we can use them without resorting to the methods or properties of some data handling class. They're just there.

This notion of a current work area is not without its problems, and we've all experienced them. If you already have a table open, say in select area 1, USE ( TableName ) will close it and open the new table in select area 1. So you usually use the following syntax to ensure that you haven't just closed a table that you still needed:

SELECT 0    && get the next available work area
USE ( TableName )

You can also use abbreviated syntax:

USE ( TableName ) IN 0

However, this doesn't select the new table; that requires a separate SELECT command. So you would ordinarily use this:

USE CUSTOMERS IN 0
SELECT CUSTOMERS

The USE command can also specify an ALIAS. In a FoxPro program, when you issue the SELECT ( TableName ) command, you actually select the table's ALIAS. The alias is by default the table's name; however, if you're working with a class of tables that might have other names but represent a single type of data, for example timesheets, you can specify an alias, as shown in Listing 6.1.

FName = GETFILE ( [DBF], Where is the timesheet to import?]
IF EMPTY ( FName )
   RETURN
  ELSE
   USE ( FName ) IN 0 ALIAS TIMESHEET
   SELECT TIMESHEET
ENDIF

When a table is open, the command LIST STRUCTURE provides a list of the fields (columns) in the table. To save the resulting list to a file, use LIST STRUCTURE TO ABC.TXT. You can also use LIST STRUCTURE TO PRINTER NOCONSOLE to send it straight to a printer.

The contents of a field in a table are referred to using the syntax TABLENAME.FIELDNAME, for example, CLIENTS.PHONE. You can also SELECT a table, and then refer simply to the PHONE field because the alias of the current work area is assumed by default.

Visual Basic .NET doesn't have the concept of a default alias. You can't type Phone and assume that it will know what table to look in. In fact, it won't look in any table.

Cursors are temporary tables. You get a cursor when you issue a SQL SELECT statement to retrieve records from a DBF. You also get a cursor when you return records from SQL Server. And as we've seen, you can use XMLToCursor() to build a cursor from an XML string.

There is also a CREATE CURSOR command, which looks exactly like the CREATE TABLE command. It creates a temporary structure like that of a DBF, typically with the extension .tmp, which you won't see unless you use the DBF ( cursorname ) function to return the name. I've only come up with one use for this, in conjunction with the APPEND FROM ( tablename ) command, which requires a table name rather than a cursor name.

Cursors, unlike tables, can have long field names. This makes them perfect for storing the contents of cursors returned from SQL Server because few SQL database administrators limit themselves to 10-character field names.

There are a number of commands and functions that are meaningful while a table is open.

If buffering mode is set to 2, 3, 4, or 5, two versions of changed records are maintained. If the TableUpdate() function is called, the changes become permanent. If TableRevert() is called, the changes are discarded.

Indexes are implemented in FoxPro either as single-index IDX files (compact or not compact) or as tags in CDX (compact multiple index) files. To create a simple index, you use the command

INDEX ON ( Expression ) TO ( NameWithoutTheExtension ) COMPACT

To create a tag in a CDX file you issue the command

INDEX ON ( Expression ) TAG ( TagName )

To attach an index, you use the command SET INDEX TO ( IdxFileName ). You can attach several IDX and CDX files in a single SET INDEX statement. However, when you open a DBF, if a CDX file of the same name exists, it is automatically opened, although no tag order is set. To do that, you issue the command

SET ORDER TO TAG ( TagName )

or simply

SET ORDER TO ( TagName )

After an index has been selected, a SEEK command for a value that's in the index will find any matching record. Depending on the SET NEAR and SET EXACT commands, the record pointer can be set to the next record following the place where the record would have been had it existed, or to the end of the alias.

Local views are cursors created based on SQL SELECT statements stored in database containers. They don't have indexes, although if the SQL SELECT statement has an ORDER BY statement, it determines the order of the records. Views can be marked as updateable either in the database container or by using CursorSetProp() settings. When a view is marked as updateable, as the view is changed, the records in the underlying table are updated.

Remote views are the same as local views except that their data sources are not FoxPro tables. The sources can be SQL Server, or any ODBC data source. In fact, you can create a remote view to a FoxPro table using an ODBC driver, although as Nixon once said, it would be wrong. (He did it anyway, as some of us recall.) Remote views can also be updateable.

At one time, many of us hoped that remote views would provide a way to use SQL Server. We were wrong. Remote views work, but barely. So, instead, we use SQL Pass-Through (SPT).

SQL is supported directly in FoxPro. The name is meant to imply that if for some reason you don't want to use remote views, real men use SQL Pass-Through (SPT). The reason for the clear distinction is that for FoxPro people, having to build an INSERT or UPDATE string seemed incredibly difficult. After all, if we use DBFs, there's nothing to do; leave the record, or issue TableUpdate() if you used buffering, and the change is permanent. Issue APPEND BLANK on an unbuffered DBF, and you have inserted a blank record, a truly unnatural act in SQL.

As long as we're quoting Nixon, let me make one thing perfectly clear: To add a record in SQL, you must create and send a complete, syntactically correct INSERT statement to SQL, using SQL delimiters. SQL's syntax is slightly different than that of FoxPro's twangy dialect, and SQL Server makes no effort to understand its distant cousin. Similarly, the UPDATE and DELETE commands must be expressed in their full glory.

SQL offers several things that DBFs don't provide. BACKUP and RESTORE are easier in every way. User access control is built in, and it's virtually impossible to damage SQL tables or index files. We've all had to build our own mechanisms to deal with these three problems, although the excellent Stonefield Database extensions written by Doug Hennig do a great job in these areas and, therefore, are part of the essential FoxPro toolkit.

The ability to back up and restore a database is essential. A SQL database consists of only two files; the database itself and a log file where transactions are stored. To back up a FoxPro application, you have to get all users to close their applications, and then copy all DBF, FPT, and CDX files to a backup location. In SQL, BACKUP is a single command, and users don't have to exit the application.

When a backup is done, the log file is erased. The theory is that if it is necessary to restore a database from the last good backup, the transactions log that has accumulated since that backup can be used to restore the database up to the moment that the database failed. It is also possible to use the BACKUP command to simply erase the transactions log, and in fact that's how it is often used.

SQL only returns a handle to a user when a valid user ID and password are supplied. You can either provide a single user ID and password for all users of your application, or issue one to each user. In either case, you don't have to write a single line of code to manage database access. That can be the single reason that justifies using SQL Server instead of DBFs.

Finally, SQL Server supports indexes that are remarkably similar to those in FoxPro; in fact, the indexing technology used by Microsoft in the latest versions of SQL Server came from FoxPro, and uses the Rushmore technology first developed years ago by the Fox Software team in Toledo. FoxPro indexes can be corrupted; SQL Server indexes are almost impossible to damage, and can be rebuilt automatically by SQL Server.

If any of these three features are important to your client, SQL Server is well worth the additional cost. However, SQL requires knowing more than is required to use DBFs. If you want to use SQL Server, you're going to have to cozy up to the Query Analyzer and get to know it. Help is available, from the Help, Transact-SQL Help menu pad. Transact-SQL is the actual name of the command language used in SQL Server, just as it was when they bought it from Sybase. (Did I mention that Microsoft bought some of its products from other companies rather than developing them in-house? Even Word.) A quick primer follows.

The SQLConnect() command is used to supply the parameters necessary to connect to a connection previously defined in a database container (DBC), including (if desired) a user ID and password. If the UID and PWD are not supplied, SQL asks for them. This is necessary because a SQL connection consists at a minimum of the name of the server and the name of the database, and the SQLConnect() function provides no mechanism for naming them.

My preference is to use the SQLStringConnect command, which requires a complete connection string. If you don't know what connection strings look like, create an empty text file with the extension .udl, and double-click on it in Windows Explorer. The dialog that appears will allow you to build one, and will store it in the .udl file. Don't forget to select Microsoft OLE DB Provider for SQL Server on page 1 of the dialog. The usual server name is “(local)” if you've got SQL Server Developer Edition on your computer, and the default user ID of “sa” and a blank password are still there unless you changed them. Click the Test button to see if it worked. If you choose the Northwind database that is installed for testing purposes, your connection string will look like this:

Provider={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;

To use it, do this:

Str = [Provider={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;]
Handle = SQLStringConnect( str )

Either of these two commands returns an integer called a Handle. I used Handle as the variable name here, but use whatever you like.

SQLExec(Handle, Cmd) executes the string defined in the variable Cmd. The command can be any of the four SQL commands: SELECT, INSERT, UPDATE, or DELETE. It can also be a SQL stored procedure, or one of the built-in SQL stored procedures that begin with the letters sp.

If the command is a SELECT, the matching records are returned in a cursor named SQLResult by default. If a third parameter, a string containing a cursor name, is supplied, the result is returned in a cursor of that name.

Finally, SQLDisconnect(Handle) closes the connection. For convenience, SQLDisconnect(0) closes any and all open connections.

As we saw in full detail in Chapter 2, you can't bind the controls on your forms to the cursor returned by SQL because it breaks the data binding. The ControlSource property of each control must be the name of an alias and field that exist at the end of the Load event of the form. That's why we create a cursor in the Load event, and then “pour” the data form that the cursor returned either by a call to SQLEXEC() or by a call to a Web service into the previously defined cursor.

FoxPro also provides a few other SQL functions to return useful information about tables. You can do the same thing by instantiating an object based on the SQLDMO.DLL (SQL Data Management Objects) component that comes with SQL. On my computer, it's located in the following directory:

Program filesMicrosoft SQL Server80ToolsBin

It even comes with a pair of help files. The one called SQLDMO80.hlp is of absolutely no use unless you're already a SQLDMO expert. The other one, SQLDMO.CHM, is pretty cool and contains excellent examples. Look for how-to articles on the Internet, or visit my Web site.

Table 6.2 lists a few FoxPro functions that are available for use with SQL Server.

Some of these merit a few comments. SQLSetProp() can be used to establish asynchronous access to SQL Server, so that the program can request data and allow the user to go on doing other things. SQLMORERESULTS() returns the next N records, where N is the number of records you've specified to be returned in each packet of records in this “progressive fetch” scheme.

Transactions are considered by some to be the difference between trivial and serious database applications. I don't agree; I've built applications for some of the most important companies and nonprofit organizations in the world in which transactions were completely irrelevant. However, if you want to be able to commit both the header and the detail records of a purchase order, or commit neither if some problem occurs, transactions are how you do it. SQL Server supports transactions. To begin a transaction, issue the following command:

= SQLSETPROP(gnHandle, 'Transactions', 2)  && manual

FoxPro includes an Upsizing Wizard, which will “automatically” create tables in SQL Server corresponding to the DBFs in your application, migrate the data in your DBFs to SQL Server, and create remote views to be used in place of DBFs throughout your application. It sounds great, but it isn't.

SQL Server is a different technology. The data is kept elsewhere, and the supposition is that you'll bring only the record or group of records that are needed for the current form. In FoxPro, you get all of the records all the time. The SQL equivalent of USE CUSTOMERS is SELECT * FROM CUSTOMERS, which brings the entire table to each workstation. The slowdown can be glacial. So unless you change the architecture of each and every screen, performance will be awful, and your users will be outraged.

On the other hand, if your queries are properly constructed to minimize data traffic, SQL performance for very large numbers of records and a large number of users will actually improve. Index traffic is the single greatest cause of poor performance in DBF-based applications, and SQL Server completely eliminates it.

The CursorAdapter class permits us to deal with data of any sort, from DBFs to SQL Server to other ODBC data sources to XML Web Services, in a single way. Two new builders ease the transition, although when you know what's required, you may prefer to write the few required lines of code yourself.

When you open the Data Environment of a form and right-click anywhere on the design surface, the context menu will appear. It contains an option to Add CursorAdapter. When you know what properties are required and how to code them, you'll use this. For the moment, click on the Builder option.

The dialog shown in Figure 6.2 will appear.

Figure 6.2. The DataEnvironment Builder.

On the DataEnvironment Builder dialog screen shown in Figure 6.2, there are four data source types: ADO, Native (DBF), ODBC, and XML. This time, choose ADO. Click on the Use Connection String radio button and fill in the connection string shown. The Build button will walk you through the process. If your server isn't “(local)”, substitute the correct name; if you've changed the UserID and Password, change them as well. Test the connection, and then select Page 2, Cursors.

Note that the connection strings for ADO and for ODBC are different. The ADO connection string to connect to the Northwind database is this:

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;
Initial Catalog=Northwind;Data Source=(local)

whereas the ODBC connection string is this:

Driver={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;

Figure 6.3 shows the resulting dialog.

Figure 6.3. The Add Cursors page of the CursorAdapter Builder.

Click on New to start up the CursorAdapter Builder. A cursor named Cursor1 with a single field named F1 will be added to the Data Environment in preparation for specifying the fields that we want, and the modal CursorAdapter Builder will appear. I'm going to supply the name Customers for both the Cursor and its Alias. I again specify ADO as the Data Source Type.

I'm going to specify customers from California for this screen, so I select tab 2, Data Access, and enter the following SELECT command:

SELECT                   ;
  Customers.CustomerID,  ;
  Customers.CompanyName, ;
  Customers.ContactName, ;
  Customers.Country,     ;
  Customers.Phone        ;
 FROM Customers          ;
 WHERE Region='CA'

The Schema edit box is instantly populated with the body of a CREATE CURSOR command:

CUSTOMERID C(5), COMPANYNAME C(40), CONTACTNAME C(30), COUNTRY C(15), PHONE C(24)

The Data Access page should look like the one shown in Figure 6.4.

Figure 6.4. The Data Access page of the CursorAdapter Builder.

Page 3 of the CursorAdapter Builder allows me to instruct the CursorAdapter to automatically send UPDATE and/or INSERT commands to SQL Server as changes are made. If you're only using the records for display, you can leave this page unchanged. Click OK to save the changes.

Updates can either be done one row at a time, or as a batch for all records in the cursor. The only issue is performance; if sending all changes at once will cause an unacceptable delay, send one each time a row changes. I've selected all fields and specified that CustomerID is the key field, as shown in Figure 6.5.

Figure 6.5. Specifying the fields to update and the key.

Add a grid, change the RecordSource to Customers, and use Ctrl+E to run the form. Change the ContactName from Jaime Yorres to Joe Smith and close the form. Run the form again to verify that your changes were indeed saved to SQL Server. You can also open the Query Analyzer, select the Northwind database, and enter the same SELECT command we used to populate the CursorAdapter. Sure enough, you just updated a SQL table without using any code.

Recall from Chapter 2 that we can write our own CommandBuilder to create an UPDATE command. The BuildUpdatecommand function shown in Listing 6.3 will build the command string, and SQLExec ( Handle, Cmd ) will execute it.

FUNCTION BuildUpdateCommand
PARAMETERS pTable, pKeyField
Cmd = [UPDATE ]  + pTable + [ SET ]
FOR I = 1 TO FCOUNT()
    Fld = UPPER(FIELD(I))
    IF Fld = UPPER(pKeyField)
       LOOP
    ENDIF
    IF TYPE ( Fld ) = [G]
       LOOP
    ENDIF
    Dta = ALLTRIM(TRANSFORM ( &Fld ))
    IF Dta = [.NULL.]
       DO CASE
          CASE TYPE ( Fld ) $ [CMDT]
               Dta = []
          CASE TYPE ( Fld ) $ [INL]
               Dta = [0]
       ENDCASE
    ENDIF
    Dta = CHRTRAN ( Dta, CHR(39), CHR(146) )
* get rid of single quotes in the data
    Dta = IIF ( Dta = [/  /], [], Dta )
    Dta = IIF ( Dta = [.F.], [0], Dta )
    Dta = IIF ( Dta = [.T.], [1], Dta )
    Dlm = IIF ( TYPE ( Fld ) $ [CM],['],;
      IIF ( TYPE ( Fld ) $ [DT],['],;
      IIF ( TYPE ( Fld ) $ [IN],[],    [])))
    Cmd = Cmd + Fld + [=] + Dlm + Dta + Dlm + [, ]
ENDFOR
Dlm = IIF ( TYPE ( pKeyField ) = [C], ['], [] )
Cmd = LEFT ( Cmd, LEN(Cmd) -2 )            ;
    + [ WHERE ] + pKeyField + [=]         ;
    + + Dlm + TRANSFORM(EVALUATE(pKeyField)) + Dlm
RETURN Cmd
ENDFUNC

Which would you rather use?

If you want to manually code the CursorAdapter class, it's not difficult. Use the Properties sheet to see which properties were set by the builder:

Alias = [Customers]
DataSourceType = [ADO]
Name = [Customers]
Tables = [Customers]
Tag = [Driver={SQL Server};Server=VAIOVAIO;Database=Northwind;UID=sa;PWD=;]
Flags = 0

Three additional properties contain the notation See Init:

SelectCmd: See Init
UpdatableFieldList: See Init
UpdateNameList: See Init

The code written by the builder and placed in the Init of the DataEnvironment, shown in Listing 6.4, explains these three entries.

local llReturn
do case
    case not pemstatus(This, '__VFPSetup', 5)
        This.AddProperty('__VFPSetup', 0)
    case This.__VFPSetup = 2
        This.__VFPSetup = 0
        return
endcase
llReturn = dodefault()
*** Setup code: DO NOT REMOVE
***<SelectCmd>
text to This.SelectCmd noshow
SELECT                   ;
  Customers.CustomerID,  ;
  Customers.CompanyName, ;
  Customers.ContactName, ;
  Customers.Country,     ;
  Customers.Phone        ;
 FROM Customers          ;
 WHERE region='CA'
endtext
***</SelectCmd>
***<KeyFieldList>
text to This.KeyFieldList noshow
CUSTOMERID
endtext
***</KeyFieldList>
***<UpdateNameList>
text to This.UpdateNameList noshow
CUSTOMERID Customers.CUSTOMERID, COMPANYNAME Customers.COMPANYNAME, CONTACTNAME ;
Customers.CONTACTNAME, COUNTRY Customers.COUNTRY, PHONE Customers.PHONE
endtext
***</UpdateNameList>
***<UpdatableFieldList>
text to This.UpdatableFieldList noshow
CUSTOMERID, COMPANYNAME, CONTACTNAME, COUNTRY, PHONE
endtext
***</UpdatableFieldList>
*** End of Setup code: DO NOT REMOVE
*** Select connection code: DO NOT REMOVE
local loConnDataSource
set multilocks on
loConnDataSource = createobject('ADODB.Connection')
***<DataSource>
loConnDataSource.ConnectionString = ;
 [Driver={SQL Server};Server=VAIOVAIO;Database=Northwind;UID=sa;PWD=;]
***</DataSource>
loConnDataSource.Open()
This.DataSource = createobject('ADODB.RecordSet')
This.DataSource.CursorLocation   = 3  && adUseClient
This.DataSource.LockType = 3  && adLockOptimistic
This.DataSource.ActiveConnection = loConnDataSource
*** End of Select connection code: DO NOT REMOVE
if This.__VFPSetup = 1
    This.__VFPSetup = 2
endif
return llReturn

This looks a little complicated, but it's not bad when you understand what's happening. Note the enhancements to the TEXT TO <propertyname> command that allow you to populate properties on the fly.

oCA = GetCursorAdapter ( [Customers] )
oCA.CursorFill

FoxPro 7 had three functions (CursorToXML(), XMLToCursor(), and XMLUpdateGram()), which constituted collectively the entire toolkit for dealing with XML. The XMLAdapter class added in Visual FoxPro 8 greatly enhances FoxPro support for XML.

You can use the XMLAdapter to read XML and convert it to a FoxPro cursor. You can also use it to create XML strings. Using an object instantiated from an XMLAdapter, you can store one or more table objects and describe XML tables as cursors. The XMLADapter is based on MSXML 4.0 Service Pack 1 or later, which is installed in the “Install Prerequisites” phase of the installation of Visual FoxPro 8.

Using the XMLAdapter, you can

TEXT TO cXML NOSHOW
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
   xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
   xmlns:rs='urn:schemas-microsoft-com:rowset'
   xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
   <s:ElementType name='row' content='eltOnly'>
  <s:AttributeType name='xmlfield' rs:number='1'
 rs:writeunknown='true' rs:nullable='true'>
   <s:datatype dt:type='number' rs:dbtype='currency' dt:maxLength='8'
  rs:scale='4' rs:precision='6' />
</s:AttributeType>
  <s:extends type='rs:rowbase'/>
   </s:ElementType>
</s:Schema>
<rs:data>
   <z:row xmlfield='12.12'/>
</rs:data>
</xml>
ENDTEXT

CLOSE DATABASES ALL
CLEAR
LOCAL oXMLAdapter as XMLAdapter
oXMLAdapter = NEWOBJECT('XMLAdapter')
oXMLadapter.LoadXML(cXML)
IF oXMLAdapter.Tables.Item(1).Fields.Item(1).DataType <> "Y" THEN
   ? 'Failed'
ELSE
   oXMLAdapter.Tables.Item(1).ToCursor()
   oXMLAdapter.XMLNamespace=""
   oXMLAdapter.ReleaseXML(.F.)
   oXMLAdapter.XMLSchemaLocation='c:myxmlfile.xsd'
   oXMLAdapter.ToXML('c:myxmlfile.xml',,.T.)
   oXMLadapter2 = NEWOBJECT('xmladapter')
   oXMLAdapter2.XMLSchemaLocation='c:myxmlfile.xsd'
   oXMLAdapter2.LoadXML('c:myxmlfile.xml',.T.,.T.)
ENDIF

oxml.LoadXML("xml1.xml",.T.)  && Read the file (second parm is "is this a file?")
oTable =oxml.Tables.Item(1)   && Get a reference to the XMLTable object
otable.ToCursor(.F.,"ABC")
BROWSE&& Cursor "ABC"

LOCAL loXMLHTTP AS       [MSXML2.IXMLHTTPRequest]
loXMLHTTP = CREATEOBJECT ( [MSXML2.XMLHTTP.4.0] )
Cmd = [SELECT * FROM CLIENTS WHERE ClID = 1]
Cmd =  CHRTRAN ( Cmd, [ ], [+] )
loXMLHTTP.Open([GET], [http://localhost/Fox/GetClientRecord.asp?cmd=] + Cmd )
loXMLHTTP.Send()
XMLTOCURSOR (loXMLHTTP.ResponseBody)
CursorSetProp([Buffering],5)
BROWSE TITLE [Make changes and press Ctrl+W to save them]

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/ActualizarRegistroCliente.asp] )
loXMLHTTP.Send()

oXML.LoadXML ( lcXML )
MESSAGEBOX( "Diffgram loaded", 48, "Received by Web Service" )

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

USE customers
loxml.addTableSchema("Customers")
loxml.ToXML("lcxml")
MESSAGEBOX(  lcxml )

Building a Web Service in FoxPro 8

You can build a Web service in FoxPro 8 in minutes. Create a project named WebProject in a directory called WebProject. Add a code file called WebClass, and enter the following code:

* WebClass.PRG
DEFINE CLASS WebClass AS Custom OLEPUBLIC

Function StateList AS String
SELECT * FROM D:WebProjectStates INTO CURSOR XStates
CURSORTOXML( "XStates", "lcXML" )
USE IN States
USE IN XStates
RETURN lcXML
ENDFUNC

ENDDEFINE

Compile it as a multithreaded DLL. Then, open the Task Pane and select Publish Your XML Web Service. Click on the button with the three little dots and select the DLL you just created. The dialog should look like Figure 6.6.

Figure 6.6. The XML Web Services Publisher dialog.

TIP

If you're recompiling after trying to use the service, the Internet Information Server will have it in use, and you'll get the message file access is denied (dllname); open a command window and type IISRESET to free it.

Click on the Advanced button and verify that the only checked methods are the ones you want to expose, and then click on the Generate button. You should see the message box shown in Figure 6.7.

Figure 6.7. The XML Web Services Publisher Results dialog.

If you instantiate the DLL directly, you can see the output. This code

oWS = CREATEOBJECT ( "WebProject.WebClass" )
? oWS.StateList

produces this output (I didn't list all of the states):

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData>
    <xstates>
        <stateabbr>AL</stateabbr>
        <statename>Alabama</statename>
    </xstates>
    <xstates>
        <stateabbr>CA</stateabbr>
        <statename>California</statename>
    </xstates>
    <xstates>
        <stateabbr>DE</stateabbr>
        <statename>Delaware</statename>
    </xstates>
    <xstates>
</VFPData>

Using this code remotely is extraordinarily easy in Visual FoxPro 8. Open a form on another machine, put a grid on the form, and set the RecordSource to States. It doesn't exist yet, but it will. Open the Load event of the form. Now, open the Toolbox, and click on My Web Services. The WebClass service you just registered is there! Drag and drop it into the load event, and you'll only have to add the two shaded lines in the following code:

LOCAL loWebClass AS "XML Web Service"
* LOCAL loWebClass AS "MSSOAP.SoapClient30"
* Do not remove or alter following line.
* It is used to support IntelliSense for your XML Web service.
*_VFPWSDef:loWebClass=http://localhost/WebClass.wsdl,WebClass,WebClassSoapPort
LOCAL loException, lcErrorMsg, loWSHandler
TRY
    loWSHandler = NEWOBJECT("WSHandler",IIF(VERSION(2)=0,"",;
                          HOME()+"FFC")+"_ws3client.vcx")
    loWebClass = loWSHandler.SetupClient("http://localhost/WebClass.wsdl", ;
                        "WebClass", "WebClassSoapPort")
                Call your XML Web service here.
                       * ex: leResult = loWebClass.SomeMethod()
         lcXML = loWebClass.StateList           && ADD THIS LINE
         XMLToCursor ( locXML, "States" )      && ADD THIS LINE
CATCH TO loException
    lcErrorMsg="Error: "+TRANSFORM(loException.Errorno)+" - "+loException.Message
    DO CASE
    CASE VARTYPE(loWebClass)#"O"
        * Handle SOAP error connecting to web service
    CASE !EMPTY(loWebClass.FaultCode)
        * Handle SOAP error calling method
        lcErrorMsg=lcErrorMsg+CHR(13)+loWebClass.Detail
    OTHERWISE
        * Handle other error
    ENDCASE
    * Use for debugging purposes
    MESSAGEBOX(lcErrorMsg)
FINALLY
ENDTRY

Press Ctrl+E, and the form runs as advertised. You can use an XMLAdapter to move this data to a cursor, then save changes as a diffgram and send the diffgram back to another function in the Web service, which uses the code shown there to update the original data table.

For me, the ability to quickly and easily build FoxPro Web services is the biggest improvement in version 8.

Data access in FoxPro is easy for simple tasks, and not much harder for more complex ones. In .NET, it's also pretty easy. This is partly due to the fact that XML is not just a transport in .NET; XML is how Microsoft spells D-B-F.

In Visual Basic .NET, all recordsets are disconnected. You must create a cursor, bring the data into the cursor, and show it to the user. After any changes are made, you construct a command to send the data back to the source from whence it came. This is the case regardless of the data source. There are no discounts for local tables in Visual Basic .NET.

Visual Basic .NET uses datasets in the same way that we use cursors in FoxPro. You create a dataset, then call the Fill method of the associated DataAdapter to pour the data into the dataset. After any changes, you call a CommandBuilder object to create an UPDATE, INSERT, or DELETE command to be sent to the driver that actually updates the data source. It's the only way it works.

Connections are strings that tell the driver how to connect to a data source. Data sources can be OLEDB or ODBC, or they can be managed providers. Managed means “written in .NET,” and for this reason they are more efficient (faster) than their OLEDB counterparts. ODBC drivers are just dreadful and are usually used only if there's nothing else available. There's a pretty good third-party market in ODBC drivers, and if you must use a data source supported by ODBC, you should look into them. Microsoft gives away several ODBC drivers for free, and as you might expect, their price is a good indication of their value.

A connection string to the Northwind database on SQL Server accessed from FoxPro was shown earlier in Listing 6.4. It's the same for Visual Basic .NET:

Driver={SQL Server};Server=VAIOVAIO;Database=Northwind;UID=sa;PWD=;

However, you can also register a SQL Server database as an ODBC data source by running ODBCAD32.EXE from the command line or by clicking on the ODBC Data Sources icon in the Control Panel. The resulting dialog lets you select the driver, specify the database, and optionally provide a user ID and password. If the user ID and password aren't supplied in the connection definition, the user will have to specify them every time a connection is made. Experiment with this a few times and talk to whoever is paying for it before you decide how to proceed. It makes a difference.

In the Visual Studio IDE, there is a Server Explorer. You can open it by selecting View, Server Explorer from the menu, or by pressing Ctrl+Alt+S, whether a project or solution is open or not. The Server Explorer is shown in Figure 6.8.

Figure 6.8. The Server Explorer.

You can define connections in advance. Because a project will often deal with only two databases, the test version and the production version, this simplifies the process of switching between one and the other.

To create a new connection, right-click on Data Connections and select Add Connection. You'll see the Data Link Properties dialog. This is exactly the same dialog that is produced if you create an empty text file with the extension UDL, as we saw earlier.

Page 1 of the dialog is the Provider page. There are about 20 that have been installed on my laptop by various product installations that I've done. If you buy and install drivers from third parties, there will be a few more. Notice that there is a Microsoft OLE DB Visual FoxPro driver, so if you wondered whether Visual Basic .NET will support your FoxPro tables, now you know. Note that the FoxPro driver option supports either a DBC or a Free Tables Directory. Experiment with both and verify that your choice does what you want it to do.

If you select a provider that has password protection, you'll be asked to supply a password. For most applications, a single user ID and password combination for all users is perfectly adequate. You can go to the Enterprise Manager in the Start, Programs, SQL Server drop-down menu to add a user.

You can also add categories of users and give different rights to each, although the granularity of SQL Server database access may not be adequate for your requirements if you want to control access down to the pageframe or field level. However, if your purpose is to keep users from updating records that they're only supposed to read, producing an error message when they click Save is a pretty blunt instrument compared to simply disabling the Edit button to begin with if they don't have editing rights. Users appreciate subtlety.

Notice on the third page of the Data Link Properties dialog that you can limit access to read-only, read-write, read-write share, read-write exclusive, or write-only. Again, these are probably more than you need in most cases. The default Share Deny None is probably just what you need. It supports optimistic buffering, which means that you're responsible for ensuring that one user's changes don't overwrite another's.

After you save a connection, you can drag and drop it onto a form, and it will be used to connect to the data source. It's used either directly by a command object or by a data adapter.

A DataAdapter is a mechanism that opens a connection and executes a SQL SELECT statement. It also automatically creates UPDATE, INSERT, and DELETE statements if a unique key is specified in the SELECT statement. That's why it's so terribly important in the SQL world to have a single unique key. Many FoxPro developers have developed the nasty habit of using compound keys, for example, InvoiceNum + LineNum for a detail record of an invoice. That's a spectacularly bad idea even in FoxPro; in SQL Server, it's unthinkable. So before you try to use a DataAdapter to connect to your data, add a unique integer key or a GUID to each of your data tables. I'll wait right here.

Good, you're back. Let's continue.

Using a DataAdapter with a FoxPro Table

Create a connection to the Customers table. If you didn't download the code, just copy the Customers table that comes with the FoxPro Northwind samples to a directory where you can play with the data without doing any permanent damage. Be sure to use COPY TO Customers WITH CDX because without an index, the DataAdapter doesn't have a clue, as you'll see. Create a connection to the directory as a Free Table directory in the Solution Explorer.

Next, open the Visual Basic .NET IDE and select File, New Project from the menu. Choose Visual Basic, Windows Application. Make sure that the project is going where you want it to go, and note that the project name is also the name of a new directory that Visual Studio is going to create for you. Visual Studio will create a project with five standard .NET namespace references needed to build a forms project: System, System.Data, System.Drawing, System.Windows.Forms, and System.XML. Remember that last one. It's there because forms need data, and XML spells data in .NET. It also adds an AssemblyInfo.vb file (like a project information file) and Form1.vb.

That first form is usually your Main form, which will contain your logo and your application's main menu. However, you can also build an application consisting of just a single form, and that's what we'll do here. Use F4 to open the Properties window and change the Text property to "My customers".

Next, open the Solution Explorer and add the connection you just created by dragging and dropping it on the form. Visual Basic .NET will ask you if you want to add a user ID and password. For now, ignore it.

Next, open the Toolbox with Ctrl+Alt+X and add an OleDBDataAdapter from the Data toolbox category. The resulting wizard asks what connection to use, and defaults to the one we had added previously Next, the wizard needs to know from which fields, and from which tables you want to generate data (see Figure 6.9).

Figure 6.9. Adding a DataAdapter—specifying a connection.

The wizard will offer to use SQL statements, create new stored procedures, or use existing stored procedures. You'd be surprised how many shops don't build the screens until some database guy has approved the SELECT, INSERT, UPDATE, and DELETE code and written procedures for them in advance. Select Use SQL Statements and click the Next button.

RANT

<rant>The advantage in stored procedures is that after you've built them, migrating to another vendor's SQL is harder. Uh, sorry, that's the advantage for the vendor. The advantage for us is—uh, sorry, there isn't any.</rant>

In Figure 6.10, the DataAdapter Configuration Wizard is looking for a SELECT statement that returns the records you want.

Figure 6.10. Enter a SELECT statement for the DataAdapter.

If you want all customers from California, type this:

SELECT * FROM Customers WHERE Region = 'CA'

SQL can use the SELECT statement and the unique index values to generate the code for the INSERT, UPDATE, and DELETE commands. However, if you only want a SELECT statement, click on the Advanced Options button and uncheck the Generate Insert, Update and Delete Statements check box. Also, if you don't want the code-generated SQL statement to verify that no fields have been changed since the SELECT, uncheck the Use Optimistic Concurrency check box. It improves performance, and in many cases it's not necessary. You be the judge of the probability that two people will try to change the same record on two workstations at the same exact instant. It's usually infinitesimal.

If you want to add a parameter to a query, write the SELECT statement as follows:

SELECT * FROM Customers WHERE (CustomerID=?)

This will cause the IDE to build a parameterized query. To execute it, you'll need to supply a value for the parameter in code before executing the Fill command:

OledbDataAdapter1.SelectCommand.Parameters(0).Value = 1

If this looks a lot like a local view in FoxPro, it should. When you open a view in FoxPro, you're executing a SELECT command. When you close the view, the view itself uses the SELECT command and the unique index key to build and execute INSERT, UPDATE, and DELETE commands as needed to update the source table. That's exactly how the DataAdapter works. You just get to see all of the details.

Right-click on the DataAdapter and select Generate Dataset. Select New, and provide the name dsCustomers. This builds an XSD file describing the schema and a .vb file containing property procedures for all fields. We'll look at this in more detail a few pages ahead, under the heading “Datasets.” For now, we'll just build one and use it.

Finally, add a grid to the form. Open the Toolbox using Ctrl+Alt+X, click on the Windows Forms controls section heading, and drag a DataGrid to the form. Open the Properties sheet, click on DataSource, and from the pull-down list select dsCustomers1.Customers. You can also use the Properties window to specify the dataset name dsCustomers1 as the DataSource, and the table name Customers as the DataMember, for the data grid.

This is necessary because a dataset can contain several tables, and we just want one for the grid. (If you like, select dsCustomers1 and you'll see a little plus sign, which you have to expand manually to pick the table and display the table in the grid. Grids know how to work with datasets, even if they have multiple tables or hierarchical datasets.)

But we're not done. We have to write some code—one line of code. Double-click anywhere on the form except the grid, and the code window will open with a first and last line of a Form_Load event wired to the Load event of the form via a Handles clause. Type in the following line of code:

Me.OleDbDataAdapter1.Fill(DsCustomers1)

You don't need the “Me.”, but it brings up IntelliSense and saves you some typing. Use the Tab key to select the current suggestion and move to the end of the selected text; or, use a period to do the same thing and add a period at the end. The Fill command of the DataAdapter was built when we constructed the SELECT command, and the dsCustomers1 dataset was constructed from the DataAdapter's SELECT command, so they're guaranteed to be conformable.

Press F5 to run the application. That's one line of code. In FoxPro, it also takes one line of code—a USE statement in the LOAD event of the form. So far, it's a close race.

NOTE

I've had a few problems with the FoxPro OLE DB driver in Visual Basic .NET. During the writing of this chapter, the DataAdapter Configuration Wizard began inserting double quotes around my table name, so that not even the SELECT command would work. And many times it failed to generate UPDATE and DELETE commands for unspecified reasons. I had no such problem with the SQL Server or Access drivers.

Windows Form Designer Generated Code

Me.SqlUpdateCommand1.CommandText = "UPDATE Customers SET CustomerID = @Customer
ID, CompanyName = @CompanyName, Contac" & _
"tName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City =
 @" & _
"City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone =
@P" & _
"hone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Ori
gi" & _
"nal_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Ori
gi" & _
"nal_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Origin
a" & _
"l_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactNa
me" & _
" IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle O
R " & _
"@Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Origi
na" & _
"l_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Origin
al" & _
"_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR
 @" & _
"Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCo
de" & _
" OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Origin
al" & _
"_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, Co
mp" & _
"anyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country
, " & _
"Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)"

A dataset is a cursor. Want to see how it works? Open a new Visual Basic blank project, and then open the Solution Explorer using Ctrl+Alt+L, right-click on the project name, and select Add New Item. Select Dataset from the available selections (others include XML file and XML Schema and others). When the designer opens, use Ctrl+Alt+S to open the Server Explorer, and then pick any connection, expand it to reveal its tables, expand Tables, and drag any table name to the designer surface. You can view the result either as XML or as a table.

Typed datasets in Visual Basic .NET are defined by

Use Ctrl+Alt+L to open the Solution Explorer, and then click on the Show All Files icon at the top of the window. Notice that the dataset you just created has a plus sign, indicating that there's more below it. Click on the plus sign to expand the tree, and you'll see a file with the same name and a .vb extension. Double-click on it to open the file. It's a Visual Basic class that describes the tables in the dataset.

When you right-click on the Windows form project and select Add, Add New Item, one of the options is Data Form Wizard, as shown in Figure 6.11.

Figure 6.11. Double-click to Select the Data Form Wizard project.

When you select Data Form Wizard, the screen shown in Figure 6.12 appears. Click on the Next button to proceed.

Figure 6.12. The Data Form Wizard.

If I've already created a typed dataset, I can use it in the form that the Data Form Wizard is building, as shown in Figure 6.13. Typed datasets belong to the project, not to a form, and can be shared by several forms.

Figure 6.13. Selecting an existing typed dataset.

The wizard will include a Load and (optionally) an Update button if you check the check box (see Figure 6.14, about halfway down the dialog form).

Figure 6.14. Setting options for the Data Form Wizard.

Finally, the customer table and all of its fields are selected by default. Click the Next button to go to the next step (see Figure 6.15).

Figure 6.15. Selecting the table and all fields.

The next screen (see Figure 6.16) is the one that I was waiting for. Do I want to present data to my users as a grid (no) or as a record (yes). I don't even know why they offer the first option, although I suppose it's easy to code. But editing records in a grid is almost never a desirable technique. So, pick Single Record in Individual Controls and click Finish. In about three seconds, you've got screen! Forms like the one shown in Figure 6.16 are where I spend most of my day, and this one was written for me in a few seconds. All I have to do is go in and tweak it.

Figure 6.16. Specifying the screen type in the Data Form Wizard.

Before we can run this, there's one thing we have to do. I started this project with another form, then added this one. When you press F5 to compile and run a project, you have to tell the project which is the startup form—like FoxPro's SET MAIN selection on the Project menu popup. To do this, click on the solution, then on the project, to make sure it's selected. Next, right-click on the project and select Properties from the context menu. You should see the screen shown in Figure 6.17. Pick the form that you want to use as the startup for the executable.

Figure 6.17. Setting the Startup form.

Press F5 to run the application. As shown in Figure 6.18, you can indeed move from one record to another, make changes and save them, and add and delete records.

Figure 6.18. The generated data form in action.

However, when I click the Load button, nothing happens! It turns out that because I told the wizard to use my existing dataset, it left me with the responsibility of loading the dataset. No big deal, right?

The code for this generated screen is 360 lines long. It returns all of the records in the table from SQL, then uses the VB equivalent of SKIP to move backward and forward through the tables. It also assumes that my users can just start typing in any field whenever they want, and that they'll know that they need to “update” before they can go to another record—their wrist will be slapped with an error message if they don't. So it has little to commend it as a database application. But it works, and it only took a few seconds.

Here's the code generated by the Data Form Wizard:

Private Sub btnCancel_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnCancel.Click
  Me.BindingContext(objdsCustomers, "Customers").CancelCurrentEdit()
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub btnDelete_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
  Handles btnDelete.Click

  If (Me.BindingContext(objdsCustomers, "Customers").Count > 0) Then
    Me.BindingContext(objdsCustomers, "Customers").RemoveAt( _
    Me.BindingContext(objdsCustomers, "Customers").Position)
    Me.objdsCustomers_PositionChanged()
  End If
End Sub

Private Sub btnAdd_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnAdd.Click
Try
'Clear out the current edits
  Me.BindingContext(objdsCustomers, "Customers").EndCurrentEdit()
  Me.BindingContext(objdsCustomers, "Customers").AddNew()
  Catch eEndEdit As System.Exception
   System.Windows.Forms.MessageBox.Show(eEndEdit.Message)
   End Try
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub btnNavFirst_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnNavFirst.Click
  Me.BindingContext(objdsCustomers, "Customers").Position = 0
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub btnLast_Click( +
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnLast.Click
  Me.BindingContext(objdsCustomers, "Customers").Position = _
 (Me.objdsCustomers.Tables("Customers").Rows.Count - 1)
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub btnNavPrev_Click( +
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnNavPrev.Click

  Me.BindingContext(objdsCustomers, "Customers").Position = _
 (Me.BindingContext(objdsCustomers, "Customers").Position - 1)
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub btnNavNext_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnNavNext.Click
  Me.BindingContext(objdsCustomers, "Customers").Position = _
 (Me.BindingContext(objdsCustomers, "Customers").Position + 1)
  Me.objdsCustomers_PositionChanged()
End Sub

Private Sub objdsCustomers_PositionChanged()
  Me.lblNavLocation.Text = (((Me.BindingContext(objdsCustomers,
  "Customers").Position + 1).ToString _
  + " of  ") + Me.BindingContext(objdsCustomers, "Customers").Count.ToString)
End Sub

Private Sub btnCancelAll_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnCancelAll.Click
  Me.objdsCustomers.RejectChanges()
End Sub

This is interesting code. There's something called a BindingContext that apparently belongs to the form (I didn't instantiate it, so it must be a part of Windows.Forms.Form). BindingContext takes two parameters, a dataset and a tablename, and contains a count property (like RECCOUNT()) and a position (like RECNO()). To skip to the next record, all you have to type is

 Me.BindingContext(objdsCustomers, "Customers").Position = _
(Me.BindingContext(objdsCustomers, "Customers").Position + 1)

That's the equivalent of FoxPro's

SKIP

It looks as if the BindingContext is also responsible for deleting records. Here's the command:

Me.BindingContext(objdsCustomers, "Customers").RemoveAt( _
Me.BindingContext(objdsCustomers, "Customers").Position)

and here's the FoxPro equivalent:

DELETE

How about adding? I found it. It's this:

Me.BindingContext(objdsCustomers, "Customers").AddNew()

That's not much harder than FoxPro's equivalent:

APPEND BLANK

I'm starting to like Visual Basic .NET. But I still like FoxPro better.

Private Sub btnLoad_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnLoad.Click
  Dim c As New SqlClient.SqlConnection
  c.ConnectionString = "Server=VAIOVAIO;Database=Northwind;UID=sa;PWD=;"
  c.Open()
  Dim da As New SqlClient.SqlDataAdapter("SELECT * FROM CUSTOMERS", c)
  da.Fill(Me.objdsCustomers, "Customers")
End Sub

In FoxPro, if you open the Data Environment, click on a cursor name, and drag the word Fields onto the surface of the form, you get all of the fields in the table, using your classes, with a label to the left of them with a name for the field to the right of the label. The controls that are used are your own subclassed controls from your own VCX file, based on your settings on the Field Mappings page of the Tools, Options dialog. The labels are the text stored in the corresponding records in the database container, if you used one. The ControlSource property of each control is automatically filled in, and when you open the form and navigate through your data, a simple THISFORM.Refresh shows you that the record pointer has moved.

In Visual Basic .NET, if you create a dataset from a DataAdapter, then drag the dataset onto the form, you get bupkis—nothing. No controls are placed on the form.

Open up the form generated by the Data Form Wizard, right-click on any of the text boxes, and look at the DataBindings property. You'll have to click on the plus sign to expand the property and see its Text property.

I've opened the drop-down for the Text property so that you can see how it has been populated, as shown in Figure 6.19.

Figure 6.19. Populating the Text property of DataBindings.

Now you know that you can define a dataset, then drop controls on the form and fill in the Text DataBindings property with individual fields from the dataset, and it will work.

However, if you open up the code, you'll find this:

Me.editCustomerID.DataBindings.Add(New System.Windows.Forms.Binding("Text", _
Me.objdsCustomers, "Customers.CustomerID"))

It seems that data binding is actually the result of a command that we can write ourselves. The syntax in pseudocode is this:

Control.DataBindings.Add ( Binding("Text", ds, "table.field"))

where ds is our dataset, Control is an object reference to each control on the form, and "table.field" is a string containing the table and field names as part of a typed dataset. It's possible to refer to them in some other way, but this is the easiest.

That's one of the reasons why we'll want to use typed datasets. If I can loop through all of the controls on the form and bind them to my dataset using names from a typed dataset, I don't have to go through dozens of controls on dozens of forms clicking and selecting the field name for each one. This is a good thing.

Dim oTable as DataTable
For each oTable in MyDataset.Tables
    Debug.WriteLine oTable.TableName
End For

Dim oTable As New DataTable = MyDataSet.Tables(0)

For Each oTable In objdsCustomers.Tables
    Dim oRow As DataRow
    For Each oRow In oTable.Rows
        Dim I As Integer
        For I = 0 To oRow.ItemArray.Length - 1
            Debug.WriteLine(oRow.ItemArray(I))
        Next
        Debug.WriteLine(oTable.TableName)
    Next
Next

Row("CustomerID") = 1234

Row(0) = 1234

Private Sub PrintValues(ByVal myTable As DataTable)
    Dim myRow As DataRow
    Dim myColumn As DataColumn
    For Each myRow in myTable.Rows
       For Each myColumn In myTable.Columns
          Console.WriteLine(myRow(myColumn))
       Next
    Next
 End Sub

XML Web services in .NET are as easy as they are in Visual FoxPro 8, and a heck of a lot easier than they are in earlier versions of Fox. Create a new project, and pick ASP.NET Web Services from the Visual Basic New Project dialog, as shown in Figure 6.20.

Figure 6.20. Creating a Visual Basic New Project.

Notice that the location of the project is http://localhost/Chapter6WebService. That means that the location is actually C:InetPubWWWRootChapter6WebService. IIS is only allowed to see virtual directories that have been expressly added. The only exception is that it can also see any subdirectory under the C:InetPubWWWRootdirectory that's not hidden from it. So by placing Web service projects there, Visual Studio .NET publishes them in the most logical place. And, because I've probably created 300 Web service projects on my computer since the early beta of .NET, I really appreciate anything that makes them easier to find. Then there's that whole “senior moment” thing.

I also changed both the external filename of Service1.asmx and the internal class name of the service to MyWebService. Changing the filename is easy; just right-click on Service1.asmx in the Solution Explorer, select Rename, and then type the new name. Then select the Web service design surface, then right-click and select Properties, and change the Service1 name to MyWebService as shown in Figure 6.21.

Figure 6.21. Naming a Visual Basic .NET XML Web service.

To write the code, double-click on the design surface, or select View, Code from the IDE menu. You'll see some commented code (probably lime green text) with placeholder code that looks like this:

'<WebMethod()> _
'Public Function HelloWorld() As String
'   Return "Hello World"
'End Function

The <WebMethod()> attribute prefix exposes your new Web service for testing on a Web page test bed that's automatically generated by Visual Studio. We'll need that, but everything else has got to go.

But first, we need a connection and a DataAdapter. Open the toolbox and drag a SQLDataAdapter to the design surface. As before, select the Northwind connection, pick the Customers table with a SELECT * FROM Customers statement, and click Finish to end the process.

Next, right-click on the DataAdapter and select Generate Dataset. Use dsCustomers as the name.

dsCustomers is a typed dataset. It's a type, as are integers and dates. Functions return types. So we'll return an XML string of type dsCustomers.

Now we're ready. Double-click on the form to open the code window. Replace the entire sample function with this:

<WebMethod> Public Function GetCustomers() As dsCustomers
   Dim Customers As New dsCustomers()
   SqlDataAdapter1.Fill(Customers)
   Return Customers
End Function

So we instantiate a dsCustomers object named Customers, use the built-in SELECT statement in the DataAdapter to fill the Customers dataset (which, as we know, is XML), and return the selected records in an XML string to the requestor.

Press F5 to run this, and the screen shown in Figure 6.22 appears.

Figure 6.22. The generated test bed fir your XML Web Service.

Visual Studio has created a test bed to test our Web service. This test bed page will list all of your Web service's functions, and will allow you to test any of them that have parameters that are primitive types and can, therefore, be entered on a Web page for use in testing. Thus any calls that require a dataset as input (such as an update routine) will be listed, but not testable.

Our new function GetCustomers is testable, so click on it. This brings up the next screen, shown in Figure 6.23. This screen appears in case we have parameters to enter. There aren't any in this case, so just click on the Invoke button, and you'll see the results in a browser window shown in Figure 6.24. I scrolled down to a customer in Venezuela, which is where I am at this instant, in a restaurant in Caracas, having cake and eating it too (I've never understood that statement…).

Figure 6.23. The Add Web Reference dialog.

Figure 6.24. The CustomersApp form running with a Web service.

<WebMethod> Public Function UpdateCustomers( _
  ByVal CustChanges As dsCustomers) _
  As dsCustomers
   If Not (CustChanges Is Nothing) Then
      SqlDataAdapter1.Update(CustChanges)
      Return CustChanges
   Else
      Return Nothing
   End If
End Function

We'll need a Windows form application to test this Web service, so add a Windows Form project to the solution. Call it WinClientCustomers. On the form, add a DataGrid and two buttons named LoadData and SaveData.

To use this Web service, you need one method in your client form to get the records, and a command button to extract any changed records from the dataset and send them back. You'll need a dataset and a data source. But in this case, it's coming from a Web service. We need to write code that goes to the Internet for the data. And that's exactly where we go for the data connection as well.

Right-click on the project and select Add Web Reference from the context menu. You'll see the screen shown in Figure 6.23.

Click on the Web Services on Local Machine link. Pick your new Web service from the resulting list. A Web References item will be added to the WinClientCustomer project, right under References.

Next we need to add a dataset. What about the connection and DataAdapter components? We don't need them. Add a dataset from the Data tab of the Toolbox, and a dialog will appear asking if you want to use the Typed Dataset with the Web Reference shown in the combo box. Click OK to accept it.

Listing 6.11 has the code for the Load event of the form:

Private Sub LoadData_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles LoadData.Click
    Dim ws As New WinClientCustomers.localhost.MyWebService
    ws.Credentials = System.Net.CredentialCache.DefaultCredentials
    DsCustomers1.Merge(ws.GetCustomers())
End Sub

Listing 6.12 contains the code needed to save changes to the dataset.

Private Sub SaveData_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles SaveData.Click
    If DsCustomers1.HasChanges() Then
       Dim ws As New WinClientCustomers.localhost.MyWebService
       Ws.Credentials = System.Net.CredentialCache.DefaultCredentials
       Dim diffCustomers As New WinClientCustomers.localhost.dsCustomers
       diffCustomers.Merge(DsCustomers1.GetChanges())
       diffCustomers = ws.UpdateCustomers(diffCustomers)
       DsCustomers1.Merge(diffCustomers)
    End If
End Sub

The only thing remaining is to enter the DataSource for the grid. Open the drop-down and select DsCustomers1.Customers, the typed dataset. Now we're ready to give it a try.

Press F5 to run this application. This will bring up the screen shown in Figure 6.24.

Change something and click the Update button, and then close the form and run it again to see if the changes were saved. Worked on mine the first time.

You can hardly beat a Web service that only takes 14 lines of code, and a client form that acquires remote data and saves the changes back to the data source in another 15 lines of code. That's less coding that it takes in FoxPro.

To be fair, there are other issues that need to be addressed in a commercial application. Concurrency, one record per form, data binding, and a nice search screen are all things that take time and code. But with this simple foundation we can build great structures. And if this recession ever ends, we might even get paid for it.