TDataSetSQLStatement c esempio di “class helper” in Delphi.

Gli helper sono un modo per estendere una classe senza utilizzare l’ereditarietà, utile anche per i record che non consentono affatto l’ereditarietà.

La documentazione ufficiale è disponibile all’indirizzo: https://docwiki.embarcadero.com/RADStudio/Sydney/en/Class_and_Record_Helpers_(Delphi).

Spesso mi son trovato a creare del codice per esportare un dataset o un singolo record con i suoi valori in uno statement SQL, e tutte le volte ne ho scritto il codice. Di seguito ho creato un “class helper” per la classe dataset che vi consente di generare gli statement “insert/update/delete”.

Praticamente una volta aggiunta alle vostre unit i TDataSet descendant avranno i metodi “SQLStatement” e  “SQLStatements” disponibili per generare il codice SQL desiderato.

Se su un form abbiamo un dataset che si chiama “DSArticoli” e volessimo ottenere il codice di “Insert” per il record corrente basterebbe scrivere:

FDQuery1.SQLStatement(TSQLStatementType.SQLInsert,'ARTICOLI',[]);

Di seguito il codice della classe helper per i TDataSet:

unit uDataSetSSQLHelper;

interface
uses System.SysUtils, System.Classes, System.IOUtils,DB;

type
  TSQLStatementType = (SQLInsert,SQLUpdate,SQLDelete);


  TDataSetSQLHelper = class helper for TDataset
  private
    function dataTypeSupported(AFieldType : TFieldType) : Boolean;
    function formatFieldValue(AFld : TField) : String;
    function whereCondition(KeyFieldNames : TArray<string>; addWhereStmt : Boolean = false):String;
    function ckKeyFields(KeyFieldNames : TArray<string>) : boolean;
    function getInsertStatement(TableName: String;KeyFieldNames : TArray<string> = []) : String;
    function getUpdateStatement(TableName: String;KeyFieldNames : TArray<string> = []) : String;
    function getDeleteStatement(TableName: String;KeyFieldNames : TArray<string> = []) : String;
  public
    function SQLStatement(StatementType: TSQLStatementType; TableName: String;KeyFieldNames : TArray<string> = []) : String;
    function SQLStatements(StatementType: TSQLStatementType; TableName: String;KeyFieldNames : TArray<string> = []) : String;
  end;


implementation

{ TDataSetSQLHelper }



{ TDataSetSQLHelper }

function TDataSetSQLHelper.ckKeyFields(KeyFieldNames: TArray<string>): boolean;
var
  LKeyFieldName: string;

begin
 if not Self.Active then
  raise Exception.Create('Dataset is not active!!');

 result := true;
 for LKeyFieldName in KeyFieldNames do
  begin
   if Self.FindField(LKeyFieldName) = nil then
    begin
     result := false;
     raise Exception.Create('KeyFiled "' + LKeyFieldName + '" not found in dataset fields');
    end;
  end;

end;

function TDataSetSQLHelper.dataTypeSupported(AFieldType: TFieldType): Boolean;
begin
 Result := not(AFieldType in [
    ftBlob,
    ftMemo,
    ftGraphic,
    ftFmtMemo,
    ftParadoxOle,
    ftDBaseOle,
    ftTypedBinary,
    ftCursor,
    ftFixedChar,
    ftWideString,
    ftADT,
    ftArray,
    ftReference,
    ftDataSet,
    ftOraBlob,
    ftOraClob,
    ftVariant,
    ftInterface,
    ftIDispatch,
    ftOraTimeStamp,
    ftOraInterval,
    ftConnection,
    ftParams,
    ftStream,
    ftTimeStampOffset,
    ftObject]);
end;

function TDataSetSQLHelper.formatFieldValue(AFld: TField): String;
var
 FS : TFormatSettings;
begin
  FS.DecimalSeparator := '.';
  FS.CurrencyDecimals := 2;


  if AFld.IsNull then
    result := 'null'
  Else
   begin

      case AFld.DataType of
        ftUnknown: result := '''' + AFld.asString.Replace('''','''''') + '''';
        ftString: result := '''' + AFld.asString.Replace('''','''''') + '''';
        ftSmallint: result := AFld.asString;
        ftInteger: result := AFld.asString;
        ftWord: result := AFld.asString;
        ftBoolean: result := AFld.AsBoolean.ToString(True);
        ftFloat: result := FloatToStr(AFld.asFloat);
        ftCurrency: result := FloatToStr(AFld.asFloat);
        ftBCD: result := FloatToStr(AFld.asFloat);
        ftDate: result := '(cast(''' + FormatDateTime('mm/dd/yyyy',AFld.AsDateTime) + ''' as DATE))';
        ftTime: result := '(cast(''' + FormatDateTime('hh:mm:ss',AFld.AsDateTime) + ''' as TIME))';
        ftDateTime: result := '(cast(''' + FormatDateTime('mm/dd/yyyy hh:mm:ss',AFld.AsDateTime) + ''' as TIMESTAMP))';
        ftBytes: AFld.asString;
        ftVarBytes: AFld.asString;
        ftAutoInc: AFld.asString;

        ftLargeint: result := AFld.asString;

        ftGuid: result := AFld.asString;
        ftTimeStamp: result := '(cast(''' + FormatDateTime('mm/dd/yyyy hh:mm:ss',AFld.AsDateTime) + ''' as TIMESTAMP))';
        ftFMTBcd: FloatToStr(AFld.asFloat);
        ftFixedWideChar: result := '''' + AFld.asString.Replace('''','''''') + '''';
        ftWideMemo: result := '''' + AFld.asString.Replace('''','''''') + '''';

        ftLongWord: result := AFld.asString;
        ftShortint: result := AFld.asString;
        ftByte: result := AFld.asString;
        ftExtended: FloatToStr(AFld.asFloat);
        ftSingle:  FloatToStr(AFld.asFloat);
      end;
   end;

