转：ADOQuery代替ClientDataSet做3Tier系统

ADOQuery代替ClientDataSet做3-Tier系统

2009年09月23日 星期三 11:16

3-Tier的系统我们一般用Midas的TClientDataSet,它搭配BDE的TQuery效率还可以，毕竟是一家的;但搭配TADOQuery就慢了很多，大概9倍，因为不晓得人家的资料存储格式，只能一个Field一个Field读出来再用TDataSetProvider打包成Data:OLEVariant;
ADO2.5提供了一个_Stream组件，配合BatchUpdate,可以做到TClientDataSet的功能,它的效率可以达到与TClientDataSet+TQuery一样:
1.Client给SQL到Server,Server用TADOQuery获取资料，然后Save到_Stream,返回；
2.Client用_Stream接收Server返回资料，给TADOQuery，用户可以随意增/删/改；
3.Client把用户修改后的资料Save到_Stream,传给Server(我现在还没办法只把异动资料存到_Stream,它把没有修改的资料也Save进去了，因为在WAN上跑时资料量对效率影响大；一种方法是存为XML格式，再用XML DOM组件去解析，把没修改的资料删掉，看各位还有更好的方法没有?);
4.Server用_Stream接收Client的修改资料，给TADOQuery，把FilterGroup设为fgPendingRecords，可以分析资料的增/删/改情况，处理企业逻辑，最后调用UpdateBatch保存资料。
5.Client在Call Server保存成功后，也调用UpdateBatch,使资料与实际一致，因为TADOQuery没有联接Connection,因此UpdateBatch相当于TClientDataSet的ClearChangeLog
Server端的代码(Com+):

function TEOStudent.ReadADOData(const ASQL: WideString): OLEVariant;
var
   AStream:_Stream;
   MS1:TMemoryStream;
   V:OLEVariant;
   P:Pointer;
begin
   try
     ADOConnection.Close;
     ADOQuery.Close;
     ADOQuery.SQL.Text:=ASQL;
     ADOQuery.Open;
     AStream:=CoStream.Create;
     OLEVariant(ADOQuery.Recordset).Save(AStream,adPersistADTG);
     ADOQuery.Close;
     AStream.Position:=0;
     V:=AStream.Read(AStream.Size);
     MS1:=TMemoryStream.Create;
     try
       P:=VarArrayLock(V);
       try
         MS1.Size:=VarArrayHighBound(V,1)+1;
         Move(P^,MS1.Memory^,MS1.Size);
       finally
         VarArrayUnLock(V);
       end;
       Result:=VarArrayCreate([0,MS1.Size-1],varByte);
       P:=VarArrayLock(Result);
       try
         Move(MS1.Memory^,P^,MS1.Size);
       finally
         VarArrayUnLock(Result);
       end;
     finally
       MS1.Free;
     end;
     SetComplete;
   except
     SetAbort;
     Raise;
   end;
end;

procedure TEOStudent.SaveADOData(AData: OLEVariant);
var
   AStream:_Stream;
   AR:_Recordset;
begin
   try
     ADOQuery.Close;
     AStream:=CoStream.Create;
     AStream.Open(EmptyParam,adModeUnknown,adOpenStreamUnspecified, ‘‘, ‘‘);
     AStream.Type_:=adTypeBinary;
     AStream.Write(AData);
     AStream.Position:=0;
     AR:=_Recordset(CoRecordset.Create);
     AStream.Position:=0;
     AR.Open(AStream,EmptyParam,adOpenKeyset,adLockBatchOptimistic,0);
     ADOConnection.Close;
     ADOConnection.Open;
     AR.Set_ActiveConnection(ADOConnection.ConnectionObject);
     ADOQuery.Recordset:=ADOInt._Recordset(AR);
{     ADOQuery.FilterGroup:=fgPendingRecords;
     ADOQuery.Filtered:=true;
     try
       ADOQuery.First;
       while not ADOQuery.Eof do
       begin
         case ADOQuery.UpdateStatus of
           usModified:ShowMessage(
   VarToStr(ADOQuery.FieldByName(‘SeqNo‘).OldValue)+‘:‘+
   ADOQuery.FieldByName(‘SeqNo‘).asString);
         end;
         ADOQuery.Next;
       end;
     finally
       ADOQuery.Filtered:=false; //stateless
     end;}
     ADOQuery.UpdateBatch;
     ADOConnection.Close;
     SetComplete;
   except
     SetAbort;
     Raise;
   end;
end;

Client端，Form上放一个TADOQuery,不用连接Connection:

获取资料
procedure TForm1.Button5Click(Sender: TObject);
var
   V:OLEVariant;
   AR:_Recordset;
   AStream:_Stream;
   MS1:TMemoryStream;
   P:Pointer;
   s:string;
begin
   DCOM.Connected:=true;
   try
     s:=‘‘;
     V:=DCOM.AppServer.ReadADOData(‘select * from FORMDD‘);
     MS1:=TMemoryStream.Create;
     try
       MS1.Size:=VarArrayHighBound(V,1)+1;
       P:=VarArrayLock(V);
       try
         Move(P^,MS1.Memory^,MS1.Size);
       finally
         VarArrayUnLock(V);
       end;
       V:=VarArrayCreate([0,MS1.Size-1],varByte);
       P:=VarArrayLock(V);
       try
         Move(MS1.Memory^,P^,MS1.Size);
       finally
         VarArrayUnLock(V);
       end;
     finally
       MS1.Free;
     end;
     AStream:=CoStream.Create;
     AStream.Open(EmptyParam,adModeUnknown,adOpenStreamUnspecified, ‘‘, ‘‘);
     AStream.Type_:=adTypeBinary;
     AStream.Write(V);

     AR:=_Recordset(CoRecordset.Create);
     AStream.Position:=0;
     AR.Open(AStream,EmptyParam,adOpenUnspecified, adLockUnspecified, -1);
     ADOQuery1.Recordset:=ADOInt._Recordset(AR);
   finally
     DCOM.Connected:=false;
   end;
end;

//保存资料
procedure TForm1.Button6Click(Sender: TObject);
var
   AStream:_Stream;
   V:OLEVariant;
begin
   DCOM.Connected:=true;
   try
     ADOQuery1.CheckBrowseMode;
     AStream:=CoStream.Create;
     OLEVariant(ADOQuery1.Recordset).Save(AStream,adPersistADTG);
     AStream.Position:=0;
     V:=AStream.Read(AStream.Size);
     DCOM.AppServer.SaveADOData(V);
     ADOQuery1.UpdateBatch;   //no connection,means clearchangelog
   finally
     DCOM.Connected:=false;
   end;
end;