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.
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.
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.
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.
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, 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 of N-Tier system design are multifold. The list below shows some of the advantages.
Some of the disadvantages are;
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.
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 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.
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.
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.
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
This.oRDS = CreateObject("rds.datacontrol")
* Check to see if creation was successful
If Type("This.oRDS") <> "O"
* If not return .F.
RETURN .F.
Else
* 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
.Refresh
EndWith
EndIf
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.
NOTE:
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.
LPARAMETERS pcField
* 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~" + ;
"store_state~store_type~store_zip"
* Valid field request for this object, so return the value
RETURN THIS.oRDS.Recordset.Fields(pcField).Value
ENDIF
ENDIF
* Invalid field request
RETURN .NULL.
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~" + ;
"store_state~store_type~store_zip"
IF VarType(pxValue) = "C"
pxValue = ALLTRIM(pxValue)
ENDIF
THIS.oRDS.Recordset.Fields(pcField).Value = pxValue
RETURN .T.
ENDIF
ENDIF
RETURN .F.
ThisForm.oRDS.RecordSet.MoveFirst
RETURN 1
ThisForm.oRDS.RecordSet.MoveLast
RETURN 1
LOCAL lnRet
lnRet = 1
ThisForm.oRDS.RecordSet.MoveNext
If ThisForm.oRDS.RecordSet.Eof
lnRet = -1
ThisForm.oRDS.RecordSet.MoveLast
EndIf
RETURN lnRet
LOCAL lnRet
lnRet = 1
ThisForm.oRDS.RecordSet.MovePrevious
If ThisForm.oRDS.RecordSet.Bof
lnRet = -1
ThisForm.oRDS.RecordSet.MoveFirst
EndIf
RETURN lnRet
ThisForm.oRDS.Refresh
THISFORM.Requery()
ThisForm.oRDS.SubmitChanges()
THIS.Requery()
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.
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;
THISFORM.oBusObj.SetValue("store_id",THIS.Value)
Which writes the Value back to the business object’s data source.
The Click event for the Top command button is;
THISFORM.oBusObj.MoveFirst()
THISFORM.Refresh()
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.
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.
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).
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
Loop
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.