Delphi s Database Architecture

Overview

Delphi's support for database applications is one of the programming environment's key features. Many programmers spend most of their time writing data-access code, which needs to be the most robust portion of a database application. This chapter provides an overview of Delphi's extensive support for database programming.

What you won't find here is a discussion of the theory of database design. I'm assuming that you already know the fundamentals of database design and have already designed the structure of a database. I won't delve into database-specific problems; my goal is to help you understand how Delphi supports database access.

I'll begin with an explanation of the alternatives Delphi offers in terms of data access, and then I'll provide an overview of the database components available in Delphi. This chapter focuses on the use of the TClientDataSet component for accessing local data, deferring all the client/server database access to Chapter 14, "Client/Server with dbExpress." I'll include an overview of the TDataSet class, an in-depth analysis of the TField components, and the use of data-aware controls. Chapter 14 follows up by providing information about more advanced database programming topics, particularly client/server programming with the use of the dbExpress library (and the InterBase Express components).

Finally, notice that almost everything discussed in this chapter is cross-platform. In particular, the examples can be ported to CLX and to Linux by recompiling and referring to CDS files in the proper folders.

Accessing a Database dbExpress, Local Data,and Other Alternatives

The early incarnations of Delphi—immediately adopted as a tool for building database-oriented applications—could access a database only by means of the Borland Database Engine (BDE). Starting with Delphi 3, the portion of VCL related to database access was restructured to open it up to multiple database access solutions, which currently include ADO, native InterBase components, the dbExpress library, and the BDE. Many third-party vendors are now able to offer alternative database access mechanisms to a variety of data formats (some not accessible through Borland components) and still provide a solution integrated with Delphi's VCL.

Tip 

In Kylix, the overall picture is slightly different. Borland decided not to port the old BDE technology to Linux and focused instead on the new thin database access layer, dbExpress.

As a further solution, for simple applications you can use Delphi's ClientDataSet component, which has the ability to save tables to local files—something Borland touts with the name MyBase. Notice that the typical simple Delphi application based on Paradox tables does not port to Kylix, due to the lack of the BDE.

The dbExpress Library

One of the most relevant new features of Delphi in the recent years has been the introduction of the dbExpress database library (DBX), available both for the Linux and the Windows platforms. I say library and not database engine because, unlike other solutions, dbExpress uses a lightweight approach and requires basically no configuration on end-user machines.

Being light and portable are the two key characteristics of dbExpress; Borland introduced it for those reasons, along with the development of the Kylix project. Compared to other powerhouses, dbExpress is very limited in its capabilities. It can access only SQL servers (no local files); it has no caching capabilities and provides only unidirectional access to the data; and it can natively work only with SQL queries and is unable to generate the corresponding SQL update statements.

At first, you might think these limitations make the library useless. On the contrary: These are features that make it interesting. Unidirectional datasets with no direct update are the norm if you need to produce reports, including generating HTML pages showing the content of a database. If you want to build a user interface to edit the data, however, Delphi includes specific components (the ClientDataSet and Provider, in particular) that provide caching and query resolution. These components allow your dbExpress-based application much more control than a separate (monolithic) database engine, which does extra things for you but often does them the way it wants to, not the way you would like.

dbExpress allows you to write an application that, aside from problems with different SQL dialects, can access many different database engines without much code modification. Supported SQL servers include Borland's own InterBase database, the Oracle database server, the MySQL database (which is popular particularly on Linux), the Informix database, IBM's DB2, and Microsoft SQL Server in Delphi 7. A more detailed description of dbExpress, the related VCL components, and many examples of its use will be provided in Chapter 14; the current chapter focuses on database architecture foundations.

Tip 

The availability of a dbExpress driver for Microsoft SQL Server in Delphi 7 fills a significant gap. This database is frequently used on the Windows platform, and developers who needed a solution portable among different database servers often had to include support for Microsoft SQL Server. Now there is one less reason to stick with the BDE. Borland has released an update of the SQL Server dbExpress driver shipping with Delphi 7 to fix a couple of bugs.

The Borland Database Engine

Delphi still ships with the BDE, which allows you to access local database formats (like Paradox and dBase) and SQL servers as well as anything accessible through ODBC drivers. This was the standard database technology in early versions of Delphi, but Borland now considers it obsolete. This is particularly true for the use of the BDE to access SQL servers through the SQL Links drivers. Using the BDE to access local tables is still officially supported, simply because Borland doesn't provide a direct migration path for this type of application.

In some cases, a local table can be replaced with the ClientDataSet component (MyBase) specifically for temporary and small lookup tables. However, this approach won't work for larger local tables, because MyBase requires the entire table to be loaded in memory to access even a single record. The suggestion is to move larger tables to a SQL server installed on the client computer. InterBase, with its small footprint, is ideal in this particular situation. This type of migration will also open to you the doors of Linux, where the BDE is not available.

Of course, if you have existing applications that use the BDE, you can continue using them. The BDE page of Delphi's Component Palette still has the Table, Query, StoreProc, and other BDE-specific components. I'd discourage you from developing new programs with this old technology, which is almost discontinued by its producer. Eventually, you should look to third-party engines to replace the BDE when your programs require a similar architecture (or you need compatibility with older database file formats).

Note 

This is the reason I've removed any coverage of the BDE in the current edition of this book. This chapter used to be based on the Table and Query components; it has been rewritten to describe the architecture of Delphi database applications using the ClientDataSet component.

InterBase Express

Borland has made available another set of database access components for Delphi: InterBase Express (IBX). These components are specifically tailored to Borland's own InterBase server. Unlike dbExpress, this is not a server-independent database engine, but a set of components for accessing a specific database server. If you plan to use only InterBase as your back-end database, using a specific set of components can give you more control over the server, provide the best performance, and allow you to configure and maintain the server from within a custom client application.

Note 

The use of InterBase Express highlights the case of database-specific custom datasets, which are available from third-party vendors for many servers. (There are other dataset components for InterBase, just as there are for Oracle, local or shared dBase files, and many others.)

You can consider using IBX (or other comparable sets of components) if you are sure you won't change your database and you want to achieve the best performance and control at the expense of flexibility and portability. The down side is that the extra performance and control you gain may be limited. You'll also have to learn to use another set of components with a specific behavior, rather than learn to use a generic engine and apply your knowledge to different situations.

MyBase and the ClientDataSet Component

The ClientDataSet is a dataset accessing data kept in memory. The in-memory data can be temporary (created by the program and lost as you exit it), loaded from a local file and then saved back to it, or imported by another dataset using a Provider component.

Borland indicates that you should use the ClientDataSet component mapped to a file with the name MyBase, to indicate that it can be considered a local database solution. I have trouble with the way Borland marketing has promoted this technology, but it has a place, as I'll discuss in the section "MyBase: Stand-alone ClientDataSet."

