syndatabase.pas

p2pdbanywhere 可以通过之udp方式远程连接数据库的组件,这个组件非常好,是Delphi7,9,2006能够使用,包含源码.

unit SynDataBase;

interface

uses
  SysUtils, Classes,ADODB,remotedataset,RemoteUdpConnection,RemoteHttpConnection,
  dialogs;

type
  TSynDataBase = class(TComponent)
  private
    FADOConnection:TADOConnection;
    FRemoteHttpConnection:TRemoteHttpConnection;
    FRemoteUdpConnection:TRemoteUdpConnection;
    //FTableName:string;
    FRTQQ:TremotedataSet;
    FQQ:TAdoQuery;
    FAbout:string;
    FOtherMarkValue:string;
    FOtherMarkField:string;
    procedure setSetupHttpParent(value:TRemoteHttpConnection);
    procedure setSetupUdpParent(value:TRemoteUdpConnection);
    procedure setAdoConnection(value:TADOConnection);
    procedure setothermarkvalue(value:string);
    procedure setothermarkField(value:string);
    //procedure settablename(value:string);
    { Private declarations }
  protected

    { Protected declarations }
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function SynDataBase(TableName:string):boolean;
    { Public declarations }
  published
    property RemoteHttpConnection: TRemoteHttpConnection read FRemoteHttpConnection write setSetupHttpParent;
    property RemoteUdpConnection: TRemoteUdpConnection read FRemoteUdpConnection write setSetupUdpParent;
    property ADOConnection: TADOConnection read FADOConnection write setADoConnection;
    property About:string read FAbout;
    property OtherMarkValue:string read FOtherMarkValue write setothermarkvalue;
    property OtherMarkField:string read FOtherMarkField write setothermarkfield;
    //property TableName:string read Ftablename write settablename;
    { Published declarations }
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('DbAnyWhere', [TSynDataBase]);
end;
constructor TSynDataBase.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FRTQQ:=TRemoteDataSet.Create(nil);
  FQQ:=TAdoQuery.Create(nil);
  FAbout:='同步数据表分远程数据表和本地数据表'+chr(13)+
          '必须要包含以下两个字段：'+chr(13)+
          'Change_Mark：长整形(无论更新增加都要递增1)'+chr(13)+
          'Delete_Mark：布尔型(删除标记)'+
          '远程的表第一个字段是自动编号字段，'+chr(13)+
          '本地表的第一个字段是长整形字段，不自动编号，'+chr(13)+
          '第一个字段和Change_mark要设索引（提高速度）。';
  FOtherMarkValue:='';
  FOtherMarkField:='';
end;
destructor TSynDataBase.Destroy;
begin
  inherited Destroy;
  freeandnil(FRTQQ);
  freeandnil(FQQ);
end;
//其他区分标志，比如目录树所属部门等
procedure TSynDataBase.setothermarkvalue(value:string);
begin
  FOtherMarkValue:=value;
end;
procedure TSynDataBase.setothermarkfield(value:string);
begin
  FOtherMarkField:=value;
end;
procedure TSynDataBase.setSetupHttpParent(value:TRemoteHttpConnection);
begin
  FRemoteUdpConnection:=nil;
  FRemoteHttpConnection:=value;
  FRTQQ.RemoteHttpConnection:=FRemoteHttpConnection;
end;
procedure TSynDataBase.setSetupUdpParent(value:TRemoteUdpConnection);
begin
  FRemoteHttpConnection:=nil;
  FRemoteUdpConnection:=value;
  FRTQQ.RemoteUdpConnection:=FRemoteUdpConnection;
end;
procedure TSynDataBase.setAdoConnection(value:TADOConnection);
begin
  FADOConnection:=value;
  FQQ.Connection:=FADOConnection;
end;
{procedure TSynDataBase.settablename(value:string);
begin
  FTableName:=value;
end; }
//同步
function TSynDataBase.SynDataBase(TableName:string):boolean;
var
   local_max:string;
   i:integer;
   sql:string;
begin
  if length(trim(FothermarkField))<>0 then
  begin
    if length(trim(FOtherMarkValue))=0 then
    begin
      Showmessage('OtherMarkValue属性不能够为空！');
      result:=false;
      exit;
    end;
  end;
  //判断连接
  if (self.RemoteHttpConnection=nil)and(self.RemoteUdpConnection=nil) then
  begin
    showmessage('请设定远程数据库连接！');
    result:=false;
    //abort;
    exit;
  end;
  if self.ADOConnection=nil then
  begin
    showmessage('请设定本地数据库连接！');
    result:=false;
    exit;
  end;
  if length(trim(tablename))=0 then
  begin
    showmessage('请指定要同步的数据表名！');
    result:=false;
    exit;
  end;
  //查询本地数据表标记的最大值
  FQQ.Close;
  if length(trim(self.FOtherMarkField))=0 then
    Fqq.SQL.Text:='select max(change_mark) as max_mark from '+tablename
  else
    Fqq.SQL.Text:='select max(change_mark) as max_mark from '+tablename+
                  ' amd '+self.FOtherMarkField+'='''+self.FOtherMarkValue+'''';
  fqq.Open;
  local_max:=fqq.fieldbyname('max_mark').AsString;
  if length(trim(local_max))=0 then local_max:='0';
  //查询远程已经更新的数据
  FRtqq.Remoteclose;
  frtqq.RTSelectType:=selectall;
  if length(trim(self.FOtherMarkField))=0 then
    frtqq.RTSQL.Text:='select * from '+tablename+' where change_mark>'+
                      local_max
  else
    frtqq.RTSQL.Text:='select * from '+tablename+' where change_mark>'+
                      local_max+' and '+self.FOtherMarkField+'='''+self.FOtherMarkValue+'''';
  frtqq.RemoteOpen;
  frtqq.DisableControls;
  //查询到后，循环更新本地表
  FQQ.Close;
  self.FADOConnection.BeginTrans;
  try
  while not frtqq.Eof do
  begin
    FQQ.SQL.Text:='delete from '+tablename+' where '+
                  frtqq.Fields[0].FieldName+'='+
                  frtqq.Fields[0].AsString;
    FQQ.ExecSQL;
    sql:='insert into '+tablename+'(';
    for i:=0 to frtqq.Fields.Count-1 do
    begin
      sql:=sql+frtqq.Fields[i].FieldName+','
    end;
    sql:=copy(sql,1,length(sql)-1)+') values(';
    for i:=0 to frtqq.Fields.Count-1 do
    begin
      sql:=sql+':A'+inttostr(i)+',';
    end;
    sql:=copy(sql,1,length(sql)-1)+')';
    FQQ.SQL.Text:=sql;
    for i:=0 to frtqq.Fields.Count-1 do
    begin
      FQQ.Parameters.ParamValues['A'+inttostr(i)]:=frtqq.Fields[i].AsVariant;
    end;
    FQQ.ExecSQL;
    frtqq.Next;
  end;
  self.FADOConnection.CommitTrans;
  result:=true;
  except
    self.FADOConnection.RollbackTrans;
    result:=false;
    //showmessage(TableName +' 同步失败。');
  end;
end;

end.