Gary's Delphi Client Dataset Based Application

• see also:
  • Delphi Programming Main Index

Features:

• allows your coding to be compartmentalised and ready to migrate to client and server multi-tier style apps
• allows your same code to create either BDE or ADO or potentially DBExpress versions
• allows updating of joined tables
• does not use master-detail datasets though
• potentially allows briefcase mode by saving to local files
• if your database generates its own autoincrementing key fields then you will need to modify that aspect
• there are many ways of doing this, but the following works well for me in a 24x7 multi-user application using Sybase
• works on Delphi 6 and upwards 

Main design:

• GUI:
  • MainForm - contains the TDatasource components remember to place dtmdlMainClient  in its uses clause
  • PasswordDlg form
• Client-side database code
  • dtmdlMainClient and dtmdlLUClient
    • 2 datamodules which hold all TClientDatasets and their associated TDatasetProviders which connect to their sql components in dtmdlMain and dtmdlLU - directly if all code is in the client exe, otherwise via a server connection interface such as a SOAPDatamodule.
    • dtmdlLUClient holds all the lookup tables but if app is small these could be in 
    • remember to put dtmdlLUClient and dtmdlClient (and dtmdlMain) in the uses clauses of dtmdlMainClient
    • remember to put dtmdlClient (and dtmdlLU in the uses clauses of dtmdlLUClient
    • place midas in the uses clauses to avoid having to deploy midas.dll
  • dtmdlClient
    • holds any variables the client needs which relate to the database
    • can hold a SOAPConnection if intend to use client server architecture
    • this is optional, if you are never planning to convert the application to a true client and server application then this datamodule is not needed
• Server-side database code
  • this code I save in a separate folder so that I can potentially have separate BDE, ADO or maybe dbexpress versions with no change to code, just different file locations in the project file for each version
  • when using persistent fields, you need to make sure each version creates the same field types - unfortunately this is not possible with DBExpress as it has TSQLTimeStamp fields instead of TDateTime fields - I wish the Delphi architects would address this so one could easily convert apps to dbexpress.
  • dtmdlDBConnection
    • holds the database connection components and any code related to the database connection
  • dtmdlMain and dtmdlLU
    • holds the SQL components and the SQL statements, parameters and persistent fields (make sure their provider flags are set the same as in the TClientDatasets)
    • to ensure the same field types are used between BDE and ADO, I generally use the following code for any integer fields:
      • Convert(int, fldname) as fldname
    • no code is needed for these components unless you wish to create audit trails of database access or edits
    • remember to put dtmdlDBConnection in the uses clauses of these

TClientDataset usage:

• if you wish TClientDatasets to automatically handle edits properly, you should really use persistent fields and ensure their provider flags are set to pfInUpdate and pfInWhere (and pfInKey if the field is the key field), and that these are identical to the persistent fields in the SQL component.
• I set the datasetProvider and its associated dataset component's UpdateMode to upWhereKeyOnly
  • I don't use upWhereAll or upWhereChanged as these sometimes fail to find the record if field data do not match and I do not want to confuse users with reconciliation dialogs when another user has edited the record before they posted their edits - I just over-ride anyone elses edits or just before the user starts editing, check that the record has not changed - not perfect but generally the least confusing for most user situations - you may wish to use upWhereAll or upWhereChanged and reconciliation dialogs though.
• TClientDataset.BeforeOpen:
  • if you use any persistent fields which are lookup fields then add this code:
    • var i:Integer;
      begin
      for i := 0 to dataset.fieldcount-1 do
      if dataset.fields[i].fieldKind = fkLookup then
      with dataset.fields[i].LookUpDataset do
      if active = false
      then open;
      end;
• TClientDataset.AfterOpen:
  • set our temporary key variable to default value:
    • cdsRosterStaff_key := -1;
  • optionally place code to generate any cloneCursor datasets if you use them, eg:
    • cdsClone.CloneCursor(Dataset as TCustomClientDataset, false);
• TClientDataset.BeforeEdit:
  • optionally abort this when you don't want the user to edit this particular record
• TClientDataset.OnNewRecord:
  • initialise field data, but in particular set the incremental key field - for this, I prefer to have a private variable for the datamodule which holds a temporary key field value (a negative value so I know it is a temporary one!) for that table:
    • private
      { Private declarations }
      cdsRosterStaff_key:Integer; //remember to set to -1 on creation of datamodule
    • dataset.Edit;
      dataset.FieldByName('keyfldname').asInteger := dec(cdsRosterStaff_key);
• TClientDataset.AfterPost:
  • ensure edits are applied to server:
    • if ((dataset as TClientdataset).ChangeCount > 0) then
      (dataset as TClientdataset).ApplyUpdates(-1);
• TClientDataset.BeforeClose:
  • ensure edits are applied to server:
    • if (dataset as TClientdataset).state in [dsEdit,dsInsert,dsDelete] then (dataset as TClientdataset).post;
  • optionally, save a copy to a local file as well (the following uses variables UsingLocal(if using local files), PDir (program directory), Conn (connection name)):
    • var cds_Filename:String;
      begin
      with Dataset as TClientDataset do
      begin
      cds_Filename := dtmdlClient.PDir+dtmdlClient.Conn.Title+'\cds\'+Dataset.name+'.cds';
      if Dataset.state = dsEdit then Dataset.post;
      //now to handle if updates were not able to be applied in the AfterPost event
      try
      if dtmdlClient.AllowUpdates then
      if not dtmdlClient.UsingLocal then
      if changeCount > 0 then
      applyUpdates(-1);
      finally
      if dtmdlClient.UseLocalFiles then
      try SaveToFile(cds_Filename);
      finally end;
      //will not be able to refresh the cds if changeCount > 0 thus force cancel
      if not dtmdlClient.UsingLocal then
      if changeCount > 0 then //if still problems then let's cancel Updates so can get new data
      (Dataset as TClientDataset).CancelUpdates;
      end; //end try
      end;
      end;
• TClientDataset.BeforeApplyUpdates:
  • try to re-establish server connection if teh computer became disconnected (may not work!):
    • if not dtmdlDBConn.dtbs1.Connected then
      dtmdlDBConn.dtbs1.Open;
• TDatasetProvider.OnGetData:
  • close any cursors on the database to prevent locks caused by BDE:
    • with (Sender as TDatasetProvider).dataset do
      if active then close;
• TDatasetProvider.BeforeUpdateRecord:
  • OK, this is a little bit complicated here as the database here has a separate table which holds the incremental key field values and thus can't just set it to max(key)+1, and I have elected here to hold temporary key field values and their new ones in a separate TClientDataset (cdsTempKeys):
    • var Tablename, IndexFieldName, NonNullField, NonNullField2:String; newSequ:Integer;
      begin
      f Sender = prvdrRosterStaff then
      begin
      TableName := 'RosterStaff';
      IndexFieldName := 'StaffID';
      NonNullField := 'DisplayName';
      NonNullField2 := '';
      end
      else raise Exception.create('Provider update handler not set for provider '+ (Sender as TDatasetProvider).name);
      {Some dos and don’ts for this event:
      
      Don’t use Edit and Post methods to modify and save data in the record 
      Use TField NewValue property to change the value of a field 
      Use TField OldValue to get original value 
      Use VarIsNull(Field.NewValue) to determine if the field is NULL 
      Use VarIsEmpty(Field.NewValue) to determine if the field has not changed since it was retrieved
      }
      case UpdateKind of
      ukInsert:
      try
      try
      if NonNullField <> '' then
      if VarIsNull(DeltaDS.fieldByName(NonNullField).newValue)
      then begin
      raise Exception.create('prvdrRosterItemsBeforeUpdateRecord error: NonNullField '+ NonNullField +' cannot be null');
      Applied := true; exit; end; // do not update this record as not linked to a patient attendance record
      if NonNullField2 <> '' then
      if VarIsNull(DeltaDS.fieldByName(NonNullField2).newValue)
      then begin
      raise Exception.create('prvdrRosterStaffBeforeUpdateRecord error: NonNullField2 '+ NonNullField2 +' cannot be null');
      Applied := true; exit; end; // do not update this record as not linked to a lookup table record
      begin //need to get next index field value
      newSequ := IncrementPrimaryKey(Tablename, IndexFieldName)+1; //now uses lookup table to get max Sequ
      if newSequ = -1 then
      begin
      raise Exception.create('prvdrRosterStaffBeforeUpdateRecord error: IncrementPrimaryKey failed on table'+ Tablename);
      exit; //error setting new SEQU in lookup table so let's abort this
      end;
      if not cdsTempKeys.active then
      begin
      cdsTempKeys.createDataset;
      cdsTempKeys.open;
      end;
      cdsTempKeys.InsertRecord([DeltaDS.FieldByName(IndexFieldName).newValue, newSequ, (Sender as TDatasetprovider).name]);
      DeltaDS.FieldByName(IndexFieldName).newValue := newSequ;
      end;
      except on E:Exception do //if key violation then hopefully retry rather than generate perpetuating key violation errors which crash system
      begin
      if tablename = 'RosterStaff' then
      begin
      dec(cdsRosterStaff_Key);
      DeltaDS.FieldByName(IndexFieldName).newValue := cdsRosterStaff_Key;
      end
      else DeltaDS.FieldByName(IndexFieldName).newValue := -1;
      ShowMessage('Exception on Insert Update table '+tablename+' newSequ ='+IntToStr(newSequ)+#13+E.Message); 
      end;
      end; //end try
      finally
      
      //Applied := true;
      //in case of key violation from error in setting newSequ, ensure we don't keep
      end;
      ukDelete: if DeltaDS.FieldByName(IndexFieldName).newValue < 2 //this is a temporary clientdataset index so has not et been sent to server
      then Applied := true; //so skip delete 
      end;
• alternatively, if the default Delphi handling of edits is not adequate (eg. updating joined tables), I just call this procedure I created which handles simple joined tables and uses a separate TClientDataset (cdsUpdate) to actually process the updates manually but only handles edits not inserts or deletes:
  • procedure ga_CDSUpdateServer(cdsUpdate:TClientDataset; table, joinedTable, keyField,joinedKeyField, ExcludeFields, JoinedFieldPrefix:String; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;
    var Applied: Boolean);
    var i:Integer; str:string; temp_Applied:Boolean;
    begin
    temp_Applied := false;
    // currently does NOT handle inserts or deletes!
    //this will update server without checking for concurrency issues & thus does not use reconciliation form
    if UpdateKind = ukModify then
    begin
    with cdsUpdate do
    begin
    if active then close;
    CommandText := '';
    Params.clear;
    Str := '';
    CommandText := 'update '+table+' set ';
    for i := 0 to DeltaDS.fields.count-1 do
    begin
    if DeltaDS.fields[i].fieldkind = fkData then
    if (pos(DeltaDS.fields[i].fieldName+';',ExcludeFields) = 0) then //Excludefields must have format: 'field1;field2;'
    if (JoinedFieldPrefix <> '') and (pos(JoinedFieldPrefix,DeltaDS.fields[i].fieldName) = 0) then //Exclude fields from joined tables eg. PP2_
    if not VarIsEmpty(DeltaDS.fields[i].newvalue) then //if changed
    begin
    if str <> '' then str := str+', ';
    str := str + DeltaDS.fields[i].origin + ' = :'+ DeltaDS.fields[i].fieldName;
    if DeltaDS.fields[i].dataType = ftDateTime then //as ADO ODBC Sybase not writing back time component
    begin
    params.CreateParam(ftString, DeltaDS.fields[i].fieldName,ptInput);
    if DeltaDS.fields[i].newValue = null then
    params.paramByName(DeltaDS.fields[i].fieldName).value := null
    else params.paramByName(DeltaDS.fields[i].fieldName).value := FormatDateTime('mm/dd/yyyy hh:nn',DeltaDS.fields[i].newValue);
    end
    else
    begin
    params.CreateParam(DeltaDS.fields[i].dataType, DeltaDS.fields[i].fieldName,ptInput);
    params.paramByName(DeltaDS.fields[i].fieldName).value := DeltaDS.fields[i].newValue;
    end;
    end;
    end;
    if str <> '' then
    begin
    CommandText := CommandText + ' ' + str;
    CommandText := CommandText + ' ' + 'where '+keyField+' = '+IntToStr(DeltaDS.fieldByName(keyField).oldValue);
    try
    execute;
    close;
    temp_Applied := true;
    except on E:EDatabaseError do
    begin
    ShowMessage('Unable to save changes to database!'+#13+E.message);
    Applied := false;
    end;
    end;//end try
    end;
    //now update any joined fields:
    if active then close;
    CommandText := '';
    Params.clear;
    Str := '';
    CommandText := 'update '+joinedtable+' set ';
    for i := 0 to DeltaDS.fields.count-1 do
    begin
    if DeltaDS.fields[i].fieldkind = fkData then
    if (JoinedFieldPrefix <> '') and (pos(JoinedFieldPrefix,DeltaDS.fields[i].fieldName) > 0) then //only do fields from joined tables eg. PP2_
    if not VarIsEmpty(DeltaDS.fields[i].newvalue) then //if changed
    begin
    if str <> '' then str := str+', ';
    str := str + DeltaDS.fields[i].origin + ' = :'+ DeltaDS.fields[i].fieldName;
    if DeltaDS.fields[i].dataType = ftDateTime then //as ADO ODBC Sybase not writing back time component
    begin
    params.CreateParam(ftString, DeltaDS.fields[i].fieldName,ptInput);
    if DeltaDS.fields[i].newValue = null then
    params.paramByName(DeltaDS.fields[i].fieldName).value := null
    else
    params.paramByName(DeltaDS.fields[i].fieldName).value := FormatDateTime('mm/dd/yyyy hh:nn',DeltaDS.fields[i].newValue);
    end
    else
    begin
    params.CreateParam(DeltaDS.fields[i].dataType, DeltaDS.fields[i].fieldName,ptInput);
    params.paramByName(DeltaDS.fields[i].fieldName).value := DeltaDS.fields[i].newValue;
    end;
    end;
    end;
    if str <> '' then
    begin
    CommandText := CommandText + ' ' + str;
    CommandText := CommandText + ' ' + 'where '+joinedkeyField+' = '+IntToStr(DeltaDS.fieldByName(keyField).oldValue);
    try
    execute;
    close;
    Applied := true;
    except on E:EDatabaseError do
    begin
    ShowMessage('Unable to save changes to database!'+#13+E.message);
    Applied := false;
    end;
    end;//end try
    end
    else Applied := temp_Applied; //if no joined fields needed updating.
    end; //with cdsUpdate do
    end;
    end;
• here is the function called above and this uses a separate TClientDataset (cdsGetMaxID):
  • function TdtmdlCISClient.IncrementPrimaryKey(tablename, IndexFieldName:String):Integer;
    var str: String;
    begin
    try
    //HAS uses table F_Sequ to store the latest Sequ value (primary key) for each table & increments this when inserting a new record
    //the primary key of this table is SQ_TABLE which is an uppercase of the table name
    //this function increments the value in the lookup table AND ASSUMES the function calling it will then use this value to actually insert the new record
    if cdsGetMaxID.active then cdsGetMaxID.close;
    str := 'Select max('+IndexFieldName+') from '+tablename;
    cdsGetMaxID.CommandText := str;
    cdsGetMaxID.Open;
    result := cdsGetMaxID.fields[0].asInteger;
    cdsGetMaxID.close;
    if prvdrGetMaxID.dataset.active
    then prvdrGetMaxID.dataset.close;
    except on E:Exception do
    begin
    if (tablename = 'RosterItems') then
    result := -1;
    ShowMessage('Error incrementing index for tablename '+tablename+#13+E.Message);
    end;
    end;
    end;