Walkthrough: Retrieving Data from Different Data Sources

Article
11/14/2006

This walkthrough guides you through the basic steps of retrieving data using various data source connection types. You can then create Visual FoxPro cursors or cursor adapters using the retrieved data. Cursor adapters can retrieve data from a wider range of data sources than is possible with conventional cursors.

Specifically, this walkthrough explains how to build a data environment for a Visual FoxPro form using the DataEnvironment Builder and how to create cursor adapters in the data environment using the CursorAdapter Builder.

This walkthrough requires the following:

Data sources using the following connection types:
- Native Northwind.dbc database included with Visual FoxPro and located in the ...\Samples\Northwind directory
- ODBC or ADO Access to a local or remote SQL server (SQL Server 7 or 2000) with Northwind.dbc database installed
- XML GetCustomers.XML DataSet file included with Visual FoxPro and located in the ...\Samples\Solution\Toledo directory
A Visual FoxPro form based on the FrmSolution class included with the Solution.vcx class library, which is located in the ...\Samples\Solution folder. A form based on the FrmSolution class appears similar to the forms used in the Solution samples.

You can create a form based on the FrmSolution class by creating a category in the Toolbox, adding the FrmSolution class to the Toolbox, and creating a form based on that class.

To create the form used in this walkthrough

On the Tools menu, click Toolbox.
Right-click the Toolbox, and click Add Category.
In the Category name box type a category name.

The category you created appears in the Toolbox.
In the Toolbox, right-click the category, and click Add Class Library.
In the Add Class Library dialog box, click the ellipsis (...) button, browse to the ...\Samples\Solution folder, and select Solution.vcx.

The classes in the Solution.vcx class library appear in the category you created in the Toolbox.
In the Toolbox, right-click frmsolution (SOLUTION), and click Create Form.

Introduction

Data Environment for a Form

Retrieving Data for Cursor Adapters

Using the Cursor Adapter on the Form

Introduction

In Visual FoxPro, you can retrieve data from local and remote data sources with the following types:

Native
Open Database Connectivity (ODBC)
ActiveX Data Object (ADO)
Extensible Markup Language (XML)

You can then create Visual FoxPro cursors from the data retrieved, based on the data source type, through cursor adapters. Cursor adapters provide the following capabilities when working with different data sources:

Retrieve data from data sources with the listed data source types.
Control loading of data into the cursor associated with the cursor adapter.
Control how data is added, updated, and deleted using CursorAdapter object properties and methods.
Add cursor adapters to containers other than a data environment, such as a form, form set, or other container.

For more information cursor adapters, see Data Access Management Using CursorAdapters and CursorAdapter Class.

You can configure a data source and, if needed, a connection type that applies to all cursor adapters in the data environment using the DataEnvironment Builder. You can then quickly create cursors that have specific properties for accessing data using the CursorAdapter Builder.

For more information, see DataEnvironment Builder and CursorAdapter Builder.

Data Environment for a Form

When you create a form, Visual FoxPro creates a DataEnvironment object as a container for Cursor, CursorAdapter, and Relation objects that are associated with the form, a form set, or a report. You need to open and set the form's data environment to the appropriate data source type and data source to retrieve data from.

For more information about DataEnvironment objects, see DataEnvironment Object.

Opening the Data Environment

Open the form's data environment and the DataEnvironment Builder before selecting the data source type and data source from which to retrieve data.

To open the form's data environment and the DataEnvironment Builder

Open the form.
In the Form Designer, right-click the form, and click DataEnvironment to open the data environment.

For a form with an empty data environment, the Open dialog box appears.
In the Open dialog box, click Cancel.
To open the Data Environment Builder, right-click the Data Environment surface, and click Builder.

The DataEnvironment Builder opens and displays the following tabs: Data Source and Cursors.

Setting Data Source Types and Data Sources

Set the data source type and configure the appropriate data source that you want to retrieve data by from setting options on the Data Source tab in the DataEnvironment Builder.

To select the data source type for the form's data environment

