Some of you might already be aware of my CmonLib, which, according to the GitHub ReadMe, is a collection of several units with different purpose.
My intention for this library was to group all those single-file-helper-units into one repository which can be added as a single submodule to any project in one go. In addition, a couple of these helpers share some common functionality which could better be mapped in one repository, while the alternative would be multiple pretty small repositories forming a hierarchy.
This means that there are very few dependencies between all units of CmonLib. For details, please refer to the ReadMe.
One of those helper units is Cmon.DataSetHelper.pas, which is the new name and place of the former DataSetEnumerator. There is another article in this blog describing that helper: Dataset Enumerator Reloaded
During the last weeks there have been some improvements and extensions to this unit which I am going to explain in this article.
Type mapping
The original implementation of the DataSetHelper was based on TValue and Variants. Thus is was limited to what type of Variants are properly supported by TValue in both directions. This lead to several tweaks handling special cases. The resulting code while started as a simple
|
1 |
FField.Value := FRTTIProp.GetValue(GetPointer(Source)).AsVariant; |
quickly morphed into a whopping
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
if FField.DataType = ftAutoInc then Exit; if FField.ReadOnly then Exit; if Value.IsEmpty then FField.Clear else if FField.DataType in [ftTimeStamp, ftTimeStampOffset] then FField.AsDateTime := Value.AsType<TDateTime> else if FField.DataType in [ftBCD, ftFMTBcd] then FField.AsFloat := Value.AsType<Double> else if FField.DataType in [ftBlob, ftMemo, ftWideMemo] then FField.AsString := Value.AsString else if FField.DataType in [ftBytes, ftVarBytes] then FField.AsBytes := Value.AsType<TBytes> else FField.Value := Value.AsVariant; |
As the number of these special cases tend to grow pretty quick, I thought about a more resilient approach: Why not take the actual type of the field/property and call the corresponding TField.AsXXX property to make use of the internal conversion in TField – much like what the special cases already adopted. Instead of checking FField.DataType there now is a case statement over TTypeKind. This lowers the previous restrictions on the record/class declarations.
TRecordFields
Cmon.DataSetHelper.pas also introduces a new class: TRecordFields.
A TRecordFields descendant declares a bunch of TField properties mapping the corresponding Fields of the dataset when open. Basically this is similar to those static fields created at design time, with the difference that these are separated for each dataset with a simpler naming. EmployeeFields.FullName.IsNull looks a little bit better than qyEmployeeFullName.IsNull and an EmployeeFields instance is simpler to be passed elsewhere as a bunch of static fields are.
This is how it looks for the Employees table used in the example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[DBFields(mapAuto)] TFieldsEmployees = class(TRecordFields) private FEmpNo: TField; FLastName: TField; FFirstName: TField; FPhoneExt: TField; FHireDate: TField; FSalary: TField; FFullName: TField; protected procedure InternalCalcFields; override; public property EmpNo: TField read FEmpNo; property LastName: TField read FLastName; property FirstName: TField read FFirstName; property PhoneExt: TField read FPhoneExt; property HireDate: TField read FHireDate; property Salary: TField read FSalary; property FullName: TField read FFullName; end; |
You might have noticed the calculated FullName field and the InternalCalcFields override with this implementation:
|
1 2 3 4 |
begin inherited; FullName.AsString := FirstName.AsString + ' ' + LastName.AsString; end; |
TRecordFields not only wires the TField instances when it is linked to a TDataSet, but also connects the OnCalcFields event and routes it to InternalCalcFields. Of course it keeps an already wired OnCalcFields event intact, which requires to call its CalcFields method inside that event handler as appropriate.
Design time support
It often bothered me that writing the declarations for the mapped records or classes was a bit tedious. With the advent of TRecordFields I looked for some automatism for creating all this boiler plate code. The CmonLibDataDesign package adds a new context menu entry Create mappings to any TDataSet descendant in the IDE Form Designer (Usually it appears below the Quick Edit item). This brings up this dialog:
In this dialog you can select if you prefer record or class for mapping as well as automatic or manual mapping should be used.
You remember? Automatic mapping requires the field or property names match the database field names, but can be overridden by a DBField attribute, while manual mapping requires a DBField attribute for each field or property to be mapped.
There is an option to create separate constants for the database field names or aliases used in a query. The add field access option controls the creation of a TRecordFields descendant.
The type name of the mapping record or class is derived from the dataset name. Either it strips any prefix given in a list or it uses a regular expression to extract the type name (without the T) from the name (To be honest: I wasn’t able to think of a reasonable example for this).
As you see, that dialog even works when multiple datasets are selected. The created code is copied to the clipboard. As it only contains declarations and no implementation, it can simply be pasted wherever needed.
The field name constants and the TRecordFields descendant are realized as nested classes with hard coded type names. The record created from quEmployee with the options shown above looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
type [DBFields(mapAuto)] TEmployee = record public type Consts = record public const EmpNo = 'EmpNo'; LastName = 'LastName'; FirstName = 'FirstName'; PhoneExt = 'PhoneExt'; HireDate = 'HireDate'; Salary = 'Salary'; FullName = 'FullName'; end; public type [DBFields(mapAuto)] Fields = class(TRecordFields) private FEmpNo: TField; FLastName: TField; FFirstName: TField; FPhoneExt: TField; FHireDate: TField; FSalary: TField; FFullName: TField; public property EmpNo: TField read FEmpNo; property LastName: TField read FLastName; property FirstName: TField read FFirstName; property PhoneExt: TField read FPhoneExt; property HireDate: TField read FHireDate; property Salary: TField read FSalary; property FullName: TField read FFullName; end; public EmpNo: Integer; LastName: string; FirstName: string; PhoneExt: string; HireDate: TDateTime; Salary: Double; FullName: string; end; TEmployeeConsts = TEmployee.Consts; TEmployeeFields = TEmployee.Fields; |
At first sight the declaration of the nested Consts type may look a bit weird, but it turned out to simplify working with it. At the end all field references (field name constant, field reference and field value) share the same name.
For convenience I have added separate alias declarations for the nested Consts and Fields types.
Side note: The datasets in the example both have a calculated field created in their fields editors without adding all the other fields. This works with the datasets FieldOptions.AutoCreateMode set to acCombineAlways.
CSV Export
As it was only one small method and it acts directly on TDataSet (and multiple class helpers are potentially difficult) I added ExportToCSV to TDataSetHelper. It first appeared at this blog in Poor Man’s CSV Export. It may not be a valid replacement for complex CSV export scenarios, but it may be a quick solution to most of the simpler tasks.
So, that’s it for the moment on TDataSetHelper. Next time, while we just are at datasets, I will write about a new addition to CmonLib called TDataSense.
