客户端动态条件

有两种方法：

一、首先要设置服务端的服务AllowWhereSQL为True;

客户端

with ClientDataModule.dtCustomers do begin

    Close;

 

    // Prepares the custom WHERE clause

    Where.Clear;

    Where.AddConditions(['CustomerIdx','FirstName','LastName','City'],

                        [cEqual,cLike,cLike,cLike],

                        [eCode.Text,eFirstName.Text,eLastName.Text,

                         eCity.Text], opAnd);

     Close;

    Memo1.Text:=Where.Clause;

    if Where.Empty

      then stWhereClause.Caption := msg_NoCondition

      else stWhereClause.Caption := Where.Clause;

 

    MaxRecords := seMaxCustomers.Value;

 

    Open;

 end;

说明：
//一 复合条件
//    Where.AddConditions(['CustomerID'],
//                        [cEqual],
//                        ['ALFKI'], opAnd);
//二  增加一段条件
//    Where.AddText('CustomerID = ''ALFKI''');
//三  增加一个in条件
    Where.AddValueGroup('CustomerID',['ALFKI']);
// OpenBraket 增加一个左括号，CloseBraket增加一个右括号
//    Where.OpenBraket;
//    Where.CloseBraket;


二、首先要设置服务端的服务AllowDynamicWhere为True;

Operations between atomic expressions can be:

• comparison (less, less or equal, equal, greater or equal, greater, not equal).
• likewise (like) for string types.
• arithmetic (addition, substraction, multiplication, division).
• occurrences (in) for list expressions.

The value of the expression can be changed by unary operators (minus, not).

Examples

WHERE LastName LIKE '%in%'

begin
  with  ClientDataModule.tbl_Employees do
  begin
    Close;
    DynamicWhere.Clear;
    DynamicWhere.Expression :=
      DynamicWhere.NewBinaryExpression(
        DynamicWhere.NewField('Employees', 'LastName'),
        DynamicWhere.NewConstant('%in%', datString),
        dboLike);
    Open();
  end;
end;

WHERE (LastName LIKE '%in%') OR (Not(EmployeeID = 5))

begin
  with  ClientDataModule.tbl_Employees do
  begin
    Close;
    DynamicWhere.Clear;
    DynamicWhere.Expression  := DynamicWhere.NewBinaryExpression(
      DynamicWhere.NewBinaryExpression(
        DynamicWhere.NewField('Employees', 'LastName'),
        DynamicWhere.NewConstant('%in%', datString),
        dboLike
      ),
      DynamicWhere.NewUnaryExpression(
        DynamicWhere.NewBinaryExpression(
          DynamicWhere.NewField('Employees', 'EmployeeID'),
          DynamicWhere.NewConstant(5, datInteger),
          dboEqual),
        duoNot
      ),
      dboOr
    );
    Open();
  end;
end;

WHERE ((LastName LIKE '%in%') OR (Not(EmployeeID = 5)))  AND (FirstName IN ('Nancy', 'Robert'))

var
  lWhereExpression: TDAWhereExpression;
begin
  with  ClientDataModule.tbl_Employees do
  begin
    Close;
    DynamicWhere.Clear;
    lWhereExpression :=
    DynamicWhere.NewBinaryExpression(
      DynamicWhere.NewBinaryExpression(
        DynamicWhere.NewBinaryExpression(
          DynamicWhere.NewField('Employees', 'LastName'),
          DynamicWhere.NewConstant('%in%', datString), dboLike),
        DynamicWhere.NewUnaryExpression(
          DynamicWhere.NewBinaryExpression(
            DynamicWhere.NewField('Employees', 'EmployeeID'),
            DynamicWhere.NewConstant(5, datInteger),
            dboEqual
          ),
          duoNot
        ),
      dboOr
      ),
      DynamicWhere.NewBinaryExpression(
        DynamicWhere.NewField('Employees', 'FirstName'),
        DynamicWhere.NewList([
          DynamicWhere.NewConstant('Robert', datString),
          DynamicWhere.NewConstant('Nancy', datString)
        ]),
        dboIn
      ),
      dboAnd
    );
    DynamicWhere.Expression := lWhereExpression;
    Open();
  end;
end;

WHERE (Upper(LastName) LIKE '%IN%')

begin
  with  ClientDataModule.tbl_Employees do
  begin
    Close;
    DynamicWhere.Clear;
    DynamicWhere.Expression :=
      DynamicWhere.NewBinaryExpression(
        DynamicWhere.NewMacro('UpperCase(LastName)'),
        DynamicWhere.NewConstant('%IN%', datString),
        dboLike);
    Open();
  end;
end;
end;