Building Middle Tier Objects in Visual FoxPro
Monday, January 1st, 2001
Janis Booth, Webmd
Janis is a senior developer for WebMD. Janis is Microsoft Certified Professional. She has received the Microsoft Most Valued Professional award each year since the program began in 1993 up until 2003. She works for the eMedicine division of WebMD which is a division of eMdeon corporation. Most of Janis' work is using Visual FoxPro as a middle layer development langauge for web based medical content authoring as well as for web sites that present medical information focused to medical professionals.
The N-tier model

The term N-Tier refers to the various levels of responsibility is a system’s design. The N in N-tier can be any number from 2 on up. A very common design is the 3-Tier model. In the 3-tier model the application is divided into 3 distinct tiers of responsibility, the user interface, the business logic, and the database. Each of these tiers can be implemented using one or more objects that are dedicated to the responsibilities of that tier.

User Interface

The user interface tier would contain all of the visual aspects of the system. Anything that involves itself with interaction with the system user is handled by this tier. All dialogs, message boxes, forms, reports, and other user interaction components would reside in the user interface tier of the system.

Business Logic

The business logic layer fills the responsibility of determining where the data comes form and how it should be formatted for the user interface. It also applies any constraint rules on the data coming form the user interface before posting the data to the database.

The business logic tier does not have any user interface components in it as it has no responsibility to interact with the user. Problems sensed with the data should be communicated to the user interface layer through return values from methods and the user interface tier should display and messages to the user.

Database Management

The database is responsible for handling the domain constraints on the data and for updating and retrieving the data in the tables. The rules in the database should be restricted to only those rules that are a direct implementation of the domain constraints. "Business rules" are not part of the database rules, instead they enforced in the business logic tier.

Other tiers

3-Tier is not the only N-Tier design. N can be any number. Some of the things that might be considered for additional tiers are, Operating system interface, Network interface, and Multiple levels of business logic tiers.

For example, you may design a system for a bank where the business logic object for an Account needs to have various different formats depending on which department of the bank is using the data. In this case you may have a business logic object for Account that is generic across the entire bank, and have other business logic objects that are specific for particular departments (each using the generic account object and adding or restricting features based on the department’s requirements).

The Advantages/Disadvantages of N-tier Design

The advantages of N-Tier system design are multifold. The list below shows some of the advantages.

  1. You can modify the business logic without making changes to either the user interface or the database.
  2. If built correctly, the business logic object can be used by multiple user interfaces
  3. Isolates the knowledge required in any given tier to that tier

Some of the disadvantages are;

  1. The system design is more complex
  2. The inherent data binding of Visual FoxPro is unavailable
  3. Memory footprint of the application is increased

With these disadvantages, why would someone want to build an N-Tier system? The answer is a single word, scalability. The N-tier design can scale up to extremely large systems without compromise. By large we are referring to the number of users, the number of differing user interface components, the size of the database, the structure of the network, and all of the other size issues for an application.

Using the N-Tier design, you can design a system that can handle multiple divergent user interfaces without requiring a rewrite of the business logic for each interface built. The business logic can be shared by multiple user interfaces. Through subclassing, the business logic classes can be customized to handle different database servers.

N-Tier design is not right for every project, but when it is needed it is an extremely powerful design approach.

Building N-Tier Applications in Visual FoxPro

Visual Foxpro can be used to build any of the common tiers in the N-Tier model. Visual FoxPro has the tools to build full featured user interfaces. The native database in Visual FoxPro is fast and robust, you can build a database server application using Visual FoxPro. However, for each of these two tiers there are other tools that do just as good or better. If the interface needs to imitate a spreadsheet, wouldn’t Excel be a better choice? If the database needs the added security of a database server, wouldn’t SQL Server or Oracle be a better choice?

The tier that Visual FoxPro excels at is the middle tier, or business logic tier, of the N-Tier model. Because of the built in Data Management Language (DML) of Visual FoxPro it is a prime candidate for manipulating data from a server and presenting it to an interface. Also, the ability to create OLE Public classes with Visual FoxPro allows for divergent interfaces and databases to use the same middle tier object to communicate with each other.

