Introducing the CursorAdapter Class
One of the most exciting new features of Visual FoxPro 8 is the CursorAdapter class, which provides a common interface for working with data from many different sources.Chuck takes you with him on an adventure in exploring how to use CursorAdapter to change the way you relate to data in VFP 8, whether native tables, ODBC, OLE DB, or XML.
With the introduction of the CursorAdapter class in VFP 8, the Fox team has finally made a significant change in the way a VFP application accesses data, whether it is native or from a remote data source. Additionally, the setup of CursorAdapter classes will be somewhat familiar to those who are well-versed in the behavior of views and SPT, as well as the alternative data sources of using ADO RecordSets or XML documents.
The CursorAdapter class is unique in that it is the first VFP base class to provide conversion between native VFP cursors and ODBC, ADO or XML data sources, all within a single class. In other words, the ability to translate an ODBC data stream, an ADO RecordSet, or an XML document into a VFP cursor is all built into the CursorAdapter class.
You can probably tell already that the CursorAdapter is an effective replacement for the local view and remote view technology from earlier versions (Note: neither of these features has been removed from VFP 8). But in some cases, it also replaces SQL Pass Through, and also reduces the need to work directly with ADO and XML in your code.
One key advantage to the CursorAdapter is for situations where you need to connect to more than one data source from within the same application. For example, if your application retrieves most of its data from SQL Server, but also needs to work with a handful of XML documents, the CursorAdapter can be used in both cases to make all the data appear to your application as a set of VFP cursors.
Another example might be a situation where the data is currently stored in VFP tables, but future plans are to move to a database server, like SQL Server or Oracle. You would build a set of CursorAdapter classes first for VFP and then, if necessary, replace these classes with SQL Server equivalents when necessary.
But, since we must walk before we can run, let's take a basic tour through the CursorAdapter class and its different incarnations. After that, it will be easier to devise a strategy for building data classes using the CursorAdapter Class.
Your First CursorAdapter Class
Like any other class, the best way to learn how to use it is to see how one is created. To keep the complexity low for these first examples, let's start by accessing VFP native tables with a CursorAdapter class. This is very much like using a Local View to retrieve data from VFP tables. Later on in this article, we'll use other CursorAdapter classes to connect to SQL Server data, OLE DB data, and an XML document.
First, you have two ways to create a CursorAdapter. You can use the Data Environment builder or you can build the class "by hand" through a program or the class designer. This example will use the Data Environment builder; later examples will be built "by hand."
If you're not familiar with the enhancements VFP 8 brings to the Data Environment, you might think that using a builder in the DE to create a CursorAdapter would only be useful within a Form, not a class. However, the DE has been enhanced in VFP 8 so it can be instantiated without the presence of a form!
The ability to translate an ODBC data stream, an ADO RecordSet, or an XML document into a VFP cursor is all built into the CursorAdapter class.
Start by creating a new Data Environment class with the create class command. Be sure to select the Data Environment class in the "Based On" drop down. Name the class deTest and store it in a class library called Tests.vcx. Once the class appears in the class designer, right click on the Data Environment and select "Builder" from the drop down. This brings forward the Data Environment builder.
In the data source type drop down, note the available options. Since this first example will connect to native VFP tables, choose Native. Once selected, use the ellipsis button to choose the Northwind database (default location is c:\program files\microsoft visual foxpro 8\samples\northwind\northwind.dbc).
Next, click the Cursors page, which is initially empty. Under the list box, choose the New button to create a new CursorAdapter class with the CursorAdapter Builder. Initially, you will see the Properties page, providing options for choosing the name of the class and the alias of the cursor created by the class.
Be sure to provide an alias that differs from the table name to avoid confusion with the base table. In this case, use caCustomer as the class name and cCustomer as the alias. You should also check the "Use DataEnvironment data source" option since you want this class to use the same data source as the data environment. Note that you could have a different data source for the CursorAdapter, allowing you to mix data sources between different classes (such as using ODBC for one class and XML for another).
To specify how the CursorAdapter will retrieve data from the source, use the Data Access page of the builder. Click the Build button to activate a command builder dialog, where you can select the field(s) of interest for your cursor. For this example, select the Customers table from the Table drop down list, and then select the "Customers.*" option in the list box below. Click the single right-facing arrow to move the selection, and then press OK. This will build the following select command for you: select CUSTOMERS.* from CUSTOMERS
If you wish to add filters, joins, or other clauses to the query, you can type them directly into this edit box. However, if you wish to parameterize the query, there are several options, covered later in this article. For now, let's add a WHERE clause so that it looks like the following: select CUSTOMERS.* from CUSTOMERS where
companyname like 'C%'
This will make it easy to tell the difference between the base table and the resultant cursor, since only a few records match this Where clause.
The schema has been built for you in the second edit box. It is usually best to take a minute and verify that the schema matches your expectations before proceeding. The data fetching properties at the bottom of this page relate mostly to how the class deals with remote data fetches, and do not apply when using VFP as a data source. We'll leave these at their defaults and cover them in more detail later.
Near the bottom of the data access page is the buffer mode override setting, which allows you to override any associated form's buffer mode setting. Generally, you want to use the optimistic table buffering mode unless you have a specific reason to use the row buffering mode. Set this to Optimistic Table Buffering for this example.
Finally, the "Break on error" setting at the bottom of the page controls how errors are handled by the CursorAdapter class. The default setting specifies that the class will trap its own errors and allow you to capture them with the AERROR function. Enabling this setting will cause a VFP error to occur whenever any problems occur within the CursorAdapter class. This means that you will need to use the ON ERROR command or Error event of the class to trap unhandled exceptions. Generally, you will want to leave this setting off so that your code can quietly handle any exceptions that may occur.
The final page (labeled "Auto Update") configures how changes are applied to the base table(s) of the class. For the most basic updates, choose the "Auto-update" and "Update all fields" checkboxes. This will direct the CursorAdapter class to automatically build the appropriate Update, Insert or Delete statements for any changes that are made to the data in the cursor. However, you must still choose the primary key field(s) for the cursor, so that these statements know how to uniquely identify the base table records. For this example, the CustomerID field is the primary key, so check the box in the column under the key.
For the time being, leave all of the other settings at their defaults. These settings are explored later on in this article. To finalize your selections in the CursorAdapter builder, click the OK button to return to the DataEnvironment builder.
At this point, you should see the caCustomer class listed on the left, and details on the right. If you decide to make modifications to this class, you can return to the DataEnvironment builder at any time, select the desired CursorAdapter class, and click the Builder button.