In-Memory DataSets: ClientDataSets and .NET DataTables Part 5: Managing the Change Cache

The change cache is the source of much of an in-memory dataset's power. It is through the change cache that you can permit a user to review their changes before committing them, as well as permit you to programmatically work with changes that have been made to the dataset.

Note: In the first article in this series, I called the change cache the change log. A reader responded that what I described was not really a log, since it doesn’t keep track of individual changes, only information about the state of individual records. The term change log originates from the TClientDataSet interface, which includes the LogChanges property and the MergeChangeLog method. I agree that the term change cache more accurately reflects this feature of in-memory datasets, and will use that term from now on.

The following are operations that can be performed on the change cache for any in-memory dataset:

• Detecting changes
• Cancel all changes
• Filter on changes
• Detect field-level changes
• Cancel a single change
• Erase the change cache
• Commit changes in the change cache to the underlying database

The first six of these operations are described in the following sections. Committing changes to an underlying database is a much larger topic, and I will cover it in the next installment of this series.

Detecting Changes

You use the ClientDataSet's ChangeCount property to determine if there have been any changes to the data in the ClientDataSet since if was originally loaded. There are no changes if ChangeCount returns 0 (zero). If there have been one or more changes, ChangeCount returns the total number of records that have been inserted, deleted, or modified (including changes to nested datasets).

Note that ChangeCount only counts changed records. For example, a record that is inserted and then modified counts as only one change.

The following code segment demonstrates the use of ChangeCount.

if ClientDataSet1.State in [dsEdit, dsInsert] then
  ClientDataSet1.Post;
  if ClientDataSet1.ChangeCount > 0 then
    ClientDataSet1.ApplyUpdates(0);

Note that there is a bug in ClientDataSets in the original release with Delphi 6 and 7 with respect to ChangeCount. If you save a ClientDataSet to a file or stream, and then later restore the ClientDataSet, ChangeCount will be zero immediately after you reload the ClientDataSet, even if there are changes in the change cache.

In .NET, you call the DataTable's GetChanges method. If GetChanges returns a nil reference, there are no changes. If there are changes, the DataTable returned by GetChanges contains the inserted, deleted, and modified records.

if DataTable1.GetChanges <> nil then
begin
  //Work with the changes here
  end;

Canceling All Changes

When you cancel all changes, the in-memory dataset reverts to the values it contained when it was originally loaded. You cancel all changes in a ClientDataSet by calling the CancelUpdates method. With a .NET DataTable, you call RejectChanges.

Canceling all changes is an irreversible action.

Filtering On Changes

Both ClientDataSets and .NET DataTables permit you to filter the dataset to display only inserted, deleted, and modified records. With a ClientDataSet, you invoke this operation using the StatusFilter property, which can be set to include the following four flags: usModified, usInserted, usDeleted, and usUnModified.

The following code segment demonstrates the use of StatusFilter:

if ClientDataSet1.State in [dsEdit, dsInsert] then
  ClientDataSet1.Post;
  if ClientDataSet1.ChangeCount > 0 then
  begin
    ClientDataSet1.StatusFilter := [usModified, usInserted, usDeleted];
    //Do something with the changed records
  end;

Just as you must use a DataView to obtain a filtered view of a DataTable, you must use a DataView to filter on Changed records. In this case, you use the RowStateFilter and set it to one of the following eight values of the DataViewRowState enumeration: OriginalRows, CurrentRows, Added, Deleted, ModifiedOriginal, ModifiedCurrent, UnChanged, and None.

The following code demonstrates how to obtain a DataView that contains only the records deleted from a DataTable:

DataView1 := DataView.Create(DataTable1);
  DataView1.RowStateFilter := DataViewRowState.Deleted;
  if DataView1.Count > 0 then
  begin
    // Do something with the deleted records
  end;

Detecting Field-Level Changes

With ClientDataSets, you detect field-level changes by examining the OldValue and NewValue variant properties of your ClientDataSet's TFields. The following code demonstrates how to do this:

if ClientDataSet1.UpdateStatus = usModified then
begin
  if ClientDataSet1.Fields[0].OldValue <> ClientDataSet1.Fields[0].NewValue then
    begin
      //The first field in the current record has been changed
    end;
end;

With .NET DataTables, you must obtain two DataViews for the table, setting the RowStateFilter on one of them to ModifiedOriginal, and the RowStateFilter of the second to ModifiedCurrent. You can them compare the fields of the two DataViews to determine what has changed. This is demonstrated in the following code sample:

DataView1 := DataView.Create(DataTable1);
DataView2 := DataView.Create(DataTable2);
DataView1.RowStateFilter := DataViewRowState.ModifiedOriginal;
DataView2.RowStateFilter := DataViewRowState.ModifiedCurrent;
if DataView1.Item[0].Row[0].ToString <> DataView0.Item[0].Row[0].ToString then
  begin
    //Field 1 of record 1 has been changed.
    //Assumes a string-compatible field
  end;

Canceling a Single Change

ClientDataSets provides you with two mechanisms for canceling a change to a record. You call the UndoLastChange method to revert the record that was last inserted, deleted, or modified to its original state. By comparison, if the ClientDataSet is pointing to a record that was inserted, deleted, or modified, you can call RevertRecord.

The following code demonstrates the use of RevertRecord:

if ClientDataSet1.UpdateStatus in [usInserted, usDeleted, usModified] then
  ClientDataSet1.RevertRecord;

For a .NET DataRow, you call the RejectChanges method to restore an inserted, deleted, or modified DataRow to its original state. The following code demonstrates how to restore all deleted records to a DataTable:

var
  DataView2: DataView;
  i: Integer;
begin
  DataView2 := DataView.Create(DataSet1.Tables[0]);
  DataView2.RowStateFilter := DataViewRowState.Deleted;
  for i := (DataView2.Count -1) downto 0 do
    DataView2.item[i].Row.RejectChanges;

Erasing the Change Cache

Erasing the change cache leaves any changes to the dataset intact, but deletes the record of those changes. Most developers do not want to erase the change cache since it makes it impossible to undo changes and also erases the information necessary to write the changes to the underlying database. About the only time that erasing the change cache makes sense is when you want to make the changes permanent before storing the dataset in a file or stream, and never have to resolve those changes back to some underlying database.

