Chapter 30 -- Visual FoxPro Enterprise Solutions

In addition to using Visual FoxPro to create stand-alone applications, you can make it part of a larger, enterprise-wide business solution. By doing so, you can integrate the features of Visual FoxPro with other Windows applications to create a comprehensive, powerful solution to your application needs.

This chapter provides an overview of where and how you can use Visual FoxPro as part of your business-wide development efforts. It doesn't include procedural information on how to accomplish particular tasks; instead, this chapter is a guided tour that outlines the features of Visual FoxPro that make it uniquely suitable for enterprise solutions.

The chapter includes information on:

• Developing for the Enterprise
• Using Visual FoxPro as an Application Front End
• Using Visual FoxPro as a Data Source

Developing for the Enterprise

Many applications that you create with Visual FoxPro are self-contained solutions to a specific business requirement. For example, you might create a Visual FoxPro application to track your customers, which can include not only database information about the customers, but tools for taking orders, invoicing, and so on. You can create all the features your application needs using the facilities already available in Visual FoxPro, including its database engine, visual design tools, and reporting capabilities.

But you can also use Visual FoxPro as part of a larger-scale application that involves two or more development tools. Using Visual FoxPro this way referred to as enterprise development allows you to take advantage of the unique capabilities of each product. Enterprise development can be as simple as maintaining a customer database in Visual FoxPro and creating a mail merge letter in Microsoft Word, or it can involve creating a complex application using client/server databases, Automation servers, electronic mail, and more.

Visual FoxPro is an ideal tool for creating enterprise-wide business solutions because it features:

• Powerful, easy-to-use application development tools, including a Form Designer and wizards.
• A fast database engine.
• Excellent connectivity with other products, including both other Windows programs such as Microsoft Excel and Word, and client/server systems such as Microsoft SQL Server.
• Integrated source control and other team development tools.

These features allow you to develop with Visual FoxPro in several roles in an enterprise-wide application. You can use Visual FoxPro:

• As a front end for other applications. In this scenario, you work primarily with Visual FoxPro; for example, you create the user interface for the application in Visual FoxPro. You then access other applications that contain data needed for the application or that can provide services that enhance those already available in Visual FoxPro. You can also upsize your Visual FoxPro data or move it to another platform.
• As a data source for other applications. To do so, you would create the user interface for the application using another program, and then access Visual FoxPro data as needed.

The strategy you choose depends on what your application goals are and what programs you want to use.

The following sections provide ideas and scenarios that illustrate how you can use Visual FoxPro in each of the ways described above. However, don t think that the applications presented here are the only types that you can create use the ideas here as a springboard for inventing and designing your own enterprise solutions.

Using Visual FoxPro as an Application
Front End

As a Visual FoxPro developer, you probably find it natural to design your applications around the program s visual design tools. For example, you probably think of your application s user interface in terms of Visual FoxPro forms, menus, and reports. In addition, when you develop applications in Visual FoxPro, you most likely think of storing the application s data in Visual FoxPro tables.

One way to integrate Visual FoxPro into an enterprise-wide application is to use the visual design tools in Visual FoxPro, but enhance them with the capabilities of other products. Another way is to create your application s look and feel using Visual FoxPro, but to extend the data storage capabilities of your application by taking advantage of the capabilities of other programs or of non-Visual FoxPro data storage options. You can also upsize your Visual FoxPro data by moving it to a database server.

Extending the Visual Design Tools in Visual FoxPro

The base classes of Visual FoxPro controls were designed to accommodate the vast majority of application interface needs. Visual FoxPro provides all the basic controls and interface elements that are required to create a standard Windows application. However, you will often find that your application requires objects or controls beyond those provided in the base Visual FoxPro classes. If so, you can extend the visual design tools by creating subclasses and by using ActiveX controls.

Creating Subclasses

An extremely powerful feature of Visual FoxPro is the ability to create subclasses of the base controls. By creating one or more subclasses, you can customize the basic Visual FoxPro controls in almost any way that's required for your application. This ability extends to being able to create new objects or controls that combine the features of other controls. For example, the grid control in Visual FoxPro contains not only its own container, properties, and methods, but those of the objects that appear in the grid such as buttons, text boxes, and so on.