Accessing data from a provider is a common approach both for client/server architectures (as you'll see in Chapter 14) and for multitier architectures (discussed in Chapter 16, "Multitier DataSnap Applications"). The ClientDataSet component becomes particularly useful if the data-access components you are using provide limited or no caching, which is the case with the dbExpress engine.

dbGo for ADO

ADO (ActiveX Data Objects) is Microsoft's high-level interface for database access. ADO is implemented on Microsoft's data-access OLE DB technology, which provides access to relational and non-relational databases as well as e-mail and file systems and custom business objects. ADO is an engine with features comparable to those of the BDE: database server independence supporting local and SQL servers alike, a heavyweight engine, and a simplified configuration (because it is not centralized). Installation should (in theory) not be an issue, because the engine is part of recent versions of Windows. However, the limited compatibility among versions of ADO will force you to upgrade your users' computers to the same version you've used for developing the program. The sheer size of the MDAC (Microsoft Data Access Components) installation, which updates large portions of the operating system, makes this operation far from simple.

ADO offers definite advantages if you plan to use Access or SQL Server, because Microsoft's drivers for its own databases are of better quality than the average OLE DB providers. For Access databases, specifically, using Delphi's ADO components is a good solution. If you plan to use other SQL servers, first check the availability of a good-quality driver, or you might have some surprises. ADO is very powerful, but you have to learn to live with it— it stands between your program and the database, providing services but occasionally also issuing different commands than you may expect. On the negative side, do not even think of using ADO if you plan future cross-platform development; this Microsoft-specific technology is not available on Linux or other operating systems.

In short, use ADO if you plan to work only on Windows, want to use Access or other Microsoft databases, or find a good OLE DB provider for each of the database servers you plan to work with (at the moment, this factor excludes InterBase and many other SQL servers).

ADO components (part of a package Borland calls dbGo) are grouped in the ADO page of the Component Palette. The three core components are ADOConnection (for database connections), ADOCommand (for executing SQL commands), and ADODataSet (for executing requests that return a result set). There are also three compatibility components—ADOTable, ADOQuery, and ADOStoredProc—that you can use to port BDE-based applications to ADO. Finally, the RDSConnection component lets you access data in remote multitier applications.

Note 

Chapter 15, "Working with ADO," covers ADO and related technologies in detail. Notice that Microsoft is replacing ADO with its .NET version, which is based on the same core concepts. So, using ADO might provide you with a good path toward native .NET applications (although Borland plans to move dbExpress to that platform, too).

Custom Dataset Components

As a further alternative, you can write your own custom dataset components, or choose one of the many offerings available. Developing custom dataset components is one of the most complex issues of Delphi programming; it's covered in depth in Chapter 17, "Writing Database Components." Reading that material, you'll also learn about the internal workings of the TDataSet class.

MyBase Stand alone ClientDataSet

If you want to write a single-user database application in Delphi, the simplest approach is to use the ClientDataSet component and map it to a local file. This local file mapping is different from the traditional data mapping to a local file. The traditional approach is to read from the file a record at a time, and possibly have a second file that stores indexes. The ClientDataSet maps an entire table (and possibly a master/detail structure) to the file in its entirety: When a program starts, the entire file is loaded in memory, and then everything is saved at once.

Warning 

This explains why you cannot use this approach in a multiuser or multiapplication situation. If two programs or two instances of the same program load the same ClientDataSet file in memory and modify the data, the last table saved will overwrite changes made by other programs.

This support for persisting the content of a ClientDataSet was created a few years ago as a way to implement the so-called briefcase model. A user could (and still can) download data from its database server to the client, save some of the data, work disconnected (while traveling with a laptop computer, for example), and finally reconnect to commit the changes.

Connecting to an Existing Local Table

To map a ClientDataSet to a local file, you set its FileName property. To build a minimal program (called MyBase1 in the example), all you need is a ClientDataSet component hooked to a CDS file (there are a few in the Data folder available under Program FilesCommon FilesBorland Shared), a DataSource (more on this later), and a DBGrid control. Hook the ClientDataSet to the DataSource via the DataSource's DataSet property and the DataSource to the DBGrid via the grid's DataSource property, as in Listing 13.1. At this point turn on the Active property of the ClientDataSet and you'll have a program showing database data even at design time; see Figure 13.1.

Listing 13.1: The DFM File of the MyBase1 Sample Program

object Form1: TForm1
 ActiveControl = DBGrid1
 Caption = 'MyBase1'
 OnCreate = FormCreate
 object DBGrid1: TDBGrid
 DataSource = DataSource1
 end
 object DataSource1: TDataSource
 DataSet = cds
 end
 object cds: TClientDataSet
 FileName = 'C:Program FilesCommon FilesBorland
 SharedDatacustomer.cds'
  end
end

Figure 13.1: A sample local table active at design time in the Delphi IDE

As you make changes and close the application, the data will be automatically saved to the file. (You might want to disable the change log, as discussed later, to reduce the size of this data.) The dataset also has a SaveToFile method and a LoadFromFile method you can use in your code.

I also made another change: I disabled the ClientDataSet at design time to avoid including all of its data in the program's DFM file and in the compiled executable file; I want to keep the data in a separate file. To do this, close the dataset at design time, after testing, and add a line to the form's OnCreate event to open it:

procedure TForm1.FormCreate(Sender: TObject);
begin
 cds.Open;
end;

From the Midas DLL to the MidasLib Unit

To run any application using the ClientDataSet component, you need to also deploy the midas.dll dynamic library referenced by the DSIntf.pas unit. The ClientDataSet component's core code is not directly part of the VCL and is not available in source code format. This is unfortunate, because many developers are accustomed to debugging the VCL source code and using it as the ultimate reference.

Warning 

The midas.dll library has no version number in its name. So, if a computer has an older version, your program will apparently run on it but may not behave properly.

The Midas library is a C-language library, but since Delphi 6 it can be bound directly into an executable by including the specific MidasLib unit (a special DCU produced by a C compiler). In this case you won't need to distribute the library in the DLL format.

XML and CDS Formats

The ClientDataSet component supports two different streaming formats: the native format and an XML-based format. The Borland SharedDemo folder mentioned earlier holds versions of a number of tables in each of the two formats. By default, MyBase saves the datasets in XML format. The SaveToFile method has a parameter allowing you to specify the format, and the LoadFromFile method works automatically for both formats.

Using the XML format has the advantage of making the persistent data also accessible with an editor and with other programs not based on the ClientDataSet component. However, this approach implies converting the data back and forth, because the CDS format is close to the internal memory representation invariably used by the component, regardless of the streaming format. Also, the XML format generates large files, because they are text based. On average, a MyBase XML file is twice the size of the corresponding CDS file.

Tip 

While you have a ClientDataSet in memory, you can extract its XML representation by using the XMLData property without streaming out the data. The next example puts this technique into practice.

Defining a New Local Table

Besides letting you hook to an existing database table stored in a local file, the ClientDataSet component allows you to create new tables easily. All you have to do is use its FieldDefs property to define the structure of the table. After doing this, you can physically create the file for the table with the Create DataSet command on the ClientDataSet component's shortcut menu in the Delphi IDE or by calling its CreateDataSet method at run time.

This is an extract from the MyBase2 example's DFM file, which defines a new local database table:

object ClientDataSet1: TClientDataSet
 FileName = 'mybase2.cds'
 FieldDefs = <
 item
 Name = 'one'
 DataType = ftString
 Size = 20
    end
    item
 Name = 'two'
 DataType = ftSmallint
 end>
 StoreDefs = True
end

Notice the StoreDefs property, which is automatically set to True when you edit the collection of field definitions. By default, a dataset in Delphi loads its metadata before opening. Only if a local definition is stored in the DFM file is this local metadata used (saving field definitions in the DFM file is also helpful to cache this metadata in a client/server architecture).

To account for the optional dataset creation, the disabling of the log (described later), and the display of the XML version of the initial data in a Memo control, the program's form class has the following OnCreate event handler:

procedure TForm1.FormCreate(Sender: TObject);
begin
 if not FileExists (cds.FileName) then
 cds.CreateDataSet;
 cds.Open;
 cds.MergeChangeLog;
 cds.LogChanges := False;
 Memo1.Lines.Text := StringReplace (
 Cds.XMLData, '>', '>' + sLineBreak, [rfReplaceAll]);
end;

The last statement includes a call to StringReplace to provide a poor man's XML formatting: The code adds a new line at the end of each XML tag by adding a new line after the close angle bracket. You can see the table's XML display with a few records in Figure 13.2. You'll learn a lot more about XML in Delphi in Chapter 22, "Using XML Technologies."

Figure 13.2: The XML display of a CDS file in the MyBase2 example. The table structure is defined by the program, which creates a file for the dataset on its first execution.

Indexing

Once you have a ClientDataSet in memory, you can perform many operations on it. The simplest are indexing, filtering, and searching for records; more complex operations include grouping, defining aggregate values, and managing the change log. Here I'll cover only the simplest techniques; more complex material appears at the end of the chapter.

Filtering a ClientDataSet is a matter of setting the IndexFieldNames property. This is often accomplished when the user clicks the field title in a DBGrid component (firing the OnTitleClick event), as in the MyBase2 example:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
 cds.IndexFieldNames := Column.Field.FieldName;
end;

Unlike other local databases, a ClientDataSet can have this type of dynamic index without any database configuration because indexes are computed in memory.

Tip 

The component also supports indexes based on a calculated field, specifically an internally calculated field, available only for this dataset (as I'll describe later in this chapter). Unlike ordinary calculated fields, which are computed every time the record is used, values of internally calculated fields are calculated once and kept in memory. For this reason, indexes consider them plain fields.

In addition to assigning a new value to the IndexFieldNames property, you can define an index using the IndexDefs property. Doing so allows you to define several indexes and keep them in memory, switching even faster from one to the other.

Tip 

Defining a separate index is the only way to have a descending index, rather than an ascending index.

Filtering

As with any other dataset, you can use the Filter property to specify the inclusion in the dataset of portions of the data the component is bound to. The filtering operation takes place in memory after loading all the records, so this is a way to present less data to the user, not to limit the memory footprint of a large local dataset.

When you're retrieving large amounts of data from a server (in a client/server architecture) you should try to use a proper query so you don't retrieve a large dataset from a SQL server. Filtering up front in the server should generally be your first choice. With local data, you might consider splitting a large number of records into a set of different files, so you can load only those you need and not all of them.

Local filtering in the ClientDataSet can be useful, particularly because the filter expressions you can use with this component are much more extensive than those you can use with other datasets. In particular, you can use the following:

• The standard comparison and logical operators: for example, Population > 1000 and
  Area < 1000
• Arithmetic operators: for example, Population / Area < 10
• String functions: for example, Substring(Last_Name, 1, 2) = 'Ca'
• Date and time functions: for example, Year (Invoice_Date) = 2002
• Others, including a Like function, wildcards, and an In operator

These filtering capabilities are fully documented in the VCL Help file. You should look for the page "Limiting what records appear" linked from the description of the Filter property of the TClientDataSet class, or reach it from the Help Contents page following this path down
the tree: Developing Database Applications, Using client datasets, Limiting what records appear.

Locating Records

Filtering allows you to limit the records displayed to the program's user, but many times you want to display all the records and only move to a specific one. The Locate method does this. If you've never used Locate, at first sight the Help file won't be terribly clear. The idea is that you must provide a list of fields you want to search and a list of values, one for each field. If you want to match only one field, the value is passed directly, as in this case (where the search string is in the EditName component):

procedure TForm1.btnLocateClick(Sender: TObject);
begin
 if not cds.Locate ('LastName', EditName.Text, []) then
 MessageDlg ('"' + EditName.Text + '" not found', mtError, [mbOk], 0);
end;

If you search for multiple fields, you have to pass a variant array with the list of values you want to match. The variant array can be created from a constant array with the VarArrayOf function or from scratch using the VarArrayCreate call. This is a code snippet:

cds.Locate ('LastName;FirstName', VarArrayOf (['Cook', 'Kevin']), [])

Finally, you can use the same method to look for a record even if you know only the initial portion of the field you are looking for. All you have to do is to add the loPartialKey flag to the Options parameter (the third) of the Locate call.

Note 

Using Locate makes sense when you're working with a local table, but it doesn't port well to client/server applications. On a SQL server, similar client-side techniques imply moving all the data to the client application first (which is generally a bad idea) and then searching for a specific record. You should locate the data with restricted SQL statements. You can still call Locate after you retrieve a limited dataset. For example, you can search for a customer by name after you select all the customers of a given town or area, obtaining a result set of a limited size. There's more about this topic in Chapter 14, which is devoted to client/server development.

Undo and SavePoint

As a user modifies the data in a ClientDataSet component, the updates are stored in a memory area called Delta. The reason for keeping track of user changes instead of holding the resulting table is due to the way the updates are handled in a client/server architecture. In this case, the program doesn't have to send the entire table back to the server, but only a list of the user's changes (by means of specific SQL statements, as you'll see in Chapter 14).

Because the ClientDataSet component keeps track of changes, you can reject those changes, removing entries from the delta. The component has an UndoLastChange method to accomplish this. The method's FollowChange parameter allows you to follow the undo operation—the client dataset will move to the record that has been restored by the undo operation. Here is the code you can use to connect to an Undo button:

procedure TForm1.ButtonUndoClick(Sender: TObject);
begin
 cds.UndoLastChange (True);
end;

An extension of the undo support is the possibility of saving a sort of bookmark of the change log position (the current status) and to restore it later by undoing all successive changes. You can use the SavePoint property either to save the number of changes in the log or to reset the log to a past situation. However, you can only remove records from the change log, not reinsert changes. In other words, the SavePoint property refers to a position in a log, so it can only go back to a position where there were fewer records! This log position is a number of changes, so if you save the current position, undo some changes, and then do more edits, you won't be able to get back to the position you bookmarked.

Tip 

Delphi 7 has a new standard action mapped to the ClientDataSet's Undo operation. Other new actions include Revert and Apply, which you'll need when the component is connected to a dataset accessing a database.

Enabling and Disabling Logging

Keeping track of changes makes sense if you need to send the updated data back to a server database. In local applications with data stored to a MyBase file, keeping this log around can become useless and consumes memory. For this reason, you can disable logging with the LogChanges property. This will also stop the undo operations, though.

You can also call the MergeChangesLog method to remove all current editing from the change log, and confirm the edits performed so far. Doing so makes sense if you want to keep the undo log around within a single session and then save the final dataset without the keeping the change log.

Note 

The MyBase2 example disables the change log as discussed here: You can remove that code and re-enable it to see the difference in the size of the CDS file and in the XML text after editing the data.

Using Data Aware Controls

Once you set up the proper data-access components, you can build a user interface to let a user view the data and eventually edit it. Delphi provides many components that resemble the usual controls but are data-aware. For example, the DBEdit component is similar to the Edit component, and the DBCheckBox component corresponds to the CheckBox component. You can find all these components in the Data Controls page of the Delphi Component Palette.

All these components are connected to a data source using the corresponding property, DataSource. Some of them relate to the entire dataset, such as the DBGrid and DBNavigator components, and the others refer to a specific field of the data source, as indicated by the DataField property. Once you select the DataSource property, the DataField property editor will contain a list of available values.

Notice that all the data-aware components are unrelated to the data-access technology, provided the data-access component inherits from TDataSet. Thus your investment in the user interface is preserved when you change the data-access technology. However, some of the lookup components and extended use of the DBGrid (displaying a lot of data) make sense only when you're working with local data and should generally be avoided in a client/server situation, as you'll see in Chapter 14.

Data in a Grid

The DBGrid is a grid capable of displaying a whole table at once. It allows scrolling and navigation, and you can edit the grid's contents. It is an extension of the other Delphi grid controls.

You can customize the DBGrid by setting its Options property's various flags and modifying its Columns collection. The grid allows a user to navigate the data using the scrollbars and perform all the major actions. A user can edit the data directly, insert a new record in a given position by pressing the Insert key, append a new record at the end by going to the last record and pressing the Down arrow key, and delete the current record by pressing Ctrl+Del.

The Columns property is a collection from which you can choose the table fields you want to see in the grid and set column and title properties (color, font, width, alignment, caption, and so on) for each field. Some of the more advanced properties, such as ButtonStyle and DropDownRows, can be used to provide custom editors for a grid's cells or a drop-down list of values (indicated in the column's PickList property).

DBNavigator and Dataset Actions

DBNavigator is a collection of buttons used to navigate and perform actions on the database. You can disable some of the DBNavigator control's buttons by removing some of the elements of the VisibleButtons set property.

The buttons perform basic actions on the connected dataset, so you can easily replace them with your own toolbar, particularly if you use an ActionList component with the predefined database actions provided by Delphi. In this case, you get all the standard behaviors, but you'll also see the various buttons enabled only when their action is legitimate. The advantages of using the actions is that you can display the buttons in the layout you prefer, intermix them with other buttons of the application, and use multiple client controls, including main and popup menus.

Tip 

If you use the standard actions, you can avoid connecting them to a specific DataSource component, and the actions will be applied to the dataset connected to the visual control that currently has the input focus. This way, a single toolbar can be used for multiple datasets displayed by a form, which can be very confusing to the user if not considered carefully.

Text Based Data Aware Controls

There are multiple text-oriented components:

DBText  Displays the contents of a field that cannot be modified by the user. It is a data-aware Label graphical control. It can be very useful, but users might confuse this control with the plain labels that indicate the content of each field-based control.

DBEdit  Lets the user edit a field (change the current value) using an Edit control. At times, you might want to disable editing and use a DBEdit as if it were a DBText, but highlight the fact that this is data coming from the database.

DBMemo  Lets the user see and modify a large text field, eventually stored in a memo or BLOB (binary large object) field. It resembles the Memo component and has full editing capabilities, but all the text is rendered in a single font.

List Based Data Aware Controls

To let a user choose a value in a predefined list (which reduces input errors), you can use many different components. DBListBox, DBComboBox, and DBRadioGroup are similar, providing a list of strings in the Items property, but they do have some differences:

DBListBox  Allows selection of predefined items (closed selection), but not text input, and can be used to list many elements. Generally it's best to show only about six or seven items to avoid using up too much space on the screen.

DBComboBox  Can be used both for closed selection and for user input. The csDropDown style of the DBComboBox allows a user to enter a new value, in addition to selecting one of the available values. The component also uses a smaller area on the form because the drop-down list is usually displayed only on request.

DBRadioGroup  Presents radio buttons (which permit only one selection), allows only closed selection, and should be used only for a limited number of alternatives. A nice feature of this component is that the values displayed can be those you want to insert in the database, but you can also choose to provide mapping. The values of the user interface (descriptive strings stored in the Items property) will map to corresponding values stored in the database (numeric or character-based codes listed in the Values property). For example, you can map numeric codes indicating departments to a few descriptive strings:

object DBRadioGroup1: TDBRadioGroup
 Caption = 'Department'
 DataField = 'Department'
 DataSource = DataSource1
 Items.Strings = (
    'Sales'
    'Accounting'
    'Production'
 'Management')
 Values.Strings = (
    '1'
    '2'
    '3'
 '4')
end

The DBCheckBox component is slightly different; it is used to show and toggle an option, corresponding to a Boolean field. It is a limited list because it has only two possible values plus the undetermined state for fields with null values. You can determine which are the values to send back to the database by setting the ValueChecked and ValueUnchecked properties of this component.

The DbAware Example

The DbAware example highlights the usage of a DBRadioGroup control with the settings discussed in the previous section and a DBCheckBox control. This example is not much more complex than earlier ones, but it has a form with field-oriented data-aware controls, instead of a grid encompassing them all. You can see the example's form at design time in Figure 13.3.

Figure 13.3: The data-aware controls of the DbAware example at design time in Delphi

As in the MyBase2 program, the application defines its own table structure, using the FieldDefs collection property of the ClientDataSet. Table 13.1 provides a short summary of the fields defined.

Table 13.1: The Dataset Fields in the DbAware Example

Name	DataType	Size
LastName	ftString	20
FirstName	ftString	20
Department	FtSmallint
Branch	ftString	20
Senior	ftBoolean
HireDate	ftDate

The program has some code to fill in the table with random values. This code is tedious and not too complex, so I won't discuss the details here, but you can look at the DbAware source code if you are interested.

Using Lookup Controls

If the list of values is extracted from another dataset, then instead of the DBListBox and DBComboBox controls you should use the specific DBLookupListBox or DBLookupComboBox component. These components are used every time you want to select for a field a value that corresponds to a record of another dataset (and not to choose a different record to display!).

For example, if you build a standard form for taking orders, the orders dataset will generally have a field hosting a number indicating the customer who made the order. Working directly with the customer number is not the most natural way; most users will prefer to work with customer names. However, in the database, the customers' names are stored in a different table, to avoid duplicating the customer data for each order by the same customer. To get around such a situation, with local databases or small lookup tables, you can use a DBLookupComboBox control. (This technique doesn't port well to client/server architecture with large lookup tables, as discussed in the next chapter.)

The DBLookupComboBox component can be connected to two data sources at the same time: one source containing the data and a second containing the display data. I built a standard form using the orders.cds file from the Delphi sample data folder; the form includes several DBEdit controls.

You should remove the standard DBEdit component connected to the customer number and replace it with a DBLookupComboBox component (and a DBText component, to fully understand what is going on). The lookup component (and the DBText) is connected to the DataSource for the order and to the CustNo field. To let the lookup component show the information extracted from another file (customer.cds) you need to add another ClientDataSet component referring to the file, along with a new data source.

For the program to work, you need to set several properties of the DBLookupComboBox1 component. Here is a list of the relevant values:

object DBLookupComboBox1: TDBLookupComboBox
 DataField = 'CustNo'
 DataSource = DataSourceOrders
 KeyField = 'CustNo'
 ListField = 'Company;CustNo'
 ListSource = DataSourceCustomer
 DropDownWidth = 300
end

The first two properties determine the main connection, as usual. The next four properties determine the field used for the join (KeyField), the information to display (ListField), and the secondary source (ListSource). In addition to entering the name of a single field, you can provide multiple fields, as I did in the example. Only the first field is displayed as combo box text, but if you set a large value for the DropDownWidth property, the combo box's drop-down list will include multiple columns of data. You can see this output in Figure 13.4.

Figure 13.4:  The output of the CustLookup example, with the DBLookupCombo-Box showing multiple fields in its drop-down list

Tip 

If you set the IndexFieldNames property of the ClientDataSet containing the orders data to the Company field, the drop-down list will show the companies in alphabetical order instead of customer-number order. I did this in the example.

Graphical Data Aware Controls

Delphi includes a graphical data-aware control: DBImage. It is an extension of an Image component that shows a picture stored in a BLOB field, provided the database uses a graphic format that the Image component supports, such as BMP or JPEG (if you add the JPEG unit to your uses clause).

Once you have a table that includes a BLOB storing an image with a compatible graphic format, hooking it to the component is trivial. If, instead, the graphic format requires a custom transformation in order to be displayed, it might be easier to use a standard non-data-aware Image component and write code so the image is updated each time the current record changes. Before I can discuss this subject, however, you need to know more about the TDataSet class and the dataset field classes.

The DataSet Component

Instead of proceeding with the discussion of the capabilities of a specific dataset at this point, I prefer to devote some space to a generic introduction of the features of the TDataSet class, which are shared by all inherited data-access classes. The DataSet component is very complex, so I won't list all its capabilities—I will only discuss its core elements.

This component provides access to a series of records that are read from some source of data, kept in internal buffers (for performance reasons), and eventually modified by a user, with the possibility of writing back changes to the persistent storage. This approach is generic enough to be applied to different types of data (even non-database data), but it has a few rules:

• There can be only one active record at a time, so if you need to access data in multiple records, you must move to each of them, read the data, then move again, and so on. You'll find an example of this and related techniques in the section "Navigating a Dataset."
• You can edit only the active record: You cannot modify a set of records at the same time, as you can in a relational database.
• You can modify data in the active buffer only after you explicitly declare you want to do so, by giving the Edit command to the dataset. You can also use the Insert command to create a new blank record and close both operations (insert or edit) by giving a Post command.

Other interesting elements of a dataset that I'll explore in the following sections are its status (and the status change events), navigation and record positions, and the role of field objects. As a summary of the capabilities of the DataSet component, I included the public methods of its class in Listing 13.2 (the code has been edited and commented for clarity). Not all of these methods are directly used everyday, but I kept them all in the listing.

Listing 13.2: The Public Interface of the TDataSet Class (Excerpted)

TDataSet = class(TComponent, IProviderSupport)
...
public
  // create and destroy, open and close
 constructor Create(AOwner: TComponent); override;
 destructor Destroy; override;
 procedure Open;
 procedure Close;
 property BeforeOpen: TDataSetNotifyEvent read FBeforeOpen write FBeforeOpen;
 property AfterOpen: TDataSetNotifyEvent read FAfterOpen write FAfterOpen;
 property BeforeClose: TDataSetNotifyEvent
 read FBeforeClose write FBeforeClose;
 property AfterClose: TDataSetNotifyEvent read FAfterClose write FAfterClose;
 
  // status information
 function IsEmpty: Boolean;
 property Active: Boolean read GetActive write SetActive default False;
 property State: TDataSetState read FState;
 function ActiveBuffer: PChar;
 property IsUniDirectional: Boolean
  read FIsUniDirectional write FIsUniDirectional default False;
 function UpdateStatus: TUpdateStatus; virtual;
 property RecordSize: Word read GetRecordSize;
 property ObjectView: Boolean read FObjectView write SetObjectView;
 property RecordCount: Integer read GetRecordCount;
 function IsSequenced: Boolean; virtual;
 function IsLinkedTo(DataSource: TDataSource): Boolean;
 
  // datasource
 property DataSource: TDataSource read GetDataSource;
 procedure DisableControls;
 procedure EnableControls;
 function ControlsDisabled: Boolean;
 
  // fields, including blobs, details, calculated, and more
 function FieldByName(const FieldName: string): TField;
 function FindField(const FieldName: string): TField;
 procedure GetFieldList(List: TList; const FieldNames: string);
 procedure GetFieldNames(List: TStrings); virtual; // virtual since Delphi 7
 property FieldCount: Integer read GetFieldCount;
 property FieldDefs: TFieldDefs read FFieldDefs write SetFieldDefs;
 property FieldDefList: TFieldDefList read FFieldDefList;
 property Fields: TFields read FFields;
 property FieldList: TFieldList read FFieldList;
 property FieldValues[const FieldName: string]: Variant
  read GetFieldValue write SetFieldValue; default;
 property AggFields: TFields read FAggFields;
 property DataSetField: TDataSetField
  read FDataSetField write SetDataSetField;
 property DefaultFields: Boolean read FDefaultFields;
 procedure ClearFields;
 function GetBlobFieldData(FieldNo: Integer;
  var Buffer: TBlobByteData): Integer; virtual;
 function CreateBlobStream(Field: TField;
 Mode: TBlobStreamMode): TStream; virtual;
 function GetFieldData(Field: TField;
 Buffer: Pointer): Boolean; overload; virtual;
 procedure GetDetailDataSets(List: TList); virtual;
 procedure GetDetailLinkFields(MasterFields, DetailFields: TList); virtual;
 function GetFieldData(FieldNo: Integer;
 Buffer: Pointer): Boolean; overload; virtual;
 function GetFieldData(Field: TField; Buffer: Pointer; NativeFormat: Boolean):
 Boolean; overload; virtual;
 property AutoCalcFields: Boolean
 read FAutoCalcFields write FAutoCalcFields default True;
 property OnCalcFields: TDataSetNotifyEvent
  read FOnCalcFields write FOnCalcFields;
 
 // position, movement
 procedure CheckBrowseMode;
 procedure First;
 procedure Last;
 procedure Next;
 procedure Prior;
 function MoveBy(Distance: Integer): Integer;
 property RecNo: Integer read GetRecNo write SetRecNo;
 property Bof: Boolean read FBOF;
 property Eof: Boolean read FEOF;
 procedure CursorPosChanged;
 property BeforeScroll: TDataSetNotifyEvent
  read FBeforeScroll write FBeforeScroll;
 property AfterScroll: TDataSetNotifyEvent
 read FAfterScroll write FAfterScroll;
 
 // bookmarks
  procedure FreeBookmark(Bookmark: TBookmark); virtual;
 function GetBookmark: TBookmark; virtual;
 function BookmarkValid(Bookmark: TBookmark): Boolean; virtual;
 procedure GotoBookmark(Bookmark: TBookmark);
 function CompareBookmarks(Bookmark1, Bookmark2: TBookmark): Integer; virtual;
 property Bookmark: TBookmarkStr read GetBookmarkStr write SetBookmarkStr;
 
 // find, locate
 function FindFirst: Boolean;
 function FindLast: Boolean;
 function FindNext: Boolean;
 function FindPrior: Boolean;
 property Found: Boolean read GetFound;
 function Locate(const KeyFields: string; const KeyValues: Variant;
 Options: TLocateOptions): Boolean; virtual;
 function Lookup(const KeyFields: string; const KeyValues: Variant;
  const ResultFields: string): Variant; virtual;
 
  // filtering
 property Filter: string read FFilterText write SetFilterText;
 property Filtered: Boolean read FFiltered write SetFiltered default False;
 property FilterOptions: TFilterOptions
  read FFilterOptions write SetFilterOptions default [];
 property OnFilterRecord: TFilterRecordEvent
  read FOnFilterRecord write SetOnFilterRecord;
 
  // refreshing, updating
 procedure Refresh;
 property BeforeRefresh: TDataSetNotifyEvent
  read FBeforeRefresh write FBeforeRefresh;
 property AfterRefresh: TDataSetNotifyEvent
  read FAfterRefresh write FAfterRefresh;
 procedure UpdateCursorPos;
 procedure UpdateRecord;
 function GetCurrentRecord(Buffer: PChar): Boolean; virtual;
 procedure Resync(Mode: TResyncMode); virtual;
 
 // editing, inserting, posting, and deleting
 property CanModify: Boolean read GetCanModify;
 property Modified: Boolean read FModified;
 procedure Append;
 procedure Edit;
 procedure Insert;
 procedure Cancel; virtual;
 procedure Delete;
 procedure Post; virtual;
 procedure AppendRecord(const Values: array of const);
 procedure InsertRecord(const Values: array of const);
 procedure SetFields(const Values: array of const);
 
  // events related to editing, inserting, posting, and deleting
 property BeforeInsert: TDataSetNotifyEvent
 read FBeforeInsert write FBeforeInsert;
 property AfterInsert: TDataSetNotifyEvent
  read FAfterInsert write FAfterInsert;
 property BeforeEdit: TDataSetNotifyEvent read FBeforeEdit write FBeforeEdit;
 property AfterEdit: TDataSetNotifyEvent read FAfterEdit write FAfterEdit;
 property BeforePost: TDataSetNotifyEvent read FBeforePost write FBeforePost;
 property AfterPost: TDataSetNotifyEvent read FAfterPost write FAfterPost;
 property BeforeCancel: TDataSetNotifyEvent
  read FBeforeCancel write FBeforeCancel;
 property AfterCancel: TDataSetNotifyEvent
  read FAfterCancel write FAfterCancel;
 property BeforeDelete: TDataSetNotifyEvent
  read FBeforeDelete write FBeforeDelete;
 property AfterDelete: TDataSetNotifyEvent
  read FAfterDelete write FAfterDelete;
 property OnDeleteError: TDataSetErrorEvent
  read FOnDeleteError write FOnDeleteError;
 property OnEditError: TDataSetErrorEvent
  read FOnEditError write FOnEditError;
 property OnNewRecord: TDataSetNotifyEvent
  read FOnNewRecord write FOnNewRecord;
 property OnPostError: TDataSetErrorEvent
  read FOnPostError write FOnPostError;
 
  // support utilities
 function Translate(Src, Dest: PChar;
 ToOem: Boolean): Integer; virtual;
 property Designer: TDataSetDesigner read FDesigner;
 property BlockReadSize: Integer read FBlockReadSize write SetBlockReadSize;
 property SparseArrays: Boolean read FSparseArrays write SetSparseArrays;
end;

The Status of a Dataset

When you operate on a dataset in Delphi, you can work in different states. These states are indicated by a specific State property, which can assume several different values:

dsBrowse  Indicates that the dataset is in normal browse mode; used to look at the data and scan the records.

dsEdit  Indicates that the dataset is in edit mode. A dataset enters this state when the program calls the Edit method or the DataSource has the AutoEdit property set to True, and the user begins editing a data-aware control, such as a DBGrid or DBEdit. When the changed record is posted, the dataset exits the dsEdit state.

dsInsert  Indicates that a new record is being added to the dataset. This might happen when calling the Insert or Append methods, moving to the last line of a DBGrid, or using the corresponding command of the DBNavigator component.

dsInactive  Indicates a closed dataset.

dsCalcFields  Indicates that a field calculation is taking place (during a call to an OnCalcFields event handler).

dsNewValue, dsOldValue, and dsCurValue  Indicate that an update of the cache is in progress.

dsFilter  Indicates that a dataset is setting a filter (during a call to an OnFilterRecord event handler).

In simple examples, the transitions between these states are handled automatically, but it is important to understand them because many events refer to the state transitions. For example, every dataset fires events before and after any state change. When a program requests an Edit operation, the component fires the BeforeEdit event just before entering edit mode (an operation you can stop by raising an exception). Immediately after entering edit mode, the dataset receives the AfterEdit event. After the user has finished editing and requests to store the data by executing the Post command, the dataset fires a BeforePost event (which can be used to check the input before sending the data to the database); it fires an AfterPost event after the operation has been successfully completed.

Another more general state-change tracking technique involves handling the DataSource component's OnStateChange event. As an example, you can show the current status with code like this:

procedure TForm1.DataSource1StateChange(Sender: TObject);
var
 strStatus: string;
begin
 case cds.State of
 dsBrowse: strStatus := 'Browse';
 dsEdit: strStatus := 'Edit';
 dsInsert: strStatus := 'Insert';
 else
 strStatus := 'Other state';
 end;
 StatusBar.Panels[0].Text := strStatus;
end;

The code considers only the three most common states of a dataset component, ignoring the inactive state and other special cases.

The Fields of a Dataset

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[0].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:

strName := Cds.FieldValues ['LastName'];
strName := Cds ['LastName'];

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

Note 

Although the Fields Editor is similar to the editors of the collections used by Delphi, fields are not part of a collection. They are components created at design time, listed in the published section of the form class and available in the drop-down combo box at the top of the Object Inspector.

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.

Figure 13.5:  The Fields Editor with the Add Fields dialog box

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

Note 

A TField component has both a Name property and a FieldName property. The Name property is the usual component name. The FieldName property is either the name of the column in the database table or the name you define for the calculated field. It can be more descriptive than the Name, and it allows blank spaces. The FieldName property of the TField component is copied to the DisplayLabel property by default. You can change the field name to any suitable text. It is used, among other things, to search a field in the TDataSet class's FieldByName method and when using the array notation.

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.

Tip 

You can also drag the fields from the editor to the form to let the IDE create visual components for you. This is a handy feature that can save you a lot of time when you're creating database-related forms.

Using Field Objects

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:

procedure TForm2.SpeedButton2Click(Sender: TObject);
begin
 ShowMessage (string (cds ['Name']) +': '+ string (cds ['Population']));
end;

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.

Figure 13.6: The output of the FieldAcc example after the Center and Format buttons have been clicked

A Hierarchy of Field Classes

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.

Table 13.2: The Subclasses of TField

Subclass	Base Class	Definition
TADTField	TObjectField	An ADT (Abstract Data Type) field, corresponding to an object field in an object relational database.
TAggregateField	TField	Represents a maintained aggregate. It is used in the ClientDataSet component and is discussed in Chapter 14.
TArrayField	TObjectField	An array of objects in an object relational database.
TAutoIncField	TIntegerField	A whole positive number connected with a Paradox table's auto-increment field (a special field automatically assigned a different value for each record). Note that Paradox AutoInc fields do not always work perfectly, as discussed in Chapter 14.
TBCDField	TNumericField	Real numbers with a fixed number of digits after the decimal point.
TBinaryField	TField	Generally not used directly. This is the base class of the next two classes.
TBlobField	TField	Binary data with no size limit (BLOB stands for binary large object). The theoretical maximum limit is 2 GB.
TBooleanField	TField	A Boolean value.
TBytesField	TBinaryField	Arbitrary data with a large (up to 64 KB characters) but fixed size.
TCurrencyField	TFloatField	Currency values with the same range as the Real data type.
TDataSetField	TObjectField	An object corresponding to a separate table in an object relational database.
TDateField	TDateTimeField	A date value.
TDateTimeField	TField	A date and time value.
TFloatField	TNumericField	Floating-point numbers (8 byte).
TFMTBCDField	TNumericField	(New field type in Delphi 6.) A true binary-coded decimal (BCD), as opposed to the existing TBCDField type, which converted BCD values to the Currency type. This field type is used automatically only by dbExpress datasets.
TGraphicField	TBlobField	A graphic of arbitrary length.
TGuidField	TStringField	A field representing a COM Globally Unique Identifier (part of the ADO support).
TIDispatchField	TInterfaceField	A field representing pointers to IDispatch COM interfaces (part of the ADO support).
TIntegerField	TNumericField	Whole numbers in the range of long integers (32 bits).
TInterfacedField	TField	Generally not used directly. This is the base class of fields that contain pointers to interfaces (IUnknown) as data.
TLargeIntField	TIntegerField	Very large integers (64 bit).
TMemoField	TBlobField	Text of arbitrary length.
TNumericField	TField	Generally not used directly. This is the base class of all the numeric field classes.
TObjectField	TField	Generally not used directly. This is the base class for fields providing support for object relational databases.
TReferenceField	TObjectField	A pointer to an object in an object relational database.
TSmallIntField	TIntegerField	Whole numbers in the range of integers (16 bits).
TSQLTimeStampField	TField	(New field type in Delphi 6.) Supports the date/time representation used in dbExpress drivers.
TStringField	TField	Text data of a fixed length (up to 8192 bytes).
TTimeField	TDateTimeField	A time value.
TVarBytesField	TBytesField	Arbitrary data; up to 64 KB of characters. Very similar to the TBytesField base class.
TVariantField	TField	A field representing a variant data type (part of the ADO support).
TWideStringField	TStringField	A field representing a Unicode (16 bits per character) string.
TWordField	TIntegerField	Whole positive numbers in the range of words or unsigned integers (16 bits).

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.

Adding a Calculated Field

Now that you've been introduced to TField objects and have seen an example of their run time use,
I will build an example based on the declaration of field objects at design time using the Fields Editor and then add a calculated field. In the country.cds sample dataset, both the population and the area of each country are available; you can use this data to compute the population density.

To build the new example, named Calc, follow these steps:

1. Add a ClientDataSet component to a form.
2. Open the Fields Editor. In this editor, right-click, choose the Add Field command, and select some of the fields. (I included them all.)
3. Select the New Field command and enter a proper name and data type (Float, for a TFloatField) for the new calculated field, as you can see in Figure 13.7.

   
   Figure 13.7: The definition of a calculated field in the Calc example

Warning 

Obviously, because you create some field components at design time using the Fields Editor, the fields you skip won't get a corresponding object. As I already mentioned, the fields you skip will not be available even at run time, with Fields or FieldByName. When a program opens a table at run time, if there are no design-time field components, Delphi creates field objects corresponding to the table definition. If there are some design-time fields, however, Delphi uses those fields without adding any extra field objects.

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;

Note 

In general, calculated fields are computed for each record and recalculated each time the record is loaded in an internal buffer, invoking the OnCalcFields event over and over. For this reason, a handler of this event should be extremely fast to execute and cannot alter the status of the dataset by accessing different records. A more time-efficient (but less memory-efficient) version of a calculated field is provided by the ClientDataSet component with internally calculated fields: These fields are evaluated only once—when they are loaded—and the result is stored in memory for future requests.

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.

Note 

The display format I just mentioned (and used in the previous example) uses the Windows International Settings to format the output. When Delphi translates the numeric value of this field to text, the comma in the format string is replaced by the proper ThousandSeparator character. For this reason, the output of the program will automatically adapt itself to different international settings. On computers that have the Italian configuration, for example, the comma is replaced by a period.

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.

Figure 13.8: The output of the Calc example. Notice the Population Density calculated column and the ellipsis button displayed when you edit it.

Lookup Fields

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.

A Data Module for Data-Access Components

To build a Delphi database application, you can place data-access components and the data-aware controls in a form. This approach is handy for a simple program, but having the user interface and the data access and data model in a single (often large) unit is far from a good idea. For this reason, Delphi implements the idea of a data module: a container of non-visual components.

At design time, a data module is similar to a form, but at run time it exists only in memory. The TDataModule class derives directly from TComponent, so it is unrelated to the Windows concept of a window (and is fully portable among different operating systems). Unlike a form, a data module has just a few properties and events. So, it's useful to think of data modules as components and method containers.

Like a form or a frame, a data module has a designer. Delphi creates a specific unit for the definition of the data module's class and a form definition file that lists its components and their properties.

There are several reasons to use data modules. The simplest is that they let you share data-access components among multiple forms, as I'll demonstrate at the beginning of Chapter 14. This technique works in conjunction with visual form linking—the ability to access components of another form or data module at design time (with the File ® Use Unit command). The second reason is that data modules separate the data from the user interface, improving the structure of an application. Data modules in Delphi even exist in versions specific for multitier applications (remote data modules) and server-side HTTP applications (web data modules).

Handling Null Values with Field Events

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 := ''
 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.

Warning 

The handling of null values in Delphi 6 and 7 can be affected by changes in the way null variants work. As discussed in Chapter 3, "The Run Time Library," in the section "The Variants and VarUtils Units," comparing a field with a null value with another field will have a different effect in the latest versions of Delphi than in the past. As discussed in that section, in Delphi 7 you can use global variables to fine-tune the effect of comparisons involving variants.

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.

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.

Customizing a Database Grid

Unlike most other data-aware controls, which have few properties to tune, the DBGrid control has many options and is more powerful than you might think. The following sections explore some of the advanced operations you can do using a DBGrid control. The first example shows how to draw in a grid, and the second shows how to use the grid's multiple-selection feature.

Painting a DBGrid

There are many reasons you might want to customize the output of a grid. A good example is to highlight specific fields or records. Another is to provide output for fields that usually don't show up in the grid, such as BLOB, graphic, and memo fields.

To thoroughly customize the drawing of a DBGrid control, you must set its DefaultDrawing property to False and handle its OnDrawColumnCell event. If you leave the value of DefaultDrawing set to True, the grid will display the default output before the method is called. In that case, all you can do is add something to the default output of the grid (unless you decide to draw over it, which will take extra time and cause flickering).

The alternative approach is to call the grid's DefaultDrawColumnCell method, perhaps after changing the current font or restricting the output rectangle. In this last case, you can provide an extra drawing in a cell and let the grid fill the remaining area with the standard output. This is what I did in the DrawData program.

The DBGrid control in this example, which is connected to the Borland's classic Biolife table, has the following properties:

object DBGrid1: TDBGrid
 Align = alClient
 DataSource = DataSource1
 DefaultDrawing = False
 OnDrawColumnCell = DBGrid1DrawColumnCell
end

The OnDrawColumnCell event handler is called once for each grid cell and has several parameters, including the rectangle corresponding to the cell, the index of the column you have to draw, the column itself (with the field, its alignment, and other subproperties), and the status of the cell. To set the color of specific cells to red, you change it in the special cases:

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject;
 const Rect: TRect; DataCol: Integer; Column: TColumn;
 State: TGridDrawState);
