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.
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.
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.
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;
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.
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.
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.