Desktop

The DynamicWhere sample demonstrates 3 different ways to use Dynamic Where to selectively retrieve data from a table in an instance of Relativity Server.

Getting Started

The sample is typically located in C:\Program Files (x86)\RemObjects Software\Data Abstract for .NET\Samples\<language>\DynamicWhere, though you may have installed the Data Abstract for .NET SDK and it's samples in another location.

To build it, you will of course need Visual Studio, and like all the samples provided with Data Abstract for .NET you will need to be running Relativity Server with the DASamples Domain and the Simple Schema available.

Running the Sample

The UI is comprised of a table that displays the data, and a toolbar that has a combo box for the server address and another for choosing which of the three Dynamic Where clauses to use to retrieve the table data from Relativity Server. Clicking on one of the available clauses will cause the sample to retrieve the table data and update the UI.

The three Dynamic Where clauses available are:

  • "All Users" which will retrieve all users from the "Users" table.
  • "Simple Condition" will retrieve all rows where the Role field contains the word "Manager".
  • "Complex Condition" builds on the "Simple Condition" to return rows where the Role field contains "Manager" and the Name field begins with J, or any rows that match the Id value of 20,22 or 24.

When the sample is run, if an instance of Relativity Server is running on the local machine then the "All Users" Dynamic Where clause will be used to to retrieve all the "User" records from the "Users" table.

Examining the Code

The sample is comprised of two classes, DataModule which handles the interaction with the schema and MainForm that handles the UI interaction. The code discussed below comes from the DataModule class.

The "Users" table is in the "simple" schema which is part of the "DASamples" domain.

Getting all the data

This section covers the "All Data" condition, it discusses creating a basic WhereExpression that will retrieve all the records in the "Users" table. The expression will be evaluated against each row in the table.

To build a the where expression the first step is to create a BinaryExpression which is one of the main types of expressions used to form WHERE clauses. It takes three arguments, the first two are used for comparison and the last, an instance of BinaryOperator, determines how those arguments are compared for example if the left and right arguments are the same or the right argument contains the left.

To retrieve all the rows, we need to create a dynamic where clause that evaluates to true for each row. Here we create a BinaryExpression which will take two ConstantExpressions which have a value of 1 (true) and a BinaryOperator that indicates that the ConstantExpressions should be tested for equality.

The where expression is then passed to the Fill method of RemoteDataAdapter along with the DataTable that will contain the table data.

 

public void GetAllData()
{
    this.usersTable.Clear();
    this.Description = "Condition: All users";
    WhereExpression trueCondition = new BinaryExpression(
            new ConstantExpression("1"),
            new ConstantExpression("1"),
            BinaryOperator.Equal
        );
    Boolean applySchema = true;
    this.DataAdapter.Fill(this.usersTable, trueCondition, applySchema);
}

 

method DataModule.GetAllData;
begin
  self.usersTable.Clear();
  self.Description := 'Condition: All users';
  var trueCondition: WhereExpression := new BinaryExpression(new ConstantExpression(1), new ConstantExpression(1), BinaryOperator.Equal);
  var applySchema: Boolean := true;
  self.DataAdapter.Fill(self.usersTable, trueCondition, applySchema)
end;

 

public func GetAllData() {

    self.usersTable.Clear()
    self.Description = "Condition: All users"
    var trueCondition: WhereExpression! = BinaryExpression(ConstantExpression("1"), ConstantExpression("1"), BinaryOperator.Equal)
    var applySchema: Boolean! = true
    self.DataAdapter.Fill(self.usersTable, trueCondition, applySchema)
}

 

Public Sub GetAllData()
    Me.usersTable.Clear()
    Me.Description = "Condition: All users"
    Dim trueCondition As WhereExpression = New BinaryExpression(New ConstantExpression("1"), New ConstantExpression("1"), BinaryOperator.Equal)
    Dim applySchema As Boolean = True
    Me.DataAdapter.Fill(Me.usersTable, trueCondition, applySchema)
End Sub

Using a simple DynamicWhere condition