In the DataEnvironment Builder, click the Data Source tab to make it visible if not already visible.
On the Data Source tab, select the appropriate data source type: ADO, Native, ODBC, or XML.

Depending on your selection, the appropriate options appear. The following procedures demonstrate how to set the data environment to native Visual FoxPro tables, ODBC, ADO, and XML.

For more information, see Data Source Tab, DataEnvironment Builder.

Specifying Native Visual FoxPro Data Sources

Follow this procedure to use native Visual FoxPro data sources.

To set the form's data environment to native Visual FoxPro tables

After selecting Native on the Data Source tab, click the ellipsis (...) button to the right of the Database box
Browse to the ...\Samples directory and select the Northwind.dbc database.

To continue this walkthrough, see Retrieving Data for Cursor Adapters.

Specifying Data Sources Using ODBC

Follow this procedure to use data sources connected through ODBC.

To set the form's data environment to data sources using ODBC

After selecting ODBC in the Data Source tab, click Use connection string.
Type the connection string to connect to the Northwind.dbc database on your local or remote SQL Server. For example:
```
Driver={Sql Server};Server=localhost;Database=Northwind;Int Security=yes;
```
Note You must have access to a local or remote SQL Server (SQL Server 7 or 2000) with Northwind.dbc database installed. If your SQL Server is not local, you can change the value of the Server= parameter in the connection string to the name of the remote server.
Click Test Connection to test the connection.

If the connection is successful, you can continue and retrieve data from the data source.

To continue this walkthrough, see Retrieving Data for Cursor Adapters.

Specifying Data Sources Using ADO

Follow this procedure to use data sources connected through ADO.

To set the form's data environment to data sources using ADO

After selecting ADO on the Data Source tab, click Use connection string.
Type the connection string to connect to the Northwind.dbc database on your local or remote SQL Server. For example:
```
Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost
```
Note You must have access to a local or remote SQL Server (SQL Server 7 or 2000) with Northwind.dbc database installed. If your SQL Server is not local, you can change the value of the Data Source= parameter in the connection string to the name of the remote server.
Click Test Connection to test the connection.

If the connection is successful, you can continue and retrieve data from the data source.

If you want to construct a connection string instead of typing one on the Data Source tab, click Build to open the Data Link Properties dialog box in the Visual FoxPro OLE DB Provider. For more information, see OLE DB Provider for Visual FoxPro.

To continue this walkthrough, see Retrieving Data for Cursor Adapters.

Specifying XML Data Sources

In this walkthrough, the method for creating cursor adapters from XML demonstrates only one example of how you can access an XML data source, more specifically, an XML DataSet file generated from ADO.NET. Actual code can vary depending on your specific needs.

You can also use cursor adapters with cursors generated from XML with XML adapters or XMLAdapter objects. You can use this combination to access more advanced features of the design-time environment and to bind controls on a form to fields in the cursor adapter. The XML adapter performs the actual work at run time.

To set the form's data environment to XML data sources

After selecting XML on the Data Source tab, click the Cursors tab.

When you create a cursor adapter from XML, you can use the CursorAdapter Builder to set the CursorAdapter properties. However, you need to create an XMLAdapter object to load the XML into its Tables collection. After creating the XML adapter, you can load an XML file into the XML adapter, set the Select command of the cursor adapter to the desired XMLTable object reference in the XMLAdapterTables collection, and then call the CursorAdapterCursorFill method to populate the cursor with XML data.

To continue this walkthrough, see Retrieving Data for Cursor Adapters.

For more information about how cursor adapters work with XML adapters, see the Creating a Cursor from an XML DataSet Sample as an example of how to access an XML file and generate XML DiffGrams and XML Functionality Using XMLAdapters.

For more information about XML adapters and cursor adapters, see XMLAdapter Class and CursorAdapter Class.

Retrieving Data for Cursor Adapters

After setting the form's data environment in the DataEnvironment Builder to data source you want to use, create CursorAdapter objects in the form's data environment to retrieve data from the data source. You can create CursorAdapter objects by setting options on the Cursors tab of the CursorAdapter Builder.

