SQL Executing

Although EntityDAC is an ORM framework and eliminates the need for direct access to database data, for cases where it is still necessary there are several methods for direct interaction with the database, which are implemented in TEntityConnection.

Transaction management

Since TEntityConnection provides methods to access the database, certain functionality for transaction management is present. The StartTransaction, CommitTransaction and RollbackTransaction methods allow to control transactions.

var
  Connection: TEntityConnection;
begin
  // create and initialize the connection
  // ...
  // begin the transaction
  Connection.StartTransaction;
  try
    // ...
    // commit the transaction
    Connection.CommitTransaction;
  except
    // rollback the transaction in case of an error
    Connection.RollbackTransaction;
  end;
end;

SQL query

The ExecuteSQL method allows to easily execute a SQL statement, which does not return data.

var
  Connection: TEntityConnection;
begin
  // create and initialize the connection
  // ...
  // execute the simple SQL statement
  Connection.ExecuteSQL('insert into EMP(ENAME) values(''Sample'')');
end;

In more complex case, the SQL statement can be parametrized (for example when there is need to return parameters as the result of a SQL statement execution).

// add necessary units to be able to use the TDBParams class
uses
  SQLDialect, EntityTypes;

var
  Connection: TEntityConnection;
  Params: TDBParams;
begin
  // create and initialize the connection
  // ...
  // create and fill query parameters
  Params := TDBParams.Create;
  try
    with Params.Add do begin
      Name := 'ret_param';
      ParamType := ptOutput;
      DataType := dbInteger;
    end;
    Connection.ExecuteSQL ('insert into EMP(ENAME) values(''Sample'') returning EMPNO into :ret_param', Params);
    ShowMessage('Return = ' + Params[0].Value.AsString);
  finally
    Params.Free;
  end;
end;

Stored procedure

A stored procedure can be executed using the ExecuteStoredProc method. The procedure parameters are passed as when executing of the parametrized query.

uses
  SQLDialect, EntityTypes;

var
  Connection: TEntityConnection;
  Params: TDBParams;
begin
  // create and initialize the connection
  // ...
  // create and fill procedure parameters
  Params := TDBParams.Create;
  try
    with Params.Add do begin
      Name := 'proc_param';
      ParamType := ptInput;
      DataType := dbInteger;
    end;
    Connection.ExecuteStoredProc('some_procedure', Params);
  finally
    Params.Free;
  end;
end;

SQL script

For executing a number of sequential SQL statements, the ExecuteScript method is used. The SQL script can be passes either as the string parameter or as TStrings. The script cannot be parametrized.

var
  Connection: TEntityConnection;
  Script: TStrings;
begin
  // create and initialize the connection
  // �
  // create and fill the script
  Script := TStringList.Create;
  try
    Script.Add('insert into EMP(ENAME) values(''Sample'');');
    Script.Add('insert into EMP(ENAME) values(''Sample 1'');');
    Connection.ExecuteScript(Script);
  finally
    Script.Free;
  end;
end;