Similarly, by subclassing base controls, you can extend the capabilities of Visual FoxPro by creating objects that add new features to existing base classes, or that combine the capabilities of several objects. For example, you can add visual features such as frames or three-dimensional effects to a text box. Or you could combine an image control, buttons, and a text box to create a bitmap-viewing control in which users can move through a series of .bmp files. Creating custom classes in this way can help you manage company-wide development by allowing you to create standardized controls that appear in all your applications. For more information about creating subclasses, see Chapter 3, Object-Oriented Programming.

Using ActiveX Controls

An alternative to creating a new control using subclasses in Visual FoxPro is to use an ActiveX control (.ocx file). These controls are created independently from Visual FoxPro, and can be integrated not only into Visual FoxPro, but into many other Windows applications as well.

In effect, ActiveX controls are off-the-shelf components that you can integrate seamlessly into your application. Using ActiveX controls provides several benefits:

• It saves you the time and effort required to create, test, and maintain a Visual FoxPro-specific control to accomplish the same tasks. The more capable the ActiveX control, the more time you save.
• Many ActiveX controls are already available from third-party suppliers to answer common application requirements. For example, if your application calls for you to display a calendar and allow users to choose dates on it, you can probably find an ActiveX control (perhaps several) that already manages this task.
• The same control can be used in multiple programs. For example, if it makes sense to do so, you can use the same ActiveX control in Visual FoxPro and Visual Basic. The same properties and methods are used in each case to manage the control, and the control will have the same appearance in all programs, making it easier for users to work with.
• ActiveX controls often provide access to Windows functionality that can otherwise be awkward or time-consuming to include using only Visual FoxPro tools. For example, you can find ActiveX controls that provide access to electronic mail (using Windows MAPI functions), to low-level Windows graphics functions, and so on. By including an ActiveX control, you can add these types of features to your application in a way that's easy to control using the ActiveX control s properties, methods, and events.

In short, using ActiveX controls enables you to extend your applications not only by integrating Windows-wide functionality, but by adding a common look-and-feel between your application and others in the same enterprise. For more information about using ActiveX controls, see Chapter 16, Adding OLE. For information about creating your own ActiveX controls, see Chapter 28, Accessing the Visual FoxPro API.

Integrating Functionality from Other Programs

You might find when developing an application that other programs are uniquely suited to accomplishing certain tasks. For example, Microsoft Word has unsurpassed merge letter capabilities, while Microsoft Excel is optimized to calculate complex formulas and easily create charts and graphs from them.

Rather than emulating these capabilities in Visual FoxPro, you can make your application an enterprise-wide solution by integrating them into your application. This way you can match the requirement of your application to the best possible tool to address it.

You can integrate the functionality of other applications into Visual FoxPro in these ways:

• Run a Visual FoxPro wizard that makes Visual FoxPro data available for use by another application.
• Write Visual FoxPro programs that use Automation to communicate with, control, and share data with other Windows programs.

The following sections provide details about these methods of extending the capabilities of Visual FoxPro.

Using Wizards

A number of Visual FoxPro wizards allow you to integrate Visual FoxPro data with the functionality of other Windows programs. For example, you can send form letters to your customers by using the Mail Merge Wizard. When you run the wizard, you can specify a table or view that contains the Visual FoxPro data to use, and then either export the data to a suitable file format (such as comma-delimited) or specify that your word processing program use the Visual FoxPro ODBC driver to access the data. If you use Microsoft Word, the wizard will even start the word processing program, create the blank merge document, and display the Mail Merge toolbar for you to link fields to your Visual FoxPro data.

Similarly, using Microsoft Excel and Microsoft Query, you can analyze your data using a pivot table, which summarizes data in columns and allows you to rearrange it to view it in different ways. By using the PivotTable Wizard in Visual FoxPro, you can use your application data as the source data for Microsoft Excel, and generate the pivot table in Microsoft Excel.

Using Automation

A more powerful way to interact with other applications is to use Automation. Using Visual FoxPro programs, you can access the objects exposed by other applications, and then control them by setting their properties and calling their methods. For example, Microsoft Excel exposes an application object as well as worksheets, columns, rows, and cells within the application object. You can directly manipulate any of these objects, including getting or setting data in them. In addition, you can usually control the application object using the full range of commands available in the program itself. For example, by managing the application object in Microsoft Excel, you can open, save, or print worksheets, invoke the Microsoft Excel chart wizard, and so on.

Automation is a particularly attractive and powerful way to tie Windows programs for several reasons:

