Delphi Programming Guide
Delphi Programmer 

Menu  Table of contents
Bookmark and Share

Part I - Foundations
  Chapter 1 Delphi 7 and Its IDE
  Chapter 2 The Delphi Programming Language
  Chapter 3 The Run-Time Library
  Chapter 4 Core Library classes
  Chapter 5 Visual Controls
  Chapter 6 Building the User Interface
  Chapter 7 Working with Forms
Part II - Delphi Object-Oriented Architectures
  Chapter 8 The Architecture of Delphi Applications
  Chapter 9 Writing Delphi Components
  Chapter 10 Libraries and Packages
  Chapter 11 Modeling and OOP Programming (with ModelMaker)
  Chapter 12 From COM to COM+
Part III - Delphi Database-Oriented Architectures
  Chapter 13 Delphi's Database Architecture
  Chapter 14 Client/Server with dbExpress
  Chapter 15 Working with ADO
  Chapter 16 Multitier DataSnap Applications
  Chapter 17 Writing Database Components
  Chapter 18 Reporting with Rave
Part IV - Delphi, the Internet, and a .NET Preview
  Chapter 19 Internet Programming: Sockets and Indy
  Chapter 20 Web Programming with WebBroker and WebSnap
  Chapter 21 Web Programming with IntraWeb
  Chapter 22 Using XML Technologies
  Chapter 23 Web Services and SOAP
  Chapter 24 The Microsoft .NET Architecture from the Delphi Perspective
  Chapter 25 Delphi for .NET Preview: The Language and the RTL
       
  Appendix A Extra Delphi Tools by the Author
  Appendix B Extra Delphi Tools from Other Sources
  Appendix C Free Companion Books on Delphi
       
  Index    
  List of Figures    
  List of tables    
  List of Listings    
  List of Sidebars  

 
Previous Section Next Section

Navigating a Dataset

You've seen that a dataset has only one active record; this active record changes frequently in response to user actions or because of internal commands given to the dataset. To move around the dataset and change the active record, you can use methods of the TDataSet class as you saw in Listing 13.2, (particularly in the section commented position, movement). You can move to the next or previous record, jump back and forth by a given number of records (with MoveBy), or go directly to the first or last record of the dataset. These dataset operations are available in the DBNavigator component and the standard dataset actions, and they are not difficult to understand.

