Console
The DynamicWhere sample demonstrates 2 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:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\<language>\DynamicWhere (Console), 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.
Included with the sample is a DataTableExtension class which has a single public static method (LogToString) that takes a DataTable object and creates a nicely formatted string representation of the table that can be printed to the Console which can be useful for debugging purposes. (An example of its output can be seen at the end of this document)
Running the Sample
When run the sample sets up a connection to an instance of Relativity Server and logs in, lastly defines the fields that should be returned using Dynamic Select (Step 1). It then creates a simple Dynamic Where condition that retrieves all rows in the "Users" table where the Role field contains the word "Manager" and prints the results to the console (Step 2). Finally a more complex condition is created that matches against rows where the Role field contains "Manager" and the Name field begins with "J", or the Id value is either 20, 22 or 24 (Step 3).
Examining the Code
All of the code can be found in the Program source file. The "Users" schema table used in this sample is comprised of 8 fields, which this sample will limit to just 4 using Dynamic Select. There are 25 rows in the table which will be filtered down to just 9 or 5 depending on the dynamic where condition.
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.
As the sample is using Dynamic Select as well, instead of passing the WhereExpression directly to the Fill method of RemoteDataAdapter, it is passed to the WhereClause property of TableRequestInfoV5, though it needs to be converted XML.
Finally pass the DataTable and the WhereExpression to the Fill method of RemoteDataAdapter.
Console.WriteLine("STEP 2. Using Simple Condition ...");
Console.WriteLine("Select Managers");
WhereExpression lCondition =
new BinaryExpression(
new FieldExpression("Role"),
new ConstantExpression("%Manager%", DataType.String),
BinaryOperator.Like
);
Console.WriteLine("Condition: {0}", lCondition.ToSQLString());
DataTable lTable = new DataTable("Users");
Boolean lApplySchema = true;
lRequestInfo.WhereClause = lCondition.ToXmlNode();
lDataAdapter.Fill(lTable, lRequestInfo, lApplySchema);
Console.WriteLine("STEP 2. Using Simple Condition ...")
Console.WriteLine("Select Managers")
var lCondition: WhereExpression! = BinaryExpression(FieldExpression("Role"), ConstantExpression("%Manager%", DataType.String), BinaryOperator.Like)
Console.WriteLine("Condition: {0}", lCondition.ToSQLString())
var lTable: DataTable! = DataTable("Users")
var lApplySchema: Boolean! = true
lRequestInfo.WhereClause = lCondition.ToXmlNode()
lDataAdapter.Fill(lTable, lRequestInfo, lApplySchema)
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.
As the sample is using Dynamic Select as well, instead of passing the WhereExpression directly to the Fill method of RemoteDataAdapter, it is passed to the WhereClause property of TableRequestInfoV5, though it needs to be converted XML.
Finally pass the DataTable and the WhereExpression to the Fill method of RemoteDataAdapter
Console.WriteLine("STEP 3. Using Complex Condition ...");
Console.WriteLine("Select Managers with name started with 'J' OR users with Id 20, 22, 24");
lCondition =
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);
Console.WriteLine("Condition: {0}", lCondition.ToSQLString());
lRequestInfo.WhereClause = lCondition.ToXmlNode();
lTable = new DataTable("Users");
lDataAdapter.Fill(lTable, lRequestInfo, lApplySchema);
Console.WriteLine("STEP 3. Using Complex Condition ...")
Console.WriteLine("Select Managers with name started with \'J\' OR users with Id 20, 22, 24")
lCondition = 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)
Console.WriteLine("Condition: {0}", lCondition.ToSQLString())
lRequestInfo.WhereClause = lCondition.ToXmlNode()
lTable = DataTable("Users")
lDataAdapter.Fill(lTable, lRequestInfo, lApplySchema)
Example Output
This is an example of the output you will see when the sample is run. You can see in Step 2 that all of the returned rows have a value in the Role field that contains the word "Manager" and then all the rows that match the more complex condition used in Step 3.
Dynamic Where sample has been started.
Target URL is http://localhost:7099/bin
RO SDK layer is configured.
RO DataAbstract layer is configured.
STEP 1: Login to DataService
Login string is User Id="simple";Password="simple";Domain="DASamples";Schema="Simple"
Login has been successful. Going further...
STEP 2. Using Simple Condition ...
Select Managers
Condition: (Role like "%Manager%")
Table: Users (9 rows from 9)
|---------------------------------------------------------------------|
| Id | Name | Type | Role |
|---------------------------------------------------------------------|
| 2 | Gale Dalton | 1 | Income Manager |
| 3 | Matthew Decker | 1 | Income Manager |
| 4 | Joseph Henson234 | 1 | Income Manager |
| 5 | Jennifer Kent | 1 | Sales Manager |
| 6 | Sandy Manning | 1 | Sales Manager |
| 7 | Glenn Cunningham | 1 | Sales Manager |
| 8 | Marcy Collins | 1 | Sales Manager |
| 9 | Kirsten Rosario | 1 | Sales Manager |
| 10 | Merle Frank | 1 | Sales Manager |
|---------------------------------------------------------------------|
STEP 3. Using Complex Condition ...
Select Managers with name started with 'J' OR users with Id 20, 22, 24
Condition: (((Name like "J%") and (Role like "%Manager%")) or (Id in [20, 22, 24]))
Table: Users (5 rows from 5)
|---------------------------------------------------------------------|
| Id | Name | Type | Role |
|---------------------------------------------------------------------|
| 4 | Joseph Henson234 | 1 | Income Manager |
| 5 | Jennifer Kent | 1 | Sales Manager |
| 20 | Anna H. Kugler... | 3 | Guest |
| 22 | Kenny S. Lay | 3 | Guest |
| 24 | Lillie R. Schr... | 3 | Guest |
|---------------------------------------------------------------------|
DONE! Please press return to exit.