sgcGrid, a Data-Aware DBGrid Replacement for Delphi
sgcGrid is a data-aware Delphi grid that binds to the TDataSet your DBGrid already uses, then groups, totals, filters and exports the same rows.
sgcGrid is a data-aware Delphi grid that binds to the TDataSet your DBGrid already uses, then groups, totals, filters and exports the same rows.
Your database code keeps working. Only the grid and the link between the grid and the dataset change.
sgcGrid binds to a FireDAC TFDQuery, a TClientDataSet or any other dataset. On top of what TDBGrid shows, it groups and totals rows, filters with a filter row, column filters and a find panel, exports to Excel and updates changed rows in place, on VCL and on FireMonkey.
A TDBGrid reads its dataset through a TDataSource. In sgcGrid, a TsgcGridSource_DataSet takes the place of the TDataSource: set its Grid and DataSet, make it Active, and the rows appear. The dataset can be a FireDAC query, a TClientDataSet or any other TDataSet descendant.
KeyField the grid is synchronised rather than reloaded: changed rows are updated in place and flash, new keys are appended and missing keys deleted.LiveUpdates makes the grid follow every Post, Delete and Refresh of the dataset.AutoColumns creates the columns from the fields when the grid has none. Designed columns are kept and matched by FieldName.DisableControls and its current record is restored. Closing the dataset keeps the rows.BooleanFields shows a flag stored as text or a number, such as S and N, as a check box.uses
SysUtils, Classes, DB, FireDAC.Comp.Client,
sgcUICore_Canvas, sgcUICore_Theme, sgcUICore_Table, sgcGrid_Columns,
sgcGrid_Data, sgcGrid_Format, sgcGrid_Render, sgcGrid_IO,
sgcGrid_Source_DataSet, sgcGrid_XLSX, sgcGrid_VCL;
procedure TOrdersForm.FormCreate(Sender: TObject);
begin
// the query that fed the TDBGrid stays as it is
qryOrders.Open;
// TsgcGridSource_DataSet takes the place of the TDataSource
FSource := TsgcGridSource_DataSet.Create(Self);
FSource.Grid := sgcVCLGrid1;
FSource.DataSet := qryOrders;
FSource.KeyField := 'ID'; // changed rows update in place
FSource.AutoColumns := True; // columns from the fields
FSource.LiveUpdates := True; // follow Post, Delete and Refresh
FSource.Active := True;
end;
The source is a component, so the same binding can live in the form file and be set in the Object Inspector:
object SourceDB: TsgcGridSource_DataSet
DataSet = cdsProducts
Grid = GridDB
KeyField = 'ID'
end
The same source binds a FireMonkey TsgcFMXGrid with the same code. Binding, grouping, summaries and filtering are part of every edition, the free Delphi Community edition included.
The rows come from the dataset; grouping, summaries, filters and export run in the grid.
GroupBy or by dragging a header to the group panelSummaryTypeOnApplySort passes the sort as field names, for example for the IndexFieldNames of a FireDAC querySaveToXLSX writes an Excel workbook with no external library, groups as a collapsible outline and group totals as SUBTOTAL formulas (Registered edition)
Changed cells flash when a row updates.
Two group levels, detail rows and tinted cells.
Filter, then sort on several columns.
Bound to a TClientDataSet through TsgcGridSource_DataSet.
procedure TOrdersForm.SetupGrid;
begin
// group by region; users drag more columns to the group panel
sgcVCLGrid1.ShowGroupPanel := True;
sgcVCLGrid1.GroupBy(['Region']);
// totals in the footer and in every group row
sgcVCLGrid1.Columns.FindByField('Amount').SummaryType := gsuSum;
sgcVCLGrid1.Columns.FindByField('ID').SummaryType := gsuCount;
sgcVCLGrid1.ShowFooter := True;
// a filter row under the header and the find panel
sgcVCLGrid1.ShowFilterRow := True;
sgcVCLGrid1.ShowFindPanel := True;
end;
procedure TOrdersForm.btnExcelClick(Sender: TObject);
begin
// Registered edition: the sheet keeps the groups and the totals
sgcVCLGrid1.SaveToXLSX('orders.xlsx', 'Orders');
end;
The stock VCL TDBGrid shows and edits a dataset. This table lists what each grid does out of the box, without extra code.
| Feature | TDBGrid | sgcGrid | sgcGrid edition |
|---|---|---|---|
| Bind a TDataSet | Through a TDataSource | TsgcGridSource_DataSet with DataSet, Grid and KeyField | All |
| Designed columns | Columns of TColumn | Columns matched by FieldName, or AutoColumns | All |
| Changed rows updated in place, with a flash | No | Yes, by KeyField | All |
| Sort by a header click | Code in OnTitleClick | Built in, with OnApplySort for the dataset | All |
| Grouping and group panel | No | GroupBy, ShowGroupPanel | All |
| Footer and group summaries | No | SummaryType gsuSum, gsuCount, gsuAverage, gsuMin, gsuMax | All |
| Filter row and column filters | No | ShowFilterRow, ShowFilterButtons | All |
| Find panel | No | ShowFindPanel, FindOnlyMatches | All |
| Bands, best fit and saved layouts | No | Bands, BestFitColumns, SaveLayoutToFile, AutoSaveLayout | All |
| Colours per cell without drawing code | Code in OnDrawColumnCell | FormatRules, OnGetCellStyle, and OnCustomDrawCell for custom painting | All |
| Multi-row selection | dgMultiSelect, SelectedRows | SelectionMode gsmMultiRow, GetSelectedKeys | All |
| Edit and post to the dataset | Yes, with dgEditing | WriteBack posts every edit to its record, SyncCursor keeps the current record in step | Registered |
| Excel export | No | SaveToXLSX, to a file or a stream | Registered |
| Tables too large to load | No | Server mode with TsgcGridServerProvider_DataSet, one page at a time | Registered |
| FireMonkey | No, VCL only | TsgcFMXGrid on Windows, macOS, iOS and Android | All |
| .NET | No | WinForms, WPF and Avalonia grids | All |
See the full sgcGrid feature matrix, the benchmarks and the sgcGrid comparison.
With WriteBack on, the source writes every grid edit to the record of its row. It locates the record by KeyField and posts the value. When the post fails, for example in a BeforePost check, the record is cancelled and the cell keeps its old value.
SyncCursor keeps the current record and the focused row together, so a TDBNavigator and TDBEdit controls on the same dataset follow the grid, and the grid follows themAllowInsert: Ins appends and posts a record, and OnNewRecord of the dataset fills the defaultsApplyFilter makes the dataset keep only the rows the grid shows, so DB-aware controls and reports on it see the filtered rowsWriteBack, SyncCursor, insert, delete, paste and undo are part of the Registered edition. The trial includes them for evaluation.
Read the DataSet Editing help topicThree steps: import the columns at design time, replace the properties and events, delete the DB grid. The data stays in the same TDataSet.
TsgcVCLGrid next to the TDBGrid.TsgcGridSource_DataSet linked to the dataset of the DataSource, copy the bounds (Parent, Align, Anchors, TabOrder and more), copy the event handlers, and clear the columns of the grid first.The import copies FieldName, Title.Caption to Caption, Width (converted to DIPs with the PixelsPerInch of the form), Visible, Alignment, ReadOnly to Editable and PickList. From code, sgcGridImportDBGrid in the unit sgcGrid_Import_VCL does the same at run time.
| TDBGrid | sgcGrid |
|---|---|
| TDBGrid | TsgcVCLGrid (unit sgcGrid_VCL) |
| DataSource | TsgcGridSource_DataSet: DataSet, Grid, KeyField, then Active := True (unit sgcGrid_Source_DataSet) |
| Columns, TColumn.FieldName | Columns, Columns[i].FieldName |
| ReadOnly, Options dgEditing | ReadOnly of the grid and WriteBack of the source (Registered) |
| dgRowSelect | SelectionMode := gsmRow |
| dgMultiSelect, SelectedRows | SelectionMode := gsmMultiRow, SelectedRows, GetSelectedKeys |
| dgIndicator | RowHeader := [grhIndicator], also grhNumbers and grhCheckBox |
| dgColLines, dgRowLines | GridLinesV, GridLinesH |
| OnDrawColumnCell | OnCustomDrawCell; for colours only, OnGetCellStyle or FormatRules |
| OnTitleClick | OnHeaderClick; the grid sorts by itself |
| OnColEnter, OnColExit | OnSelectionChange, with SelectedCol |
The TDBGrid migration kit has the full member map, before and after code from the MigrateFromDBGrid demo, which compiles with Delphi 7 and Delphi 13 (Win32 and Win64), and the known differences. Other kits are on the sgcUI migration page.
One grid model drives the VCL and the FireMonkey grid, so a VCL form and a FireMonkey form share the binding code.
| Target | Component | Platforms |
|---|---|---|
| Delphi 7 to 13, VCL | TsgcVCLGrid | Win32, and Win64 from XE2 |
| Delphi XE6 to 13, FireMonkey | TsgcFMXGrid | Windows, macOS, iOS and Android |
| C++ Builder 2007 to 13 | TsgcVCLGrid, TsgcFMXGrid | Generated .hpp headers and static libraries |
| .NET Framework 4.7.2, .NET 8, 9 and 10 | TsgcWinFormsGrid, WPF and Avalonia grids | WinForms, WPF, Avalonia |
The import of TDBGrid columns runs in the VCL form designer. FireMonkey forms bind the dataset with the same TsgcGridSource_DataSet.
DataSet binding with designed columns matched by FieldName, lookup fields, cell, row and multi-row selection, row header, custom draw, Ctrl+C, group panel, footer and group summaries, filter row, Excel style column filter, find panel, bands, best fit, and JSON layout save and load. The free Delphi Community edition ships sgcGrid.
Two way editing into the dataset (WriteBack), cursor sync (SyncCursor), insert, delete, paste and undo, Excel export, the card view and server mode for very large tables. sgcGrid is sold alone with Single, Team and Site licenses and full source code, and is included in sgcUI Standard, Professional, Enterprise and All-Access.
See sgcGrid pricing or download the trial, which includes every Registered feature for evaluation.
Drop a TsgcVCLGrid next to the TDBGrid, right click it and choose Import columns from DB grid. The import copies the columns, creates a TsgcGridSource_DataSet linked to the same dataset, copies the bounds and the event handlers, and reports the events to port by hand. Then delete the TDBGrid. The migration kit maps every property and event.
TDBGrid is a VCL control. sgcGrid has a FireMonkey grid, TsgcFMXGrid, for Delphi XE6 to 13 on Windows, macOS, iOS and Android, and the same TsgcGridSource_DataSet binds it to any TDataSet with the same code as the VCL grid.
Yes. Call GroupBy with the field names, or let users drag a column header to the group panel. Set SummaryType on a column (sum, count, average, minimum or maximum) and turn on ShowFooter: the totals appear in the footer and in every group row. Grouping and summaries are part of every edition, Community included.
Bind the dataset to sgcGrid and call SaveToXLSX with a file name or a stream. The workbook needs no external library and keeps what the user sees: the visible columns in display order with their widths and number formats, the frozen header, groups as a collapsible outline and group totals as SUBTOTAL formulas. Excel export is part of the Registered edition; the Community edition exports JSON and CSV.
Yes. A TFDQuery or TFDTable is a TDataSet, so TsgcGridSource_DataSet binds it like a TClientDataSet. OnApplySort passes the sort of the grid in the form of FireDAC IndexFieldNames, for example Name;Date:D. For a table too large to load, server mode (Registered) with TsgcGridServerProvider_DataSet reads one page at a time and lets the database sort, filter, count and group with SQL, run by FireDAC or another query component.
Yes, in the Registered edition. With WriteBack the source posts every edit to the record of its row, located by KeyField, and SyncCursor keeps the current record and the focused row together, so a TDBNavigator and TDBEdit controls on the same dataset stay in step. In the Community edition the grid shows the dataset read only.