V
violetxh
Unregistered / Unconfirmed
GUEST, unregistred user!
使用ClientDataSet的ApplyUpdates(0)保存从表数据时,有时部分数据不能存入到数据库中我使用的是SQL数据库安装在一台服务器中。后来我用一个函数将ClientdataSet的数据变化转换成SQL语句,这样主表是SQL语句,从表也是SQL语句,分别发送到服务器,保存成功,但我感觉这样很麻烦,同时效率也不高//获取ClientDataset.delta的SQL语句function GetSqlS(AdoCon:TADOConnection; pdelta: OleVariant; const ptablename, pkeyfields: WideString): WideString; function 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; 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'; adpen; ado.GetFieldNames(FieldList); FreeAndNil(ado); for i:=1 to FieldList.Count do if Cdsupdate.FindField(FieldList[i-1])<>nil then Cdsupdate.FindField(FieldList[i-1]).tag:=1; FreeAndNil(FieldList); 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 Cmdstr:=' update '+ptablename+' set '+s1+' where '+s2; 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 Cmdstr:=' Insert into '+ptablename+'('+s1+') values('+s2+')'; 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 FreeAndNil(Cdsupdate); FreeAndNil(keylist); end; Result:=sqlstr;end;