DataSetProvider里有个BeforeUpdateRecord事件是可以抓到变动的字段自动生成sql的
我写的事件如下
procedure TfrmDLLForm.DataSetProvider1BeforeUpdateRecord(Sender: TObject;
SourceDS: TDataSet; DeltaDS: TCustomClientDataSet;
UpdateKind: TUpdateKind; var Applied: Boolean);var
i:integer;
updstr,insfstr,insvstr,whrstr:widestring;
sqlstr:string;
begin
if updatekind=ukModify then
begin
//////////////////////////////////////////修改 for i:=0 to Deltads.FieldCount-1 do
begin
if (deltads.Fields[i].FieldKind=Fkdata) and (pfInWhere in deltads.Fields[i].ProviderFlags) then
if vartype(deltads.Fields[i].OldValue) in [varDouble,varCurrency,varInteger,varSmallint,varSingle] then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='+floattostr(deltads.Fields[i].oldValue)+' and '
else if vartype(deltads.Fields[i].OldValue) in [varboolean] then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='+booltostr(deltads.Fields[i].oldValue)+' and '
else
if vartype(deltads.Fields[i].newValue)=vardate then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='''+datetimetostr(deltads.Fields[i].oldValue)+''''+' and '
else
whrstr:=whrstr+deltads.Fields[i].FieldName+'='''+vartostr(deltads.Fields[i].oldValue)+''''+' and '; end;
whrstr:=leftstr(whrstr,length(whrstr)-5);
sqlstr:='update Tygeneralize_basic set '+updstr+' where id='+trim(cbmdsetid.Text);
//////////////////////////////////////////修改end
else if updatekind=ukDelete then
begin
sqlstr:='Delete test where id='+trim(cbmdsetid.Text);
end else
begin
for i:=0 to Deltads.FieldCount-1 do
begin
if (deltads.Fields[i].FieldKind=Fkdata) and (pfInUpdate in deltads.Fields[i].ProviderFlags) and (not varisempty(deltads.Fields[i].newValue)) then
begin
insfstr:=insfstr+deltads.Fields[i].FieldName+',';
if vartype(deltads.Fields[i].Value) in [varDouble,varCurrency,varInteger,varSmallint,varSingle] then // in [ftSmallint, ftInteger,ftBoolean,ftBCD,ftBytes, ftVarBytes, ftAutoInc, ftBlob] then
insvstr:=insvstr+floattostr(deltads.Fields[i].Value)+','
else if vartype(deltads.Fields[i].Value) in [varboolean] then
insvstr:=insvstr+booltostr(deltads.Fields[i].Value)+','
else
if vartype(deltads.Fields[i].Value)=vardate then
insvstr:=insvstr+''''+datetimetostr(deltads.Fields[i].Value)+''''+','
else
insvstr:=insvstr+''''+vartostr(deltads.Fields[i].Value)+''''+','
end;
end;
insfstr:=leftstr(insfstr,length(insfstr)-1);
insvstr:=leftstr(insvstr,length(insvstr)-1);
sqlstr:='Insert into test ('+insfstr+') values('+insvstr+')';
end;
end;这是抓出变动的字段自动生成sql的,但现在我把架构改成三层的了,把DataSetProvider放到了中间层,客户端只有一个ClientDataSet,现在我想在ClientDataSet里实现上面那样的功能,如何实现!请高手们指教,谢谢(能不能借助第三方控件呢)!
我写的事件如下
procedure TfrmDLLForm.DataSetProvider1BeforeUpdateRecord(Sender: TObject;
SourceDS: TDataSet; DeltaDS: TCustomClientDataSet;
UpdateKind: TUpdateKind; var Applied: Boolean);var
i:integer;
updstr,insfstr,insvstr,whrstr:widestring;
sqlstr:string;
begin
if updatekind=ukModify then
begin
//////////////////////////////////////////修改 for i:=0 to Deltads.FieldCount-1 do
begin
if (deltads.Fields[i].FieldKind=Fkdata) and (pfInWhere in deltads.Fields[i].ProviderFlags) then
if vartype(deltads.Fields[i].OldValue) in [varDouble,varCurrency,varInteger,varSmallint,varSingle] then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='+floattostr(deltads.Fields[i].oldValue)+' and '
else if vartype(deltads.Fields[i].OldValue) in [varboolean] then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='+booltostr(deltads.Fields[i].oldValue)+' and '
else
if vartype(deltads.Fields[i].newValue)=vardate then
whrstr:=whrstr+deltads.Fields[i].FieldName+'='''+datetimetostr(deltads.Fields[i].oldValue)+''''+' and '
else
whrstr:=whrstr+deltads.Fields[i].FieldName+'='''+vartostr(deltads.Fields[i].oldValue)+''''+' and '; end;
whrstr:=leftstr(whrstr,length(whrstr)-5);
sqlstr:='update Tygeneralize_basic set '+updstr+' where id='+trim(cbmdsetid.Text);
//////////////////////////////////////////修改end
else if updatekind=ukDelete then
begin
sqlstr:='Delete test where id='+trim(cbmdsetid.Text);
end else
begin
for i:=0 to Deltads.FieldCount-1 do
begin
if (deltads.Fields[i].FieldKind=Fkdata) and (pfInUpdate in deltads.Fields[i].ProviderFlags) and (not varisempty(deltads.Fields[i].newValue)) then
begin
insfstr:=insfstr+deltads.Fields[i].FieldName+',';
if vartype(deltads.Fields[i].Value) in [varDouble,varCurrency,varInteger,varSmallint,varSingle] then // in [ftSmallint, ftInteger,ftBoolean,ftBCD,ftBytes, ftVarBytes, ftAutoInc, ftBlob] then
insvstr:=insvstr+floattostr(deltads.Fields[i].Value)+','
else if vartype(deltads.Fields[i].Value) in [varboolean] then
insvstr:=insvstr+booltostr(deltads.Fields[i].Value)+','
else
if vartype(deltads.Fields[i].Value)=vardate then
insvstr:=insvstr+''''+datetimetostr(deltads.Fields[i].Value)+''''+','
else
insvstr:=insvstr+''''+vartostr(deltads.Fields[i].Value)+''''+','
end;
end;
insfstr:=leftstr(insfstr,length(insfstr)-1);
insvstr:=leftstr(insvstr,length(insvstr)-1);
sqlstr:='Insert into test ('+insfstr+') values('+insvstr+')';
end;
end;这是抓出变动的字段自动生成sql的,但现在我把架构改成三层的了,把DataSetProvider放到了中间层,客户端只有一个ClientDataSet,现在我想在ClientDataSet里实现上面那样的功能,如何实现!请高手们指教,谢谢(能不能借助第三方控件呢)!
呵,我想在客户端动态生成SQL语句在客户端下SQL,
在服务端加入一个过程,这个过程的功能就是传回生成的sql语句,
客户端调用这个过程即可返回sql语句。
如果需要帮助,给我留言
非常感谢您的建议,能不能麻烦您给个完整的例子呢,麻烦您了!我的email 是[email protected],能不能发个完整的例子给我,我不知道如何从服务端传回sql语句,谢谢!
要求:
将你的sqlstr变量放到窗口的public下面声明,
然后增加一个下面的过程。procedure 模块名.GetSql(out aSql : WideString); safecall;
begin
aSql := sqlstr;
end;客户端调用就可以了,不知道你是不是用的远程数据模块?
我用的是远程数据模块,我刚才试了一下但我不知道如何在数据模块里定义一个有返回字符串的函数
procedure TfrmDLLForm.GetSql(out aSql : WideString); safecall;
上面这个声明放在组件声明的下面。//sqlstr变量放到窗口的public下面声明//下面是实现
procedure TfrmDLLForm.GetSql(out aSql : WideString); safecall;
begin
aSql := sqlstr;
end;