begin
 // red font color if length > 100
 if (Column.Field = cdsLengthcm) and (cdsLengthcm.AsInteger > 100) then
 DBGrid1.Canvas.Font.Color := clRed;
 
 // default drawing
 DBGrid1.DefaultDrawDataCell (Rect, Column.Field, State);
end;

The next step is to draw the memo and the graphics fields. For the memo, you can implement the memo field's OnGetText and OnSetText events. The grid will even allow editing on a memo field if its OnSetText event is not nil. Here is the code for the two event handlers. I used Trim to remove trailing nonprinting characters that make the text appear to be empty when editing:

procedure TForm1.cdsNotesGetText(Sender: TField;
 var Text: String; DisplayText: Boolean);
begin
 Text := Trim (Sender.AsString);
end;
 
procedure TForm1.cdsNotesSetText(Sender: TField; const Text: String);
begin
 Sender.AsString := Text;
end;

For the image, the simplest approach is to create a temporary TBitmap object, assign the graphics field to it, and paint the bitmap to the grid's Canvas. As an alternative, I removed the graphics field from the grid by setting its Visible property to False and added the image to the fish name, with the following extra code in the OnDrawColumnCell event handler:

var
 Picture: TPicture;
 OutRect: TRect;
 PictWidth: Integer;
begin
 // default output rectangle
 OutRect := Rect;
 
 if Column.Field = cdsCommon_Name then
 begin
 // draw the image
 Picture := TPicture.Create;
 try
 Picture.Assign(cdsGraphic);
 PictWidth := (Rect.Bottom - Rect.Top) * 2;
 OutRect.Right := Rect.Left + PictWidth;
 DBGrid1.Canvas.StretchDraw (OutRect, Picture.Graphic);
 finally
 Picture.Free;
 end;
 // reset output rectangle, leaving space for the graphic
 OutRect := Rect;
 OutRect.Left := OutRect.Left + PictWidth;
 end;
 
 // red font color if length > 100 (omitted — see above)
 
 // default drawing
 DBGrid1.DefaultDrawDataCell (OutRect, Column.Field, State);