This section covers the "Simple Condition" dynamic where expression which will retrieve all records from the "Users" table where the Role field contains the word "Manager".

To build the where expression needed here the first step is to create a BinaryExpression which will take three arguments, the first is a FieldExpression whose constructor takes the name of the table field to match against. The second argument is an instance of ConstantExpression which will contain the value that will be tested against the value in the table field and the last argument is an instance of BinaryOperator that will determine how to compare the ConstantExpression against the value in the field.

The where expression is then passed to the Fill method of RemoteDataAdapter along with the DataTable that will contain the table data.

 

public void GetDataUsingSimpleDynamicWhere()
{
    this.usersTable.Clear();
    this.Description = "Condition: Role CONTAINS 'manager'";
    WhereExpression simpleCondition =
        new BinaryExpression(
            new FieldExpression("Role"),
            new ConstantExpression("%Manager%", DataType.String),
            BinaryOperator.Like
        );
    Boolean applySchema = true;
    this.DataAdapter.Fill(this.usersTable, simpleCondition, applySchema);
}

 

method DataModule.GetDataUsingSimpleDynamicWhere;
begin
  self.usersTable.Clear();
  self.Description := 'Condition: Role CONTAINS ''manager''';
  var simpleCondition: WhereExpression := new BinaryExpression(new FieldExpression('Role'), new ConstantExpression('%Manager%', DataType.String), BinaryOperator.Like);
  var applySchema: Boolean := true;
  self.DataAdapter.Fill(self.usersTable, simpleCondition, applySchema)
end;

 

public func GetDataUsingSimpleDynamicWhere() {

    self.usersTable.Clear()
    self.Description = "Condition: Role CONTAINS \'manager\'"
    var simpleCondition: WhereExpression! = BinaryExpression(FieldExpression("Role"), ConstantExpression("%Manager%", DataType.String), BinaryOperator.Like)
    var applySchema: Boolean! = true
    self.DataAdapter.Fill(self.usersTable, simpleCondition, applySchema)
}

 

Public Sub GetDataUsingSimpleDynamicWhere()
    Me.usersTable.Clear()
    Me.Description = "Condition: Role CONTAINS 'manager'"
    Dim simpleCondition As WhereExpression = New BinaryExpression(New FieldExpression("Role"), New ConstantExpression("%Manager%", DataType.String), BinaryOperator.Like)
    Dim applySchema As Boolean = True
    Me.DataAdapter.Fill(Me.usersTable, simpleCondition, applySchema)
End Sub

Using a complex DynamicWhere condition

This section covers the "Complex Condition" dynamic where expression which will retrieve all records from the "Users" table where the Role field contains the word "Manager" and the Name field begins with a "J", or any records where the Id value is equal to 20, 22 or 24.

Building the required where expression is similar to before, we need to create two BinaryExpressions the first which will match the Role and Name fields, the second will handle matching the Id values. The last argument is an "OR" BinaryOperator to indicate that we want a match if either expression is true.

The first BinaryExpression is comprised of two BinaryExpressions which are essentially the same. They are comprised of a FieldExpression whose constructor takes the name of the table field to match against and the second argument is an instance of ConstantExpression which will contain the value that will be tested against the value in the table field. The last argument is an instance of BinaryOperator that will determine how to compare the ConstantExpression against the value in the field.

The second BinaryExpression has a FieldExpression whose constructor takes the name of the table field to match against as the first argument, the second argument is a ListExpression which takes an array of WhereExpressions which has the values we want to match against. The IN BinaryOperator will test if the value in the field is in the list.

The where expression is then passed to the Fill method of RemoteDataAdapter along with the DataTable that will contain the table data.

 

