clientdataset生成sql

遍历clientdataset，生成sql语句，参照咏南写则，增加了一表多主键的情况

function TForm1.vartosql(value: Variant): wideString;
var
tmp:widestring;
begin
   if (varisnull(Value)) or (varisempty(Value)) then
    Result:='NULL'
   else
    case Vartype(value) of
      varDate:
      begin
        tmp := formatDatetime('yyyy-mm-dd hh:mm:ss', VartoDatetime(Value));
        Result:=Quotedstr(tmp);
      end;
      varString,varOlestr:
        Result:=Quotedstr(Trim(Vartostr(Value)));
      varboolean:
      begin
        if Value then
          Result:='1'
        else
          Result:='0';
      end;
      varSmallint,varInteger,varDouble,varShortInt,varInt64,varLongWord,varCurrency:
      begin
        Result:=trim(Vartostr(Value));
      end;
    else
        Result:=Quotedstr(Trim(Vartostr(Value)));
    end;
end;

//TableName 表名
//keys 字段名，支持多个主键
procedure TForm1.InnerApplyUpdates(cds1:TClientDataSet;TableName,keys:WideString);
var
i,j:integer;
s1,s2,CmdStr,keyWhere:string;
cds:TClientDataSet;
keyList:TStringList;
begin
  cds:=TClientDataSet.Create(nil);
  cds.Data:=cds1.Delta;

  //获取每个Key的值
  keyList := split(keys,',');
  keyWhere:=' Where ';
  for j:=0 to keyList.count-1 do
  begin
     if j=(keyList.count-1) then
     begin
       keyWhere:=keyWhere+keyList[j] + ' = '+VarToSql(cds[keyList[j]]);
     end
     else
     begin
       keyWhere:=keyWhere+keyList[j] + ' = '+VarToSql(cds[keyList[j]])+' and ';
     end;
  end;

  if cds.RecordCount>0 then
  begin
     cds.First;
     while not cds.Eof do
     begin
        CmdStr:='';
        if cds.UpdateStatus =usModified then
        begin
            s1 := '';
            for i:=0 to cds.FieldCount-1 do
            begin
               if cds.Fields[i].NewValue <> Variants.Unassigned then
               begin
                  if s1 = '' then
                    s1 := Trim(cds.Fields[i].FieldName) + ' = ' + VarToSql(cds.Fields[i].Value)
                  else
                    s1 := s1 + ',' + Trim(cds.Fields[i].FieldName) + ' = ' + VarToSql(cds.Fields[i].Value);
               end;
            end;
          
            if s1 <> '' then
            begin
              CmdStr := 'Update ' + TableName + ' Set ' + s1 +keyWhere;
            end;    
        end
        else if cds.UpdateStatus =usInserted then
        begin
            s1 := '';
            for i:=0 to cds.FieldCount-1 do
            begin
               if cds.Fields[i].NewValue <> Variants.Unassigned then
               begin
                  if s1 = '' then
                  begin
                    s1 := Trim(cds.Fields[i].FieldName);
                    s2 := VarToSql(cds.Fields[i].Value);
                  end
                  else
                  begin
                    s1 := s1 + ',' + Trim(cds.Fields[i].FieldName);
                    s2 := s2 + ',' + VarToSql(cds.Fields[i].Value);
                  end;
               end;
            end;
          
            if s1 <> '' then
            begin
               CmdStr := 'Insert into ' + TableName + '(' + s1 + ') Values (' + s2 + ')';
            end;    
        end
        else if cds.UpdateStatus =usDeleted then
        begin
            CmdStr := 'Delete ' + TableName +keyWhere;
        end;

        if CmdStr <> '' then
        begin
          Memo1.Lines.Add(CmdStr);
        end;
        cds.Next;
     end;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  InnerApplyUpdates(cds1,'dm_bm','fwzh,fc,bmdm');
end;


function TForm1.split(s,s1:string):TStringList;
begin
  Result:=TStringList.Create;
  while Pos(s1,s)>0 do
  begin
    Result.Add(Copy(s,1,Pos(s1,s)-1));
    Delete(s,1,Pos(s1,s));
  end;
  Result.Add(s);
end;