• You have direct access to the other program, including all its objects and commands.
• You can share data directly with the other program without having to export it or convert it to another format.
• You can control the other program using the familiar properties and methods model.
• The other program doesn't necessarily need to be visible to the user when you invoke it. For example, you can invoke Microsoft Excel, place some data into cells, run a complex calculation on the data, read the result, and then display it in Visual FoxPro, all without ever displaying Microsoft Excel. Your user would continue to see only Visual FoxPro, unless you explicitly wanted to display Microsoft Excel.
• The commands (methods and properties) to control the other program are embedded within familiar Visual FoxPro programs. You don't need to learn a different programming language in order to be able to control the other program.

Automation is particularly powerful because it's an open-ended method for working with other programs. In essence, Automation simply makes available to you the data and commands from other applications, for you to use in the way best suited to your application.

A sample scenario illustrates how you can integrate several Windows programs. Imagine that you store your customer and sales data in Visual FoxPro. You'd like to create a sales report that summarizes quarterly sales.

One solution would be to use Automation to copy the Visual FoxPro sales data to cells in a Microsoft Excel worksheet. You can then invoke the Microsoft Excel chart wizard to create a chart of the data and copy it to the Windows Clipboard. Still using Automation, you can invoke Microsoft Word and create or open a sales report document (if you create it as a new document, you can insert standard text that you store in Visual FoxPro), and then paste in the chart you created in Microsoft Excel.

This is just one way you can use Automation to make Visual FoxPro part of an enterprise-wide solution. By becoming familiar with the objects and methods available in programs that you typically use, you can imagine many more ways to make each program enhance the capabilities of the other. For details about Automation, see Manipulating Objects with Automation in Chapter 16, Adding OLE.

Extending Data Storage Capabilities in Visual FoxPro

The data table and indexing capabilities of Visual FoxPro are usually more than adequate to the requirements of an application, whether you're concerned about speed or database size. However, there are times when you might want to extend Visual FoxPro by using data that's stored in some other format. This might be true if:

• Your application needs access to legacy data that's created and maintained by an existing application. For example, suppose that, as part of your sales application, you need access to data being maintained by an accounting application that was written using a different program, perhaps even on a different platform.
• You want to optimize data access by using a database server, which can greatly speed data access, particularly in very large databases.
• You want to share data with other programs, and therefore want to store the data in a format that's accessible to all the programs.
• The data is best suited to the format of a particular program (such as a spreadsheet). This might be true, for example, if your application required only occasional access to data that was otherwise being maintained by the other program.

If the data you need is in the format of a spreadsheet, word processing document, or other Windows program, you can access it using Automation. For example, you might do this if your application required a collection of form letters. In that case, the letters might be stored as Microsoft Word documents, and your application would use Automation to invoke Word, open the appropriate letter, and insert or substitute text as necessary.

A more common approach to using non-Visual FoxPro data is to use ODBC to access it. ODBC drivers allow you to connect to data in the format of other programs typically other database programs and query or edit it using standard SQL commands.

For example, you might decide that security and transaction processing capabilities are a vital part of your application, so you want to store the data using the Microsoft SQL Server. To access the data, you define a connection to the SQL Server, using the ODBC driver for it. You can then run ordinary queries (and other SQL commands) as if the data were in Visual FoxPro format.

Other applications can access the same data and take advantage of the same features. For example, a Microsoft Excel worksheet can get its data from the same SQL Server database. Not only will the worksheet enjoy the same performance advantages that your application does, it can also take advantage of the security and transaction processing features of the server, which aren't otherwise available in a Microsoft Excel worksheet.

In some instances, you might want to go further and use SQL commands that are specific to the data source you're accessing with ODBC. For example, Microsoft SQL Server allows you to create and run stored procedures, which can manipulate data at the server (rather than in your application). To take advantage of stored procedures, you can send native SQL statements to the database server. Pass-through SQL commands also allow you to perform system administration tasks on the server, and in some instances they will execute faster than similar SQL statements executed in Visual FoxPro.

For more details about extending data storage capabilities in Visual FoxPro, refer to the documentation in the following table.

For details about	See
Automation	Manipulating Objects with Automation in Chapter 16, Adding OLE
Using ODBC to access data	Accessing Remote Data in Chapter 8, Creating Views
Using Visual FoxPro in a client/server environment	Chapter 19, Designing Client/Server Applications

Upsizing Visual FoxPro Data

You can choose to keep your data in Visual FoxPro tables or on another platform such as a database server. Or you can do both: keep your data in Visual FoxPro tables while you're developing, or until your database grows large, and then move (or upsize) the data to another platform.

