Session E-TIER

Building Middle Tier Objects
in Visual Foxpro

James W. Booth

Introduction to N-Tier System Architecture

Who is this session for?

This session is directed to people who have some experience with Visual FoxPro.The subjects discussed are fairly advanced and relate to system design concepts. If you are currently trying to come to grips with the commands and functions in Visual Foxpro this session may not provide assistance to your goals.

The ideas discussed may still be of interest to you even if you are a beginner, if you have experience in database management development with other tools.

This session is for you if …

    Ø       You have built applications using Visual FoxPro.

    Ø       You are familiar with multi-tier application design.

    Ø       You are familiar with OLE servers in Visual FoxPro.

    Ø       You want to know more about n-tier system design.

What is N-Tier?

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 presentation (user interface), the business logic, and the database tiers. Each of these tiers can be implemented using one or more objects that are dedicated to the responsibilities of that tier.

The historic system architectures with which we are all familiar are the one and two tier models.  In the one tier model all system requirements are handled within a single application tier.  The presentation of data to the user, that accessing of the data storage and the actual storage of that data are contained in a single level.In the two-tier model (client-server) the responsibilities are divided between a presentation (user interface) tier and the database tier.The applications needs for presenting the data to the user is handled by one set of programs while another, independent set of programs handles the actual storage and retrieval of the data.

A common N-Tier design uses three tiers as described below.


The presentation (user interface) tier contains all of the visual aspects of the system. This tier handles things that are involved with interaction with the system user.All dialogs, message boxes, forms, reports, and other user interaction components reside in the user interface tier of the system.

Business Logic

The business logic tier fills the responsibility of determining where the data comes from 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, multiple levels of business logic tiers, and communication 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 Problems that the N-Tier Architecture Addresses

The environment in which our applications must run has changed considerably.In the past our applications needed to run in the multi-user environment of the local area network.  The diagram below represents this environment.


One major property of this environment is that the workstations are permanently connected to the server.The connected nature of this situation allows our applications to act a certain ways because of the very fact that there is a connection to the data.We can assume the position of record pointers, what tables are open, which indexes are ordering the records and other things.

The influence of the Internet today changes things on us.The diagram below shows the situation as it is now.


The big change is that the workstations are no longer connected to the data.A station will send a request to the server and receive a reply.Once the server replies it goes on to handle requests from other stations.No permanent connection to the data exists.Because of this we cannot assume anything about the records pointers, what tables are open, or what indexes are being used.In other word, we cannot assume anything about the state of the server or the database.We must write our applications to be stateless.

In addition to the disconnected nature of the Internet we also encounter the situation where our data may be presented to the user through a variety of different and divergent interfaces.Our data may be presented in a spreadsheet, a business application, a web browser, or a database application.Our applications must be capable of providing the data to these various presentations and still protect the integrity of the data and the business rules.The diagram below shows this situation.


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. The various tiers can reside on different computers

  2. The tiers can be maintained independently

  3. The tiers act as insulators

  4. Application logic can be shared by divergent presentations

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

  4. Performance may be reduced

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.

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 Middle Tier 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 include 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 data

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

The Properties and Methods

The properties and methods that are used in this business object are listed and described in the table below.








Obtains a Primary Key value based on a partial company name. Returns the Primary key value.



Field Name, Primary Key

Obtains the value of a field for a particular primary key.  Returns the field value.



Primary Key

Obtains a record’s data based on the primary key.  Returns nothing.



Primary Key, Array of fields and values to be saved

Updates a set of fields with a set of values for a given primary key.  Returns nothing.



The SQL Connection handle

The SQL connection handle for this business object’s ODBC connection to its data.



SCATTER NAME of resulting data

Temporary storage of the data obtained from methods.  Used internally by this object.

There is code in two of the customer class events.The Init event establishes the ODBC connection, saves the connection handle, and obtains an empty record.The Destroy event disconnects from the data source.

The Code

The code found in the methods and events of this class is fairly simple, but let’s review it here anyway.

The Init event

    * Get a connection to data
    This.nHandle = SQLCONNECT("Northwind")
    * Load an empty record

The first line uses the SQLConnect function to obtain a connection to the data source.The second line calls the class’s Requery method to get a blank record by passing a dummy primary key to the method.

The GetCustomer method

    * Parameters:  Name = The full or partial name of a customer to obtain
    * Get cursor for the name passed
    SQLExec(This.nHandle,"SELECT * FROM Customers WHERE CompanyName LIKE '" ;
    + ALLTRIM(pcName) + "%'", "Customer")
    * Return the Primary key for the first match
    RETURN Customer.CustomerID

The parameter passed is a full or partial name for a customer.  The SQLExec function sends a SELECT statement to the data source to get records matching the name passed.  The Primary key value of the first matching customer is returned to the caller.

The GetValue method

    LPARAMETERS pcField, pcKey
    * Parameters:pcField = The field to get data for
    *pcKey = The primary key for the record desired
    * Get the data for the primary key
    * Return the desired field value
    RETURN THIS.oCustomer.&pcField

Two parameters are accepted, first the name of a field to return a value for and the second is the primary key for the desired record.The class’s Requery method is called to obtain the record desired and store the field values into the oCustomer property and then the desired field’s value is returned.

The Requery method

    * Parameters: pcKey = The primary key to be used
    * Get the data for the primary key passed
    SQLExec(This.nHandle,"SELECT * FROM Customers WHERE CustomerID = '" ;
            + ALLTRIM(pcKey) + "'", "Customer")
    * Store the record to the oCustomer property
    SELECT Customer
    SCATTER MEMO NAME This.oCustomer

The parameter accepted is the primary key of the record desired. The SQLExec sends a SELECT statement to the data source to obtain the desired record and the field values are temporarily stored in the oCustomer property of the class.

The SaveChanges method

    LPARAMETERS pcKey, paData
    * Update backend with changes
    * Parameters: pcKey = The PK for the record to be updated
    *             paData = Column1 = Field Name
    *                      Column2 = Data value
    LOCAL lcPK, lcUpdate, lnCount
    lcPK = ALLTRIM(pcKey)
    * Build the SQL UPDATE command
    lcUpdate = [UPDATE Customers SET ]
    * Add the fields and values to be updated
    FOR lnCount = 1 TO ALEN( paData,1)
    lcUpdate = lcUpdate + paData(lnCount,1) + [ = '] + paData(lnCount,2) + [']
    IF lnCount < ALEN(paData,1)
    lcUpdate = lcUpdate + [,]
    * Complete the WHERE clause
    lcUpdate = lcUpdate + [ WHERE Customerid = '] + lcPK + [']
    * Execute the update
    SQLExec(This.nHandle, lcUpdate)

This method takes two arguments, the primary key for the record desired and an array that has field names in column one and field values in column two.  The code builds an SQL UPDATE command based on the fields in the array and the primary key and then uses SQLExec to send the UPDATE command to the data source.

The Destroy event

    * Disconnect from the data source

The destroy event simply disconnects from the data source.

Key Observation

In the above code listing there is one thing there that may be overlooked.  There is no dependence in the code on what record might have been current on any previous call to a method.  The code makes no assumption regarding the record to be affected.  This provides statelessness for the object.  Each call to the object for services must identify the specific record desired.


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.

vorheriger Vortrag D-ACOD

zur Übersicht der Gruppe TIER

nächster Vortrag E-STUD



Frankfurter Str. 21 b


D-61476 Kronberg

per Fax an:


oder per e-Mail an:

© Texte, Grafiken und Inhalt: ISYS GmbH