As you can see in this code, the program shows the image in a small rectangle on the left of the grid cell and then changes the output rectangle to the remaining area before activating the default drawing. You can see the effect in Figure 13.12.

Figure 13.12: The DrawData program displays a grid that includes the text of a memo field and the ubiqui-tous Borland fish.

A Grid Allowing Multiple Selection

The second example of customizing the DBGrid control relates to multiple selection. You can set up the DBGrid so that a user can select multiple rows (that is, multiple records). Doing so is easy, because all you have to do is toggle the dgMultiSelect element of the grid's Options property. Once you select this option, a user can keep the Ctrl key pressed and click with the mouse to select multiple grid rows, with the effect shown in Figure 13.13.

Figure 13.13: The MltGrid example has a DBGrid control that allows the selection of multiple rows.

Because the database table can have only one active record, the grid keeps a list of bookmarks to the selected records. This list is available in the SelectedRows property, which is of type TBookmarkList. Besides accessing the number of objects in the list with the Count property, you can get to each bookmark with the Items property, which is the default array property. Each list item is of a TBookmarkStr type, which represents a bookmark pointer you can assign to the table's Bookmark property.

Note 

TBookmarkStr is a string type for convenience, but its data should be considered opaque and volatile. You shouldn't rely on any particular structure to the data you find if you peek at a bookmark's value, and you shouldn't hold on to the data too long or store it in a separate file. Bookmark data will vary with the database driver and index configuration, and it may be rendered unusable when rows are added to or deleted from the dataset (by you or by other users of the database).

