多层数据库应用基于Delphi DataSnap方法调用的实现

1、多层数据库应用基于Delphi DataSnap方法调用的实现(二更新数据集收藏传统的数据集的读取和更新,是通过中间层的TDataSetProvider来完成的。TDataSetProvider负责从它上游的数据集读取数据生成Data包,再传给客户端;另一方面,在客户端提交更新时(TClientDataSet.ApplyUpdates,TDataSetProvider还负责解析上传的Delta包,并最终实现数据库的更新。现在在我们当前的方法调用方式下,不能再通过TDataSetProvider.ApplyUpdates来自动完成更新了,但是,我们还可以借用TDataSetProvider 解析

2、Delta数据包的功能,手动生成SQL语句来完成数据库的更新。手动更新看似复杂,实际上编码并不多,而且这种方式具有很大的灵活性,同时还解决了在传统方式下,多表联合查询不能完全自动更新的软肋。下面来看看示例代码:中间层代码.interfaceusesSysUtils, Classes, DSServer, DBXOracle, FMTBcd, DB, SqlExpr, WideStrings, Provider, CodeSiteLogging, DBClient;type$METHODINFO ONTServerMethods1 = class(TDataModuleSQLConnection

3、1: TSQLConnection;SQLDataSet1: TSQLDataSet;DataSetProvider1: TDataSetProvider;procedure DataSetProvider1BeforeUpdateRecord(Sender: TObject; SourceDS: TDataSet;DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean; private Private declarations FOnDeltaRecordUpdate: TBeforeUpdat

4、eRecordEvent;procedure UpdateEmployeesDelta(Sender: TObject; SourceDS: TDataSet;DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;var Applied: Boolean;public Public declarations function GetEmployeeFullName(EmployeeId: Integer: string;function GetEmployees: TDataSet;function UpdateEmployees(EMP

5、Delta: OleVariant: Boolean;end;$METHODINFO OFF.cedure TServerMethods1.DataSetProvider1BeforeUpdateRecord(Sender: TObject; SourceDS: TDataSet;DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean;beginif Assigned(FOnDeltaRecordUpdate thenFOnDeltaRecordUpdate(S

6、ender, SourceDS, DeltaDS, UpdateKind, Applied;end;procedure TServerMethods1.UpdateEmployeesDelta(Sender: TObject; SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean;varSQLStr, Clause1, Clause2: string;I: Integer;beginApplied := False;case UpdateKind of$R

7、EGION Process ukModifyukModify:beginwith DeltaDS dobeginfor I := 0 to Fields.Count - 1 doif not FieldsI.IsNull thenClause1 := Clause1 + , + FieldsI.FieldName + =: + FieldsI.FieldName;Clause1 := System.Copy(Clause1, 2, Length(Clause1 - 1;SQLStr := UPDA TE EMPLOYEES SET + Clause1 + WHERE EMPLOYEE_ID =

8、 :EMPLOYEE_ID;end;with SQLDataSet1 dobeginClose;CommandText := SQLStr;if not DeltaDS.FieldsI.IsNull thenParamByName(DeltaDS.FieldsI.FieldName.Value := DeltaDS.FieldsI.Value;ParamByName(EMPLOYEE_ID.AsInteger := DeltaDS.FieldByName(EMPLOYEE_ID.OldValue;if (ExecSQL( = 0 then Abort;end;end;$ENDREGION$RE

9、GION Process ukInsertukInsert:beginbeginif not DeltaDS.FieldsI.IsNull thenbeginClause1 := Clause1 + , + DeltaDS.FieldsI.FieldName;Clause2 := Clause2 + , : + DeltaDS.FieldsI.FieldName;end;end;Clause1 := System.Copy(Clause1, 2, Length(Clause1 - 1;Clause2 := System.Copy(Clause2, 2, Length(Clause2 - 1;S

10、QLStr := INSERT INTO EMPLOYEES( + Clause1 + VALUES( + Clause2 + ;with SQLDataSet1 dobeginClose;CommandText := SQLStr;if not DeltaDS.FieldsI.IsNull thenParamByName(DeltaDS.FieldsI.FieldName.Value := DeltaDS.FieldsI.Value;if (ExecSQL( = 0 then Abort;end;end;$ENDREGION$REGION Process ukDeleteukDelete:b

11、eginSQLStr := DELETE EMPLOYEES WHERE EMPLOYEE_ID = :EMPLOYEE_ID;with SQLDataSet1 dobeginClose;CommandText := SQLStr;ParamByName(EMPLOYEE_ID.AsInteger := DeltaDS.FieldByName(EMPLOYEE_ID.OldValue;if (ExecSQL( = 0 then Abort;end;end;$ENDREGIONend;Applied := True;end;function TServerMethods1.UpdateEmplo

12、yees(EMPDelta: OleVariant: Boolean;varErrorCount: Integer;begin/指定Employees的专有更新过程FOnDeltaRecordUpdate := UpdateEmployeesDelta;DataSetProvider1.ApplyUpdates(EMPDelta, 0, ErrorCount;Result := ErrorCount = 0;end;1、增加一个DataSetProvider1控件,让它与SQLDataSet1控件关联。2、申明一个事件句柄FOnDeltaRecordUpdate: TBeforeUpdateR

13、ecordEvent,然后在DataSetProvider1的BeforeUpdateRecord事件过程中,再嵌套一个TBeforeUpdateRecordEvent 事件。3、申明和定义一个私有过程UpdateEmployeesDelta,其参数定义遵循TBeforeUpdateRecordEvent事件过程的定义,此过程专门用来处理Employees的更新。实际上,所有表的更新只有Update、Insert和Delete三种类型,针对当前传入的Delta包,可以手动构造出SQL语句。这里只有具有的表不一样,但处理流程都是完全一样的,所以,若有其他新表的更新,可以直接套用相关代码段,甚至可

14、以做成一个专门的一个通用过程。4、申明和定义UpdateEmployees函数,此方法是暴露给客户端进行调用的。若客户端有多个表需要在一个事务中完成更新,则可以在此过程中手动启动事务,分别完成多个表Delta 数据包的更新,最后提交或rollback。若有新的方法暴露给客户端来更新其他表的时候,只需要相应地再创建一个私有更新过程,并把此过程指针赋给FOnDeltaRecordUpdate即可。客户端代码:通过在客户端中的TSQLConection控件,可以生成DataSnap client class:.TServerMethods1Client = classprivateFDBXConne

15、ction: TDBXConnection;FInstanceOwner: Boolean;FEchoStringCommand: TDBXCommand;FReverseStringCommand: TDBXCommand;FGetEmployeeFullNameCommand: TDBXCommand;FGetEmployeesCommand: TDBXCommand;FGetJobsCommand: TDBXCommand;FUpdateEmployeesCommand: TDBXCommand;publicconstructor Create(ADBXConnection: TDBXC

16、onnection; overload;constructor Create(ADBXConnection: TDBXConnection; AInstanceOwner: Boolean;overload;destructor Destroy; override;function GetEmployeeFullName(EmployeeId: Integer: string;function GetEmployees: TDataSet;function UpdateEmployees(EMPDelta: OleVariant: Boolean;end;.在调用模块中,比如一个按钮事件中完成Employees被更新数据的提交,其中FMethodProxy为TServerMethods1Client的实例:.procedure TForm2.Button2Click(Sender: TObject;beginif ClientDataSet1.ChangeCount 0 thenbeginFMethodProxy.UpdateEmployees(ClientDataSet1.Delta;end;end;.通过上述方式,中间层业务类模块,只需要三个控件,就可以完成现在和今后所有的数据读取和更新操作。更重要的是,由于每次方法调用之间是不需要客