Version 12.2 of the DevExpress VCL controls brings several improvements to the Server Mode feature of the ExpressQuantumGrid control. Both Advantage Database and PostgreSQL are now supported. There is also now support for in-place editing and banded table views when using Server Mode.
But one of the most welcome improvements, I think, is the new capability to specify custom SQL for the SELECT statement that the Server Mode data source components will use. When I last blogged about ExpressQuantumGrid Server Mode, the most vocal feedback was the need to specify custom SELECT statements. Until now this was only possible using Views within the underlying database. But starting with 12.2, DevExpress has introduced two new entries to the collection of Server Mode components to support using custom queries: TdxServerModeADOQueryDataSource and TdxServerModeDBXQueryDataSource. These are counterparts to the existing data source components that feature a SQL property for specifying the SELECT statement.
To see how easy this is to put into practice, download an open my sample from this blog post. Make sure you’ve also created a database and used the contained SQL scripts to create and populate the LotsaRows table.
Open Unit1 and select the Design tab. Delete the existing TdxServerModeADODataSource and replace it with a TdxServerModeADOQueryDataSource.
Setting up the TdxServerModeADOQueryDataSource component is very similar to setting up its non-query counterpart. For the Connection property select the existing ADOConnection1 component and for the SQLAdapter property, specify MSSQL Adapter. For the new SQL property, click the ellipsis button and enter the following SQL statement:
With the SQL statement specified, click the drop down arrow next to KeyFieldNames and select the ID column. Finally, set Active to True.
That’s it for setting up the TdxServerModeADOQueryDataSource. Now, select the Server Mode table view on the form and bind it to the TdxServerModeADOQueryDataSource using the DataController.DataSource property.
At this point you should see that the project is working again more or less as it did in my previous blog post. A million rows of data show, quickly, within the TcxGrid control, featuring fast grouping and sorting.
To actually see the new feature in practice, add a new TcxCheckBox to the main form, adjusting the grid control and giving it a proper caption.
And handle the Click event of the TcxCheckBox with the following code:
procedure TForm1.Column3CheckBoxClick(Sender: TObject); var SQL: string; begin //improve performance by disabling gridview updates during data operations cxGrid1DBTableView1.BeginUpdate; try dxServerModeADOQueryDataSource1.Active := False; //build SQL statement based on checkstate SQL := 'SELECT * FROM LotsaRows'; if Column3CheckBox.Checked then SQL := SQL + ' WHERE Column3 = 1'; dxServerModeADOQueryDataSource1.SQL.Text := SQL; dxServerModeADOQueryDataSource1.Active := True; finally cxGrid1DBTableView1.EndUpdate; end; end;
And that’s it! By swapping out the existing TdxServerModeADODataSource for its query-enabled counterpart, we can now specify a custom SQL SELECT statement and still benefit from “blazing fast” population, sorting, and grouping of one million rows of data.
You can download the updated Delphi sample source code here.