To summarize the steps, here is the code for the MltGrid example, which is activated by clicking the button to move the Name field of the selected records to the list box:

procedure TForm1.Button1Click(Sender: TObject);
var
 I: Integer;
 BookmarkList: TBookmarkList;
 Bookmark: TBookmarkStr;
begin
 // store the current position
 Bookmark := cds.Bookmark;
 try
 // empty the list box
 ListBox1.Items.Clear;
 // get the selected rows of the grid
 BookmarkList := DbGrid1.SelectedRows;
 for I := 0 to BookmarkList.Count - 1 do
 begin
 // for each, move the table to that record
 cds.Bookmark := BookmarkList[I];
 // add the name field to the listbox
 ListBox1.Items.Add (cds.FieldByName ('Name').AsString);
 end;
 finally
 // go back to the initial record
 cds.Bookmark := Bookmark;
 end;
end;

Dragging to a Grid

Another interesting technique is to use dragging with grids. Dragging from a grid is not difficult, because you know which current record and column the user has selected. Dragging to a grid, however, is tricky to program. Recall that in Chapter 3 I mentioned the "protected hack"; I'll use this technique to implement dragging to a grid.

The example, called DragToGrid, has a grid connected to the country dataset, an edit box in which you can type the new value for a field, and a label you can drag over a grid cell to modify the related field. The problem is how to determine this field. The code is only a few lines, as you can see here, but it is cryptic and requires some explanation:

type
 TDBGHack = class (TDbGrid)
 end;
 
procedure TFormDrag.DBGrid1DragDrop(Sender, Source: TObject; X, Y: Integer);
var
 gc: TGridCoord;
begin
 gc := TDBGHack (DbGrid1).MouseCoord (x, y);
 if (gc.y > 0) and (gc.x > 0) then
 begin
 DbGrid1.DataSource.DataSet.MoveBy (gc.y - TDBGHack(DbGrid1).Row);
 DbGrid1.DataSource.DataSet.Edit;
 DBGrid1.Columns.Items [gc.X - 1].Field.AsString := EditDrag.Text;
 end;
 DBGrid1.SetFocus;
end;

The first operation determines the cell over which the mouse was released. Starting with the x and y mouse coordinates, you can call the protected MouseCoord method to access the row and column of the cell. Unless the drag target is the first row (usually hosting the titles) or the first column (usually hosting the indicator), the program moves the current record by the difference between the requested row (gc.y) and the current active row (the grid's protected Row property). The next step puts the dataset into edit mode, grabs the field of the target column (Columns.Items [gc.X - 1].Field), and changes its text.

Database Applications with Standard Controls

Although it is generally faster to write Delphi applications based on data-aware controls, this approach is not required. When you need precise control over the user interface of a database application, you might want to customize the transfer of the data from the field objects to the visual controls. My view is that doing so is necessary only in specific cases, because you can customize the data-aware controls extensively by setting the properties and handling the events of the field objects. However, trying to work without the data-aware controls should help you better understand Delphi's default behavior.

The development of an application not based on data-aware controls can follow two different approaches: You can mimic the standard Delphi behavior in code, possibly departing from it in specific cases, or you can go for a more customized approach. I'll demonstrate the first technique in the NonAware example and the latter in the SendToDb example.

Mimicking Delphi Data Aware Controls

To build an application that doesn't use data-aware controls but behaves like a standard Delphi application, you can write event handlers for the operations that would be performed automatically by data-aware controls. Basically, you need to place the dataset in edit mode as the user changes the content of the visual controls and update the field objects of the dataset as the user exits from the controls, moving the focus to another element.

Tip 

This approach can be handy for integrating a control that's not data aware into a standard application.

The other element of the NonAware example is a list of buttons corresponding to some of the buttons in the DBNavigator control; these buttons are connected to five custom actions. I could not use the standard dataset actions for this example because they automatically hook to the data source associated with the control having the focus—a mechanism that fails with the example's non-data-aware edit boxes. In general, you could also hook a data Source with each of the actions' DataSource property, but in this specific case we don't have a data source in the example.

The program has several event handlers I haven't used for past applications using data-aware controls. First, you have to show the current record's data in the visual controls (as in Figure 13.14) by handling the OnAfterScroll event of the dataset component:

procedure TForm1.cdsAfterScroll(DataSet: TDataSet);
begin
 EditName.Text := cdsName.AsString;
 EditCapital.Text := cdsCapital.AsString;
 ComboContinent.Text := cdsContinent.AsString;
 EditArea.Text := cdsArea.AsString;
 EditPopulation.Text := cdsPopulation.AsString;
end;

Figure 13.14:  The output of the NonAware example in Browse mode.The program manu-ally fetches the data every time the current record changes.

The control's OnStateChange event handler displays the table's status in a status bar control. As the user begins typing in one of the edit boxes or drops down the combo box list, the program sets the table to edit mode:

procedure TForm1.EditKeyPress(Sender: TObject; var Key: Char);
begin
 if not (cds.State in [dsEdit, dsInsert]) then
 cds.Edit;
end;

This method is connected to the OnKeyPress event of the five components and is similar to the OnDropDown event handler of the combo box. As the user leaves one of the visual controls, the OnExit event handler copies the data to the corresponding field, as in this case:

procedure TForm1.EditCapitalExit(Sender: TObject);
begin
 if (cds.State in [dsEdit, dsInsert]) then
 cdsCapital.AsString := EditCapital.Text;
end;

The operation takes place only if the table is in edit mode—that is, only if the user has typed in this or another control. This behavior is not ideal, because extra operations are done even if the edit box's text didn't change; however, the extra steps happen fast enough that they aren't a concern. For the first edit box, you check the text before copying it, raising an exception if the edit box is empty:

procedure TForm1.EditNameExit(Sender: TObject);
begin
 if (cds.State in [dsEdit, dsInsert]) then
 if EditName.Text <> '' then
 cdsName.AsString := EditName.Text
 else
 begin
 EditName.SetFocus;
 raise Exception.Create ('Undefined Country');
 end;
end;

An alternative approach for testing the value of a field is to handle the dataset's BeforePost event. Keep in mind that in this example, the posting operation is not handled by a specific button but takes place as soon as a user moves to a new record or inserts a new one:

procedure TForm1.cdsBeforePost(DataSet: TDataSet);
begin
 if cdsArea.Value < 100 then
 raise Exception.Create ('Area too small');
end;

In each case, an alternative to raising an exception is to set a default value. However, if a field has a default value, it is better to set it up front, so a user can see which value will be sent to the database. To accomplish this, you can handle the dataset's AfterInsert event, which is fired immediately after a new record has been created (I could have used the OnNewRecord event, as well):

procedure TForm1.cdsAfterInsert(DataSet: TDataSet);
begin
 cdsContinent.Value := 'Asia';
end;

Sending Requests to the Database

You can further customize your application's user interface if you decide not to handle the same sequence of editing operations as in standard Delphi data-aware controls. This approach allows you complete freedom, although it might cause some side effects (such as limited ability to handle concurrency, which I'll discuss in Chapter 14).