The Responsibilities of a Business Logic Object

The responsibilities of a middle tier object vary widely. Things like business rule enforcement, separation of the user interface and the database source, and providing a single data access layer for multiple divergent database servers are among the possible responsibilities.

As with many other things in object oriented development, the system design dictates the actual functions provided by a middle tier object.

Enforcing the business rules

In any database system there are rules that control what is valid data and what is invalid data. These rules can be divided into domain constraints and business rules. A domain describes all of the possible values that may be found in the entity or attribute to which the domain applies. For example, the domain for a City field may included all of the possible city names in the world.

Business rules are a subset of a domain. Business rules further limit the possible values to be only part of the complete domain. With the city field example, perhaps our company is located in Germany and has only German clients. In this case the domain for the city field will be all of the cities of the world, but the business rules would limit the field to only those cities in Germany.

While databases are very good at enforcing domains, they can be overly restrictive if used to enforce business rules. The middle tier business logic object is an ideal candidate to enforce the business rules. Visual FoxPro is especially well suited for this job because its local data engine allows the creation of metadata designs to describe the specific rules to be enforced. This allows the developer to create data driven business rules that can change over time without requiring modification of any code.

Fetching and Posting data for the user interface

In the 3-Tier design the user interface is separated from the data source by the middle tier, or the business logic tier. This separation allows the developer to build independent user interface and data storage layers. Future enhancements of the system can incorporate new user interfaces or new data storage technologies without a change in one causing the need to change the other.

The middle tier object performs the role of translation of data from the format found in the data storage system to a format that can be used by the user interface. It also translates the data from the user interface to a format that can be stored by the database.

With this design a change in the database only requires that the middle tier object be enhanced, as well as a change in the user interface also only requires that the middle tier object be enhanced.

Using Visual FoxPro classes to build a Business Logic Object

Let’s dig into some code now to see one of the many ways you can design a middle tier object in Visual FoxPro. The business logic class we will create is named Customer and will provide access to the Fitch Mather sample data that installs with Visual FoxPro 6.0. The table being used is the Stores table.

The first issue I dealt with was what baseclass to create the Customer object from. I chose to use the Form baseclass because it provides for using a private data session which will protect the data from other instances of the customer object.

To the customer class I added a property named oRDS to use as a reference to an RDS DataControl. I used RDS as the data access methodology so that the customer class can be easily moved to access data in other database systems. The RDS DataControl is created in the Init of the Customer class. The Init code is listed below.

* Create the RDS DataControl

* Check to see if creation was successful
If Type("This.oRDS") <> "O"

   * If not return .F.


   * If success set some properties of the datacontrol
   With This.oRDS

      * Set the Data source name
      .Connect = "dsn=dsnFitchMather"

      * Set up the sql statement to run
      .SQL = "Select * from stores"

      * Set for synchronous execution
      .ExecuteOptions = adcExecSync

      * Set the fetch in the background
      .FetchOptions = adcFetchBackground

      * Run the query


The comments in the above code are self explanatory. Once the customer object exists it has the RDS DataControl inside of it and the RDS DataControl is fetching the data.


The constants referred to in the code above are taken from a header file named adcvbs.h. The contents of this header file are listed below.

* Microsoft ADC
* (c) 1997 Microsoft Corporation. All Rights Reserved.
* ADO constants include file for VBScript

*---- enum Values ----
#Define adcExecSync 1
#Define adcExecAsync 2

*---- enum Values ----
#Define adcFetchUpFront 1
#Define adcFetchBackground 2
#Define adcFetchAsync 3

*---- enum Values ----
#Define adcReadyStateLoaded 2
#Define adcReadyStateInteractive 3
#Define adcReadyStateComplete 4
To this class I have added a number of methods that are listed in the following table.
Method Purpose
GetValue Used to get the value of a field
SetValue Used to set the value of a field
MoveFirst Move to the first record in the record set
MoveLast Move to the last record
MoveNext Move to the next record
MovePrev Move to the previous record
Requery Refreshes the DataControl by rerunning the SQL. The requery method is here to make the syntax in using the customer object similar to the native VFP syntax for requerying a view.
RevertChanges Discards pending data changes
SaveChanges Commits pending data changes