To create a new cursor adapter

In the DataEnvironment Builder, click the Cursors tab to make it visible.
On the Cursors tab, click New to open the CursorAdapter Builder.

The CursorAdapter Builder appears, displays the Properties, Data Access, and Auto-Update tabs, and creates a new cursor adapter named Cursor1.

For more information, see Cursors Tab, DataEnvironment Builder.

Setting Properties for Cursor Adapters

Set properties for a new cursor adapter by editing settings in the Properties tab of the CursorAdapter Builder.

To set properties for a new cursor adapter

In the CursorAdapter Builder, click the Properties tab to make it visible if not already visible.
On the Properties tab, edit the name for Cursor1 if you want; rename the cursor adapter alias to Customer.

Note Usually, you type the name and alias you want for the cursor adapter.
Click Use DataEnvironment data source to use the data source previously set for the form's data environment.

For more information, see Properties Tab, CursorAdapter Builder.

Specifying How Cursor Adapters Retrieve Data

After creating a new cursor adapter, specify how the cursor adapter retrieves data from the data source by setting options in the Data Access tab of the CursorAdapter Builder.

If you are using the Native, ODBC, or ADO data source type, following the instructions for constructing the Select command to retrieve data from the data source. If you are using the XML data source type, follow the instructions for specifying a well-formed XML file that contains schema.

To construct the Select command for data sources using Native, ODBC, and ADO

In the CursorAdapter Builder, click the Data Access tab to make it visible.
On the Data Access tab, click Build to open the Select Command Builder dialog box.
In the Select Command Builder dialog box, select the CUSTOMERS table in the Table list.

The fields that the CUSTOMERS table contains appear in the list on the left side of the Select Command Builder dialog box.
In the list on the left, click CUSTOMERS.* to include the CUSTOMERS table and all its fields in the Select command.

Note Usually, if you want to select individual fields in the table for the Select command, click tableName.fieldName to add each field separately to the list of selected fields appearing on the right side of the Select Command Builder dialog box.
Click the single right arrow (>) to add CUSTOMERS.* to the list of selected fields, and then click OK.

The constructed Select command appears in the Select command box, and the schema for the cursor adapter appears in the Schema box.

You can set additional options for data retrieval. However, this walkthrough uses the default settings for these options.

Note In this walkthrough, buffer mode for the cursor adapter is set to optimistic row buffering by default on the Data Access tab for Native, ODBC, and ADO data source types. Therefore, when you make changes in a grid row on the form and move to another row, those changes are sent to the Customer.dbf table in the data source through the appropriate connection type. For more information about buffer modes, see BufferModeOverride Property.

For more information about additional options you can set, see Data Access Tab, CursorAdapter Builder.

To specify an XML file for XML data sources

In the CursorAdapter Builder, click the Data Access tab to make it visible.
On the Data Access tab, click Build located above the Schema box to display the Open dialog box.
Browse to the ...\Samples\Solution\Toledo\ folder, and select the GetCustomers.XML file.

The schema for the cursor adapter appears in the Schema box.
In the Buffer mode override box, select Optimistic table buffering.

Note In this walkthrough, buffer mode for the cursor adapter is set to optimistic table buffering on the Data Access tab for XML data source types to make it possible for the XML adapter to create XML DiffGrams. For more information about buffer mode settings, see BufferModeOverride Property.
Click OK to close the CursorAdapter Builder, and then click OK again to close the DataEnvironment Builder.

The new cursor adapter, now named Customer, appears in the Data Environment for the form and contains all the fields from the GetCustomer.xml file.

You can set additional options for data retrieval. However, except for the buffer mode setting for the cursor created by the cursor adapter in the XML example, this walkthrough uses the default settings for these options. For more information about these options, see Data Access Tab, CursorAdapter Builder.

To continue with the XML example, see Using the Cursor Adapter on the Form.

Configuring Updating for Cursor Adapters

After constructing the Select command for the cursor adapter to retrieve data, you can specify how the cursor adapter performs updates to the data source by setting options on the Auto-Update tab of the CursorAdapter Builder.

