main.pas

ODAC+SDAC源代码

{
Insert - example of using SP with return Identity value as result of executing
Update - example of using SP with return arbitrary value as result of executing
Delete - example of using SP with return arbitrary value as parameter
}

unit Main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, MemDS, DBAccess, Grids, DBGrids, CRGrid, StdCtrls,
  ActnList, ExtCtrls, DBCtrls, MSAccess, SdacVcl;

type
  TMainForm = class(TForm)
    MSConnection: TMSConnection;
    MSQuery: TMSQuery;
    DataSource: TDataSource;
    scCreateInsert: TMSSQL;
    Panel1: TPanel;
    btOpen: TButton;
    btClose: TButton;
    DBNavigator1: TDBNavigator;
    btCreate: TButton;
    DBGrid1: TDBGrid;
    btDrop: TButton;
    scDrop: TMSSQL;
    scCreateUpdate: TMSSQL;
    scCreateDelete: TMSSQL;
    procedure btCreateClick(Sender: TObject);
    procedure btOpenClick(Sender: TObject);
    procedure btCloseClick(Sender: TObject);
    procedure MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
      StatementTypes: TStatementTypes; Params: TMSParams);
    procedure MSQueryAfterUpdateExecute(Sender: TCustomMSDataSet;
      StatementTypes: TStatementTypes; Params: TMSParams);
    procedure btDropClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation

{$R *.DFM}

procedure TMainForm.btCreateClick(Sender: TObject);
begin
  scCreateInsert.Execute;
  scCreateUpdate.Execute;
  scCreateDelete.Execute;
end;

procedure TMainForm.btDropClick(Sender: TObject);
begin
  scDrop.Execute;
end;

procedure TMainForm.btOpenClick(Sender: TObject);
begin
  MSQuery.Open;
end;

procedure TMainForm.btCloseClick(Sender: TObject);
begin
  MSQuery.Close;
end;

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then begin
    Params.ParamByName('EmployeeID').ParamType := ptResult;
    // Params.ParamByName('RETURN_VALUE').DataType was setted equal to Sender.FieldByName('EmployeeID').FieldType
  end;

  if stUpdate in StatementTypes then begin
    Params.ParamByName('RETURN_VALUE').ParamType := ptResult;
    Params.ParamByName('RETURN_VALUE').DataType := ftInteger; // Field 'RETURN_VALUE' does not present in Sender, so we need to set DataType manually

    Params.ParamByName('FullName').ParamType := ptOutput;
    Params.ParamByName('FullName').DataType := ftString; // Field 'FullName' does not present in Sender, so we need to set DataType manually
  end;

  if stDelete in StatementTypes then begin
    Params.ParamByName('Result').ParamType := ptOutput;
    Params.ParamByName('Result').DataType := ftInteger; // Field 'Result' does not present in Sender, so we need to set DataType manually
  end;
end;

procedure TMainForm.MSQueryAfterUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
var
  EmployeeID, ReturnedEmployeeID: integer;
begin
  if stUpdate in StatementTypes then begin
    EmployeeID := Sender.FieldByName('EmployeeID').AsInteger;
    ReturnedEmployeeID := Integer(Params.ParamByName('RETURN_VALUE').Value);

    if EmployeeID <> ReturnedEmployeeID then
      raise Exception.Create('Error on Update occured!');

    Caption := String(Params.ParamByName('FullName').Value);
  end;

  if stDelete in StatementTypes then begin
    EmployeeID := Params.ParamByName('EmployeeID').AsInteger;
    ReturnedEmployeeID := Integer(Params.ParamByName('Result').Value);

    if EmployeeID + 2 {see StoredProc SDAC_DeleteEmployees} <> ReturnedEmployeeID then
      raise Exception.Create('Error on Delete occured!');
  end;
end;

end.