end;

function TDataSetSQLHelper.getDeleteStatement(TableName: String;
  KeyFieldNames: TArray<string>): String;
begin
 ckKeyFields(KeyFieldNames);
 result := 'delete from ' + TableName + #10#13 + whereCondition(KeyFieldNames,true);
end;

function TDataSetSQLHelper.getInsertStatement(TableName: String;
  KeyFieldNames: TArray<string>): String;
var
  I: Integer;
  LFieldList: String;
  LValueList: String;
begin
 LFieldList := '';
 LValueList := '';
 ckKeyFields(KeyFieldNames);
 result := 'insert into ' + TableName  + '(' + #10#13;

 for I := 0 to Self.FieldCount-1 do
  begin
   if dataTypeSupported(Self.Fields[I].DataType) then
    begin
     if LFieldList = '' then
       LFieldList := Self.Fields[I].FieldName
     else
       LFieldList := LFieldList + ',' + Self.Fields[I].FieldName;
    end;
  end;
 result := result + LFieldList +  ')values(' + #10#13;
 for I := 0 to Self.FieldCount-1 do
  begin
   if dataTypeSupported(Self.Fields[I].DataType) then
    begin
     if LValueList = '' then
       LValueList := formatFieldValue(Self.Fields[I])
     else
       LValueList := LValueList + ',' + formatFieldValue(Self.Fields[I]);
    end;
  end;

 result := result + LValueList + ')';

end;

function TDataSetSQLHelper.getUpdateStatement(TableName: String;
  KeyFieldNames: TArray<string>): String;
var
  I: Integer;
begin
 ckKeyFields(KeyFieldNames);

 Result := 'update ' + TableName + ' set ';
 for I := 0 to Self.FieldCount-1 do
  begin
   if dataTypeSupported(Self.Fields[I].DataType) then
    begin
      Result := Result + #1013 + Self.Fields[I].Name + ' = ' + formatFieldValue(Self.Fields[I]);
      if I < Self.FieldCount-1 then
       Result := Result + ' ,';
    end;
  end;

 Result := Result + #10#13 + whereCondition(KeyFieldNames);

end;

function TDataSetSQLHelper.SQLStatement(StatementType: TSQLStatementType;
  TableName: String; KeyFieldNames: TArray<string>): String;
begin
 case StatementType of
   SQLInsert: result := getInsertStatement(TableName,KeyFieldNames);
   SQLUpdate: result := getUpdateStatement(TableName,KeyFieldNames);
   SQLDelete: result := getDeleteStatement(TableName,KeyFieldNames);
 end;
end;




function TDataSetSQLHelper.SQLStatements(StatementType: TSQLStatementType;
  TableName: String; KeyFieldNames: TArray<string>): String;
begin
 Result := '';
 Self.First;
 while not Self.Eof do
  begin
    if Result = '' then
      Result := SQLStatement(StatementType,TableName,KeyFieldNames) + ';'
    Else
      Result := Result + #10#13 + SQLStatement(StatementType,TableName,KeyFieldNames) + ';';

    Self.Next;
  end;
end;

function TDataSetSQLHelper.whereCondition(
  KeyFieldNames: TArray<string>; addWhereStmt : Boolean = false): String;
var
  I: Integer;
  LFieldName: string;


 function addCondition(KeyFieldName : String) : String;
  begin
    result := '(' + KeyFieldName + ' = ' + formatFieldValue(Self.FieldByName(KeyFieldName)) + ')';
  end;


begin
 result := '';
 if (KeyFieldNames = nil)or(Length(KeyFieldNames) = 0) then
  begin
   for I := 0 to Self.FieldCount-1 do
    begin
     if dataTypeSupported(Self.Fields[I].DataType) then
     begin
       LFieldName := Self.Fields[I].FieldName;
       if result = '' then
        result := addCondition(LFieldName) + #10#13
       else
        result := result + 'and' + addCondition(LFieldName) + #10#13;
     end
    end
  end
 else
  begin
   for LFieldName in KeyFieldNames do
    begin
     if result = '' then
      result := addCondition(LFieldName) + #10#13
     else
      result := result + 'and' + addCondition(LFieldName) + #10#13;
    end
  end;

 if addWhereStmt then
  result := ' where (' + result + ')';
end;

end.