What is not obvious, though, is how a dataset handles extreme positions. If you open any dataset with a navigator attached, you can see that as you move record by record, the Next button remains enabled even when you reach the last record. Only when you try to move forward after the last record does the button become disabled (and the current record doesn't change). This happens because the Eof (end of file) test succeeds only when the cursor has been moved to a special position after the last record. If you jump with the Last button instead, you'll immediately be at the end. You'll encounter the same behavior for the first record (and the Bof test). As you'll soon see, this approach is handy because you can scan a dataset testing for Eof to be True, and know at that point you've already processed the last record of the dataset.

In addition to moving record by record or by a given number of records, programs might need to jump to specific records or positions. Some datasets support the RecordCount property and allow movement to a record at a given position in the dataset using the RecNo property. You can use these properties only for datasets that support positions natively, which basically excludes all client/server architectures, unless you grab all the records in a local cache (something you'll generally want to avoid) and then navigate on the cache. As you'll see in Chapter 14, when you open a query on a SQL server, you fetch only the records you are using, so Delphi doesn't know the record count (at least, not in advance).

You can use two alternatives to refer to a record in a dataset, regardless of its type:

  • You can save a reference to the current record and then jump back to it after moving around. You do so by using bookmarks, either in the TBookmark or the more modern TBookmarkStr form. This approach is discussed in the section "Using Bookmarks."

  • You can locate a dataset record that matches given criteria, using the Locate method. This approach, which is presented in the next section, works even after you close and reopen the dataset, because you're working at a logical (not physical) level.

The Total of a Table Column

So far in our examples, the user can view the current contents of a database table and manually edit the data or insert new records. Now you will see how you can change data in the table through the program code. The employee dataset you have already used has a Salary field, so a manager of the company can browse through the table and change the salary of a single employee. But what is the company's total salary expense? And what if the manager wants to give everyone a 10 percent salary increase (or decrease)?

The program, which also demonstrates the use of an action list for the standard dataset actions, has buttons to calculate the sum of the current salaries and change them. The total action lets you calculate the sum of the salaries of all the employees. Basically, you need to scan the table, reading the value of the cdsSalary field for each record:

var
  Total: Double;
begin
  Total := 0;
  cds.First;
  while not cds.EOF do
  begin
    Total := Total + cdsSalary.Value;
    cds.Next;
  end;
  MessageDlg ('Sum of new salaries is ' +
    Format ('%m', [Total]), mtInformation, [mbOk], 0);
end

This code works, as you can see from the output in Figure 13.11, but it has some problems. One problem is that the record pointer is moved to the last record, so the previous position in the table is lost. Another is that the user interface is refreshed many times during the operation.

Click To expand
Figure 13.11: The output of the Total program, showing the total salaries of the employees

Using Bookmarks

To avoid the two problems I just mentioned, you need to disable updates and to store the current position of the record pointer in the table and restore it at the end. You can do so using a table bookmark: a special variable that stores the position of a record in a database table. Delphi's traditional approach is to declare a variable of the TBookmark data type and initialize it while getting the current position from the table:

var
  Bookmark: TBookmark;
begin
  Bookmark := cds.GetBookmark;

At the end of the ActionTotalExecute method, you can restore the position and delete the bookmark with the following two statements (inside a finally block to ensure the pointer's memory is definitely freed):

cds.GotoBookmark (Bookmark);
cds.FreeBookmark (Bookmark);

As a better (and more up-to-date) alternative, you can use the TDataset class's Bookmark property, which refers to a bookmark that is disposed of automatically. (The property is technically implemented as an opaque string—a structure subject to string lifetime management—but it is not a string, so you're not supposed to look at what's inside it.) This is how you can modify the previous code:

var
  Bookmark: TBookmarkStr;
begin
  Bookmark := cds.Bookmark;
  ...
  cds.Bookmark := Bookmark;

To avoid the other side effect of the program (you see the records scrolling while the routine browses through the data), you can temporarily disable the visual controls connected with the table. The dataset has a DisableControls method you can call before the while loop starts and an EnableControls method you can call at the end, after the record pointer is restored.

Tip 

Disabling the data-aware controls connected with a dataset during long operations not only improves the user interface (because the output is not changing constantly), but also speeds up the program considerably. The time spent updating the user interface is much greater than the time spent performing the calculations. To test this fact, try commenting out the DisableControls and EnableControls methods in the Total example and see the speed difference.

You face some dangers from errors in reading the table data, particularly if the program is reading the data from a server using a network. If any problem occurs while retrieving the data, an exception takes place, the controls remain disabled, and the program cannot resume its normal behavior. To avoid this situation, you should use a try/finally block; to make the program 100 percent error-proof, use two nested try/finally blocks. Including this change and the two just discussed, here is the resulting code:

procedure TSearchForm.ActionTotalExecute(Sender: TObject);
var
  Bookmark: TBookmarkStr;
  Total: Double;
begin
  Bookmark := Cds.Bookmark;
  try
    cds.DisableControls;
    Total := 0;
    try
      cds.First;
      while not cds.EOF do
      begin
        Total := Total + cdsSalary.Value;
        cds.Next;
      end;
    finally
      cds.EnableControls;
  end
  finally
    cds.Bookmark := Bookmark;
  end;
  MessageDlg ('Sum of new salaries is ' +
    Format ('%m', [Total]), mtInformation, [mbOK], 0);
end;
Note 

I wrote this code to show you an example of a loop to browse the contents of a dataset, but there is an alternative approach based on the use of a SQL query that returns the sum of the values of a field. When you use a SQL server, the speed advantage of a SQL call to compute the total can be significant, because you don't need to move all the data of each field from the server to the client computer. The server sends the client only the final result. There is also a better alternative when you're using a ClientDataSet, because totaling a column is one of the features provided by aggregates (discussed toward the end of this chapter). Here I discussed a generic solution, which should work for any dataset.

Editing a Table Column

The code of the increase action is similar to the action you just saw. The ActionIncreaseExecute method also scans the table, computing the total of the salaries, as the previous method did. Although it has just two more statements, there is a key difference: When you increase the salary, you change the data in the table. The two key statements are within the while loop:

while not cds.EOF do
begin
  cds.Edit;
  cdsSalary.Value := Round (cdsSalary.Value * SpinEdit1.Value) / 100;
  Total := Total + cdsSalary.Value;
  cds.Next;
end;

The first statement brings the dataset into edit mode, so that changes to the fields will have an immediate effect. The second statement computes the new salary, multiplying the old salary by the value of the SpinEdit component (by default, 105) and dividing it by 100. That's a 5 percent increase, although the values are rounded to the nearest dollar. With this program, you can change salaries by any amount with the click of a button.

Warning 

Notice that the dataset enters edit mode every time the while loop is executed. This happens because in a dataset, edit operations can take place only one record at a time. You must finish the edit operation by calling Post or by moving to a different record, as in the previous code. Then, to change another record, you have to re-enter edit mode.


 
Previous Section Next Section


 


 


Copyright © 2004-2016 "Delphi Sources". Delphi Programming Guide
     Twitter     Facebook