Icon Creating and Loading DataSets

Before using the TDataSet component, you must first create an instance of the component, which you can do at design-time or at run-time.

Creating a DataSet at Design-Time
The easiest way to create a dataset is by using the Database Manager in the IDE to define a database and its contained datasets. Once a dataset has been defined under a database in the database manager, you can easily add the dataset to an existing application by simply dragging it from the database manager and dropping it on a form or database. The relevant property information, including the column definitions, will automatically be populated for the dataset. A database defined in the database manager can be used to create a database in a project by dragging the database from the database manager and dropping it into the project manager for the currently-opened project. When the database is dropped on or within the Units node of the project manager, a new TDatabase (or descendant) instance will be created for the project, along with an associated unit, and all of the defined datasets for the database will automatically be created as TDataSet instances in the new database instance.

If you do not wish to use the database manager to create a dataset, you can also create a new dataset by dragging a TDataSet component from the component palette and dropping it on a form or database. Please see the Using the Form and Database Designers topic for more information on the required steps to complete this action. Once you have dropped the TDataSet component on a form or database, you can manually define the columns in the dataset by double-clicking on the TDataSet's Columns property. This will launch the Columns Editor directly under the object inspector, and you can then use the Columns Editor to add, edit, or delete the columns in the dataset.

Creating a DataSet at Run-Time
In cases where visual forms and databases are not being used, such as with a non-visual project or in a library procedure/function, you can create a dataset instance at run-time using code. The following is an example of creating a dataset, opening it, and populating it with some rows at run-time:

function CreateStatesDataSet: TDataSet;
begin
   Result:=TDataSet.Create(nil);
   with Result.Columns.Add do
      begin
      Name:='Abbrev';   
      DataType:=dtString;
      Length:=2;
      end;
   with Result do
      begin
      Open;
      Insert;
      Columns['Abbrev'].AsString:='CA';
      Save;
      Insert;
      Columns['Abbrev'].AsString:='FL';
      Save;
      Insert;
      Columns['Abbrev'].AsString:='NY';
      Save;
      end;
end;

Datasets are associated with a given database by being created with the database as the (sole) owner parameter. As you can see in the above example, the dataset is created with a nil owner parameter, which will cause this dataset instance to be associated with the global Database TDatabase instance.

Loading a DataSet at Run-Time
As seen in the above example, you can add rows directly at run-time without ever having to communicate with the web server in order to request data. However, most applications will need to load rows into a dataset from a database by using the web server application as middleware for serving up the necessary rows. There are two different ways to load rows into a dataset at run-time: the TDatabase LoadRows method or the TDataSet LoadRows method.

TDatabase LoadRows Method

The TDatabase LoadRows method is the easiest way to load the rows into a dataset because it automatically handles the actual server request to the web server. The TDatabase component uses the following properties to construct the GET request to the web server for the rows:
  • TDatabase BaseURL
    This property defaults to 'databases', but can be changed to any value that you wish. Please note that it is best to use a relative URL path here so that all requests will be made relative to the URL from which the application was loaded. If you're accessing a database module then, by default, you should set this property to 'databasemodules/<module name>', where <module name> is the name of the database module that you wish to access. Please see the Creating Web Server Modules for more information on creating database modules to handle database requests.


  • TDatabase DatabaseName
    This property defaults to the same value as the TDatabase component's Name property, but is automatically populated for you if you use the drag-and-drop method of creating a TDatabase at design-time. This property can be changed to any value that you wish, and is simply used to identify the database via a URL parameter used for the web server request.


  • TDatabase Params
    This property is a string list (TStrings) of "name=value" pairs that represents the URL parameters for all web server requests for the database. These parameters are strictly application-specific and are not used by by the TDatabase component.


  • TDataSet DataSetName
    This property defaults to the same value as the TDataSet component's Name property, but is automatically populated for you if you use the drag-and-drop method of creating a TDataSet at design-time. This property can be changed to any value that you wish, and is simply used to identify the dataset via a URL parameter used for the web server request.


  • TDataSet Params
    This property is a string list (TStrings) of "name=value" pairs that represents the URL parameters for the web server request. If the dataset that is being loaded is a query that requires parameters, then you should make sure to specify them using this property.
