To page or not to page…comparing multi-platform grids using paging

We still appreciate Delphi for its ease to hook up visual components to data that is stored in any kind of database. Connection, Query, Datasource, TDBGrid, press F9.

As a Delphi developer you do not really have to think about what is going on in the background. You have even the ease and comfort that edit, insert, append and delete operations are available.

Huge datasets and multi-platform considerations
However, as soon as the dataset grows large, TDBGrid is not the best solution anymore. There are plenty of other options available from third parties, to name a few very well-known components:

• VCL DataGrid, DevExpress
• VirtualTreeView,  Jam Software
• TDBAdvGrid, TTMSFNCGrid TMS Software
• InfoPower, Woll2woll Software

With recent versions of Delphi, we have to consider multi-platform. A mobile app or Mac-compatible app is often a requirement by the customer when it comes to browsing content.

The list above shortens significantly. So far my research only yielded few component sets that offer FireMonkey support:

• TTMSFMXGrid, TMSSoftware
• TTMSFNCGrid, TMSSoftware
• Woll2woll Firepower

There is no typo in the list above. TMS Software offers a component that is truly multi-platform.The FNC component can be used in VCL and FMX all the same. Thus, this would be the obvious choice if you will have to put a lot of work in browsing data comfortably.

FNC comes with a disadvantage – so far. There is no common data-binding framework. Especially, as Lazarus is also in the mix for FNC, you cannot use the TDatasource or Live Bindings approach in VCL and FireMonkey. You will have to evaluate if this restriction rules out the component. However, the pure multi-platform applicability may weigh higher on the scorecard. If not, the usage of TDBAdvGrid in VCL and switching over to TTMSFMXGrid in your FireMonkey solution might be the solution you are looking for as the basic principles of the VCL control have been carried over to the FMX world. No adjustments to the datasource will be needed if you use FireDAC either.

Paging – realizing the limitations
Now back to the initial reason for writing this blog post: Paging.

We do not need to consider TDBGrid or TGrid as both components are bound to one framework. There is extensive of both grids performance on the net. No need to add anything to that. Furthermore, both grids do not offer any property to adjust their behavior.

Going back to the example from my last blog post, I have a TDBAdvGrid in a VCL forms application that I use to append, edit and delete data. Data is bound using a TDatasource which refers to a FireDAC query.

The number of records is very limited, so I will be fine with either setting of the Page mode — at least one could think so. It is always dangerous just accepting defaults on the one hand and also to use fancy features without realizing the impact on the other.

Let’s have a look what the grid does when paging is set to on or off.

• PageMode = true. The grid navigates the datasource and retrieves data for the records that need to be shown. The datasource remains connected, the grid keeps the current row in sync with the current record in the datasource.
• PageMode = false. The grid opens the datsources and retrievs all the records to display. The datasource is then no longer used and the current row of the grid is not being synced with the datasource.

Reading those two bullet points, I am still astounded that I never really realized the impact this has on the way you have to design your user experience.

Let’s consider these aspects:

• Sorting
• Grouping, Sums, Averages
• Edit, Append, Delete

Sorting
Can you sort the grid according to one column if the grid uses paging? No. Remember, the grid did not retrieve all values from the datasource. In order to use sorting without any additional coding, out-of-the box, you need to set Paging to true.

Grouping
Basically, the same reasoning as for sorting. You cannot group or calculate sums for data if you do not have access to the whole set of values.

Edit, Append, Delete
In order to edit data you need a direct connection to the datasource. This is only the case if you do use Paging. So if you set Paging to false, you lose the ability to edit data.

Solutions
It would be a pretty lame blog post if I did not have any solutions to the dilemma. Right now the situation would present itself that you cannot sort your grid if you want to edit the data as well. That would be a pretty huge limitation.

Thus, I am going to present a solution that lifts that restriction.

In order to edit the data, we need the connection to the dataset. Thus, PageMode set to true is the only viable option in this case. The trick is to refresh the dataset whenever the user wants to display a different sort order. Thus, the grid refetches the data it needs to show according to the new sort order.

TFDQuery makes this very easy. Instead of simply stating

SELECT * FROM myFancyTable;

we add an order-by-clause:

SELECT * FROM myFancyTable ORDER BY field;

Just a word of caution when it comes to using parameters in this case. Do not try to make ‘field’ in the order-by-clause a parameter. It will fail as FireDAC will provide it as a value and not a field. Strangely enough it will work during design-time. Thus, to provide the complete SQL statement at run-time is the only viable solution.

The grid offers an event that allows us to allow and disallow sorting of a specific column when it is clicked.

procedure TFrmPacks.gridPacksCanSort(Sender: TObject; ACol: Integer; var DoSort: Boolean); 
var
 lSelField : TField; 

begin 
 DoSort := false;
 
 // toggle sort order
 if gridPacks.SortSettings.Direction = sdAscending then
 begin
   gridPacks.SortSettings.Direction := sdDescending
 end else 
 begin
   gridPacks.SortSettings.Direction := sdAscending;
 end; 
 
 lSelField := gridPacks.Columns[ ACol ].Field;

 if (lSelField.FieldKind = fkLookup ) then
 begin
   lSelField := dsPacks.DataSet.FieldByName( lSelField.KeyFields );
 end;
 
 dmDataModel.SortPacksUIBy(lSelField, gridPacks.SortSettings.Direction = sdDescending);
end;

My grid is called “gridPacks” and at first look, we disallow any sorting by setting “DoSort” to false. Yes, pecicely. The grid is not supposed to sort. The grid is only supposed to display the current record and the records around it according to the current paging window. The sort operation is handled by the connected datasource.
Thus, we determine the sort order and we determine the field that is associated with the column that has been clicked. This piece of code takes one additional fact into consideration: lookup fields. Lookup fields do not exist in the underlying datasource. This is a painful restriction when offering  editing as we cannot join tables in the SELECT-query. As a way to at least be able to sort according to the column, the underlying key field is provided. This will not yield an alphabetic sort order according to the value of the lookup value, but at least some sort of order that the same lookup values are grouped together.

My datamodule “dmDataModel” offers a method that changes the sort order of the query connected to the grid:

procedure TdmDataModel.SortPacksUIBy(aField: TField; isDescending : Boolean);
begin 
  if Assigned( aField ) then 
  begin 
    qryPacksUI.Close; 
    qryPacksUI.SQL.Text := 'select * from packs order by ' + aField.FieldName; 
    if isDescending then 
    begin
      qryPacksUI.SQL.Add( 'DESC'); 
    end; 
    qryPacksUI.Active := true; 
  end; 
end;

Please pay special attention to the fact that the VCL form (UI layer) does not directly refer to the data query! Even if you make changes to the query or the underlying connection, your user interface will not be affected!

“SortPacksUIBy” is pretty straight forward. If a field has been provided, close the query and change its statement according to the parameters. Finally, reopen.

After reopening the query, the grid will receive a notification by TDatasource to refresh its values and a new sort order has been established.