For this new example, I replaced the first edit box with another combo box and replaced all the buttons related to table operations (which corresponded to DBNavigator buttons) with two custom buttons that get the data from the database and send an update to it. Again, this example has no DataSource component.

The GetData method, connected to the corresponding button, gets the fields corresponding to the record indicated in the first combo box:

procedure TForm1.GetData;
begin
 cds.Locate ('Name', ComboName.Text, [loCaseInsensitive]);
 ComboName.Text := cdsName.AsString;
 EditCapital.Text := cdsCapital.AsString;
 ComboContinent.Text := cdsContinent.AsString;
 EditArea.Text := cdsArea.AsString;
 EditPopulation.Text := cdsPopulation.AsString;
end;

This method is called whenever the user clicks the button, selects an item in the combo box, or presses the Enter key while in the combo box:

procedure TForm1.ComboNameClick(Sender: TObject);
begin
 GetData;
end;
 
procedure TForm1.ComboNameKeyPress(Sender: TObject; var Key: Char);
begin
 if Key = #13 then
 GetData;
end;

To make this example work smoothly, at startup the combo box is filled with the names of all the countries in the table:

procedure TForm1.FormCreate(Sender: TObject);
begin
 // fill the list of names
 cds.Open;
 while not cds.Eof do
 begin
 ComboName.Items.Add (cdsName.AsString);
 cds.Next;
 end;
end;

With this approach, the combo box becomes a sort of selector for the record, as you can see in Figure 13.15. Thanks to this selection, the program doesn't need navigational buttons.

Figure 13.15:  In the SendToDb example, you can use a combo box to select the record you want to see.

The user can also change the values of the controls and click the Send button. The code to be executed depends on whether the operation is an update or an insert. You can determine this by looking at the name (although with this code, a wrong name can no longer be modified):

procedure TForm1.SendData;
begin
  // raise an exception if there is no name
 if ComboName.Text = '' then
 raise Exception.Create ('Insert the name');
 
  // check if the record is already in the table
 if cds.Locate ('Name', ComboName.Text, [loCaseInsensitive]) then
 begin
    // modify found record
 cds.Edit;
 cdsCapital.AsString := EditCapital.Text;
 cdsContinent.AsString := ComboContinent.Text;
 cdsArea.AsString := EditArea.Text;
 cdsPopulation.AsString := EditPopulation.Text;
 cds.Post;
 end
 else
 begin
    // insert new record
 cds.InsertRecord ([ComboName.Text, EditCapital.Text,
 ComboContinent.Text, EditArea.Text, EditPopulation.Text]);
    // add to list
 ComboName.Items.Add (ComboName.Text)
 end;