As an example, consider a database and dataset that is defined as the following in the database manager in the IDE:

Database Name: Production

DataSet Name: CustomerOrders

Row Source:

SELECT * FROM custord
WHERE CustomerID={CustomerID='ADF'}

Base Table: custord

Assuming that a dataset instance called "CustomerOrders" was created at design-time by dragging and dropping the dataset from the database manager on to a form called "MasterDetailForm", the following code is all that would be needed to load the dataset:

procedure TMasterDetailForm.LoadOrders;
begin
   CustomerOrders.Params.Clear;
   CustomerOrders.Params.Add('CustomerID='+QuotedStr(Customer.Columns['CustomerID'].AsString));
   Database.DatabaseName:='Production';  // Uses the default global Database TDatabase instance
   Database.LoadRows(CustomerOrders);
end;

Information You should always use single quotes around all string parameters. Failure to do so will result in the dataset load not working correctly. Use the QuotedStr function to ensure that any string parameters are properly quoted.

In the above example, the relative URL that will be used for the web server GET request would be:

databases?method=rows&database=Production&dataset=CustomerOrders&CustomerID='ADF'

If the application was loaded from 'http://localhost', then the complete URL used for the web server GET request would be:

http://localhost/databases?method=rows&database=Production&dataset=CustomerOrders&CustomerID='ADF'

If aren't using the global Database TDatabase instance and, instead, have created a TDatabase instance in the application, then the code is only slightly different. Assuming that a database instance called "Production" and a dataset instance called "CustomerOrders" was created at design-time by dragging and dropping the database from the database manager on to the project manager, the following code is all that would be needed to load the dataset:

procedure TProduction.LoadOrders;
begin
   CustomerOrders.Params.Clear;
   CustomerOrders.Params.Add('CustomerID='+QuotedStr(Customer.Columns['CustomerID'].AsString));
   LoadRows(CustomerOrders);
end;

In the above example, the URL used for the web server GET request would be exactly the same as before when the global Database TDatabase instance was used instead of a specific TDatabase instance.

After the request is successfully executed, the TDatabase LoadRows method automatically opens the dataset using the TDataSet Open method before also automatically calling the TDataSet LoadRows method.

TDataSet LoadRows Method

The TDataSet LoadRows method directly accepts the dataset rows as a JSON-formatted string. This means that this method is more useful for situations where the dataset rows are stored in memory or local storage and need to be directly loaded from one of those locations. It is recommended that you always use the TDatabase LoadRows method for loading rows from a web server.

Information The LoadRows method requires that the dataset be open prior to being called. Use the Open method to open the dataset.

Tracking Load Operations
The TDataSet BeforeLoad event is fired before the dataset load actually begins. To prevent the load from occurring, return False as the result in an event handler for this event.

If a dataset load request was sent to the web server and was not successful due to an exception or the web server application returning an HTTP result code other than 200 (OK), the OnLoadError event will be fired and will include the error message. If an event handler is not defined for the OnLoadError event, then an exception will be raised with the error message. If a load fails for any reason, then the load request is placed in a pending requests queue. This is also true for transaction commits. This queue ensures that the database requests can be retried and, when retried, are sent to the web server in the order in which they occurred. You can see if there are any pending database requests by examining the TDatabase NumPendingRequests property. If the NumPendingRequests property is greater than 0, then there are commit and/or dataset load requests that need to be retried at some point. Use the TDatabase RetryPendingRequests method to retry any pending database requests, and the TDatabase CancelPendingRequests method to cancel any pending database requests.

The TDataSet AfterLoad event is fired after the dataset load completes successfully. If there were any errors during the load process, then this event handler will not get called.
Image