解析delta得到sql语句的函数

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs,ADODB,DBClient,db;

type
  TForm1 = class(TForm)
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function vartosql(value: variant): string;
begin
  if varisnull(value) then
    Result := 'NULL'
  else
    case vartype(value) of
      vardate:
        Result := Quotedstr(Datetimetostr(vartodatetime(value)));
      varString, varOlestr:
        Result := Quotedstr(Trim(Vartostr(value)));
      varboolean:
        begin
          if value then
            result := '1'
          else
            result := '0';
        end;
    else
      Result := quotedstr(Trim(Vartostr(value)));
    end;
end;

function gensqls(AdoCon:TADOConnection; pdelta: OleVariant; const ptablename, pkeyfields: WideString): WideString;
var
i, j: integer;
s1, s2: string;

Cmdstr: string;

FieldList, Keylist: TstringList;

Cdsupdate: TClientDataSet;

sqlstr: WideString;

ado: TADOQuery;

begin

if varisnull(pdelta) then

Exit;

Cdsupdate:=TClientDataSet.Create(nil);

Cdsupdate.data:=pdelta;

if not Cdsupdate.Active then

Cdsupdate.Open;

try

FieldList:=TstringList.Create;

Keylist:=TstringList.Create;

Keylist.Delimiter:=',';

Keylist.DelimitedText:=pkeyfields;

ado:=TADOQuery.Create(nil);

ado.Connection:=AdoCon;

ado.sql.Text:='select * from '+ptablename+' where 1=0';

ado.Open;

ado.GetFieldNames(FieldList);

ado.Free;

for i:=1 to FieldList.Count do

if Cdsupdate.FindField(FieldList[i-1])<>nil then

Cdsupdate.FindField(FieldList[i-1]).tag:=1;

FieldList.Free;

if Cdsupdate.RecordCount>0 then

begin

Cdsupdate.First;

s1:='';

s2:='';

while not Cdsupdate.Eof do

begin

Cmdstr:='';

case Cdsupdate.UpdateStatus of

usUnmodified: //?原?据行取得修改?件

begin

s2:='';

for j:=1 to Keylist.Count do

begin

if s2='' then

s2:=Keylist[j-1]+'='+vartosql(Cdsupdate[Keylist[j-1]])

else

s2:=s2+' and '+Keylist[j-1]+'='+vartosql(Cdsupdate[Keylist[j-1]]);

end;

end;

usModified:

begin

s1:='';

for i:=1 to Cdsupdate.FieldCount do

begin

if (not Cdsupdate.Fields[i-1].isNull)and(Cdsupdate.Fields[i-1].tag=1) then

begin

if s1='' then

s1:=Trim(Cdsupdate.Fields[i-1].FieldName)+' = '+vartosql(Cdsupdate.Fields[i-1].value)

else

s1:=s1+','+Trim(Cdsupdate.Fields[i-1].FieldName)+' = '+vartosql(Cdsupdate.Fields[i-1].value);

end;

end;

if s1<>'' then

begin

Cmdstr:=' update '+ptablename+' set '+s1+' where '+s2;

end;

end;

usInserted:

begin

s1:='';

s2:='';

for i:=1 to Cdsupdate.FieldCount do

if (not Cdsupdate.Fields[i-1].isNull)and(Cdsupdate.Fields[i-1].tag=1) then

begin

if s1='' then

begin

s1:=Trim(Cdsupdate.Fields[i-1].FieldName);

s2:=vartosql(Cdsupdate.Fields[i-1].value);

end

else

begin

s1:=s1+','+Trim(Cdsupdate.Fields[i-1].FieldName);

s2:=s2+','+vartosql(Cdsupdate.Fields[i-1].value);

end;

end;

if s1<>'' then

begin

Cmdstr:=' Insert into '+ptablename+'('+s1+') values('+s2+')';

end;

end;

usDeleted:

begin

s2:='';

for j:=1 to Keylist.Count do

begin

if s2='' then

s2:=Keylist[j-1]+'='+vartosql(Cdsupdate[Keylist[j-1]])

else

s2:=s2+' and '+Keylist[j-1]+'='+vartosql(Cdsupdate[Keylist[j-1]]);

end;

Cmdstr:='Delete '+ptablename+' where '+s2;

end;

end;

if Cmdstr<>'' then

sqlstr:=sqlstr+Cmdstr+';'+chr(13)+chr(10);

Cdsupdate.Next;

end;

end;

finally

Cdsupdate.close;

Cdsupdate.Free();

end;

Result:=sqlstr;

end;

end.