Before sending the data to the table, you can do any sort of validation test on the values. In this case, it doesn't make sense to handle the events of the database components, because you have full control over when the update or insert operation is performed.

Grouping and Aggregates

You've already seen that a ClientDataSet can have an index different from the order in which the data is stored in the file. Once you define an index, you can group the data by that index. In practice, a group is defined as a list of consecutive records (according to the index) for which the value of the indexed field doesn't change. For example, if you have an index by state, all the addresses within that state will fall in the group.

Grouping

The CdsCalcs example has a ClientDataSet component that extracts its data from the familiar Country dataset. The group is obtained, along with the definition of an index, by specifying a grouping level for the index:

object ClientDataSet1: TClientDataSet
 IndexDefs = <
 item
 Name = 'ClientDataSet1Index1'
 Fields = 'Continent'
 GroupingLevel = 1
 end>
 IndexName = 'ClientDataSet1Index1'

When a group is active, you can make it obvious to the user by displaying the grouping structure in the DBGrid, as shown in Figure 13.16. All you have to do is handle the OnGetText event for the grouped field (the Continent field in the example) and show the text only if the record is the first in the group:

procedure TForm1.ClientDataSet1ContinentGetText(Sender: TField;
 var Text: String; DisplayText: Boolean);
begin
 if gbFirst in ClientDataSet1.GetGroupState (1) then
 Text := Sender.AsString
 else
 Text := '';
end;

Figure 13.16:  The CdsCalcs example demon-strates that by writing a little code, you can have the DBGrid control visually show the grouping defined in the ClientDataSet.

Defining Aggregates

Another feature of the ClientDataSet component is support for aggregates. An aggregate is a calculated value based on multiple records, such as the sum or average value of a field for the entire table or a group of records (defined with the grouping logic I just discussed). Aggregates are maintained; that is, they are recalculated immediately if one of the records changes. For example, the total of an invoice can be maintained automatically while the user types in the invoice items.

Note 

Aggregates are maintained incrementally, not by recalculating all the values every time one value changes. Aggregate updates take advantage of the deltas tracked by the ClientDataSet. For example, to update a sum when a field is changed, the ClientDataSet subtracts the old value from the aggregate and adds the new value. Only two calculations are needed, even if there are thousands of rows in that aggregate group. For this reason, aggregate updates are instantaneous.

There are two ways to define aggregates. You can use the Aggregates property of the ClientDataSet, which is a collection; or you can define aggregate fields using the Fields Editor. In both cases, you define the aggregate expression, give it a name, and connect it to an index and a grouping level (unless you want to apply it to the entire table). Here is the Aggregates collection of the CdsCalcs example:

object ClientDataSet1: TClientDataSet
 Aggregates = <
 item
 Active = True
 AggregateName = 'Count'
 Expression = 'COUNT (NAME)'
 GroupingLevel = 1
 IndexName = 'ClientDataSet1Index1'
 Visible = False
    end
    item
 Active = True
 AggregateName = 'TotalPopulation'
 Expression = 'SUM (POPULATION)'
 Visible = False
 end>
 AggregatesActive = True

Notice in the last line of the previous code snippet that you must activate the support for aggregates, in addition to activating each specific aggregate you want to use. Disabling aggregates is important, because having too many of them can slow down a program.

The alternative approach, as I mentioned, is to use the Fields Editor, select the New Field command from its shortcut menu, and choose the Aggregate option (available, along with the InternalCalc option, only in a ClientDataSet). This is the definition of an aggregate field:

object ClientDataSet1: TClientDataSet
 object ClientDataSet1TotalArea: TAggregateField
 FieldName = 'TotalArea'
 ReadOnly = True
 Visible = True
 Active = True
 DisplayFormat = '###,###,###'
 Expression = 'SUM(AREA)'
 GroupingLevel = 1
 IndexName = 'ClientDataSet1Index1'
 end

The aggregate fields are displayed in the Fields Editor in a separate group, as you can see in Figure 13.17. The advantage of using an aggregate field, compared to a plain aggregate, is that you can define the display format and hook the field directly to a data-aware control, such as a DBEdit in the CdsCalcs example. Because the aggregate is connected to a group, as soon as you select a record from a different group, the output is automatically updated. Also, if you change the data, the total immediately shows the new value.

Figure 13.17: The bottom portion of a ClientDataSet's Fields Editor displays aggregate fields.

To use plain aggregates, you have to write a little code, as in the following example (notice that the Value of the aggregate is a variant):

procedure TForm1.Button1Click(Sender: TObject);
begin
 Label1.Caption :=
 'Area: ' + ClientDataSet1TotalArea.DisplayText + #13'Population : '
 + FormatFloat ('###,###,###', ClientDataSet1.Aggregates [1].Value) +
 #13'Number : ' + IntToStr (ClientDataSet1.Aggregates [0].Value);
end;

Master Detail Structures

Often, you need to relate tables that have a one-to-many relationship. This means that for a single record in the master table, there are many detailed records in a secondary table. A classic example is an invoice and the items of the invoice; another is a list of customers and the orders each customer has placed.

These are common situations in database programming, and Delphi provides explicit support with the master/detail structure. The TDataSet class has a DataSource property for setting up a master data source. This property is used in a detail dataset to hook to the current record of the master dataset, in combination with the MasterFields property.

Master Detail with ClientDataSets

The MastDet example uses the customer and orders sample datasets. I added a data source component for each dataset, and for the secondary dataset I assigned the DataSource property to the data source connected to the first dataset. Finally, I related the secondary table to a field of the main table, using the MasterFields property's special editor. I did all this using a data module, as discussed in the earlier sidebar "A Data Module for Data-Access Components."

The following is the complete listing (but without the irrelevant positional properties) of the data module used by the MastDet program:

object DataModule1: TDataModule1
 OnCreate = DataModule1Create
 object dsCust: TDataSource
 DataSet = cdsCustomers
  end
  object dsOrd: TDataSource
 DataSet = cdsOrders
  end
  object cdsOrders: TClientDataSet
 FileName = 'orders.cds'
 IndexFieldNames = 'CustNo'
 MasterFields = 'CustNo'
 MasterSource = dsCust
  end
  object cdsCustomers: TClientDataSet
 FileName = 'customer.cds'
  end
end

In Figure 13.18, you can see an example of the MastDet program's main form at run time. I placed data-aware controls related to the master table in the upper portion of the form, and I placed a grid connected with the detail table in the lower portion. This way, for every master record, you immediately see the list of connected detail records—in this case, all orders placed by the current client. Each time you select a new customer, the grid below the master record displays only the orders pertaining to that customer.

Figure 13.18:  The MastDet example at run time

Handling Database Errors

Another important element of database programming is handling database errors in custom ways. Of course, you can let Delphi show an exception message each time a database error occurs, but you might want to try to correct the errors or show more details. You can use three approaches to handle database-related errors:

• Wrap a try/except block around risky database operations. This is not possible when the operation is generated by interaction with a data-aware control.
• Install a handler for the OnException event of the global Application object.
• Handle specific dataset events related to errors, such as OnPostError, OnEditError, OnDeleteError, and OnUpdateError.

Although most of the exception classes in Delphi deliver an error message, database exceptions often include error codes, native SQL server error codes and messages, and the like. The ClientDataSet adds only an error code to its exception class, EDBClient. Showing how to handle it, as I'll do next, will provide you with a guideline for other cases.

As an example, I built a database program that shows the details of the errors in a memo component (errors are automatically generated when the user clicks the program buttons). To handle all the errors, the DBError example installs a handler for the OnException event of the Application global object. The event handler logs some information in a memo showing the details of the database error if it is an EDBClient:

procedure TForm1.ApplicationError (Sender: TObject; E: Exception);
begin
 if E is EDBClient then
  begin
 Memo1.Lines.Add('Error: ' + (E.Message));
 Memo1.Lines.Add(' Error Code: ' +
 IntToStr(EDBClient (E).ErrorCode));
  end
  else
 Memo1.Lines.Add('Generic Error: ' + (E.Message));
end;

What s Next?

In this chapter, you saw examples of database access from Delphi programs. I covered the basic data-aware components, as well as the development of database applications based on standard controls. I explored the internal architecture of the TDataSet class and of field objects and discussed many events and properties shared by all datasets and used by all database applications. Even though most of the examples used the ClientDataSet component accessing local data, the component is often also the gateway for data in a SQL server (in a client/server architecture) or in a remote application (in a three-tier architecture). I discussed calculated fields, lookup fields, customizations of the DBGrid control, and some advanced techniques.

I haven't delved into the database and data-access side of the picture, which depends on the type of database engine and server you're using. Chapter 14 will focus on this topic, with an in-depth overview of client/server development using the dbExpress library provided by Borland. I'll also cover InterBase and the IBX component, giving some elements of a real-world application.

Following chapters will continue to explore the database side of Delphi programming, discussing ADO connectivity and components, Borland's three-tier architecture (DataSnap, formerly MIDAS), the development of data-aware controls and custom dataset components, and reporting technologies.