remotestoredproc.pas

DbAnyWhere开发套件

unit RemoteStoredProc;

interface

uses
  SysUtils, Classes,remotedataset,dialogs,DB,tools;

type
  TRemoteStoredProc = class;
  TProcItem= class(TCollectionItem)
  private
    FNotNull:Tstrings;
    FDepend:Tstrings;
    //调用过程名
    FProcName:string;
    //参数字段列表
    FParamsFieldList:Tstrings;
    //参数所属数据集
    FParamsDataSet:TRemoteDataSet;  
    procedure setprocname(value:string);
    procedure setparams(value:Tstrings);
    procedure setparamsdataset(value:Tremotedataset);
    procedure setnotnull(value:Tstrings);
    procedure setdepend(value:Tstrings);
  protected
    { Protected declarations }
  public
    constructor Create(Collection: TCollection);override;
    destructor Destroy; override;
    { Public declarations }
  published
    property ProcName:string read FProcName write setprocname;
    property ParamsFieldList:Tstrings read FParamsFieldList write setparams;
    property ParamsDataSet:TRemotedataSet read FParamsDataSet write setparamsdataset;
    property NotNull:Tstrings read FNotNull write setnotnull;
    property Depend:Tstrings read Fdepend write setdepend;  
    { Published declarations }
  end;
  TProcItemClass = class of TProcItem;
  TProcItems = class(TCollection)
  private
    FRemoteStoredProc:TRemoteStoredProc;
    function GetProcItem(Index:integer):TProcItem;
    procedure setProcItem(Index:integer;Value:TProcItem);
    { Private declarations }
  protected

    { Protected declarations }
  public
    constructor Create(RemoteStoredProc: TRemoteStoredProc; ProcItemClass: TProcItemClass);
    property Items[Index: Integer]: TProcItem read GetProcItem write setProcItem; default;
    { Public declarations }
  published

    { Published declarations }
  end;
  TRemoteStoredProc = class(TComponent)
  private
    FProcItems:TProcItems;
    FRemoteDataSet:TRemoteDataSet;
    //前面加的sql
    FBeforeProcSQL:Tstrings;
    //后面的sql
    FAfterProcSQL:Tstrings;
    procedure setProcitems(value:TProcItems);
    procedure setremotedataset(value:TRemoteDataSet);
    procedure setbsql(value:Tstrings);
    procedure setAsql(value:Tstrings);
    procedure doproc(value:integer);
    { Private declarations }
  protected
    function  CreateProcItems: TProcItems; dynamic;
    { Protected declarations }
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure ExecProc;
    procedure OpenProc;
    { Public declarations }
  published
    property ProcItems:TProcItems read FProcItems write setprocitems;
    property RemoteDataSet:TRemoteDataSet read FRemoteDataSet write setremotedataset;
    property BeforeProcSQL:Tstrings read FBeforeProcSQL write setbsql;
    property AfterProcSQL:Tstrings read FAfterProcSQL write setasql;
    { Published declarations }
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('DbAnyWhere', [TRemoteStoredProc]);
end;
constructor TRemoteStoredProc.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FProcItems:=self.CreateProcItems;
  FBeforeProcSQL:=TstringList.Create;
  FAfterProcSQL:=Tstringlist.Create;
end;
destructor TRemoteStoredProc.Destroy;
begin
  inherited Destroy;
  Freeandnil(FProcItems);
  Freeandnil(FBeforeProcSQL);
  FreeandNil(FAfterProcSQL);
end;
constructor TProcItems.Create(RemoteStoredProc: TRemoteStoredProc; ProcItemClass: TProcItemClass);
begin
  inherited Create(ProcItemClass);
  FRemoteStoredProc:=RemoteStoredProc;
end;
function TProcItems.GetProcItem(Index:integer):TProcItem;
begin
  Result:= TProcItem(inherited Items[Index]);
end;
procedure TProcItems.SetProcItem(Index:integer;Value:TProcItem);
begin
  Items[Index].Assign(Value);
end;
procedure TRemoteStoredProc.setProcitems(value:TProcItems);
var
   i:integer;
begin
  Fprocitems.Clear;
  for i:=0 to value.Count-1 do
  begin
    fprocitems.Add;
  end;
end;
function  TRemoteStoredProc.CreateProcItems: TProcItems;
begin
  Result := TProcItems.Create(Self, TProcItem);