The following sections will present the code that is in these methods.



* Check for valid parameter
IF NOT EMPTY( pcField ) AND VARTYPE( pcField ) = "C"

   * Check for valid field name for this object
   IF LOWER(pcField) $ "store_name~store_add1~store_addr2~store_addr3~" + ;
    "store_city~store_id~store_desc~store_phone1~" + ;

      * Valid field request for this object, so return the value
      RETURN THIS.oRDS.Recordset.Fields(pcField).Value



* Invalid field request
LPARAMETERS pcField, pxValue

IF NOT EMPTY( pcField ) AND VARTYPE( pcField ) = "C"
   IF LOWER(pcField) $ "store_name~store_add1~store_addr2~store_addr3~" + ;
    "store_city~store_id~store_desc~store_phone1~" + ;
      IF VarType(pxValue) = "C"
         pxValue = ALLTRIM(pxValue)
      THIS.oRDS.Recordset.Fields(pcField).Value = pxValue
      RETURN .T.

lnRet = 1
If ThisForm.oRDS.RecordSet.Eof
   lnRet = -1
lnRet = 1
If ThisForm.oRDS.RecordSet.Bof
   lnRet = -1
You may wonder why I have created all of these methods to do tings that could be done by directly referring to the RDS data control itself. The answer is that this approach provides a developer’s interface to the business object that is independent of the nature of the data object. I can subclass this class and write code that deals with local VFP data, or ADO instead of RDS, or ODBC through remote views. None of these modification would require that any code in the UI level be changed at all.

Using the Business Object

The project also includes a VFP form that sues this business class. The form’s name is Customer.scx. Let’s examine the code in this form that uses the business class.

This form has a custom property named oBusObj added to it. IN the Load event of the form is the following code.

THISFORM.oBusObj = NewObject("Customer")

This creates an instance of the customer object and stores a reference to it in the oBusObj property of the form. In the Refresh of the store number textbox is this code;

THIS.Value = THISFORM.oBusObj.GetValue("store_id")

This code calls the GetValue of the business object and sets the Value property of the textbox to be the returned value from the method. The Valid event for the same textbox is;


Which writes the Value back to the business object’s data source.

The Click event for the Top command button is;



Are you starting to see a pattern here? Can you appreciate how easy it is to use this business object to handle the data access? The other code in the form is similar except for which methods of the business object is called.

Creating an ActiveX server from the Visual FoxPro class

So, what’s the big deal? Why is it better to use a separate object to handle data access when the form has a wonderful data environment and the controls can directly bind to data?

The answer to these questions lies in the word scalability. Scalability is the ability of a system to grow over time to include new features, higher data volumes, additional user interfaces, and other enhancements. If you sue the data environment of a VFP form for accessing the data, then you are limited to using VFP to build the user interfaces or you will need to create the same data access capabilities in some other tool.

What if the same data needs to be available to both VFP and to Microsoft Excel? The answer is to make the Customer class an OLE Public class and build an COM DLL with it included. To make the class an OLE Public class, open it in the class designer and then select Class Info form the Class menu. Check the box for OLE Public.

To build the COM DLL open the project and choose Project Info from the Project menu, select the Servers tab and set your class options (these options are documented in the help file quite well). Save those setting and then choose Build and select the Build COM DLL and click OK. This will build the COM DLL with your COM server class in it and register the DLL on your machine. For others to use the class the DLL will need to be installed and registered on their machine, this can be done as part of a Setup process for your application.

Once you have done this you can modify your VFP form to use the COM class. Just change the Load event to be;

THISFORM.oBusObj = NewObject("BusObj.Customer")

Where the BusObj is the name of the DLL file you created.

Using Multiple User Interfaces in N-tier Design

The advantage of this design is that the same business object can be used by multiple different user interfaces thus allowing one class definition to control data access for every UI that your system uses. Although your application may be limited to VFP forms only in the beginning, following this N-Tier design will make it much easier for you to add other interfaces to the system in the future (making the system scalable).

Using Microsoft Excel for the User Interface

Here is a sample Microsoft Excel macro that uses the same business class to populate an Excel sheet with data.

