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.

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.

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;

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.