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