Building Middle Tier Objects
in Visual Foxpro
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.
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.
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.
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.
The various tiers can reside on different computers
The tiers can be maintained independently
The tiers act as insulators
Application logic can be shared by divergent presentations
Some of the disadvantages are:
The system design is more complex
The inherent data binding of Visual FoxPro is unavailable
Memory footprint of the application is increased
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
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 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.
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 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
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
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
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
EXTERNAL ARRAY 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
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.
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.