To configure how the cursor adapter updates the data source

In the CursorAdapter Builder, click the Auto-Update tab.
On the Auto-Update tab, select the Auto-update check box.

Selecting the Auto-update check box populates the field list on the Auto-Update tab with fields that you selected on the Data Access tab and enables automatic updating for specified fields.
Select the Update all fields check box to designate all fields from the specified table for automatic updating.

A check mark appears in the automatic updating column, indicated by a pencil symbol, for each field in the field list.
Select the check box in the key field column, indicated by a key symbol, for the CUSTOMERID field to designate it as the primary key field.
Clear the check box in the automatic updating column for the CUSTOMERID field to prevent it from being modified as the primary key.
Click OK to close the CursorAdapter Builder, and then click OK again to close the DataEnvironment Builder.

The new cursor adapter, now named Customer, appears in the Data Environment for the form and contains all the fields from the Customer table as specified.

You can set additional options for how the cursor adapter updates the data source. However, this walkthrough uses the default settings for these options.

For more information, see Auto-Update Tab, CursorAdapter Builder.

Using the Cursor Adapter on the Form

After creating the cursor adapter in the form's data environment, you can drop the cursor adapter on the form to create a grid.

To create a grid using the created cursor adapter

In the form's data environment, click and drag the Customer cursor adapter to the form to create a grid.

If you are using the XML data source type, type the following code in the form's Init event to create and configure the XMLAdapter object:

LOCAL oXMLAdapter as XMLAdapter
DODEFAULT()
ThisForm.AddProperty('oXMLAdapter',CREATEOBJECT('XMLAdapter'))
WITH ThisForm.oXMLAdapter
   .LoadXml(ThisForm.cRunPath+'getcustomers.xml',.T.)
ENDWITH
ThisForm.DataEnvironment.cursor1.SelectCmd="ThisForm.oXMLAdapter.Tables.Item(1)"
ThisForm.DataEnvironment.cursor1.CursorFill()
GO TOP
ThisForm.BindControls = .T.
ThisForm.grdCustomer.AutoFit()

In the Properties window, set the form's BindControls property to False (.F.).
Save the form in the ...\Samples\Solution\Toledo folder.
On the Visual FoxPro toolbar, click the Run (!) button to run the form.

The form appears and displays a grid that contains the following:

Native, ODBC, and ADO data source types: Data from the fields in the CUSTOMER table

XML data source type: Data loaded from the XML DataSet, GetCustomer.xml

When using XML adapters with cursor adapters, you can generate XML DiffGrams using the XML adapter created. For example, the following code creates an XML DiffGram string in a local variable called lcXML:

LOCAL lcXML, llIncludeBefore, llChangesOnly, llIsFile, lcSchemaLocation
WITH ThisForm.oXMLAdapter
   * Release XML document but preserve schema.
   .ReleaseXML(.F.)   
   .UTF8Encoded = .T.    && Indicates international characters.
   .IsDiffgram = .T.       && Generate XML DiffGram.
   llIncludeBefore = .T. && Include <diffgram:before> format.
   llChangesOnly = .T.   && Generate only changes made.
   llIsFile = .F.        && Sample XML is a stream.
   lcSchemaLocation = "" && Sample schema is inline.
   .ToXML("lcXML",lcSchemaLocation,llIsFile,llIncludeBefore,llChangesOnly)
ENDWITH

Share via

Walkthrough: Retrieving Data from Different Data Sources

Contents

Introduction

Data Environment for a Form

Opening the Data Environment

Setting Data Source Types and Data Sources

Specifying Native Visual FoxPro Data Sources

Specifying Data Sources Using ODBC

Specifying Data Sources Using ADO

Specifying XML Data Sources

Retrieving Data for Cursor Adapters

Setting Properties for Cursor Adapters

Specifying How Cursor Adapters Retrieve Data

Configuring Updating for Cursor Adapters

Using the Cursor Adapter on the Form

See Also

Additional resources