For example, you can prototype your application by keeping all the data in local Visual FoxPro tables. This gives you the flexibility to modify your tables, views, and indexes as you develop the application without the complexities of managing tables on a database server. You can keep sample data in the local tables so you test your forms, reports, and other programs. When the database structure is complete, you can upsize your data to a database server and put the application into production.

Another way to work is to keep your data in Visual FoxPro tables only as long as is practical. When the database grows large, you can upsize it and take advantage of the optimized performance provided by a database server. The exact point at which it makes sense to upsize your database depends on many factors, including the complexity of the database, the performance of your local computer or network, and the demands of your application.

Finally, you can prototype your database in Visual FoxPro, and then upsize it in order to share the data with other applications that can also access a database server. Similarly, you can upsize the database in order to take advantage of the security and server-side transaction processing capabilities of the database server.

For details on upsizing databases, see Chapter 20, Upsizing Visual FoxPro Databases.

Using Visual FoxPro as a Data Source

A different way to integrate Visual FoxPro into an enterprise solution is to use it as one component, but not necessarily as the primary application. In effect, you'd be treating it as a back end for an application written using another product. In this case, the user wouldn't directly see Visual FoxPro. Instead, the application s user interface would be written using tools in the other application, and would communicate with Visual FoxPro in the background to get or manipulate data.

Visual FoxPro works well in this role because it can make available its database engine, which provides rapid data access to other applications. In addition, Visual FoxPro can make its objects and command sets available to other programs, including custom objects that you create.

Making Visual FoxPro Data Available to
Other Programs

One way for an enterprise-wide application to take advantage of Visual FoxPro is to use the Visual FoxPro database engine for storing and managing data. This provides high-performance storage and query capability to other programs.

Programs can connect to Visual FoxPro data using the Visual FoxPro ODBC driver. This driver exposes the Visual FoxPro database engine to standard SQL commands.

For example, an application might use Microsoft Excel as a calculation tool for complex data analysis. If the data to be manipulated is highly fluid, it might make sense not to store it in a worksheet, but in a database. The worksheet could then be written to use the Visual FoxPro ODBC driver to connect to the database, extract the relevant information, and display it in a worksheet for further processing.

Another example might be a kiosk application, such as an information stand at an airport or convention center. You could create the information display using a multimedia authoring program. But if some of the data in the application changed often, it would be cumbersome to change pages in the presentation. Instead, the presentation program could connect to a Visual FoxPro database using the ODBC driver, and then extract data at run time.

For more information, see Visual FoxPro ODBC Driver Help (Drvvfp.hlp) installed in the \Vfp98\Distrib\Src\System directory. It's also available from the ODBC program group if you installed ODBC during Visual FoxPro Setup.

Making Visual FoxPro Objects and Commands Available to Other Programs

In addition to making Visual FoxPro data available to other programs as part of an enterprise solution, you can expose Visual FoxPro objects and commands. Other applications can call the methods and set properties of objects in Visual FoxPro; including not only the base objects, but objects defined in custom classes.

For example, you can create an application in Microsoft Excel that stores data in a Visual FoxPro database. In addition to simply reading and writing the data, Microsoft Excel can call Visual FoxPro commands to display a form as a dialog box. One use might be to gather data for a parameterized view.

Another way to expose Visual FoxPro objects is to create an Automation server. This allows you to create application-specific objects that can perform almost any function that you can program Visual FoxPro to accomplish, with the further advantage that you can distribute the server.

One use for a custom server is to create an object that includes a set of business rules that ensure the integrity of data that another application passes to it. For example, you can create an object in Visual FoxPro for storing employee information that not only validates that the application has passed valid employee information, but checks the user s access level to ensure that the user has security access to make the employee changes.

A custom server can also expose an object that incorporates complex logic for updating or reading information. For example, an order entry object might not only be able to store the order, but could also maintain a transaction log of orders, update inventory, calculate a sales commission, and so on.

This type of Automation server is ideal for creating the middle layer of a three-tiered enterprise application. In this model, the data forms the lowest level and the application forms the highest. The functionality is in the middle, and provides a specific, application-independent view of the data that incorporates business rules (or other data processing capabilities) that don t properly belong with the data or with the application alone.

For information about creating custom Automation servers, see Creating Automation Servers in Chapter 16, Adding OLE.

Creating a Data Warehouse Using Visual FoxPro

