delphi 根据DELTA自动生成SQL语句


delphi 根据DELTA自动生成SQL语句

上传客户端的CLIENTDATASET.delta到服务器的clientdataset.data,服务端解析clientdataset的数据生成相应的SQL语句。

相对于直接调用datasetprovider.applyupdates()方法提交数据而言,前者的可控性更强,对于某些要求灵活性很强的场合,前者可能是必须的提交方式。

procedure TBaseService.ApplyUpdates(const Delta: OleVariant; TableName, KeyField: WideString);
var
 Flag: Boolean;
begin
 if VarIsNull(Delta) then exit;
 with (FParent as TDataServer2) do
 begin
 cdsDelta.Close;
 cdsDelta.Data := Delta;
 Flag := cdsDelta.FindField('SYS_STATUS') <> nil;
 end; // with
 if Flag then
 InnerApplyUpdates2(TableName, KeyField)
 else
 InnerApplyUpdates(TableName, KeyField);
end;

procedure TBaseService.InnerApplyUpdates(TableName, KeyField: WideString);
var
 i: integer;
 s1, s2: string;
 CmdStr: string;
 FieldList: TStringList;
begin
 with (FParent as TDataServer2) do
 begin
 FieldList := TStringList.Create;
 Connection.GetFieldNames(TableName, FieldList);
 if not cdsDelta.Active then cdsDelta.Open;
 for i := 1 to FieldList.Count do
 if cdsDelta.FindField(FieldList[i - 1]) <> nil then
 cdsDelta.FindField(FieldList[i - 1]).Tag := 1;
 FieldList.Free;
 if cdsDelta.RecordCount > 0 then
 begin
 cdsDelta.First;
 s1 := '';
 s2 := '';
 while not cdsDelta.Eof do
 begin
 CmdStr := '';
 case cdsDelta.UpdateStatus of
 usUnmodified:
 begin
 s2 := VarToSql(cdsDelta[KeyField]);
 end;
 usModified:
 begin
 s1 := '';
 for i := 1 to cdsDelta.FieldCount do
 if (not cdsDelta.Fields[i - 1].IsNull) and (cdsDelta.Fields[i - 1].Tag = 1) then
 begin
 if s1 = '' then
 s1 := Trim(cdsDelta.Fields[i - 1].FieldName) + ' = ' + VarToSql(cdsDelta.Fields[i - 1].Value)
 else
 s1 := s1 + ',' + Trim(cdsDelta.Fields[i - 1].FieldName) + ' = ' + VarToSql(cdsDelta.Fields[i - 1].Value);
 end;
 if s1 <> '' then
 begin
 CmdStr := 'Update ' + TableName + ' Set ' + s1 + ' Where ' + KeyField + ' = ' + s2;
 end;
 end;
 usInserted:
 begin
 s1 := '';
 s2 := '';
 for i := 1 to cdsDelta.FieldCount do
 if (not cdsDelta.Fields[i - 1].IsNull) and (cdsDelta.Fields[i - 1].Tag = 1) then
 begin
 if s1 = '' then
 begin
 s1 := Trim(cdsDelta.Fields[i - 1].FieldName);
 s2 := VarToSql(cdsDelta.Fields[i - 1].Value);
 end
 else
 begin
 s1 := s1 + ',' + Trim(cdsDelta.Fields[i - 1].FieldName);
 s2 := s2 + ',' + VarToSql(cdsDelta.Fields[i - 1].Value);
 end;
 end;
 if s1 <> '' then
 begin
 CmdStr := 'Insert into ' + TableName + '(' + s1 + ') Values (' + s2 + ')';
 end;
 end;
 usDeleted:
 begin
 s2 := VarToSql(cdsDelta[KeyField]);
 CmdStr := 'Delete ' + TableName + ' Where ' + KeyField + ' = ' + s2;
 end;
 end;
 if CmdStr <> '' then Cmd.Execute(CmdStr);
 cdsDelta.Next;
 end;
 cdsDelta.First;
 cdsDelta.EmptyDataSet;
 cdsDelta.Close;
 end;
 end;
end;

联系电话:
020-00000000
联系电话:
020-00000000
联系电话:
020-12345678