批量执行SQL

代码：

unit DHibernateBatchSQL;

interface

uses
   Classes, SysUtils, db, ADODB;

type
   TOnException = procedure(Sender: TObject; E: Exception) of object;
   TOnFinishOne = procedure(Sender: TObject; FinishedSQL: string) of object;
   TDHibernateBatchSQL = class(TComponent)
   private
     FConnection: TADOConnection;
     FBatchSQL: TStringList;
     FBeforeExecute: TNotifyEvent;
     FAfterExecute: TNotifyEvent;
     FOnException: TOnException;
     FOnFinishOne: TOnFinishOne;
     FAbout: string;
     function GetBatchSQL: TStrings;
     procedure SetBatchSQL(const Value: TStrings);
   protected
     FSQLList: TStringList;
     procedure ExtractBatchSQLToSQLList;
   public
     constructor Create(AOwner: TComponent); override;
     destructor Destroy; override;
     procedure Execute;
   published
     property About: string read FAbout write FAbout;
     property Connection: TADOConnection read FConnection write FConnection;
     property BatchSQL: TStrings read GetBatchSQL write SetBatchSQL;
     property BeforeExecute: TNotifyEvent read FBeforeExecute write FBeforeExecute;
     property AfterExecute: TNotifyEvent read FAfterExecute write FAfterExecute;
     property OnException: TOnException read FOnException write FOnException;
     property OnFinishOne: TOnFinishOne read FOnFinishOne write FOnFinishOne;
   end;

implementation

{ TDHibernateBatchSQL }

constructor TDHibernateBatchSQL.Create(AOwner: TComponent);
begin
   inherited Create(AOwner);
   FConnection := nil;
   FBatchSQL := TStringList.Create;
   FSQLList := TStringList.Create;
end;

destructor TDHibernateBatchSQL.Destroy;
begin
   FConnection := nil;
   FBatchSQL.Free;
   FSQLList.Free;
   inherited Destroy;
end;

procedure TDHibernateBatchSQL.Execute;
var
   i                  : Integer;
   hql                : string;
begin
   if FConnection = nil then
     raise Exception.Create('No ADOConnection found!');
   if not FConnection.Connected then
     FConnection.Open();
   if Assigned(BeforeExecute) then
     BeforeExecute(Self);
   ExtractBatchSQLToSQLList;
   //
   with TADOQuery.Create(nil) do
   begin
     // todo: exceute the sql
     for i := 0 to FSQLList.Count - 1 do
     begin
       hql := FSQLList[i];
       // check whether select, insert, update, delete included.
       if Pos('select', hql) > 0 then
       begin
         // do select
         Close;
         SQL.Text := hql;
         try
           Open;
           if Assigned(OnFinishOne) then
             OnFinishOne(self, hql);
         except
           on E: Exception do
             if Assigned(OnException) then
               OnException(Self, E);
         end;
       end
       else
         if (Pos('insert', hql) > 0) or (pos('update', hql) > 0) or (Pos('delete', hql) > 0) then
         begin
           // do insert, update, delete
           Close;
           sql.Text := hql;
           try
             ExecSQL;
             if Assigned(OnFinishOne) then
               OnFinishOne(self, hql);
           except
             on E: Exception do
               if Assigned(OnException) then
                 OnException(Self, E);
           end;
         end
         else
         begin
           // not matched! exception throws.
           if Assigned(OnException) then
             OnException(self, exception.Create('SQL not contains select, insert, update or delete.'));
         end;
     end;
     close;
     Free;
   end;
   if Assigned(AfterExecute) then
     AfterExecute(Self);
end;

procedure TDHibernateBatchSQL.ExtractBatchSQLToSQLList;
var
   str                : string;
begin
   FSQLList.Clear;
   str := FBatchSQL.Text;
   // remove the CR_LF
   str := StringReplace(str, #13#10, EmptyStr, [rfReplaceAll, rfIgnoreCase]);
   // split the string to list
   FSQLList.Delimiter := '|';
   FSQLList.DelimitedText := str;
end;

function TDHibernateBatchSQL.GetBatchSQL: TStrings;
begin
   Result := FBatchSQL;
end;

procedure TDHibernateBatchSQL.SetBatchSQL(const Value: TStrings);
begin
   FBatchSQL.Assign(Value);
end;

end.