public void GetDataUsingComplexDynamicWhere()
{
    this.usersTable.Clear();
    this.Description = "Condition: (Role CONTAINS 'manager') AND (Name BEGINSWITH 'J') OR (Id IN (20, 22, 24))";
    WhereExpression complexCondition =
        new BinaryExpression(
            new BinaryExpression(
                new BinaryExpression(
                    new FieldExpression("Name"),
                    new ConstantExpression("J%", DataType.String),
                    BinaryOperator.Like
                ),
                new BinaryExpression(
                    new FieldExpression("Role"),
                    new ConstantExpression("%Manager%", DataType.String),
                    BinaryOperator.Like
                ),
                BinaryOperator.And
            ),
            new BinaryExpression(
                new FieldExpression("Id"),
                    new ListExpression(
                        new WhereExpression[] {
                            new ConstantExpression(20, DataType.Integer),
                            new ConstantExpression(22, DataType.Integer),
                            new ConstantExpression(24, DataType.Integer)
                        }
                    ),
                    BinaryOperator.In
            ),
            BinaryOperator.Or
       );
    Boolean applySchema = true;
    this.DataAdapter.Fill(this.usersTable, complexCondition, applySchema);
}

 

method DataModule.GetDataUsingComplexDynamicWhere;
begin
  self.usersTable.Clear();
  self.Description := 'Condition: (Role CONTAINS ''manager'') AND (Name BEGINSWITH ''J'') OR (Id IN (20, 22, 24))';
  var complexCondition: WhereExpression := new BinaryExpression(new BinaryExpression(new BinaryExpression(new FieldExpression('Name'), new ConstantExpression('J%', DataType.String), BinaryOperator.Like), new BinaryExpression(new FieldExpression('Role'), new ConstantExpression('%Manager%', DataType.String), BinaryOperator.Like), BinaryOperator.And), new BinaryExpression(new FieldExpression('Id'), new ListExpression(array of WhereExpression([new ConstantExpression(20, DataType.Integer), new ConstantExpression(22, DataType.Integer), new ConstantExpression(24, DataType.Integer)])), BinaryOperator.In), BinaryOperator.Or);
  var applySchema: Boolean := true;
  self.DataAdapter.Fill(self.usersTable, complexCondition, applySchema)
end;

 

public func GetDataUsingComplexDynamicWhere() {

    self.usersTable.Clear()
    self.Description = "Condition: (Role CONTAINS \'manager\') AND (Name BEGINSWITH \'J\') OR (Id IN (20, 22, 24))"
    var complexCondition: WhereExpression! = BinaryExpression(BinaryExpression(BinaryExpression(FieldExpression("Name"), ConstantExpression("J%", DataType.String), BinaryOperator.Like), BinaryExpression(FieldExpression("Role"), ConstantExpression("%Manager%", DataType.String), BinaryOperator.Like), BinaryOperator.And), BinaryExpression(FieldExpression("Id"), ListExpression(([ConstantExpression(20, DataType.Integer), ConstantExpression(22, DataType.Integer), ConstantExpression(24, DataType.Integer)] as? WhereExpression![])), BinaryOperator.In), BinaryOperator.Or)
    var applySchema: Boolean! = true
    self.DataAdapter.Fill(self.usersTable, complexCondition, applySchema)
}

 

Public Sub GetDataUsingComplexDynamicWhere()
    Me.usersTable.Clear()
    Me.Description = "Condition: (Role CONTAINS 'manager') AND (Name BEGINSWITH 'J') OR (Id IN (20, 22, 24))"
    Dim complexCondition As WhereExpression = New BinaryExpression(New BinaryExpression(New BinaryExpression(New FieldExpression("Name"), New ConstantExpression("J%", DataType.String), BinaryOperator.Like), New BinaryExpression(New FieldExpression("Role"), New ConstantExpression("%Manager%", DataType.String), BinaryOperator.Like), BinaryOperator.And), New BinaryExpression(New FieldExpression("Id"), New ListExpression(New WhereExpression() {New ConstantExpression(20, DataType.Integer), New ConstantExpression(22, DataType.Integer), New ConstantExpression(24, DataType.Integer)}), BinaryOperator.In), BinaryOperator.Or)
    Dim applySchema As Boolean = True
    Me.DataAdapter.Fill(Me.usersTable, complexCondition, applySchema)
End Sub