关于三层主从表数据添加，删除，更新的方案实现【高手关注200分】(200)

function Tcommons.ApplyupdateMD(const DBName: WideString;
Mdelta: OleVariant;
const MtableName, MkeyField: WideString;
Ddetla: OleVariant;
const DtableName, DkeyField, UserCode, PCname: WideString): WordBool;vari,j:integer;ms1,ms2,ds1,ds2:String;mCmdstr,dCmdstr:string;MFieldList,DFieldList:TstringList;MCdsupdate,DCDSupdate:Tclientdataset;ExecMSQLADO,ExecDSQLADO:Tadoquery;MAlreadyPost,DAlreadyPost:Boolean;
begin
if Varisnull(Mdelta) and Varisnull(Ddetla) then
begin
Result:=False;
Exit;
end;
if OpenIndicatinnDB(DBname) then
begin
try MAlreadyPost:=False;
DAlreadyPost:=False;
MFieldList:=TstringList.Create;
DFieldList:=TstringList.Create;
With DMServer.AdoConMaindo
begin
GetFieldNames(MTablename,MFieldList);
GetFieldNames(DTablename,DFieldList);
end;
MCdsupdate:=GetNewClientDataset;
DCDSupdate:=GetNewClientDataset;
Mcdsupdate.Close;
Dcdsupdate.Close;
Mcdsupdate.Data:=Mdelta;
Dcdsupdate.Data:=Ddetla;
if not MCDSupdate.Active then
MCDSupdate.Open;
if not DCDSupdate.Active then
DCDSupdate.Open;
for i:=1 to MFieldList.Countdo
begin
if MCDSupdate.FindField(MFieldList[i-1])<>nil then
MCDSupdate.FindField(MFieldList[i-1]).tag:=1;
end;
for j:=1 to DFieldList.Countdo
begin
if DCDSupdate.FindField(DFieldList[j-1])<>nil then
Dcdsupdate.FindField(DfieldList[j-1]).tag:=1;
end;
if MCDSupdate.RecordCount > 0 then
begin
MS1:='';
Ms2:='';
MCmdStr:='';
case MCdsupdate.UpdateStatus of usUnmodified: begin
MS2:=MCDSupdate[MkeyField];
end;
usModified: begin
MS1:='';
for i:=1 to MCDSupdate.FieldCountdo
if (not MCDSupdate.Fields[i-1].isnull) and (MCDSupdate.Fields[i-1].tag=1) then
begin
if MS1='' then
MS1:=Trim(MCDSupdate.Fields[i-1].fieldName)+' = '+Vartosql(MCDSupdate.Fields[i-1].value) else
MS1:=MS1+','+Trim(MCDSupdate.Fields[i-1].fieldName)+' = '+Vartosql(MCDSupdate.Fields[i-1].value);
end;
if MS1<>'' then
begin
MCmdstr:='update '+Mtablename+' set '+MS1+' where '+MkeyField+'='+MS2;
end;
end;
usInserted: begin
MS1:='';
MS2:='';
for i:=1 to MCDSupdate.FieldCountdo
if (not MCDSupdate.Fields[i-1].isnull) and (MCDSupdate.Fields[i-1].tag=1) then
begin
if MS1='' then
begin
MS1:=Trim(MCDSupdate.Fields[i-1].FieldName);
MS2:=Vartosql(MCDSupdate.Fields[i-1].value);
end else
begin
MS1:=MS1+','+Trim(MCDSupdate.Fields[i-1].FieldName);
MS2:=MS2+','+Vartosql(MCDSupdate.Fields[i-1].value);
end;
end;
if MS1<>'' then
begin
MCmdstr:='Insert into '+Mtablename+'('+MS1+') values('+MS2+')';
end;
end;
usDeleted: begin
MS2:=vartosql(MCDSupdate[MkeyField]);
MCmdStr:='Delete '+MtableName+' where '+MkeyField+'='+Ms2;
end;
end;
end;
if not IntransAction then
begin
Trans;try ExecMSQLADO:=GetNewAdoquery;
ExecDSQLADO:=GetNewAdoquery;
if MCmdStr<>'' then
begin
try with ExecMSQLADOdo
begin
Close;
sql.Clear;
sql.Text:=MCmdStr;
ExecSQL;
MAlreadyPost:=True;
Syslog(DBName,c_Succeed,MTableName,McmdStr,UserCode,PCname);
end;
except MAlreadyPost:=False;
Result:=False;
Exit;
end;
end else
MAlreadyPost:=False;
if DCDSupdate.RecordCount > 0 then
begin
DCDSupdate.First;
DS1:='';
Ds2:='';
while not DCDSupdate.Eofdo
begin
DCmdStr:='';
case DCdsupdate.UpdateStatus of usUnmodified: begin
DS2:=DCDSupdate[DkeyField];
end;
usModified: begin
DS1:='';
for j:=1 to DCDSupdate.FieldCountdo
if (not DCDSupdate.Fields[j-1].isnull) and (DCDSupdate.Fields[j-1].tag=1) then
begin
if DS1='' then
DS1:=Trim(DCDSupdate.Fields[j-1].fieldName)+' = '+Vartosql(DCDSupdate.Fields[j-1].value) else
DS1:=DS1+','+Trim(DCDSupdate.Fields[j-1].fieldName)+' = '+Vartosql(DCDSupdate.Fields[j-1].value);
end;
if DS1<>'' then
begin
DCmdstr:='update '+Dtablename+' set '+DS1+' where '+DkeyField+'='+DS2;
end;
end;
usInserted: begin
DS1:='';
DS2:='';
for j:=1 to DCDSupdate.FieldCountdo
if (not DCDSupdate.Fields[j-1].isnull) and (DCDSupdate.Fields[j-1].tag=1) then
begin
if DS1='' then
begin
DS1:=Trim(DCDSupdate.Fields[j-1].FieldName);
DS2:=Vartosql(DCDSupdate.Fields[j-1].value);
end else
begin
DS1:=DS1+','+Trim(DCDSupdate.Fields[j-1].FieldName);
DS2:=DS2+','+Vartosql(DCDSupdate.Fields[j-1].value);
end;
end;
if DS1<>'' then
begin
DCmdstr:='Insert into '+Dtablename+'('+DS1+') values('+DS2+')';
end;
end;
usDeleted: begin
DS2:=vartosql(DCDSupdate[DkeyField]);
DCmdStr:='Delete '+DtableName+' where '+DkeyField+'='+Ds2;
end;
end;
if DCmdStr<>'' then
begin
try with ExecDSQLADOdo
begin
close;
sql.Clear;
sql.Text:=DCmdStr;
Execsql;
DAlreadyPost:=True;
Syslog(DBName,c_Succeed,DTableName,DcmdStr,UserCode,PCname);
end;
except DAlreadyPost:=False;
break;
end;
end else
DAlreadyPost:=False;
DCDSupdate.next;
end;
end;
if MAlreadyPost and DAlreadyPost then
begin
if intransaction then
commitTrans;
Result:=True;
end else
begin
if intransaction then
RollbackTrans;
Result:=False;
end;
finally FreeExistAdoquery(ExecMsqlado);
FreeExistAdoquery(ExecDsqlado);
end;
finally MFieldList.Free;
DFieldList.Free;
FreeExistClientDataset(MCdsupdate);
FreeExistClientDataset(DCDSupdate);
end;
endelse
Result:=False;
end;