Shifting TFields in DataSets Bound to DBGrids

I've been working with Delphi since day 1, with particular emphasis on database development. As a result, it's not often that I encounter a fundamental behavior of data-related components that catches me off guard. Well, it happened last week. And what I observed may be the source of a bug in a large number of Delphi database applications.

Here is what I observed: The Fields in an open DataSet changed order at runtime. Specifically, a Field that was originally in the zeroeth position (DataSet.Fields[0]) at the time that I opened the Dataset, was in different position in the Fields array a short time later. I discovered this behavior when an exception was raised as a result of my attempting to programmatically read the integer value of the Field that I created in the first (zeroeth) position in my DataSet. Between the time I created the DataSet, and the execution of my code, the integer field had moved.

What happened wasn't magic. The Fields didn't change position by themselves, nor did they change based on anything I did in my code. What caused the Fields to physically change position in the DataSet was that the user had changed the order of the Columns in a DBGrid to which a ClientDataSet was attached (through a DataSource component, of course). The user's ability to change the position of the Columns in a DBGrid, by the way, is the default behavior of a DBGrid.

Besides being interesting (I kind of assumed that once a DataSet was opened, the position of the Fields in the Fields array was pretty much set), this behavior is the potential source of intermittent exceptions, the type that are particularly difficult to track down. It turns out that this behavior, which I've never seen described before, has been around since Delphi 1. (Actually, I discovered this effect in Delphi 7, Delphi 2007, and Delphi 2010. However, I understand that the underlying source of this behavior has been around since Delphi 1, though I have not confirmed this.)

I created a very simple Delphi application that demonstrates this effect. It consists of a single form with one DBGrid, a DataSource, a ClientDataSet, and a Button. The ClientDataSet is bound to the DBGrid through the DataSource. The OnCreate event handler of this form looks like the following:

Listing 1: FormCreate

Button1, which is labeled Show ClientDataSet Structure, contains the following OnClick event handler.

Listing 2: ClientDataSet Structure

To demonstrate the moving field effect, run this application and click the button labeled Show ClientDataSet Structure. You should see something like that shown in Figure 1.

Figure 1: First Structure Order

Next, drag the Columns of the DBGrid to re-arrange the display order of the fields. Click the Show ClientDataSet Structure button once again. This time you will see something similar to that shown in Figure 2.

 
Figure 2: Second Structure Order

What is remarkable about this example is that there is an apparent effect on the position of the Fields in the ClientDataSet, such that the field that was in the ClientDataSet.Field[0] position at one point is not necessarily there moments later. And, unfortunately, this is not distinctly a ClientDataSet issue. I performed the same test with BDE-based TTables and ADO-based ADOTables and got the same effect.

It turns out that this behavior has three contributing factors. These are

• A DBGrid connects to a DataSet through a DataSource
• The DBGrid permits the user to move columns at runtime
• The Columns of the DBGrid are dynamic, meaning that they are created by the DBGrid at runtime

If you programmatically refer to the Fields of a DataSet connected to a DBGrid in which the preceding three conditions exist using a literal indexer, your application can raise an exception, or produce inaccurate results, if the user moves one or more of the Columns in that DBGrid. In the following section I will consider several solutions to this problem.

There Are Several Solutions

There are two relatively simply steps you can take to eliminate this bug. Either you can create persistent Columns in your DataSet, or you can prevent the user from moving the DBGrid's Columns.

Creating persistent columns can be done it design time or runtime. To do it at design time, add the Columns using the Columns Editor. Display the Columns Editor by right-clicking the DBGrid and selecting Columns Editor or by clicking the ellipsis in the Columns property of the DBGrid in Object Inspector. If your DataSet is Active, you can click the Add All Fields button in the Columns Editor toolbar. Otherwise, add one or more Columns and set their FieldName property in the Property Editor.

To create persistent Columns at runtime, use the Add or Create methods of the DBGrid's Columns property. You can then set specific properties of the added or created Columns.

You prevent a user from moving the Columns of a DBGrid by removing the dgResizeColumn flag from the Options property of the DBGrid. While this approach is effective, it eliminates a potentially valuable user interface option. Furthermore, removing this flag not only restricts column reordering, it prevents column resizing. (To learn how to limit column reordering without removing the column resizing option, see http://delphi.about.com/od/adptips2005/a/bltip0105_2.htm.)

The second workaround is to avoid referring to a DataSet's Fields based on a literal indexer to the Fields array (since this is the essence of the problem). In order words, if you need to refer to the Count field in the preceding code sample, don't use ClientDataSet1.Fields[2]. So long as you know the name of the Field, you can use something like ClientDataSet1.FieldByName('Count').

There is one rather big drawback to the use of FieldByName, however. Specifically, this method identifies the field by iterating through the Fields property of the DataSet, looking for a match based on the field name. Since it does this every time you call FieldByName, you should avoid this method in situations where the Field needs to be referenced many times, such as in a loop that navigates a large DataSet.

If you do need to refer to the field repeatedly (and a large number of times), consider using something like the following code snippet:

Listing 3: Using DataSet Fields

If you already have code that uses an indexer for the Fields array, and it works reliably, so long as the user does not move the Columns of the bound DBGrid, there is another solution. Change your code to use the FieldByNumber method of the DataSet's Fields property.

There are two interesting aspects to the use of FieldByNumber. First, you must qualify its reference with the Fields property of your DataSet. Second, unlike the Fields array, which is zero-based, FieldByNumber takes a one-based parameter to indicate the position of the Field you want to reference.

The following is an updated version of the Button1 event handler shown earlier that uses the FieldByNumber method.

Listing 4: Updated DataSet Structure

For the sample project, this code produces the following output, regardless of the orientation of the Columns in the associated DBGrid. This can be seen in Figure 3.

 
Figure 3: Third DataSet Structure Order

There is a third solution, but this is only available when your DataSet is a ClientDataSet, like the one in my sample project. In those situations, you can create a clone of the original ClientDataSet, and it will have the original structure. As a result, whichever Field was created in the zeroeth position will still be in that position, regardless of what a user has done to a DBGrid that displays the ClientDataSet's data.

Note that I am not suggesting that you should reference Fields in a DataSet using integer literals. Personally, the use of a Field variable that gets initialized through a one time call to FieldByName is more readable, and is immune to changes in the physical order of a table's structure (though not immune to changes in the names of your fields!).

Wrap Up

There are two final points I want to make. First, the actual structure of the underlying data is not affected. Specifically, if, after changing the order of the Columns in a DBGrid, you call the SaveToFile method of a ClientDataSet bound to that DBGrid, the saved structure is the original (true internal) structure. Similarly, if you assign the Data property of one ClientDataSet to another, the target ClientDataSet also shows the true structure (which is similar to the effect observed when a source ClientDataSet is cloned).

Similarly, changes to the column orders of DBGrids bound to other tested DataSets, including TTable and ADOTable, do not affect the structure of the underlying tables. For example, a TTable that displays data from the customer.db sample Paradox table that ships with Delphi does not actually change that table's structure on disk (nor would you expect it to).

The second point is that this is not a bug in either the TDataSet or TDBGrid classes. This is how these classes were designed to work. And although this behavior can introduce bugs in your applications, this is because we were not aware of this behavior until now. And, you now know about this behavior, as well as how to prevent it from causing exceptions in your Delphi applications.