With ClientDataSets, you erase the change cache by calling MergeChangeLog. (Note that you can simply turn off the change cache with a ClientDataSet by setting the ClientDataSet's LogChanges property to a Boolean False before making any changes.)

With .NET DataTables, call AcceptChanges to erase the change cache.

Copyright © 2010 Cary Jensen. All Rights Reserved.

Delphi Developer Days 2010

It’s less than a month away from the first city in the 2010 Delphi Developer Days tour, and I thought this might be a good time to share some history and insight into this event. To begin with, Delphi Developer Days is an intense, two-day Delphi event featuring me and Marco Cantù. Our first city on this tour is Baltimore/Washington DC (May 11-12), continuing on to Chicago (May 14-15), Los Angeles (May 17-18), London, England (May 26-27); and finishing up in Frankfurt, Germany (May 30 – June 1).

Marco and I have known each other for a long time, are good friends, and have been looking for an opportunity to work together. In May of 2008 we talked about the lack of a live Delphi event in the US, and thought that this provided us an opportunity to offer something unique.

We weren’t interested in trying to run a conference similar to the past annual Borland Conferences or DelphiLive!. Instead, we were looking to offer something similar to a traditional training, but with the interaction and networking that a somewhat larger event can provide. As a result, we limit attendance to around 30 attendees in each city. This gives us a chance to interact with each attendee, while maintaining a critical mass for networking. (In addition to our consultant and development services, we’re both seasoned Delphi trainers.)

But there were a number of challenges that we knew we’d have to address in order for the event to be successful. To begin with, Delphi is a mature product. While there are always some newcomers, most Delphi developers have been using it for some time. Furthermore, not everyone is using the latest versions of Delphi. In fact, by some estimates, nearly half of all active Delphi developers are using Delphi 7 or earlier.

Another challenge is that there are a lot of different types of Delphi developers. Many are engaged in traditional client/server database development, while others are building server-side applications that operate over the Internet. How do you offer something that will appeal to such a diverse group of developers?

Honestly, I think the solution we came up with is the right one. To begin with, we embrace the richness of the Delphi community. We include timely topics about the latest versions of Delphi. Even if you are working with an older version, these topics are relevant. Specifically, even if you are not ready to upgrade now, you want to be informed about what the latest versions can do for you and your development efforts. In the long run, unless your applications are all at the end of their lifecycle, you will upgrade sooner or later.

At the same time, we realize that there is always room for fundamentals, topics that apply to many versions of Delphi (and some of these go all the way back to Delphi 1!). As far as types of applications, we understand that we need to have a healthy dose of both Windows client development as well as distributed computing.

Finally, we wanted to deliver this type of content in a way that would be fun for us as well as our attendees, and do so in a way that gives the attendees something of value, something that lasts well beyond the two days we spend together.

All of these considerations went into the format of Delphi Developer Days. There are a total of 12 presentations over the course of two days, four of which Marco and I do together, and eight which we do individually (four sessions where we each present simultaneously in separate rooms). In choosing the topics, we tried to include something for everybody, from “What’s New in Delphi and Delphi Prism,” to “Leveraging ClientDataSets.” From “Internet Delphi Application Technologies Compared,” to “Delphi Development for Windows 7.” We even include a foundation presentation on “Designing Interfaces and Objects.” (For a complete schedule, visit http://www.delphideveloperdays.com/descriptions.html.)

When Marco and I are presenting separately, attendees can attend either talk. But what if you are interested in both topics? While we tried to keep the simultaneous individual session topics from overlapping, we acknowledge that this is an issue. Fortunately, both Marco and I are authors, and have published close to 40 books between us. To put this another way, we know how to write, and like doing it.

Each attendee to Delphi Developer Days receives a course book written by us that includes a paper for each talk. So, if you choose to attend one individual session, you can read the paper for the other session that is going on at the same time. Last year our course book was 500 pages in length (you get our slideshows and code, too, but these papers are detailed papers, covering more information than we have time for in our live sessions). We are currently writing the course book for this year’s event, and I’m guessing that it will be similar in length.

This is a course book that you can’t get anywhere else. While there is occasional overlap with other writings that we’ve done (such as our blogs, books, magazine articles, or other courseware), we do not publish or otherwise make this material available outside of Delphi Developer Days. Sorry, please don’t ask to buy the course book; it’s for attendees only.

As I mentioned above, this is not the first time Marco and I have done this. Delphi Developer Days 2009 was a wonderful success. Marco and I had a blast, and the feedback we received encouraged us to do it again this year, with two additional cities added to the tour.

So how does this year’s tour compare to last year’s? In addition to adding two cities (Los Angeles and Frankfurt, Germany, cities added in response to requests from the Delphi community), we added new topics and brought back some of the popular ones. And in order to make this year’s event relevant for previous attendees, we made sure that when a previous topics was covered during our breakout sessions, it was paired with a new topic, giving previous attendees a choice of something new. And, judging from the number of last year’s attendees who have registered for this year, we succeeded.

I would be remiss if I didn’t also mention our sponsors. Embarcadero Technologies and Sybase are once again our platinum sponsors, and each city will include a short presentation by representatives of one or both of these companies. In addition, we have more than 20 additional sponsors from almost every segment of the Delphi community. Many of these sponsors offer tools that work with Delphi, and many of them have provided copies of their products as door prizes for attendees. These sponsors are among the most active companies in the Delphi community, and we encourage you to visit their sites and learn more about their products. You will find a list of our current sponsors at http://www.delphideveloperdays.com/sponsors.html.

But there is more. On the first night of each US city, either Anders Ohlsson or David I (David Intersimone) will be present to provide a two hour “Embarcadero Delphi Evening,” complete with loads of information about upcoming plans. (Anders will be in Baltimore/Washington DC and Chicago, David I in Los Angeles.) Even if you can’t attend Delphi Developer Days, you can come to these free events. Visit http://edn.embarcadero.com/article/40546 to register (Delphi Developer Days attendees do not need to register. Your registration for Delphi Developer Days takes care of that).

I want to finish up by saying that Marco and I are huge Delphi fans, and we are extremely positive about the future of Delphi and the Delphi Community as a whole. We hope to see you there. It’s going to be a blast.

Learn more at http://www.delphideveloperdays.com/

Copyright © 2010 Cary Jensen. All Rights Reserved.

In-Memory DataSets: ClientDataSets and .NET DataTables Part 4: Navigating, Sorting, and Filtering

In the preceding article in this series I discussed how to populate in-memory datasets. In this continuation, I will look at some of the ways that you can programmatically work with that data, including navigating, sorting, and filtering the data in memory.

Active ClientDataSets are essentially a cursor pointer that refers to the current record in the dataset. To navigate which record your ClientDataSet refers to, you use methods of the ClientDataSet to change the current record. These methods include First, Next, Prior, Last, and MoveBy, to name a few.

The following line of code demonstrates how to navigate to each record of (scan) a ClientDataSet:

ClientDataSet1.Open;
while not ClientDataSet1.EOF do
begin
  //Do something here with the current record
  ClientDataSet1.Next;
end;

DataTables in the .NET framework do not support the concept of a current record. Instead, you use the Rows property to indicate which DataRow of the DataTable you want to work with.

The following code segment demonstrates how to programmatically navigate the DataRows of a DataTable.

var
i: Integer;
DataRow1: DataRow;
begin
  for i := 0 to DataTable1.Rows.Count – 1 do
  begin
    DataRow1 := DataTable1.Rows[i];
    //Do something with the DataRow
  end;

While the DataTable lacks the concept of a current record, Windows Forms applications have a concept that is very similar to current record. Anytime one or more properties are bound to a multivalue binding context (such as a DataTable) in a Windows Forms application, a CurrencyManager is created that can be used to determine to which DataRow the form's controls are synchronized. If you bind data using a BindingSource instance, CurrencyManager property points to the underlying CurrencyManager. Similarly, there are classes, such as BindingSource, that provide features similar to a CurrencyManager.

Note that in .NET Web Forms applications, most multi-value controls automatically iterate through the records of a DataTable or DbDataReader implementing instance when you call the DataBind method. As a result, Web forms do not have currency managers.

Sorting In-Memory Data

ClientDataSets are sorted using ClientDataSet indexes. There are two types of indexes in ClientDataSets, temporary indexes and persistent indexes.

You create a temporary index using the IndexFieldNames property of the ClientDataSet. When you assign a field name (or a comma-separated list of field names) to the IndexFieldNames property, an index is create for that field (or fields) at that moment the property is assigned, and the records of the ClientDataSet are then sorted based on that ascending index. This is demonstrated in the following code segment:

ClientDataSet1.IndexFieldNames := 'State;City';

Persistent indexes, on the other hand, are defined using the IndexDefs property of the ClientDataSet. After defining one or more IndexDef instances, you then set the IndexName property of the ClientDataSet to the name of one of those IndexDef instances.

Indexes created using IndexDefs are more flexible than temporary indexes. While temporary indexes can only be ascending, case-sensitive indexes, your IndexDef definitions can define ascending or descending indexes, as well as case insensitive indexes.

The following line of code demonstrates the creation and use of a persistent index using an IndexDef definition:

ClientDataSet1.IndexDefs.Add('IDIndex', 'State;City',[ixCaseInsensitive]);
ClientDataSet1.IndexName := 'IDIndex';

DataTables cannot be sorted directly. If you want to sort the records in a DataTable, you use a DataView. You sort a DataView using its Sort property, which can hold either the name of a DataColumn to sort the DataView DataRows, or a comma-separated list of DataColumn names.

The following code demonstrates creating a DataView to display a sorted list of a DataTable's DataRows.

var DataView1: DataView;
DataView1.Create(DataTable1);
DataView1.Sort := 'Customer ID';

Rather than creating a new DataView to display sorted DataRows, you can use the DefaultView of a DataTable to refer to the DataView that each DataTable automatically supports. This is shown in the following code segment:

DataTable1.DefaultView.Sort := 'Customer ID';

The alternative is to create a new DataView instance for each sort order you want to use. The following example creates two DataViews for a single DataTable, each with its own sort order:

var
DataView1, DataView2: DataView;
begin
  DataView1 := DataView.Create(DataTable1);
  DataView1.Sort := 'Customer ID';
  DataView2 := DataView.Create(DataTable1);
DataView2.Sort := 'Country;City';

Filtering In-Memory Data

When you filter a ClientDataSet, it behaves as though it contains only those records that meet the filter criteria. For example, if you filter a ClientDataSet for all records where the City field contains the name 'London', that ClientDataSet will act as though it only contains records where the City field value is London.

Note: There is one exception to the behavior of filtered ClientDataSets. Specifically, if you read the XMLData property of a ClientDataSet or call SaveToFile or SaveToStream, the results are as if the ClientDataSet is not filtered. In other words, all records are involved, not just the filtered records. To save only the filtered records, you must point to the filtered ClientDataSet with the DataSet property of a DataSetProvider that is referenced by another ClientDataSet. After opening that other ClientDataSet, it will contain only the filtered records (a copy), which you can then save to a file or stream. These records, however, do not include a change log.

There are two ways to filter a ClientDataSet. One is to use the Filter and Filtered properties. Filter takes a Boolean expression. Records that are available in the filtered view are those for whom the expression evaluates to True. The following code segment demonstrates how to filter a ClientDataSet using the Filter and Filtered properties.

ClientDataSet1.Filter := '[City] = ' + QuotedStr('London');
ClientDataSet1.Filtered := True;

The second way to filter a ClientDataSet is to use the SetRange method. Unlike Filter and Filtered, SetRange requires that you first set an index. Since an index is being used, SetRange is a much faster filter.

The following code demonstrates the use of SetRange.

ClientDataSet1.IndexFieldNames := 'City';
ClientDataSet1.SetRange(['London'],['London']);

You cancel filtering with Filter and Filtered by either setting Filtered to False or setting Filter to an empty string. You cancel filtering using SetRange by calling CancelRange. (For an interesting use of ClientDataSet where Filtered is False but a non-empty value appears in the Filter property, see the FindFirst, FindLast, FindNext, and FindPrior methods).

As is the case with sorting, you do not actually filter a DataTable. Instead, you use a DataView, which supports filtering through its RowFilter property. Filters are more complicated for Delphi developers, since single quotes are used to delimit string literals in the Filter expression. For example, consider the following filter:

DataView1.RowFilter := '[City] = ''London''';

With Delphi Prism, you can use the double quote character, which makes creating filter strings much easier, as shown in the following code.

DataView1.RowFilter := "[City] = 'London'";

You remove a previously set filter on a DataView by setting its RowFilter property to an empty string.

While it’s true that filtering a DataTable requires the use of a DataView, it is not necessary to actually create a new DataView when you want to set a filter. Each DataTable has a default DataView, which controls how the records in the DataTable appear. This DataView is accessed through the DefaultView property of the DataTable. As a result, assuming that the preceding code sample used a DataView created for a DataTable named DataTable1, the following code segment produces the same result:

DataTable1.DefaultView.RowFilter := "[City] = 'London'";

Copyright © 2009-2010 Cary Jensen. All Rights Reserved

Shifting TFields in TDataSets Bound to TDBGrids: A Potential Source of Bugs in Your Code

I've been working with Delphi since the beginning, 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 month. And what I observed may be the source of an infrequent yet potentially disastrous bug in a large number of Delphi database applications.

Here is what I observed: The TFields in an open TDataSet changed order at runtime. Specifically, a TField that was originally in the zeroeth position (DataSet.Fields[0]) at the time that I created the TDataset, 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 TIntegerField that I created in the first (zeroeth) position in my TDataSet. Between the time I created the TDataSet, and the execution of my code, the integer field had moved.

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

Besides being interesting (I kind of assumed that once a DataSet was opened, the position of the TFields 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 observed 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 specifically confirmed this.)

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

procedure TForm1.FormCreate(Sender: TObject);
begin
  with ClientDataSet1.FieldDefs do
  begin
    Clear;
    Add('StartOfWeek', ftDate);
    Add('Label', ftString, 30);
    Add('Count', ftInteger);
    Add('Active', ftBoolean);
  end;
  ClientDataSet1.CreateDataSet;
end;

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

procedure TForm1.Button1Click(Sender: TObject);
var
  sl: TStringList;
  i: Integer;
begin
  sl := TStringList.Create;
  try
    sl.Add('The Structure of ' + ClientDataSet1.Name);
    sl.Add('- - - - - - - - - - - - - - - - - ');
    for i := 0 to ClientDataSet1.FieldCount - 1 do
    sl.Add(ClientDataSet1.Fields[i].FieldName);
    ShowMessage(sl.Text);
  finally
    sl.Free;
  end;
end;

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

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

What is remarkable about this example is that position of the TFields in the TClientDataSet's Fields property changed, 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 TClientDataSet issue. I performed the same test with BDE-based TTables and ADO-based TADOTables and got the same effect.

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

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

If you programmatically refer to the TFields of a DataSet connected to a TDBGrid 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 TColumns in that TDBGrid. In the following section I will consider several solutions to this problem, as well as share with you the reason for it.

There Are Several Solutions

There are a number of tactics that you can use to eliminating this potential bug from your applications. The first is to define the TColumns of your TDbGrid using persistent TColumns.

Creating persistent TColumns can be done either at design time or runtime. To do it at design time, add the TColumns using the Columns Editor. Display the Columns Editor by right-clicking the TDBGrid and selecting Columns Editor or by clicking the ellipsis in the Columns property of the TDBGrid 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 TColumns and set their FieldName property in the Property Editor.

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

The second solution, though one that has some negative consequences, it to prevent a user from moving the TColumns of a TDBGrid. This can be done by removing the dgResizeColumn flag from the Options property of the TDBGrid. 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 Zarko Gajic's article How to allow column resize by disable movement (in TDBGrid).

A third solution is to avoid referring to a TDataSet's individual TFields based on a literal indexer to the Fields array property (since this is the essence of the problem). In other words, if you need to refer to the Count TField in the preceding code sample, don't use ClientDataSet1.Fields[2]. So long as you know the name of the TField, 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 TDataSet, 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 TField needs to be referenced many times, such as in a loop that navigates a large TDataSet.

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

var
CountField: TIntegerField;
Sum: Integer;
begin
 Sum := 0;
  CountField := TIntegerField(ClientDataSet1.FieldByName('Count'));
  ClientDataSet1.DisableControls; //assuming we're attached to a DBGrid
  try
    ClientDataSet1.First;
    while not ClientDataSet1.EOF do
    begin
      Sum := Sum + CountField.AsInteger;
      ClientDataSet1.Next;
    end;
  finally
    ClientDataSet1.EnableControls;
  end;
end;

The fourth solution is to use the FieldByNumber method of the TDataSet's Fields property. 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 TColumns of the bound TDBGrid, there is another solution. Change your code to use the FieldByNumber.

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.

procedure TForm1.Button1Click(Sender: TObject);
var
  sl: TStringList;
  i: Integer;
begin
  sl := TStringList.Create;
  try
    sl.Add('The Structure of ' + ClientDataSet1.Name +
       ' using FieldByNumber');
    sl.Add('- - - - - - - - - - - - - - - - - ');
    for i := 0 to ClientDataSet1.FieldCount - 1 do
    sl.Add(ClientDataSet1.Fields.FieldByNumber(i + 1).FieldName);
    ShowMessage(sl.Text);
  finally
    sl.Free;
  end;
end;

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

Figure 3

There is a fifth solution, but this is only available when your TDataSet is a TClientDataSet, like the one in my sample project. In those situations, you can create a clone of the original TClientDataSet, and it will have the original structure. As a result, whichever TField originally appeared in the zeroeth position will still be in that position, regardless of what a user has done to a TDBGrid that displays the TClientDataSet's data.

Note that I am not suggesting that you should reference TFields in a TDataSet using integer literals. Personally, the use of a TField 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 a couple of 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 TColumns in a TDBGrid, you call the SaveToFile method of a TClientDataSet bound to that TDBGrid, the saved structure is the original (true internal) structure. Similarly, if you assign the Data property of one TClientDataSet to another, the target TClientDataSet also shows the true structure (which is similar to the effect observed when a source TClientDataSet is cloned).

Similarly, changes to the column orders of TDBGrids bound to other tested TDataSets, 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 (or TColumn or TField, for that matter). 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.

The final point comes to us from StackOverflow user Sertac Akyuz, who responded to a question about this behavior that I posted to that Web site. I had inspected the source for both the TDataSet as well as TDbGrid classes, and could not locate where this behavior was originating from. Sertac wrote that the behavior is actually found in the TColumns and TFields classes. Specifically, changing the column position of a dynamic TColumn results in a call to set the corresponding TField's Index property, which affects the position of the TField in its TDataSet's Fields property.

Now that you know that this potential problem exists, under what conditions it can surface, as well as the source of the effect, you should now take a look at your applications to see if you have TDbGrids with dynamic TColumns that the user can move at runtime. If you also refer to the underlying TFields associated with these TColumns using literal indexers to the TDataSet Fields property, you can eliminate potential bugs that will result from your indexer referring to the wrong field at runtime by using one of the solutions I outlined earlier in this article.

Copyright (C) 2010 Cary Jensen. All Rights Reserved

Migrating Existing Delphi Applications to Unicode-enabled Delphi

Over the life of an application it is often necessary or desirable to migrate the application to a newer version of the development environment. By doing so, the application can take advantage of a more modern interface, improvements in performance or memory management, as well as new or improved features.

It comes as no surprise, therefore, that there are many Delphi applications that have been converted, upgraded, and migrated through two or more versions of Delphi.

There is one migration, however, that stands out from the rest. That is the migration of a native code application written in Delphi 2007 or earlier to the latest version. What makes this one so different is that a number of the fundamental data types, types that have been around since the Delphi 1 and Delphi 2 days, have changed. I am talking about String, Char, and PChar, and I am referring to their support for Unicode.

In late 2009, Michael Rozlog, Senior Directory of Delphi Solutions, contacted me to see if I was interested in writing a white paper about migrating Delphi applications to Unicode-enabled Delphi. Ironically, I was in the midst of writing training material about what Unicode support meant for application development. And, I was keenly aware that a Unicode migration white paper, if it was to be effective, needed to rely on much more than just my personal experience. This really was a job for the greater RAD Studio community.

Here was my thinking. Delphi 2009 had been out for almost a year and a half, and Delphi 2010 had shipped three months earlier. As a result, many developers had been through the process of migrating existing applications to the Unicode enabled versions. And, if there was some way of collecting their stories, a clear picture of the migration process would emerge.

The call for stories, code samples, and advice on Unicode migration went out using blogs, tweets, the Embarcadero Developer Network, and some gentle personal prodding of various Delphi experts. And during 6 weeks in October of November I was fortunate enough receive a lot of input. In all, more than 20 contributors provided material, sometimes with a single, valuable code sample, and in other cases, with extensive narratives describing the preparation, process, and techniques used to migrate their applications.

And what a group of contributors this was. A number of them are the innovators behind some of the most popular third-party tools available to Delphi Developers. I also received input from well-known authors, trainers, bloggers, and authorities in the Delphi community. Everyone had something valuable to say, and it all contributed nicely to the paper.

This material is now available in the white paper Delphi Unicode Migration for Mere Mortals: Stories and Advice from the Front Lines. This paper begins with a brief overview of some of the technical aspects of Delphi's Unicode support. It then addresses specific areas of application development that may be affected by the changes to Delphi's default string types.

Throughout the paper you will find direct quotes from the contributors, and in most cases you will also find code samples that reflect the kind of changes that you may have to make to your code as you upgrade existing applications (as well as possible changes to some of the core techniques you are accustom to using). I tried hard to give credit where credit was due, as I strongly feel that a paper of this scope and breadth would be nearly impossible were it not for the generous contributions of the contributors.

To everyone who contributed material, and especially to those brave individuals who agreed to review the paper for technical accuracy, thank you.

If you are preparing to migrate an existing application to Delphi 2010 (or Delphi 2009), or are in the midst of your own migration, you will hopefully find a lot of valuable information in this white paper. I do have one request, however. When you are ready to read the white paper, please download it right before reading. I hope to update this paper sometime in the future with additional material, if it makes sense to do so. In fact, the version of this paper that out there at the time of this writing (January 9th, 2010) includes two corrections that were reported by readers. If you downloaded the PDF prior to January 8th, you have the older version. Get the newer version.

One final note. If you also have stories, advise, or code samples that are not reflected in the current version of the white paper, and would like to have your material considered for a future revision, please do not hesitate to send it to me. I cannot promise that new contributions will actually appear in the paper (there was some nice material that didn't fit into this version), but I would like to consider it.

Send your contributions to cjensen@jensendatasystems.com. Use the subject line "Unicode Migration." I promise to acknowledge every contribution, so if you don't hear from me within a week of sending it, it fell through the cracks. Please resend.

Again, the white paper can be downloaded from Delphi Unicode Migration for Mere Mortals: Stories and Advice from the Front Lines.

Copyright © 2010 Cary Jensen. All Rights Reserved.

Creating a More Manageable Development Environment

I recently bought a new laptop, something that I do every year or so. And the recent release of Windows 7 gave me a good excuse to do so. But this new purchase also gave me another opportunity to do something that I’ve been thinking about for a while, the opportunity to set up my machine in a fashion that will improve my ability to maintain my development environment.

Here’s the problem. Over time, in some cases in as little as three or four months, my computer performs noticeably slower than when it was first configured. Eventually it gets so bad that I need to reinstall my operating system and all of my software and tools. For me, this can take days.

Part of my problem is that I need quite a few different versions of a number of different programs. For example, I currently support a number of applications written in Delphi 7, Delphi 2007 (both Win32 and Delphi for .NET), Delphi 2009 and Delphi 2010, Visual Studio 2008 (including Delphi Prism). (I am also asked to teach classes in other versions of Delphi, such as Delphi 2006, or even Delphi 5.)

In addition, I use a variety of database servers, including Advantage Database Server and SQL Server, and these must be installed as well. And then there are the various tool sets, set as third-party component sets for the various applications I support, source code control, utilities, and the list goes on and on.

Actually, it’s much worse than it sounds. Let’s take Delphi 2007, for instance. Once you install Delphi 2007 and it’s help files, and register it as a legitimate copy, you must also download and install the various patches. I think that the total time, from start to finish, to get Delphi 2007 installed is something on the order of 5 hours.

And don’t get me started on Visual Studio 2008. In addition to the core development environment, there were several service packs, as well as updates such as Silverlight 3, and so forth. These needed to be downloaded and installed. VS 2008 must have taken a total of 8 hours.

Believe me, I’ve gotten pretty good at configuring my system, but this is a waste of time. Why can’t I just install it all once and be done with it.

I’ve tried to solve this problem in the past. One of my approaches was to carefully install everything I needed, and then make a good, clean image using something like Acronis True Image or Norton Ghost. And while this works, somewhat, it isn’t perfect. After restoring from an image, there is still a lot to do to get a system back into shape.

The fact is, that “clean” image you create early on isn’t perfect. It doesn’t have all the nifty tools you’ve picked up in the intervening months since you created the image. Nor does it have the latest third-party components that you have added to some of your more recent development projects.

Incremental images are not the answer, either. Once you start using your system, things get progressively worse, performance-wise. As a result, while that pristine, original image that you created, once restored, will perform very nicely, those incremental images you’ve been making along the way each contain some of the “gunk” that has been eating away at your performance. Restore from one of those and you’re starting with a non-optimal setup (and you will still need a day or so to get things really back to where you need them).

A Possible Solution

Over the past year I have been playing with an idea that is not so original. Most of us use products like VMWare Workstation or Virtual PC to create virtual installations of guest operating systems. Most often we use these guest OSes to install beta software, so that it can run in a clean environment, without having to worry about it doing something ugly to our primary OS (host operating system). Once testing is done, we simply delete the guest operating system and go about our way.

Well, sometime last year I bought a copy of Windows XP Pro, and installed it as a guest OS under VMWare. I then installed Delphi 2007, as well the various support tools that I use. I used this guest to compare how Delphi (and then other tools) worked under XP versus Vista (which I was using as my host OS).

This was all very educational, until at one point something weird happened to my Delphi 2007 installation under my host (personally, I blame the Internet Explorer 8 installation, but I’ll leave that speculation for another article). At the time I was on the road, working for 10 days at a client’s site. And, I didn’t have my RAD Studio 2007 installation disks with me.

Fortunately, I was able to load the guest OS, and continue developing without missing a beat. I simply retrieved a clean copy of the source code from the version control system, and off I went.

A colleague of mine and I talked about what had happened at length, and came to the same conclusion. Maybe I should isolate my development environment from the host operating system as a matter of practice. Furthermore, maybe I should isolate each of my different development environments from each other. Maybe, I thought, this would decrease (I wanted to say eliminate, but that would be blatant hubris) the “gunk,” and reduce the opportunity for incremental decay of performance.

The Virtual Road is Paved With Good Intentions

Here is what I did. I bought a fast computer with loads of memory. This machine rus an Intel Core 2 Quad Q9000 CPU running at 2 GHz. (Importantly, this chip supports hardware virtualization.) The machine also has 8 GB of DDR 3 RAM. Plenty of memory, plenty of cores, I was good to go.

From here, I installed onto my host operating system, which was Windows 7 64-bit Home Premium, just that stuff that an ordinary computer user needs, and nothing else. I’m talking about basic stuff, like a word processor, an email client, a browser, antivirus, backup/restore software, a Twitter client, iTunes, and most importantly, virtualization software, which in this case, was VMWare Workstation 7.0. Once all of this was installed, and any available updates applied, I made a nice clean image of this base.

Next, I went to work on the guest operating systems. And I was very systematic about this. First, I created a guest OS with the absolute basics: Windows 7 64-bit Ultimate and antivirus. After installing all updates, I made a full cloned of this guest OS. Onto the clone I installed my very basic stuff that I need in all of my development environments, including version control software, various utilities (like SysInternals Suite), as well as Advantage Database Server and SQL Server (hey, I’m a database guy. No matter what I’m doing, I’m going to need a database). Let’s refer to this virtual machine as “Database Base.”

Now I’m ready for the big time. I cloned Database Base, and installed onto this clone a copy of Delphi 2010. I created a second clone of Database Base and installed Visual Studio 2008 (that took an entire day). In all, I’ve got about seven of these development environments so far.

I also have a nice, 500 GB, portable, external eSATA drive (this laptop supports eSATA, which is about three times faster than USB 2.0). I backed up the image of my host OS, as well as all of the nice new guest OSes, to this drive.

Despite the limitations, which I’ll share with you in a moment, this is a pretty nice setup. First of all, since I don’t actually develop in the host, and have only limited software installed on it, I gain two benefits. First, the host is small, and really, if I had to install it from scratch, it would take hours, not days. And, since I have a nice, clean image of the host, I can actually restore it in a matter of minutes, if necessary.

The second benefit is that there is little going on in the host. Sure, I end up installing necessary evils such as Goto Meeting, Adobe Air, and other stuff like that, but it’s limited, since I am not developing in the host. And, so far (it’s been a couple of months now), the host is showing little signs of slowing down.

And it’s working out fairly well with the virtual machines, so far. Performance is nice I’m allocating 3 GB of RAM to each virtual machine, and this give them some room to stretch, while permitting me to run two of them simultaneously without choking the host OS (though, frankly, things do get slow when the host has only 2 GBs to play on, so I try not to run two guests at a time).

And here’s the great thing. Since I’ve got the individual VMs backed up on the eSATA drive, I can restore them as well. In fact, if my idea works as planned, I should be able to migrate these VMs to my next machine, meaning that I may not have to install RAD Studio 2007 ever again. (I wish it were true, but I know in my heart that it is a lie. I know I’ll have to install RAD Studio 2007 again, but just not as many times as I would had I not taken this particular route.)

There are two additional, and compelling, advantages that I now have. First, if something awful happens to one of my development environments, I’ve got a quick solution. For example, if, when testing a new routine that removes an old key from the Windows registry I accidentally delete every ProgID, no worries. I simply copy the backed up guest VM file from my eSATA drive (which, of course, I have backed up to another storage drive as well), and I’m cooking with gas (this means that I’m back to work quickly).

The second advantage is this. When I get to the point of shipping a product (or hit a major milestone, or whatever), I can make a backup of the VM that I used, and I’ll have that exact environment forever. If I ever need to return to the precise installation of service packs, updates, component sets, and the like, that was used to create that special build, I’ve got it, right there, in that virtual machine that I’ve saved (and backed up, of course).

But It’s Not Perfect

I wish I could say that I’m completely satisfied with this solution, but I cannot. There are problems, and some of them are not trivial. They are not horrific, either. In other words, while there are benefits to this approach, I’ve realized some serious limitations, even though I’m only a few months into this experiment.

The first issue is, honestly, a pretty minor one: It takes a little bit longer to get up and running, as far as development goes. In short, I have to wait for two OSes to load (the host and a guest) before I can get to work. Fortunately, these OSes, being Windows 7, do load quickly, so its only a minor inconvenience.

The second issue is more complicated. I run square into a major issue involving software updates. You know, those annoying message you get from our fine friends at Microsoft that inform you that updates are being installed (actually, I don’t get those, because I refuse to let Microsoft decide when to install updates. I get to choose when.)

Well, each of the individual VMs have this problem, which means that right now I don’t install one update, I install eight (host plus seven guests). And, Java and Adobe, and every other bloke on the block who wants to make sure that their bugs don’t destroy my system(s), want to install updates as well. You get the picture, and it isn’t pretty.

Ok, if you only work in one development environment, say Visual Studio, this will not be an issue. Most of us, though, must support a variety of environments. So if you take the road I did, creating a separate guest OS for each, you're going to have to face this issue (which may be to ignore updates altogether, with the exception of major service packs).

There is another, related issue. What if I find a great new tool (for example, the best merge tool that you’ve ever seen, one that flawlessly and perfectly merges two different versions of the same source file). Or, what if I realize, after creating all of my swell VMs, that I failed to install one of my more useful utilities. Well, at present, I have to install these things separately in each VM. A time consuming task if taken all at once, or an annoyance if done piecemeal each time I discover the missing utility in a particular VM.

I was hoping that a feature of VMWare Workstation, called linked clones, was the answer (it’s not). A linked clone is where you create a clone that is based on an existing VM. When I first started looking into linked clones, I thought my problems were solved. But after reading more closely, the VMWare Workstation help makes it clear that a linked clone is associated with a particular snapshot of an existing VM, and that subsequent changes to the cloned VM do not appear in the clone.

I was really hoping that I could create a linked clone to a base VM, like Database Base, and then perform maintenance only to Database Base. For example, if a Windows update is released, I was hoping I could update only Database Base, and all the linked clones created from it would automatically have the updates. That’s not how it works. Even with linked clones, the individual clones need Windows updates (Oh, the humanity!).

While there is a slight performance decrement when using linked clones (not really an issue; we're not trying to run games on these systems), there is a benefit. Specifically, the individual linked clones, while requiring that you keep around a copy of the original VM that you cloned, take up much less disk space than full clones. For example, one full clone I have takes up 19 GB, while a comparable linked clone consumes 6.5 GB of disk space. This makes the linked clone much more convenient to backup and restore.

What’s The Solution?

As I said, this approach has some serious benefits, but it doesn’t solve all of the problems, either. Is there a perfect solution? I don't know.

Some developers I've talked to have used a system like this, and report that they are very pleased. In those cases, however, they had only one guest OS to maintain. And, either they only needed one development tool, or they installed all of their development environments on the single guest OS. While that might work, these guest OSes tend to get very large, and increase the likelihood that one tool (Visual Studio, for example) may introduce issues with another (say Eclipse).

Other developers I know accept the predictable cycle of re-installation of the OS and all tools. Of these, one or the more compelling approaches involved not only installing all tools and development environments, but also installing all installation CDs and DVDs to a directory or partition on the system. In addition, each time they install a service pack, they first download it to a folder along side the installation disk images.

So long as you a good image of your base operating system, a backup of your installation images, and keep a handy record of your serial numbers, registration keys, and the like, re-installation goes much faster. It still takes time (days?), but it takes much less time than when using disks.

So, while I'm pretty satisfied with my current setup, I'm still looking for something better. I’d like to hear what you think, and what you’ve done to address these issues.

Copyright © 2009 Cary Jensen. All Rights Reserved

In-Memory DataSets: ClientDataSet and .NET DataTable Compared: Part 3: Populating In-Memory DataSets

In the preceding article in this series I showed you how to create in-memory datasets at runtime (as well as how to define a ClientDataSet’s metadata at design time). In this third installment in this series I will demonstrate how to populate your in-memory dataset.

In short, data can be inserted into an in-memory dataset in one of four ways. These are:

1. You can add data programmatically
2. You can load previously saved data
3. You can retrieve data from a database
4. You can permit an end user to enter data manually

Each of these techniques is described in the following sections.

Writing Data Programmatically

After creating a ClientDataSet, you use its TField instances to add data programmatically. Alternatively, you can use the ClientDataSet's InsertRecord or AppendRecord methods to add data directly. The following code sample demonstrates adding a record using TField instances:

ClientDataSet1.Insert;
ClientDataSet1.FieldByName('Account ID').AsInteger := 1001;
//This next line assumes that the second column is a string field
ClientDataSet1.Field[1].AsString :=
  'Frank Borland';
ClientDataSet1.Post;
//Adding a record using the InsertRecord method:
ClientDataSet1.InsertRecord([1002, 'Phillipe Kahn']);

With .NET DataTables, you add a DataRow instance, which you then populate with data. This is demonstrated in the following code segment.

  //Add two records to the DataTable
  var DataRow1: DataRow := DataTable1.NewRow();
  //Referencing columns using column name
  DataRow1['Account '] := 1001;
  DataRow1['Customer Name'] := 'Frank Borland';
  DataTable1.Rows.Add(DataRow1);

  DataRow1 := DataTable1.NewRow();
  //Referencing columns using ordinal position
  DataRow1[0] := 1002;
  DataRow1[1] := 'Bill Gates';
  DataTable1.Rows.Add(DataRow1);

Loading Data From Files or Streams

There are a variety of ways to load a ClientDataSet from a file or stream. One of the most common is to invoke the ClientDataSet's LoadFromFile or LoadFromStream methods. This is shown in the following code sample:

ClientDataSet1.LoadFromFile('c:\mydata.xml');

You can also use the XMLData property, which is a read/write property that represents the ClientDataSet's data as a string of XML. The following line of code shows how you can load a ClientDataSet from a memo field of a table:

ClientDataSet1.XMLData :=
  ClientDataSet2.FieldByName('Hold Data').AsString;

Loading a DataTable from a file or stream can either be done directly (ADO.NET 2.0 or later) or can be done using a DataSet that contains the DataTable (ADO.NET 1.1). The following line of code demonstrates this technique:

DataTable1.ReadXML('c:\netdata.xml', XmlReadMode.ReadSchema);

It is important to note that both ClientDataSets and DataTables that are loaded from a file or stream will obtain their metadata and data store during the loading process. In other words, it is not necessary to define the structure of a ClientDataSet or DataTable prior to loading it from a file or stream.

The Other Side: Saving Data to Files or Streams

Of course, for you to be able to correctly load your data into a ClientDataSet or DataTable, the data that you are loading must be in the correct form. Although it may be possible to transform an incompatible XML file into the correct format (using XSLT or Delphi's XML Mapper Utility), in most cases the file or stream that you are loading is one that was previously created by saving a dataset to a file or stream.

With ClientDataSets, you save your data to a file or stream using the SaveToFile or SaveToStream methods. The following is an example of how this may look:

ClientDataSet1.SaveToFile('c:\mydata.xml', dfXML);

Similarly, saving a ClientDataSet's data to a memo field in a database looks something like this:

ClientDataSet2.FieldByName('Hold Data').AsString :=
  ClientDataSet1.XMLData;

If you'd rather store your data in a more compressed format (the native CDS format takes 20 to 60 percent less space than the XML format), you can do something like this:

var
  ms: TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    ClientDataSet1.SaveToStream(ms);
    TBlobField(ClientDataSet2.
      FieldByName('rawdata')).LoadFromStream(ms);
  finally
    ms.Free;
  end;

You save the contents of a .NET DataTable using the WriteXml method. While there are a number of overloaded versions of this method (16 at last count), one of the more interesting parameters is the XML write mode.

There are two write mode enumeration values that are typically used by developers writing out the contents of a DataTable. These are XmlWriteMode.WriteSchema and XmlWriteMode.DiffGram.

When you call WriteXML with the WriteSchema enumeration, DataTable metadata is written to the XML file in the form of a schema definition. This information is required in order for a DataTable loading the saved XML to accurately reconstruct the metadata of the DataTable.

By comparison, if you use XmlWriteMode.IgnoreSchema, the DataTable will try to reconstruct the metadata based on the data it encounters while loading, which is rarely 100 percent correct. For example, if you are loading a saved DataTable from XML, and there are string fields, the DataTable will estimate the size of the string fields based on the longest string found in the XML file, which may be substantially shorter than the original DataTable's metadata permits.

Like WriteSchema, XmlWriteMode.DiffGram writes schema information into the XML file. DiffGram writes the change cache information as well, which makes this enumeration essential if you want to persist the DataTable's state. Recall that the change cache is crucial if you want to be able to write the changes back to an underlying database.

The following example shows a DataTable and its change cache being written to an XML file.

DataTable1.WriteXml('c:\savedat.xml', XmlWriteMode.DiffGram);

If you need to save a DataTable as text, which is what you need to do in order to persist the DataSet to a memo field of a database, you must write your XML to a descendant of TextWriter (an abstract class). The following example shows a DataSet, and its change log, being persisted to a memo field in a database using a StringWriter (obviously a TextWriter descendant).

  var StringWriter1: StringWriter;
  StringWriter1 := StringWriter.Create;
  DataTable1.WriteXml(StringWriter1, XmlWriteMode.DiffGram);
  DataTable2.Rows[0]['Current Data'] :=
    StringWriter1.ToString;

Retrieving Data From a Database

Loading data into a ClientDataSet from a database requires the use of a DataSetProvider. The DataSetProvider, in turn, points to a TDataSet descendant. When you make the ClientDataSet active, the DataSetProvider will open the TDataSet, navigate the result set, loading the data in the TDataSet to an OleVariant. Once the DataSetProvider completes this navigation, it (usually) closes the TDataSet (based on a number of factors, which I will not go into now), and populates the ClientDataSet's Data property with the OleVariant.

This process is demonstrated in the following code segment:

DataSetProvider1.DataSet := SqlDataSet1;  //using dbExpress
ClientDataSet1.ProviderName := DataSetProvider1.Name;
ClientDataSet1.Open;

Note that when you use this technique, both the ClientDataSet and the DataSetProvider must be owned by the same owner. If that is not the case, or if the DataSetProvider does not have a name, you can use the following technique:

DataSetProvider1.DataSet := Query1;
ClientDataSet1.Data := DataSetProvider1.Data;

In .NET, you acquire data into a DataTable from a database using the Fill method of an instance of a DbDataAdapter. Before you can call Fill, the DbDataAdapter class must have a SQL SELECT statement associated with an DbCommand class assigned to its SelectCommand property. The following code segment demonstrates loading a DataTable using a DataStoreDataAdapter:

Connection1 := DataStoreConnection.Create;
Connection1.ConnectionString := 'host=LocalHost;user=sysdba; ' +
  'password=masterkey;database="C:\Users\Public\Documents\' +
  'Delphi Prism\Demos\database\databases\BlackfishSQL\employee"';
Connection1.Open();
Command1 := Connection1.CreateCommand;
Command1.CommandText := 'select * from customers';
DataAdapter1 := DataStoreDataAdapter.Create;
DataAdapter1.SelectCommand := Command1;
DataTable1 := DataTable.Create();
DataAdapter1.Fill(DataTable1);

As is the case when you load data into an in-memory dataset from a file or a stream, you do not need to define the metadata or data store in advance of loading a dataset from a database. The metadata will be obtained from the result set you load into the dataset.

Direct Input From the User

Both ClientDataSets and DataTables can be associated with GUI (graphical user interface) controls and displayed to a user. The user can then use those GUI controls to view and edit the contents of the dataset.

If the in-memory dataset is created by loading the data from a file, stream, or database, it is not necessary to define the metadata of the dataset prior to presenting it to the user. If the dataset is not loaded by one of these techniques, it is necessary to define the metadata (otherwise the dataset will have no fields/columns, and therefore no data store).

In Win32 applications, you display data from datasets using data aware controls (primarily found on the Data Controls page of the Component Palette). At a minimum, you must point a DataSource to the dataset, and then assign the DataSource property of the data aware control to that DataSource. For those data aware controls that bind to a specific field in the dataset, you must also assign the DataField property.

For .NET controls (any visual control that descends from System.Windows.Form.Control), you bind the control using a BindingSource, its DataBindings property, or, if available, use its DataSource field (and its DataMember field if necessary). (Binding System.Web.UI controls is somewhat similar, though differences do exist. This issue, however, is beyond the scope of this article).

In the next segment in this series I will described how to programmatically navigate ClientDataSets and .NET DataTables.

Copyright © 2009 Cary Jensen. All Rights Reserved