CRUD
TsgcHTMLComponent_CRUD: the list, search, paging, create and edit form, delete confirmation and write path of one table as a single component, over a TDataSet or your own SQL, in Delphi, C++ Builder and .NET.
TsgcHTMLComponent_CRUD: the list, search, paging, create and edit form, delete confirmation and write path of one table as a single component, over a TDataSet or your own SQL, in Delphi, C++ Builder and .NET.
The page every business application writes again for each table, as one component. Give it a key field and a route prefix, then bind a TDataSet or hand it an adapter of six methods over your own SQL. ShowList answers the markup, and what the page posts back goes through ProcessAction.
TsgcHTMLComponent_CRUD, with TsgcHTMLCRUDAdapter and TsgcHTMLCRUDAdapter_DataSet in the same unit
The list with search and pager, the create and edit form, and the delete confirmation
Delphi, C++ Builder, .NET
Set KeyField and RoutePrefix, bind an active DataSet and call ShowList(search, page). For your own SQL, give the component an adapter with SetAdapter instead. Route what the page posts back through ProcessAction.
uses
Classes, sgcHTML_Component_CRUD;
var
oCRUD: TsgcHTMLComponent_CRUD;
vHTML: string;
begin
oCRUD := TsgcHTMLComponent_CRUD.Create(nil);
try
oCRUD.CRUDID := 'customers';
oCRUD.Title := 'Customers';
oCRUD.RoutePrefix := '/customers';
oCRUD.KeyField := 'ID';
oCRUD.PageSize := 15;
oCRUD.Actions := [caCreate, caEdit, caDelete, caView, caExport];
oCRUD.VersionField := 'UPDATED_AT'; // optimistic locking
oCRUD.OnValidate := CustomersValidate;
// an active dataset: the columns are read off it
oCRUD.DataSet := qryCustomers;
vHTML := oCRUD.ShowList('', 1); // search text, page number
finally
oCRUD.Free;
end;
end;
// The rules that are about your data. Add a line per problem: nothing is
// written and the form comes back with the messages.
procedure TServer.CustomersValidate(Sender: TObject;
aOperation: TsgcHTMLCRUDOperation; const aKey: string;
aValues, aErrors: TStrings);
begin
if Length(Trim(aValues.Values['NAME'])) < 3 then
aErrors.Add('The name needs at least three characters.');
end;
// A message from the browser carries action, crud and key with the form fields
if oCRUD.ProcessAction(vAction, vKey, oFields, vHTML) then
aResponse := vHTML; // the markup to swap into the page
// Or over your own SQL: an adapter of six methods
type
TCustomersAdapter = class(TsgcHTMLCRUDAdapter)
public
function Locate(const aKey: string): Boolean; override;
function Read(const aKey: string; aValues: TStrings): Boolean; override;
function List(const aSearch: string; aPage, aPageSize: Integer;
aRows: TStrings): Integer; override;
function Insert(aValues: TStrings; var aKey: string;
aErrors: TStrings): Boolean; override;
function Update(const aKey: string; aValues, aErrors: TStrings): Boolean;
override;
function Delete(const aKey: string; aErrors: TStrings): Boolean; override;
end;
oCRUD.SetAdapter(TCustomersAdapter.Create(oCRUD)); // the component owns it// includes: sgcHTML_Component_CRUD.hpp
TsgcHTMLComponent_CRUD *oCRUD = new TsgcHTMLComponent_CRUD(NULL);
try
{
oCRUD->CRUDID = "customers";
oCRUD->Title = "Customers";
oCRUD->RoutePrefix = "/customers";
oCRUD->KeyField = "ID";
oCRUD->PageSize = 15;
oCRUD->Actions = TsgcHTMLCRUDActions() << caCreate << caEdit << caDelete
<< caView << caExport;
oCRUD->VersionField = "UPDATED_AT"; // optimistic locking
oCRUD->OnValidate = CustomersValidate;
// an active dataset: the columns are read off it
oCRUD->DataSet = qryCustomers;
String html = oCRUD->ShowList("", 1); // search text, page number
}
__finally
{
delete oCRUD;
}
// The rules that are about your data. Add a line per problem: nothing is
// written and the form comes back with the messages.
void __fastcall TServer::CustomersValidate(TObject *Sender,
TsgcHTMLCRUDOperation aOperation, const String aKey,
TStrings *aValues, TStrings *aErrors)
{
if (aValues->Values["NAME"].Trim().Length() < 3)
aErrors->Add("The name needs at least three characters.");
}
// A message from the browser carries action, crud and key with the form fields
String html;
if (oCRUD->ProcessAction(action, key, fields, html))
response = html; // the markup to swap into the page
// For your own SQL, derive from TsgcHTMLCRUDAdapter, override its six methods
// (Locate, Read, List, Insert, Update, Delete) and call oCRUD->SetAdapter().using System.Collections.Generic;
using esegece.sgcWebSockets;
var crud = new TsgcHTMLComponent_CRUD();
crud.CRUDID = "customers";
crud.Title = "Customers";
crud.RoutePrefix = "/customers";
crud.KeyField = "ID";
crud.PageSize = 15;
crud.Actions = TsgcHTMLCRUDActions.caCreate | TsgcHTMLCRUDActions.caEdit
| TsgcHTMLCRUDActions.caDelete | TsgcHTMLCRUDActions.caView
| TsgcHTMLCRUDActions.caExport;
crud.VersionField = "UPDATED_AT"; // optimistic locking
// The key travels with the page: it is in neither the list nor the form
var id = crud.Columns.Add();
id.FieldName = "ID";
id.InList = false;
id.InForm = false;
var name = crud.Columns.Add();
name.FieldName = "NAME";
name.Caption = "Name";
name.Required = true;
name.MaxLength = 120;
// There is no TDataSet: read a DataTable, an IList<T> or a table behind a DbConnection
crud.SetAdapter(new TsgcHTMLCRUDAdapter_DataTable(crud, customersTable));
// The rules that are about your data
crud.OnValidate += (object sender, TsgcHTMLCRUDOperation op, string recordKey,
List<string> values, List<string> errors) =>
{
if (TsgcHTMLComponent_CRUD.ValueOf(values, "NAME").Trim().Length < 3)
errors.Add("The name needs at least three characters.");
};
string html = crud.ShowList("", 1); // search text, page number
// A message from the browser carries action, crud and key with the form fields
if (crud.ProcessAction(action, key, fields, out string reply))
response = reply; // the markup to swap into the pageThe members you reach for most often.
TsgcHTMLCRUDAdapter is the write path, with six virtual methods: Locate, Read, List, Insert, Update and Delete. TsgcHTMLCRUDAdapter_DataSet drives the bound dataset, and SetAdapter gives the component one of your own and takes ownership. List answers one page as tab separated lines, the key as the last cell, and returns how many rows the whole set has.
There is no TDataSet, so the port ships three adapters: TsgcHTMLCRUDAdapter_DataTable over a DataTable, TsgcHTMLCRUDAdapter_List<T> over any IList<T> and TsgcHTMLCRUDAdapter_DbConnection over a table behind a DbConnection, with every value sent as a parameter. Declare the Columns: they are not read off the table.
ShowList(search, page), ShowNew, ShowEdit(key), Save(key, values, html) and DeleteRecord(key, html) each answer the markup the host swaps in. ProcessAction routes the posted crudList, crudNew, crudEdit, crudSave and crudDelete to them, and answers False for any other action so a page can hold several components.
Actions is a set of caCreate, caEdit, caDelete, caView and caExport, and a page with only caView is a report. EditMode is cemModal or cemPage, ConfirmDelete asks before a delete, PageSize defaults to 25 and never goes above 500, and SearchFields lists the fields the search box looks in, separated by semicolons.
A TsgcHTMLCRUDColumn has FieldName, Caption, Visible, ReadOnly, Required, InList, InForm, Width and MaxLength. With none declared, the component reads them off the active dataset: the caption from DisplayLabel, the required flag and the length, and the key is shown but never edited. LoadColumnsFromDataSet does it on demand.
A required column that is empty and a value longer than MaxLength are refused first. Then OnValidate is asked: add a line per problem to aErrors and nothing is written, the form comes back with the messages. OnApplyUpdates runs before the adapter, and setting aHandled makes the write yours. OnApplied fires after a write went through, to log it or refresh whoever else is looking at the list.
Name a version field in VersionField. A save compares what the browser sends with what is stored, and refuses the write when somebody else changed the record in between, so the second person to press Save is not overwriting the first.
Details holds the child lists of a record. Each item names another TsgcHTMLComponent_CRUD in CRUD, the field of the child that holds the master key in MasterField, and a Caption. They render under the form while a record is being edited. SetMaster(field, value) narrows a list to one master record.
With caExport the toolbar shows an Export link to RoutePath('export.csv'). Answer that route with GetExportCSV(search): it exports the search the list is showing, up to 500 rows, not the whole table. Every value is quoted, so a comma or a line break cannot break the file.
Every key arrives from the browser. ShowEdit, Save and DeleteRecord ask the adapter to Locate it before anything is edited or deleted, so changing a number in a URL never reaches another record. Actions is enforced too: a page without caDelete refuses a delete even when a forged form posts one. Cells and fields are escaped, so a record that holds markup is shown as text.
The search box, New, Edit, Delete and the form are forms marked data-sgc-ws-send, each with hidden action, crud and key fields, and the host hands them to ProcessAction. A message over the socket carries no path and never reaches the router, so authorise it yourself. In Delphi, TsgcHTMX_Engine_Server.MessageSession answers the session behind the handshake cookies, or nil. For HTTP, RoutePath builds the paths a host declares on the engine router: /customers, /customers/new, /customers/{id}/edit, /customers/save and /customers/{id}/delete.
Create it in code, as above, or use the wizard in the Delphi IDE: Tools › New sgcHTML CRUD page lists the datasets on the open form, reads the fields of the one you pick and adds a configured component to the form, with the two wiring lines on the clipboard. It needs Delphi 10.4 or later. Outside the IDE, the sgcHTMLGen command line tool writes the source of the page in Object Pascal or in C#, from the table, the route prefix and the fields, each written as name:flag, for example ID:key or NAME:req:60.
Part of sgcHTML, which is sold independently of sgcWebSockets. The unit compiles where SGC_HTML is defined, which sgcVer.inc does for the HTML pack on every platform except Android and iOS. The demo is Demos\60.HTML\01.RunTime\02.AdminCRUD, where the customers area is built on this component next to the hand written page it replaced.
| Online HelpUsage guide for CRUD pages, the adapter and the write path. | Open | |
| All sgcHTML ComponentsBrowse the full feature matrix of 80+ components. | Open | |
| Download Free TrialThe 30-day trial ships the 60.HTML demo projects. | Open | |
| PricingSingle, Team and Site licenses with full source code. | Open |