end;
constructor TProcItem.Create(Collection: TCollection);
begin
  inherited Create(Collection);
  FParamsFieldList:=Tstringlist.Create;
  FNotNull:=Tstringlist.Create;
  FDepend:=Tstringlist.Create;
end;
destructor TProcItem.Destroy;
begin
  inherited Destroy;
  freeandnil(FParamsFieldList);
  FreeAndNil(FNotNull);
  FreeAndNil(FDepend);
end;
procedure TProcItem.setprocname(value:string);
begin
  FProcName:=value;
end;
procedure TProcItem.setparams(value:Tstrings);
begin
  FParamsFieldList.Assign(value);
end;
procedure TProcItem.setparamsdataset(value:Tremotedataset);
begin
  FParamsDataSet:=value;
end;
procedure TRemoteStoredProc.ExecProc;
begin
  self.doproc(2);
end;
procedure TRemoteStoredProc.DoProc(value:integer);
var
   i,j,k,h:integer;
   paramsstr:string;
   sqlstr:string;
   templist:Tstringlist;
   tempboolean:boolean;
begin
  //showmessage(Self.FBeforeProcSQL.Text);
  //执行无数据集返回的过程
  //判断
  if self.FRemoteDataSet=nil then
  begin
    showmessage('请设置函数执行组件！');
    abort;
  end;
  if self.FProcItems.Count=0 then
  begin
    showmessage('没有任何可以执行的函数！');
    abort;
  end;
  //判断参数集和过程名
  for i:=0 to self.FProcItems.Count-1 do
  begin
    if self.FProcItems[i].FParamsDataSet=nil then
    begin
      showmessage('没有设置参数数据集！');
      abort;
    end;
    if length(trim(self.FProcItems[i].FProcName))=0 then
    begin
      showmessage('没有设置执行函数名！');
      abort;
    end;
    //判断集合中是否有数据
    self.FProcItems[i].FParamsDataSet.Edit;
    self.FProcItems[i].FParamsDataSet.Post;
    if (not self.FProcItems[i].FParamsDataSet.Active)or
       (self.FProcItems[i].FParamsDataSet.RecordCount=0)  then
    begin
      showmessage('空数据无法执行函数！');
      abort;
    end;
  end;
  //判断是否有不允许为空的
  for k:=0 to self.FProcItems.Count-1 do
  begin
    self.FProcItems[k].FParamsDataSet.First;
    for i:=0 to self.FProcItems[k].FParamsDataSet.RecordCount-1 do
    begin
      for j:=0 to self.FProcItems[k].FNotNull.Count-1 do
      begin
        if (self.FProcItems[k].FParamsDataSet.fieldbyname(
           self.FProcItems[k].FNotNull.Names[j]).IsNull)or
            (length(trim(self.FProcItems[k].FParamsDataSet.fieldbyname(
           self.FProcItems[k].FNotNull.Names[j]).AsString))=0)then
        begin
          if self.FProcItems[k].FDepend.IndexOfName(self.FProcItems[k].FNotNull.Names[j])>-1 then
          begin
            templist:=Tstringlist.Create;
            templist.Text:=self.FProcItems[k].FDepend.Values[self.FProcItems[k].FNotNull.Names[j]];
            templist.Text:=replace(templist.Text,'&',char(13),false);
            tempboolean:=true;
            for h:=0 to templist.Count-1 do
            begin
              if pos('^',templist.Strings[h])>0 then //取反值
              begin
                templist.Strings[h]:=replace(templist.Strings[h],'^','',false);
                if self.FProcItems[k].FParamsDataSet.fieldbyname(templist.Strings[h]).AsBoolean then
                begin
                  tempboolean:=false;
                  break;
                end;
              end else
              begin
                if not self.FProcItems[k].FParamsDataSet.fieldbyname(templist.Strings[h]).AsBoolean then
                begin
                  tempboolean:=false;
                  break;
                end;
              end;
            end;
            if tempboolean then
            begin
              //if self.FProcItems[k].FParamsDataSet.FieldByName('')
              if self.FProcItems[k].FParamsDataSet.RecordCount>1 then
              begin
                showmessage('第'+inttostr(self.FProcItems[k].FParamsDataSet.RecNo)+
                '行的['+self.FProcItems[k].FNotNull.ValueFromIndex[j]+']不能够为空！');
                abort;
              end else
              begin
                showmessage('['+self.FProcItems[k].FNotNull.ValueFromIndex[j]+']不能够为空！');
                abort;
              end;
            end;
          end else
          begin
            //if self.FProcItems[k].FParamsDataSet.FieldByName('')
            if self.FProcItems[k].FParamsDataSet.RecordCount>1 then
            begin
              showmessage('第'+inttostr(self.FProcItems[k].FParamsDataSet.RecNo)+
              '行的['+self.FProcItems[k].FNotNull.ValueFromIndex[j]+']不能够为空！');
              abort;
            end else
            begin
              showmessage('['+self.FProcItems[k].FNotNull.ValueFromIndex[j]+']不能够为空！');
              abort;
            end;
          end;
        end;
      end;
      self.FProcItems[k].FParamsDataSet.Next;
    end;
  end;
  //showmessage(Self.FBeforeProcSQL.Text);
  //开始执行过程,先循环过程体
  self.FRemoteDataSet.Remoteclose;
  //showmessage(Self.FBeforeProcSQL.Text);
  if length(trim(Self.FBeforeProcSQL.Text))>0 then
    self.FRemoteDataSet.RTSQL.Assign(Self.FBeforeProcSQL);
  for i:=0 to self.FProcItems.Count-1 do
  begin
    //对数据集进行循环
    sqlstr:='';
    self.FProcItems[i].FParamsDataSet.DisableControls;
    self.FProcItems[i].FParamsDataSet.First;
    for j:=0 to self.FProcItems[i].FParamsDataSet.RecordCount-1 do
    begin
      sqlstr:=' exec '+self.FProcItems[i].FProcName+' ';
      //循环拼凑生成参数
      paramsstr:='';
      for k:=0 to self.FProcItems[i].FParamsFieldList.Count-1 do
      begin
        if self.FProcItems[i].FParamsDataSet.fieldbyname(
           self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).IsNull then
           {paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+
                   '='''+self.FProcItems[i].FParamsDataSet.fieldbyname(
                   self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).AsString+''','}
           paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+'=null,'
        else
        begin
          if self.FProcItems[i].FParamsDataSet.fieldbyname(
             self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).DataType
             in [ftdate,ftdatetime,fttime,ftstring,ftmemo,ftFixedChar,ftWideString] then
          begin
            paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+
                     '='''+self.FProcItems[i].FParamsDataSet.fieldbyname(
                     self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).AsString+''','
          end else if self.FProcItems[i].FParamsDataSet.fieldbyname(
             self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).DataType
             in [ftboolean] then
          begin
            if self.FProcItems[i].FParamsDataSet.fieldbyname(
                     self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).AsBoolean then
              paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+'=1,'
            else
              paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+'=0,';
          end else
          begin
            paramsstr:=paramsstr+self.FProcItems[i].FParamsFieldList.Names[k]+
                     '='+self.FProcItems[i].FParamsDataSet.fieldbyname(
                     self.FProcItems[i].FParamsFieldList.ValueFromIndex[k]).AsString+','
          end;
        end;
      end;
      paramsstr:=copy(paramsstr,1,length(paramsstr)-1);
      sqlstr:=sqlstr+paramsstr;
      self.FRemoteDataSet.RTSQL.Add(sqlstr);
      self.FProcItems[i].FParamsDataSet.Next;
    end;
    self.FProcItems[i].FParamsDataSet.EnableControls;
  end;
  //showmessage(self.FRemoteDataSet.RTSQL.Text);
  //abort;
  //self.FRemoteDataSet.RTSQL.SaveToFile('c:\test.txt');
  if length(trim(Self.FAfterProcSQL.Text))>0 then
     self.FRemoteDataSet.RTSQL.Add(Self.FAfterProcSQL.Text);
  if value=2 then
    self.FRemoteDataSet.RemoteExeSQL
  else if value=1 then
    self.FRemoteDataSet.RemoteOpen;
end;
procedure TRemoteStoredProc.OpenProc;
begin
  self.doproc(1);
end;
procedure TRemoteStoredProc.setremotedataset(value:TRemoteDataSet);
begin
  FRemoteDataSet:=value;
end;
procedure TProcItem.setnotnull(value:Tstrings);
begin
  FNotNull.Assign(value);
end;
procedure TProcItem.setdepend(value:Tstrings);
begin
  Fdepend.Assign(value);
end;
procedure TRemoteStoredProc.setbsql(value:Tstrings);
begin
  FBeforeProcSQL.Assign(value);
end;
procedure TRemoteStoredProc.setAsql(value:Tstrings);
begin
  FAfterProcSQL.Assign(value);
end;

end.