SQLLite Datensatz hinzufügen, wenn nicht vorhanden

[Sir Rufo]

Ich würde mir das Leben nicht so schwer machen. Das Beispiel ist zwar für FireDAC, das Konzept sollte sich aber problemlos auf alle anderen DB-Frameworks umsetzen lassen.

Delphi-Quellcode:

unit Unit1;

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client;

const
  SQL_QUERY_MAX = 1;
  SQL_QUERY: array [0 .. SQL_QUERY_MAX] of string = (
    {0} 'SELECT id FROM foo WHERE val=:val',
    {1} 'INSERT INTO foo (val) VALUES(:val)' );

type
  TDataModule1 = class( TDataModule )
    FDConnection1: TFDConnection;
    FDQuery1: TFDQuery;
  private
    FQueries: array [0 .. SQL_QUERY_MAX] of TFDQuery;
    function GetQuery( const Index: Integer ): TFDQuery;
  protected
    property FooGetQuery: TFDQuery index 0 read GetQuery;
    property FooAddQuery: TFDQuery index 1 read GetQuery;
  public
    function GetFooId( const Val: string ): Integer;
    function AddFoo( const Val: string ): Integer;
    function GetFooIdOrAdd( const Val: string ): Integer;
  end;

var
  DataModule1: TDataModule1;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}
{ TDataModule1 }

function TDataModule1.AddFoo( const Val: string ): Integer;
var
  LQry: TFDQuery;
begin
  LQry := FooAddQuery;
  LQry.ParamByName( 'val' ).Value := Val;
  LQry.ExecSQL;
  Result := LQry.Connection.GetLastAutoGenValue( 'id' );
end;

function TDataModule1.GetFooId( const Val: string ): Integer;
var
  LQry: TFDQuery;
begin
  LQry := FooGetQuery;
  LQry.ParamByName( 'val' ).Value := Val;
  LQry.Open;
  try
    if LQry.Eof
    then
      Result := 0
    else
      Result := LQry.Fields[0].Value;
  finally
    LQry.Close;
  end;
end;

function TDataModule1.GetFooIdOrAdd( const Val: string ): Integer;
begin
  Result := GetFooId( Val );
  if Result = 0
  then
    Result := AddFoo( Val );
end;

function TDataModule1.GetQuery( const Index: Integer ): TFDQuery;
begin
  if FQueries[index] = nil
  then
    begin
      Result := TFDQuery.Create( Self );
      Result.Connection := FDConnection1;
      Result.SQL.Text := SQL_QUERY[index];
      FQueries[Index] := Result;
    end
  else
    Result := FQueries[Index];
end;

end.