Option Explicit
Public Dummy As Variant
Public oCustomer As Object

Sub nTier()
' nTier Macro
Dim lnRet As Integer
Set oCustomer = CreateObject("BusObj.Customer")
ActiveSheet.Cells(1, 1) = "Store ID"
ActiveSheet.Cells(1, 2) = "Store Name"
ActiveSheet.Cells(1, 3) = "Store City"
lnRet = Refresh()
' frmRefresh.Show
End Sub

Public Function Refresh()
' nTier Macro
' Refreshes the contents of the business logic object and the sheet.
Dim lnRet As Integer
Dim lnRow As Integer
lnRet = oCustomer.MoveFirst()
lnRow = 2
Do While lnRet > 0
   ActiveSheet.Cells(lnRow, 1) = oCustomer.GetValue("store_id")
   ActiveSheet.Cells(lnRow, 2) = oCustomer.GetValue("store_name")
   ActiveSheet.Cells(lnRow, 3) = oCustomer.GetValue("store_city")
   lnRet = oCustomer.MoveNext()
   lnRow = lnRow + 1
Refresh = 1
End Function
This macro will create an instance of the Customer class and then populate the rows and columns of the sheet with data from the business object.


There is a lot of talk going around about N-Tier system design. Some people are heavy proponents of using N-Tier for everything, others feel that N-Tier is overkill in many places. My opinion is that anything I can do to improve the ability to enhance a system for my client over time is effort well spent.

The N-Tier design gives me the ability to manage data through the uses of classes which can be subclassed and specialized from within VFP and at the same time make those classes available to other development tools thus providing a single location for modifying the data access. This single location is one of the major benefits achieved through N-Tier design. If the client switches from using one database server to another, there is only one place to make the necessary changes and all of the various user interfaces will be updated.

More articles from this author
Monday, January 1st, 2001, by Janis Booth
This material was presented at GLGDW and DevCon and will be presented at Frankfurt next month. Overview We have all heard it, "This application needs security in it that is very powerful and that protects the data?". What does that mean? What is "Powerful" security? What does the data need to be protected from? How much should this security cost? How do we measure the cost of the security? Consider your home, you would like to feel that there is some security there. You can use deadbo...
Monday, January 1st, 2001, by Janis Booth
This material was presented at GLGDW and DevCon and will be presented at Frankfurt next month. Overview What we will see Designing a framework is not a small undertaking. There are many considerations to be addressed by the design. Using an effective design can mean the difference between success or failure on a development project. In this session we will discuss the major points and issues related to the design of an application framework. The high points are listed below. Establ...
Monday, January 1st, 2001, by Janis Booth
Object oriented system development is new to many of us and being new it presents some challenges. Among those is the process of planning and managing a project. Most of us have developed projects before. On those projects we have worked with the users to discover what the system was to do and how it should do those things. We designed the programs and wrote the applications. So what is so special about object orientation? One of the issues with object orientation is the very fact that ...
Monday, January 1st, 2001, by Janis Booth
User interface design is a vast and often misunderstood area of application design. Too often, in application development, there is a large amount of time spent designing the data and the application framework. There is very little time spent designing the user interface. We build forms and put controls in them, without concern for the user’s goals. We build menu systems without thinking about where the options should be to be most useful. Forms tend to become crowded as we try to s...
Monday, January 1st, 2001, by Janis Booth
Visual FoxPro is Object Oriented. So what does that to me as a developer? How can object orientation make my life easier? We all know there is a learning curve to climb in order to understand objects and we’ve been told there is a payoff for climbing that curve. This papern will investigate one of those payoffs, a reduction in our development time. Development Time Development time is always at a premium and steps we can take that reduce it are quite valuable. Object orientation ...
Monday, January 1st, 2001, by Janis Booth
This session will be focused on the new OLE Drag and Drop capabilities in Visual FoxPro 6.0. Although our focus is on Drag and Drop we will spend some time discussing the user interface technique of direct manipulation in some detail in order to understand where drag and drop fits into user interface design. The prerequisites for this session are having some familiarity with Visual FoxPro Forms and the controls we use in those forms. Also being comfortable with the designers and the prop...