Building Data Aware Classes in Visual FoxPro Part 2: Using Data Transports and Temporary Storage
Abstract:
In the last article (Part 1), we discussed how to encapsulate data access code and business rules in Visual FoxPro by using classes contained in Visual Class Libraries (.vcx) files. However, most Visual FoxPro programs, need to provide a means of data capture, retrieval and navigation, which is why a lot of programmers provide data navigation buttons (next, previous, last and first buttons). This is one of the reasons why Visual FoxPro programmers have often simply added the required tables to the data environment of the form or report thus, making the data natively available to the form. If Data Access and Validate code is encapsulated in classes, how can data be returned to a form and how can data-bound forms be built? This article seeks to demonstrate that you can build effective, powerful, effective, flexible database applications even though your data access code is encapsulated in ‘data aware classes’. This article explores the use of arrays as data transports and Cursors as temporary storage locations for forms.
Introduction
In part I of this article, it was demonstrated that encapsulating data access code and business rules in classes made the code easily accessible from different modules in your application and obviated the need to write the same data access code repeatedly in different application forms within your application. Also, communication and data exchange between the form and the classes was by means of properties (members) of the class. If your application form (user interface) needed to save a record created by the user, the properties (variables or members) of the class were updated with values typed into controls on the form and then the Save method of the class was called.
Similarly, when a user needed to display an existing record, the EmpID property of the class was updated and the Find method of the class was called to search for the record. When the record was located, the properties of the class (i.e. the object created from the class) would be updated with the values of the record found so that the form could then display the value of each property in the corresponding form controls (e.g. text boxes).
While this method generally works and may be effective for work with single records in a small system, what happens when you need to return more than one record at a time such as in the scenario described earlier? Could there be an alternative way to pass data to a class or return data from it?
What is wrong with just using properties?
To begin with, let us just consider the drawbacks of just using properties only as described! While working in that way is simple and generally works, it has several drawbacks such as the following: 1) Your ability to process more than one record at a time is severely curtailed because the property variables can hold data for only one record at a time. This means that if you wanted for example to populate a list box on your form, an alternative mechanism would surely be appropriate since you have not added any tables to your form’s data environment (but have delegated all data access and retrieval code to your data aware class), 2) If your application were running in a networked environment, each property you set or retrieve would cause a round-trip on the network (especially if your classes were compiled as automation components and hosted remotely on a remote machine).
How much better it would be to be able to pass all records to be saved at one time or return all records that you need to use at one time! Not only does this solve the few problems itemized above, it reduced network load and increases data availability…but how?
Combining the power of Temporary Storage with effective Data Transports
Arrays are data storage containers but by using them as Data Transports, you could create and have effective communications between your front-end (form, report, etc) and your middle-tier (data aware classes). To get your data aware class to return data to your form, the class could read the required records into an array that was passed to it as a parameter by reference. Once the array is returned, a cursor (temporary table) could be created to store the records returned so that the records could then be used and manipulated locally. Your form could contains the following code in its Init event:
LOCAL oE AS Object, lAnswer AS Logical, intRows AS Integer, cMsg AS Character
DIMENSION arrE(1,4) && Create the Array
oE = CREATEOBJECT(“Employees”) && Create an Object from your class
-* Call the GetAllRecs method and pass the array as a parameter
lAnswer = oE.GetAllRecs(arrE,intRows)
IF NOT lAnswer && Method did not complete successfully
cMsg = “Error occurred obtaining employee records!”
MESSAGEBOX(cMsg,48,”Test Program”)
CANCEL && Abort the form Opening Process…Prevent Form from Opening
ENDIF
-* Now build the cursor to host the data returned
CREATE CURSOR TEmployees (EmpID c(15) UNIQUE, ;
EmpName c(50), EmpDept c(10), EmpStatus c(10)) FROM ARRAY arrE
The Dimension array creates the array that will be passed to the class to return information from the backend table. The array is a two dimensional array where the first dimension represents the number of records in the table and the second dimension represents the total number of fields in the table. The CREATEOBJECT() function create the object as usual and returns to the reference to the object variable oE. The line lAnswer = oE.GetAllRows is the line that calls the method to return data from the class, passing the array you have declared. The CREATE CURSOR line is where the temporary cursor is created for the form and then hosted locally on the form. Creating the cursor early in the init event of the form allows your data to be available for use in the form early on
Now that the table records you need are hosted locally, you could work with the cursor just as you could any table by adding new records (APPEND), searching for records in it (LOCATE), or even deleting records in the cursor (DELETE). While these operations were taking place in the Form (front-end), the back-end table would remain available for other users to obtain data in exactly the same way (increased data availability).
Another way to use this approach would be to bind the fields of the cursor (temporary table) to the corresponding appropriate controls on the Form for ease of navigation (say you wanted to use Next | Last | Previous | First buttons), you could individually bind each control using code such as:
THISFORM.txtEmpID.ControlSource = TEmployees.EmpID
THISFORM.txtEmpName.ControlSource = TEmployees.EmpName
THISFORM.txtEmpDept.ControlSource = TEmployees.EmpDept
THISFORM.txtEmpStatus.ControlSource = TEmployees.EmpStatuis
To bind the Employee ID text box on your form, we assigned a field of the cursor to the ControlSource property of the corresponding control to which we wanted to display the information for each field in our original table. This allows you to directly edit the contents of the cursor by typing information into a control on your form. You could then add a Next button and then write the following code in its Click event:
SELECT TEmployees && Ensure this is the active cursor especially if you have several in your form
IF NOT EOF() && We are not yet at end of file
SKIP
ENDIF
THISFORM.Refresh
IF NOT EOF() checks to make sure that we are not at the end of file after which a SKIP moves us one record forward. You could also add a Previous button and then add the following code to its Click even:
SELECT TEmployees
IF NOT BOF() && It it not yet End of file
SKIP -1
ENDIF
THISFORM.Refresh
IFNOT BOF() checks to ensure that it is not beginning-of-file after which a SKIP -1 moves backward one record. THISFORM.Refresh ensures that the controls on the form are refreshed with the current record in the cursor. For a Last button, the code to be added to its Click event could be like the following:
SELECT TEmployees
GO BOTT
THISFORM.Refresh
The GO BOTT command moves us to the last record in the table. If you decided to add a First button, you could add the following code to its click event:
SELECT TEmployees
GO TOP
THISFORM.Refresh
The GO TOP record moves us to the first record in the table. After making changes to the cached records, you would have to resubmit the records in your cursor back to your class so the changes could then be saved. The click event of such a Save button could contain the following code:
LOCAL lAnswer
DIMENSION arrE(1,4)
SELECT TEmployees
COPY TO ARRAY arrE
lAnswer = THISFORM.Employees1.SaveRecords(arrE)
IF NOT lAnswer
MESSAGEBOX(“Error occurred saving records!’,48,”Test Program’
ENDIF
The SaveRecords method of the Employees class could then contain code such as the following:
PARAMETERS arrE,intRows
LOCAL intCnt As Integrer, lFileInUse As Logical
STORE 0 TO intCnt
IF USED(“Employees”)
lFileInUse = .T.
ELSE
lFileInUse = .F.
USE Employees IN 0
ENDIF
FOR intCnt = 1 TO intRows
SELECT Employees
GO TOP
LOCATE FOR ALLTRIM(Employees.EmpID) = ALLTRIM(arrE(intCnt,1))
IF NOT FOUND() && Record does not exist…create it
APPEND BLANK
REPLACE Employees.EmpID WITH arrE(intCnt,1)
ENDIF
-* Save changes to the rest of the record
REPLACE Employees.EmpName WITH arrE(intCnt,2)
REPLACE Employees.EmpDept WITH arrE(intCnt,3)
REPLACE Employees.EmpStatus WITH arrE(intCnt,4)
ENDFOR
IF NOT lFileInUSe
USE IN Employees
ENDIF
RETURN .T. && Tell user you completed process successfully
In the code above, the For…EndFor loop ensures that each row of the array is processed. It is a counter. For each row of the array, the program checks in the table to see of the record exists! If it does, changes to the record are saved. If it does not (IF NOT FOUND()), an APPEND BLANK creates a new blank record in the table and the new record saved to the table through the REPLACE statements that follow.
What if the user had deleted a series of records on the form that should also be deleted on the back end table? A similar technique of submitting the list of deleted records could do the job quite well! A Delete button on the form could contain the following code in its Click even to delete the currently displayed record:
DELETE
You could then add an Apply Deletes button to remove all deleted records at once by adding the following code to the Click even of the Apply Deletes button:
LOCAL intRows AS Integer
DIMENSION arrE(1,4) && Create the array
SELECT TEmployees && Make the Employees cursor the active work area
COPY TO ARRAY arrE FOR DELETED() = .T. && Copy all recs marked for deletion
COUNT ALL FOR DELETED() = .T. TO intRows && Count all records marked for deletion
lAnswer = THISFORM.Employees1.ApplyDeletes(arrE,intRows) && Call Class method to delete
In the code above, the Copy To Array command populates the array arrE only with the records marked for deletion. The DELETED() function returns true for any records marked for deletion. In order to return the total number of records marked for deletion, the COUNT…TO command is also used, qualified also with the DELETED() function to ensure that only records marked for deletion are taken into account in the COUNT command. The ApplyDeletes method of the Employees class is then called, with arrE and intRows passed as parameters to it. The ApplyDeletes method of the Employees class could contain the following code:
PARAMETERS arrE,intRows
LOCAL cMsg AS Character, lFileInUse AS Logical, intCnt AS Integer
intCnt = 0
IF USED(“Employees”) && File is already open
lFileInUse = .T.
ELSE
USE Employees IN 0
lFileInUse = .F.
ENDIF
SELECT Employees
FOR intCnt = 1 TO intRows
SELECT Employees
GO TOP
LOCATE FOR ALLTRIM(Employees.EmpID) = ALLTRIM(arrE(intCnt,1))
IF FOUND() && Record is located
DELETE && Mark it for deletion in the back end table
ENDIF
ENDFOR
IF NOT lFileInUse && If we opened the table, close it too
USE IN Employees
ENDIF
RETURN .T. && Tell calling app that you concluded successfully
I the code above, the Employees table us opened. The For…EndFor look ensures that each element of the array of deleted records is searched and matched against the Employees table in the back-end database. If the record is found, a DELETE command marks the record for deletion in the actual table. The Return .T. ensures that the calling application knows that the process completed successfully.
So much about forms, what about reports?
Visual FoxPro Reports also form a part of a complete Visual FoxPro application so that users of your applications can draw out needed information for hard copy. Reports are usually built in the Reports Designer and like forms, reports too usually have a data environment in which you would have to put the tables and queries that contain the data used on the report. If we advocate a policy of pooling all data access through data aware classes and not tightly binding reports and forms to the database, how can we obtain data for reports?
Usually, we are required to prepare the data required for a report before running the report. This would mean adding tales or views to the form’s data-environment (what we are advocating against) or using a DO <query> or SQL statement in the Init event of the form or even using a USE command to open the relevant data sources to be used in the report!
In the model we are advocating in this article, we could employ the power and richness of the Visual FoxPro programming language to ensure that we declare an array, pass the array to a method of the data-aware class along with the criteria required and then use the contents of the array returned to build a cursor or table to be used locally! This all could be accomplished in the Init event of the data environment just as we would have done if we had used a DO <query> or any of the other methods as described in the Visual FoxPro online documentation (but that would perhaps be an article for another day when time permits)! But the point is however made that the way to obtain data for your report from a data aware class would basically be the same method as you have used on your form!
Conclusion
This article demonstrates that you can build windows client applications with Visual FoxPro and increase data availability and application flexibility by using every-day application features that you already know – arrays and cursors to build the most powerful applications. Of course, when you deal with applications in which the user interface is disconnected from the back-end database such as those we have been demonstrating in these series of articles, you will have to write code to handle any data conflicts that may arise (e.g. some other user has just changed a record you want to save or a record that you have also amended) and so on. However, the point is made in the article that using Data Aware classes as the bed-rock of your application’s development allows you to separate the data access tier from your user services (forms and reports) and thus increases data availability.
So far, the articles in this series (both the part I and this specific article) have assumed that you are building Visual FoxPro Windows Clients (using Visual FoxPro Forms) to access data stored in a Visual FoxPro database. But what if the application database you are required to access is stored in a file format other than the native Visual FoxPro format such as perhaps Oracle, Advantage Database Server or Ms SQL Server? Part III of this article series to come soon will explore how you can access powerful Server Databases from your Data-Aware classes!