In addition to creating your application in Visual FoxPro, you can use the program to create and maintain a data warehouse, or version of your data that is optimized for reporting. To create a data warehouse, you make a copy of the data required for reporting, and then make it available to users who need it. By maintaining this data separately from your live data, you can:

• Structure it to make reporting easier or faster than if users created reports from the live data.
• Place data for reports in a location separate from the live data, which reduces contention for data, improves performance, and can make data available to users who shouldn't see live data for security purposes.

A data warehouse is a snapshot of the data at the time you create it. You refresh the data in the warehouse periodically, scheduling the update according to the reporting needs for your application.

For example, imagine that you're creating an application to manage a library, including an inventory of materials. During the day, the system is in constant use as patrons check materials in and out, and query the system to locate or reserve books. In addition to managing these individual transactions, librarians want to be able to analyze their library, to determine facts such as what materials are the most popular, what books are overdue, and so on.

To help with the analysis, the application can create a data warehouse out of the transaction information. The application can warehouse the data periodically, perhaps every night, and the librarians can create queries without affecting the performance of the system during the day. In addition, the data warehouse can exclude details about the patrons using the library, because this information isn't required for the analysis and might be considered confidential information.

To get the most benefit from a data warehouse, you create it on a server separate from the live data. If the live data and data warehouse are on the same server, you can still get the benefit of having optimized data in the warehouse. However, as users make queries against the warehouse, they can generate a large amount of network traffic which might affect performance of the live system.

When you create the data warehouse, you can simply copy the live files over to parallel files in the data warehouse. Alternatively, you can restructure the data in the warehouse to optimize it for reporting. For example, you might create indexes for the warehouse data that reduce reporting overheard.

As another example, data in the live application should be normalized in order to avoid data duplication. However, in the data warehouse it might be useful to combine tables that would otherwise be separate; this can eliminate the need to join tables, making it easier for less experienced users to create reports.

You can also match the level of detail in the data warehouse to the reporting requirements of your application. For the most flexibility, you should store the same level of detail in the data warehouse as you have in the live data. However, if users wanted to create only summary reports (such as spreadsheets or charts), you could roll up detail data from the application and store only summary data in the data warehouse.

Using Visual FoxPro as a World Wide Web
Search Engine

If your enterprise solution involves creating a World Wide Web server for the Internet, you can incorporate Visual FoxPro into the application as a search engine. This allows you to make the power of your Visual FoxPro database available to anyone who can access your Web server, whether on the Internet or via a company intranet.

For example, imagine that as part of your company-wide intranet you want to make available an employee directory. Employees could point their browsers to a Search for Employee page, which would display a page that looks like a Visual FoxPro form, containing text boxes to enter criteria. To perform a search, users would enter the employee s name, phone extension, department, position, or any other information they had available, and then choose a Search Now button. In a moment or two they would see a listing of the employees who met the search criteria. They could then save this listing as a text file that could then be imported into another program, such as a word processor.

Understanding Visual FoxPro as a Web Search Engine

In general, in order to use Visual FoxPro as an information server for the Web, you need these components:

• An existing Web server with HTTP service, running on Microsoft Windows NT operating system.
• A Visual FoxPro application that can be called as an Automation server. This application can run on any server accessible to the Web server.
• A means displaying search results, which usually consists of a Web page template into which you can insert data.

The usual sequence of events that involve a Visual FoxPro search on the Web are these:

1. The user displays your application s search page by pointing a Web browser to it. The search page includes any text and graphics that you want, plus text boxes in which users can enter search text.
   
   
2. The user chooses a Search Now button. The data from the completed form is sent to the Web server for processing, along with the name of your Web page search application.
   
   
3. The Web server calls your application using ISAPI (Internet Server API) protocol, passing to it a parameter containing the search information.
   
   
4. Your application searches the database. When it gets results, it inserts them into a Web page template, and then sends the Web page back to the server as a stream of characters.
   
   
5. The Web server sends the results page to the browser that initiated the search.
   
   
6. The browser displays the results page for the user.

If you've created Web pages, most of the steps in this process are probably familiar. For example, you might already know how to create Web pages. Even if you aren't already familiar with Web page design, you'd probably find the process of creating these pages relatively easy.

For an example of how to use Visual FoxPro as a Web search engine, see the sample Foxisapi.dll in the in the Visual Studio \Samples\Vfp98\Servers\Foxisapi directory. Refer to the Readme.txt file in that directory for details about how to run the example.