I mentioned earlier that a dataset has only one record that is current, or active. The record is stored in a buffer, and you can operate on it with some generic methods, but to access the data of the record you need to use the dataset's field objects. This explains why field components (technically, instances of a class derived from the TField class) play a fundamental role in every Delphi database application. Data-aware controls are directly connected to these field objects, which correspond to database fields.
By default, Delphi automatically creates the TField components at run time, each time the program opens a dataset component. This is done after reading the metadata associated with the table or the query the dataset refers to. These field components are stored in the dataset's Fields array property. You can access these values by number (accessing the array directly) or by name (using the FieldByName method). Each field can be used to read or modify the current record's data by using its Value property or type-specific properties such as AsDate, AsString, AsInteger, and so on:
var strName: string; begin strName := Cds.Fields.AsString strName := Cds.FieldByName('LastName').AsString
Value is a variant type property, so using the type-specific access properties is a little more efficient. The dataset component has also a shortcut property for accessing the variant-type value of a field: the default FieldValues property. A default property means you can omit it from the code by applying the square brackets directly to the dataset:
Creating the field components each time a dataset is opened is only a default behavior. As an alternative, you can create the field components at design time, using the Fields Editor (double-click a dataset to see the Fields Editor in action, or activate the dataset's shortcut menu or that of the Object TreeView and choose the Fields Editor command). After creating a field for the LastName column of a table, for example, you can refer to its value by applying one of the AsXxx methods to the proper field object:
strName := CdsLastName.AsString;
In addition to being used to access the value of a field, each field object has properties for controlling visualization and editing of its value, including range of values, edit masks, display format, constraints, and many others. These properties, of course, depend on the type of the field—that is, on the specific class of the field object. If you create persistent fields, you can set some properties at design time instead of writing code at run time (perhaps in the dataset's AfterOpen event).
As you open the Fields Editor for a dataset, it appears empty. You have to activate the shortcut menu of this editor or of the Fields pseudonode in the Object TreeView to access its capabilities. The simplest operation you can do is to select the Add command, which allows you to add any other fields in the dataset to the list of fields. Figure 13.5 shows the Add Fields dialog box, which lists all the fields available in a table. These are the database table fields that are not already present in the list of fields in the editor.
The Fields Editor's Define command lets you define a new calculated field, lookup field, or field with a modified type. In this dialog box, you can enter a descriptive field name, which might include blank spaces. Delphi generates an internal name—the name of the field component—which you can further customize. Next, select a data type for the field. If it is a calculated field or a lookup field, and not just a copy of a field redefined to use a new data type, select the proper radio button. You'll see how to define a calculated field and a lookup field in the sections, "Adding a Calculated Field" and "Lookup Fields."
All the fields you add or define are included in the Fields Editor and can be used by data-aware controls or displayed in a database grid. If a field of the physical dataset is not in this list, it won't be accessible. When you use the Fields Editor, Delphi adds the declaration of the available fields to the form's class as new components (much as the Menu Designer adds TMenuItem components to the form). The components of the TField class (more specifically, its subclasses) are fields of the form, and you can refer to these components directly in your program code to change their properties at run time or to get or set their value.
In the Fields Editor, you can also drag the fields to change their order. Proper field ordering is particularly important when you define a grid, which arranges its columns using this order.
Before we look at an example, let's go over the use of the TField class. Don't underestimate the importance of this component: Although it is often used behind the scenes, its role in database applications is fundamental. As I already mentioned, even if you do not define specific objects of this kind, you can always access the fields of a table or a query using their Fields array property, the FieldValues indexed property, or the FieldByName method. Both the Fields property and the FieldByName function return an object of type TField, so you sometimes have to use the as operator to downcast their result to its type (like TFloatField or TDateField) before accessing specific properties of these subclasses.
The FieldAcc example has a form with three speed buttons in the toolbar panel, which access various field properties at run time. The first button changes the formatting of the grid's population column. To do this, you have to access the DisplayFormat property, which is a specific property of the TFloatField class:
procedure TForm2.SpeedButton1Click(Sender: TObject); begin (cds.FieldByName ('Population') as TFloatField).DisplayFormat := '###,###,###'; end;
When you set field properties related to data input or output, the changes apply to every record in the table. When you set properties related to the value of the field, however, you always refer to the current record only. For example, you can output the population of the current country in a message box by writing the following:
When you access the value of a field, you can use a series of As properties to handle the current field value using a specific data type (if this data type is available; otherwise, an exception is raised):
AsBoolean: Boolean; AsDateTime: TDateTime; AsFloat: Double; AsInteger: LongInt; AsString: string; AsVariant: Variant;
These properties can be used to read or change the value of the field. Changing the value of a field is possible only if the dataset is in edit mode. As an alternative to the As properties, you can access the value of a field by using its Value property, which is defined as a variant.
Most of the other properties of the TField component, such as Alignment, DisplayLabel, DisplayWidth, and Visible, reflect elements of the field's user interface and are used by the various data-aware controls, particularly DBGrid. In the FieldAcc example, clicking the third speed button changes the Alignment of every field:
procedure TForm2.SpeedButton3Click(Sender: TObject); var I: Integer; begin for I := 0 to cds.FieldCount - 1 do cds.Fields[I].Alignment := taCenter; end;
This change affects the output of the DBGrid and of the DBEdit control I added to the toolbar, which shows the name of the country. You can see this effect, along with the new display format, in Figure 13.6.
The VCL includes a number of field class types. Delphi automatically uses one of them depending on the data definition in the database, when you open a table at run time or when you use the Fields Editor at design time. Table 13.2 shows the complete list of subclasses of the TField class.
The availability of any particular field type, and the correspondence with the data definition, depends on the database in use. This is particularly true for the field types that provide support for object relational databases.
Now that you've been introduced to TField objects and have seen an example of their run time use,
To build the new example, named Calc, follow these steps:
Of course, you also need to provide a way to calculate the new field. This is accomplished in the OnCalcFields event of the ClientDataSet component, which has the following code (at least in a first version):
procedure TForm2.cdsCalcFields(DataSet: TDataSet); begin cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value; end;
Everything fine? Not at all! If you enter a new record and do not set the value of the population and area, or if you accidentally set the area to zero, the division will raise an exception, making it problematic to continue using the program. As an alternative, you could have handled every exception of the division expression and set the resulting value to zero:
try cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value; except on Exception do cdsPopulationDensity.Value := 0; end;
However, you can do even better: You can check whether the value of the area is defined—if it is not null—and whether it is not zero. It is better to avoid using exceptions when you can anticipate possible error conditions:
if not cdsArea.IsNull and (cdsArea.Value <> 0) then cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value else cdsPopulationDensity.Value := 0;
The code for the cdsCalcFields method (in each of the three versions) accesses some fields directly. It can do so because you used the Fields Editor, and it automatically created the corresponding field declarations, as you can see in this excerpt of the form's interface declaration:
type TCalcForm = class(TForm) cds: TClientDataSet; cdsPopulationDensity: TFloatField; cdsArea: TFloatField; cdsPopulation: TFloatField; cdsName: TStringField; cdsCapital: TStringField; cdsContinent: TStringField; procedure cdsCalcFields(DataSet: TDataset); ...
Each time you add or remove fields in the Fields Editor, you can see the effect of your action immediately in the grid present in the form (unless the grid has its own column objects defined, in which case you often don't see any change). Of course, you won't see the values of a calculated field at design time; they are available only at run time, because they result from the execution of compiled Delphi language code.
Because you have defined components for the fields, you can use them to customize some of the grid's visual elements. For example, to set a display format that adds a comma to separate thousands, you can use the Object Inspector to change the DisplayFormat property of some field components to ###,###,###. This change has an immediate effect on the grid at design time.
After working on the table components and the fields, I customized the DBGrid using its Columns property editor. I set the Population Density column to read-only and set its ButtonStyle property to cbsEllipsis to provide a custom editor. When you set this value, a small button with an ellipsis is displayed when the user tries to edit the grid cell. Clicking the button invokes the DBGrid's OnEditButtonClick event:
procedure TCalcForm.DBGrid1EditButtonClick(Sender: TObject); begin MessageDlg (Format ( 'The population density (%.2n)'#13 + 'is the Population (%.0n)'#13 + 'divided by the Area (%.0n).'#13#13 + 'Edit these two fields to change it.', [cdsPopulationDensity.AsFloat, cdsPopulation.AsFloat, cdsArea.AsFloat]), mtInformation, [mbOK], 0); end;
I haven't provided a real editor but rather a message describing the situation, as you can see in Figure 13.8, which shows the values of the calculated fields. To create an editor, you might build a secondary form to handle special data entries.
As an alternative to placing a DBLookupComboBox component in a form (discussed earlier in this chapter in the section "Using Lookup Controls"), you can also define a lookup field, which can be displayed with a drop-down lookup list inside a DBGrid component. You've seen that to add a fixed selection to a DBGrid, you can edit the PickList subproperty of the Columns property. To customize the grid with a live lookup, however, you have to define a lookup field using the Fields Editor.
As an example, I built the FieldLookup program, which has a grid that displays orders; it includes a lookup field to display the name of the employee who took the order, instead of the employee's code number. To accomplish this functionality, I added to the data module a ClientDataSet component referring to the employee.cds dataset. Then I opened the Fields Editor for the orders dataset and added all the fields. I selected the EmpNo field and set its Visible property to False to remove it from the grid (you cannot remove it altogether, because it is used to build the cross-reference with the corresponding field of the employee dataset).
Now it is time to define the lookup field. If you followed the preceding steps, you can use the Fields Editor of the orders dataset and select the New Field command to open the New Field dialog box. The values you specify here will affect the properties of a new TField added to the table, as demonstrated by the DFM description of the field:
object cds2Employee: TStringField FieldKind = fkLookup FieldName = 'Employee' LookupDataSet = cds2 LookupKeyFields = 'EmpNo' LookupResultField = 'LastName' KeyFields = 'EmpNo' Size = 30 Lookup = True end
This is all that is needed to make the drop-down list work (see Figure 13.9) and to also view the value of the cross-reference field at design time. Notice that you don't need to customize the Columns property of the grid because the drop-down button and the value of seven rows are used by default. However, this doesn't mean you cannot use this property to further customize these and other visual elements of the grid.
Figure 13.9: The output of the FieldLookup example, with the drop-down list inside the grid displaying values taken from another database table
This program has another specific feature. The two ClientDataSet components and the two DataSource components have not been placed on a form but rather on a special container for nonvisual components called a data module (see the sidebar "A Data Module for Data-Access Components"). You can obtain a data module from Delphi's File ® New menu. After adding components to it, you can link them from controls on other forms with the File ® Use Unit command.
In addition to a few interesting properties, field objects have a few key events. The OnValidate event can be used to provide extended validation of a field's value and should be used whenever you need a complex rule that the ranges and constraints provided by the field cannot express. This event is triggered before the data is written to the record buffer, whereas the OnChange event is fired soon after the data has been written.
Two other events—OnGetText and OnSetText—can be used to customize a field's output. These two events are extremely powerful: They allow you to use data-aware controls even when the representation of a field you want to display is different from the one Delphi will provide by default.
Handling null values provides an example of the use of these events. On SQL servers, storing an empty value for a field is a separate operation from storing a null value for a field. The latter tends to be more correct, but Delphi by default uses empty values and displays the same output for an empty or a null field. Although this behavior can be useful in general for strings and numbers, it becomes extremely important for dates, where it is hard to set a reasonable default value and where if the deletes the contents of the field, you might have invalid input.
The NullDates program displays specific text for dates that have a null value and clears the field (setting it to the null value) when the user uses an empty string in input. Here is the relevant code of the field's two event handlers:
procedure TForm1.cdsShipDateGetText(Sender: TField; var Text: String; DisplayText: Boolean); begin if Sender.IsNull then Text := '<undefined>' else Text := Sender.AsString; end; procedure TForm1.cdsShipDateSetText(Sender: TField; const Text: String); begin if Text = '' then Sender.Clear else Sender.AsString := Text; end;
Figure 13.10 shows an example of the program's output, with undefined (or null) values for some shipping dates.
Figure 13.10: By handling the OnGetText and On-SetText events of a date field, the NullDates example displays specific output for null values.
|Copyright © 2004-2022 "Delphi Sources" by BrokenByte Software. Delphi Programming Guide||