Changing DataSet Data (FireDAC)
Go Up to Editing Data (FireDAC)
FireDAC datasets, including TFDQuery, TFDTable, TFDStoredProc, and TFDMemTable, are supporting the insertion, the edition, and the deletion of records, using the standard TDataSet methods. By default, editing and posting updates are enabled for FireDAC datasets.
Controlling the Edition
The edition of datasets is enabled, when UpdateOptions.ReadOnly is False. The following operations are enabled:
- Appending when UpdateOptions.EnableInsert is True:
FDQuery1.Append;
FDQuery1.FieldsByName('id').AsInteger := 100;
FDQuery1.FieldsByName('name').AsString := 'Audi A6';
FDQuery1.Post;
- Editing when UpdateOptions.EnableUpdate is True:
FDQuery1.Edit;
FDQuery1.FieldsByName('name').AsString := 'Audi A6 Avant';
FDQuery1.Post;
- Deleting when UpdateOptions.EnableDelete is True:
FDQuery1.Delete;
To avoid receiving the "Field value is required" message at Post call, set UpdateOptions.CheckRequired to False (the default is True).
Posting Updates
To post updates to a database, FireDAC will automatically generate updating SQL commands, when the original SQL query conforms to the following:
- The command must be SELECT.
- The first table in the FROM clause must preserve the primary key (PK).
- The PK fields must be in the SELECT list.
- The SELECT must be without the DISTINCT, GROUP BY, UNION, etc phrases. That is a logical rule, rather than "a must".
Also the correct metadata must be provided, including unique identifying fields. The simplest way to enable the automatic updates posting for TFDQuery is to set UpdateOptions.RequestLive to True (the default is True). There is no automated possibility to check that a query is updatable, the programmer must evaluate that himself.
If the query does not conform to the above rules, or if the posting must be overridden by the application, then you can use the TFDUpdateSQL component or the OnUpdateRecord event handler.
The updates may be posted to a database immediately with the Post or Delete methods calls, or they may be cached and posted later. The posting of the deferred updates may be used together with offline connections.
Emptying the Dataset
A dataset may be emptied in an effective way without recording a record deletion and posting deletion to a DB. In most cases, this is useful for in-memory datasets. To empty a dataset, use one of the following methods:
- EmptyDataSet - removes all dataset records;
- EmptyView - removes only the accessible records after applying filters, ranges, etc.