Creatio development guide
PDF
This documentation is valid for Creatio version 7.10.0. We recommend using the newest version of Creatio documentation.

DataService. Reading records

Glossary Item Box

General provisions

The DataService web service of bpm'online is a RESTful (Representational State Transfer, REST) service. RESTful data management interface does not require converting data to an external format, such as XML. In a simple RESTful service, each information unit is determined by a global Identifier such as URL. Each URL, in its turn, has a strictly specified format. This is not an optimal way to transfer large arrays of data.

Since the DataService web service is implemented based on the ServiceStack .NET framework, the data can be automatically converted into different file formats, such as XML, JSON, HTML, CSV and JSV. The data structure is determined by so-called data contracts. The data contracts used by DataService are listed in the "DataService web service" article.

SelectQuery data contract

The SelectQuery data contract is used for reading section records. The query data is transferred to DataService via HTTP, with the help of POST by the following URL:

// URL format of the POST query to read data from DataService.
http(s)://[Bpm'online application address]/[Configuration number]/dataservice/[Data fromat]/reply/SelectQuery
// URL example of the POST query to read data from DataService.
http(s)://example.bpmonline.com/0/dataservice/json/reply/SelectQuery

The SelectQuery data contract has a complex hierarchical structure with a number of nesting levels. In the bpm'online server core, it is represented by a SelectQuery class of theTerrasoft.Nui.ServiceModel.DataContract namespace of the Terrasoft.Nui.ServiceModel.dll library of classes. The hierarchical data structure of the SelectQuery data contract can be conveniently viewed in JSON format:

{
    "RootSchemaName":"[Object root schema name]",
    "OperationType":[Type of record operation],
    "Columns":{
        "Items":{
            "Name":{
                "OrderDirection":[Sorting order],
                "OrderPosition":[Column position],
                "Caption":"[Title]",
                "Expression":{
                    "ExpressionType":[Expression type],
                    "ColumnPath":"[Path to column]",
                    "Parameter":[Parameter],
                    "FunctionType":[Function type],
                    "MacrosType":[Macro type],
                    "FunctionArgument":[Function argument],
                    "DatePartType":[Type of date part],
                    "AggregationType":[Aggregation type],
                    "AggregationEvalType":[Aggregation scope],
                    "SubFilters":[Buit-in filters]
                }
            }
        }
    },
    "AllColumns":[Indicates that all columns are selected],
    "ServerESQCacheParameters":{
        "CacheLevel":[Caching level],
        "CacheGroup":[Caching group],
        "CacheItemName":[Record key in repository]
    },
    "IsPageable":[Indicates page-by-page],
    "IsDistinct":[Indicates uniqueness],
    "RowCount":[Number of selected records],
    "ConditionalValues":[Conditions for building a pageable query],
    "IsHierarchical":[Indicates hierarchical data selection],
    "HierarchicalMaxDepth":[Maximum nesting level of the hierarchical query],
    "HierarchicalColumnName":[Column name used to create hierarchical query],
    "HierarchicalColumnValue":[Initial value of hierarchical column],
    "Filters":[Filters]
    }
}

Primary properties of the SelectQuery class and their possible values are available in table 1.

Table 1. SelectQuery class properties

Property Type Notes
RootSchemaName string String that contains root schema name of the added record object.
OperationType QueryOperationType

Type of write operation. Specified as a QueryOperationType enumeration value of the Terrasoft.Nui.ServiceModel.DataContract name space. The QueryOperationType.Select value is set for SelectQuery.

Values of the QueryOperationType enumeration:

Select 0
Insert 1
Update 2
Delete 3
Batch 4
Columns SelectQueryColumns Contains a collection of the record columns being read. It has the SelectQueryColumns type defined in the Terrasoft.Nui.ServiceModel.DataContract name space. It must be configured if the AllColumns checkbox is set to false and a set of specific root schema columns, which does not include the [Id] column, is required.
AllColumns bool Indicates if all columns are selected. If the value is set to true, all columns of the root schema will be selected by the query.
ServerESQCacheParameters ServerESQCacheParameters Parameters of EntitySchemaQuery caching on server. The ServerESQCacheParameters type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.
IsPageable bool Indicates whether the data is selected page-by-page.
IsDistinct bool Indicates whether duplicates must be eliminated in the resulting data set.
RowCount int Number of selected strings. By default, the value is -1, i.e. all strings are selected.
ConditionalValues ColumnValues Conditions of creating a page-by-page query. The ColumnValues type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.
IsHierarchical bool Indicates whether the data is selected hierarchically.
HierarchicalMaxDepth int Maximum nesting level of a hierarchical query.
hierarchicalColumnName string Name of the column used for creating a hierarchical query.
hierarchicalColumnValue string Initial value of hierarchical column from which the hierarchy will be built.
Filters Filters Collection of query filters. The Filters type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.
ColumnValues ColumnValues Contains collection of column values for the added record. The ColumnValues type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.

The SelectQueryColumns class has a single Items property, defined as a collection of keys and values Dictionary<string, SelectQueryColumn>. The key is the string with the name of the added column. The value is an instance of the SelectQueryColumn class, defined in the Terrasoft.Nui.ServiceModel.DataContract name space. The properties of the SelectQueryColumn are available in the table 2.

Table 2. SelectQueryColumn class properties

Property Type Notes
OrderDirection OrderDirection Sorting order. Specified with a value from the OrderDirection enumeration of the Terrasoft.Common name space defined in the Terrasoft.Common class library.
OrderPosition int Sets position number in the collection of the query columns, by which the sorting is done.
Caption string Column title.
Expression ColumnExpression Property that defines expression of the type of selected column.

The ColumnExpression class defines expression that sets the type of the schema column. The class is defined in the Terrasoft.Nui.ServiceModel.DataContract name space of the Terrasoft.Nui.ServiceModel library. The properties of an instance of this class are filled in depending on the ExpressionType property, which sets the expression type. The full list of the ColumnExpression class properties is available in table 3.

Table 3. Primary properties of the ColumnExpression class

Property Type Notes
ExpressionType EntitySchemaQueryExpressionType

Type of expression that determines the value that the added column will contain. Specified with a value from the EntitySchemaQueryExpressionType enumeration of the Terrasoft.Core.Entities name space defined in the Terrasoft.Core class library. The EntitySchemaQueryExpressionType.Parameter value is set for InsertQuery.

Values of the EntitySchemaQueryExpressionType enumeration:

SchemaColumn 0 Schema column
Function 1 Function
Parameter 2 Parameter
SubQuery 3 Subquery
ArithmeticOperation 4 Arithmetic operation
ColumnPath string Path to the column in relation to the root schema. Rules for building paths are available in the "The use of EntitySchemaQuery for creation of queries in database" article.
Parameter Parameter

Determines the value that the added column will contain. The Parameter type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.

FunctionType FunctionType

Function type. Specified with a value from the FunctionType enumeration, which is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.

Values of the FunctionType enumeration:

None 0 Not defined
Macros 1 Macro
Aggregation 2 Aggregate function
DatePart 3 Part of date value
Length 4 Length
MacrosType EntitySchemaQueryMacrosType

Macro type. Specified with a value of the EntitySchemaQueryMacrosType enumeration, which is defined in the Terrasoft.Core.Entities name space.

FunctionArgument BaseExpression Function argument. Accepts a value if the function is defined with a parameter. The BaseExpression class is defined in the Terrasoft.Nui.ServiceModel.DataContract name space, is an ancestor for the ColumnExpresion class and has the same set of properties.
DatePartType DatePart

Part of date value Specified with a value from the DatePart enumeration, which is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.

Values of the DatePart enumeration:

None 0 Not defined
Day 1 Day
Week 2 Week
Month 3 Month
Year 4 Year
Weekday 5 Week day
Hour 6 Hour
HourMinute 7 Minute

AggregationType

AggregationType Aggregate function type. Specified with a value from the AggregationType enumeration defined in the Terrasoft.Common name space defined in the Terrasoft.Common class library.
AggregationEvalType AggregationEvalType Aggregate function scope. Specified with a value from the AggregationEvalType enumeration defined in the Terrasoft.Common name space defined in the Terrasoft.Common class library.
SubFilters Filters Collection of subquery filters. The Filters type is defined in the Terrasoft.Nui.ServiceModel.DataContract name space.

The Parameter class is defined in the Terrasoft.Nui.ServiceModel.DataContract name space. Its properties are available in table 4.

Table 4. Parameter class properties

Property Type Notes
DataValueType DataValueType

Type of data for the value that the added column will contain. Specified as a DataValueType enumeration value of the Terrasoft.Nui.ServiceModel.DataContract name space.

Values of the DataValueType enumeration:

Guid 0
Text 1
Integer 4
Float 5
Money 6
DateTime 7
Date 8
Time 9
Lookup 10
Enum 11
Boolean 12
Blob 13
Image 14
ImageLookup 16
Color 18
Mapping 26
Value object

The object that contains the value of the added column.

ArrayValue string[]

Array of the added column values. Used when serializing arrays and BLOBs.

ShouldSkipConvertion

bool

Indicates the need to skip the process of providing the type for the Value property.

The ServerESQCacheParameters class is defined in the Terrasoft.Nui.ServiceModel.DataContract name space. Its properties are available in table 5.

Table 5. ServerESQCacheParameters class properties

Property Type Notes
CacheLevel int

Data allocation level in the EntitySchemaQuery cache.

CacheGroup string

Caching group.

CacheItemName string

Repository record key.

The Filters class is defined in the Terrasoft.Nui.ServiceModel.DataContract name space. For details on the properties of this class and its use, please see the "DataService. Data filtering" article.

Example of reading records in a third-party application

Case description

Create a console application that uses DataService to read records from the [Contact] section with the following columns:

  • Id
  • Full name
  • Number of activities – aggregate column that displays the number of activities of this contact.

Case realization

The complete source code for implementation of this case is available here.

Case implementation algorithm

1. Create and set up a C# application project

Using the Microsoft Visual Studio development environment (version 2012 Update 4 and up), create a Visual C# console application project and specify project name, for example, DataServiceSelectExample. Set ".NET Framework 4.5" for the project property [Target framework].

In the References section of the project, add dependencies from the following libraries:

  • System.Web.Extensions.dll – class library included in .NET Farmework;
  • Terrasoft.Core.dll – library of base bpm'online server core classes. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Core.dll;
  • Terrasoft.Nui.ServiceModel.dll — application service class library. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Nui.ServiceModel.dll;
  • Terrasoft.Common.dll – library of base bpm'online server core classes. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Common.dll.

Add the "using" directives to the application source code file:

using System;
using System.Text;
using System.IO;
using System.Net;
using System.Collections.Generic;
using Terrasoft.Nui.ServiceModel.DataContract;
using Terrasoft.Core.Entities;
using System.Web.Script.Serialization;
using Terrasoft.Common;

2. Add fields and constants and field declarations to the source code

To access DataService features, add the following fields and constants to the application source code:

// Bpm'online primary application URL. Must be replaced with a custom one.
private const string baseUri = @"http://example.bpmonline.com";
// Query string to the Login method of the AuthService.svc service.
private const string authServiceUri = baseUri + @"/ServiceModel/AuthService.svc/Login";
// SelectQuery path string.
private const string selectQueryUri = baseUri + @"/0/DataService/json/SyncReply/SelectQuery";
// Bpm'online authentication cookie.
private static CookieContainer AuthCookie = new CookieContainer();

Here, three string fields are declared. These fields will be used to form authentication query and read data queries execution paths. Authentication data will be saved in the AuthCookie field.

3. Add method that performs bpm'online application authentication

Authentication is required to enable access of the created application to the DataService.

Both the algorithm and example of implementation method, which contains query to AuthService.svc for user authentication, are available in the "Authenticating external requests to bpm'online services" article.

4. Add implementation of the record add query

Because the selectQueryUri constant declared earlier contains a path for sending data in the JSON format, sent data must be configured beforehand as a string that contains a JSON object that corresponds to the SelectQuery data contract. This can be done directly in a string variable, although a much more secure and convenient way of doing this would be to create an instance of the SelectQuery class, fill out its properties and then serialize it to a string. This can be done with the help of the following source code:

// Instance of the query class.
var selectQuery = new SelectQuery()
{
    // Root schema name.
    RootSchemaName = "Contact",
    // Collection of query columns.
    Columns = new SelectQueryColumns()
};
// Expression that specifies the type of [[Full name] column.
var columnExpressionName = new ColumnExpression()
{
    // Expression type — schema column.
    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
    // Path to column.
    ColumnPath = "Name"
};
// Configuring the [Name] column. 
var selectQueryColumnName = new SelectQueryColumn()
{
    //Title.
    Caption = "Full name",
    // Sorting order — ascending.
    OrderDirection = OrderDirection.Ascending,
    // Sorting order position.
    OrderPosition = 0,
    // Expression that specifies column type.
    Expression = columnExpressionName
};
// Expression that specifies [Number of activities] column type.
var columnExpressionActivitiesCount = new ColumnExpression()
{
    // Expression type — subquery.
    ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
    // Path to column in relation to root schema.
    ColumnPath = "[Activity:Contact].Id",
    // Function type — aggregation.
    FunctionType = FunctionType.Aggregation,
    // Aggregation type — quantity.
    AggregationType = AggregationType.Count
};
// Configuring the [Number of activities] column.
var selectQueryColumnActivitiesCount = new SelectQueryColumn()
{
    //Title.
    Caption = "Number of activities",
    // Sorting direction — ascending.
    OrderDirection = OrderDirection.Ascending,
    // Sorting order position.
    OrderPosition = 1,
    // Expression, which specifies column type.
    Expression = columnExpressionActivitiesCount
};

// Adding columns to query.
selectQuery.Columns.Items = new Dictionary<string, SelectQueryColumn>()
{
    {
        "Name",
        selectQueryColumnName
    },
    {
        "ActivitiesCount",
        selectQueryColumnActivitiesCount
    }
};
// Serialization of an instance of query class to add to JSON string.
var json = new JavaScriptSerializer().Serialize(selectQuery);

The next step is to execute POST DataService query. To do this, create an instance of the HttpWebRequest class, fill its properties and connect the string with JSON object, created earlier, after which – execute the DataService query and process its result. To do this, add the following source code:

// Converting a JSON object string to a byte array.
byte[] jsonArray = Encoding.UTF8.GetBytes(json);
// Creating an instance of HTTP request.
var selectRequest = HttpWebRequest.Create(selectQueryUri) as HttpWebRequest;
// Defining request method.
selectRequest.Method = "POST";
// Defining request content type.
selectRequest.ContentType = "application/json";
// Adding earlier received authentication cookies to a data fetch query.
selectRequest.CookieContainer = AuthCookie;
// Set length for request content.
selectRequest.ContentLength = jsonArray.Length;
// Placing JSON object to request content.
using (var requestStream = selectRequest.GetRequestStream())
{
    requestStream.Write(jsonArray, 0, jsonArray.Length);
}
// Executing HTTP request and getting reply from server.
using (var response = (HttpWebResponse)selectRequest.GetResponse())
{
    // Displaying reply in console.
    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
    {
        Console.WriteLine(reader.ReadToEnd());
    }
}

The complete source code for implementation of this case is available here.

Example of reading records in bpm'online application

Case description

In the [Contacts] section, add a button which will open the method that will use DataService to read records in the [Contacts] section with the following columns:

  • Id
  • Full name
  • Number of activities – aggregate column, which displays the number of activities of this contact.

Case realization

The complete source code for implementation of this case is available here.

Case implementation algorithm

1. Add a button in the [Contacts] section

The process of adding a button in a section, is covered in the "How to add a button to a section".

Create a replacing client module of the [Contacts] section (Fig. 1).

Fig. 1. Properties of the replacing client module

In the created client schema, add SelectQueryContactButtonCaption localizable string and set its value to "Select contacts" (Fig. 2).

Fig. 2. Localizable string properties

Add a configuration object with the settings determining the button position to the diff array.

//Setup of section button display.
diff: /**SCHEMA_DIFF*/[
    // Metadata for adding a custom button in a section.
    {
        // Indicates that an elementis added on a page.
        "operation": "insert",
        // Meta name of the parent control element where the button is added.
        "parentName": "ActionButtonsContainer",
        // Indicates that the button is added to the control element collection
        // of parent element (meta-name specified in parentName).
        "propertyName": "items",
        // Meta-name of the added button.
        "name": "SelectQueryContactButton",
        // Additional properties of the element.
        "values": {
            // Type of added element - button.
            itemType: Terrasoft.ViewItemType.BUTTON,
            // Binding button title to a schema localizable string.
            caption: { bindTo: "Resources.Strings.SelectQueryContactButtonCaption" },
            // Binding of the button pressing handler method.
            click: { bindTo: "onSelectQueryContactClick" },
            "layout": {
                "column": 1,
                "row": 6,
                "colSpan": 1
            }
        }
    }

]/**SCHEMA_DIFF*/

2. Add handler method for the button pressing event

To enable reading the records when the button is clicked, add the following method to the methods section of the replacing client schema:

methods: {
    // Handler method for button click.
    onSelectQueryContactClick: function() {
        // Creating an instance of the Terrasoft.InsertQuery class.
        var select = Ext.create("Terrasoft.EntitySchemaQuery", {
            // Root schema name.
            rootSchemaName: "Contact"
        });
        // Adding the [Full name] column to query.
        select.addColumn("Name");
        // Adding [Number of activities] aggregate column to a query.
        select.addAggregationSchemaColumn(
            // Path to column in relation to the root schema.
            "[Activity:Contact].Id",
            // Aggregation type — quantity.
            Terrasoft.AggregationType.COUNT,
            // Column title.
            "ActivitiesCount",
            // Aggregation function scope - for all elements.
            Terrasoft.AggregationEvalType.ALL);
        // Update query to server
        // Getting whole collection of records and displaying it in the browser console.
        select.getEntityCollection(function(result) {
            if (!result.success) {
                // Processing/logging of error.
                this.showInformationDialog("Data query error");
                return;
            }
            // Displayed message.
            var message = "";
            // Analyzing resulting collection and generating displayed message.
            result.collection.each(function(item) {
                message += "Full name: " + item.get("Name") +
                ". Number of activities: " + item.get("ActivitiesCount") + "\n";
            });
            // Displaying message in console.
            window.console.log(message);
        }, this);
    }
}

NOTE

Unlike the previous example, authentication is not needed in this case, because the program code is executed by bpm'online directly.

In the client of the application core, there is not a class like the server core SelectQuery class. To select data from a section, use the Terrasoft.EntitySchemaQuery class. For more information on this class methods and properties, please see the "The use of EntitySchemaQuery implementation on client" article .

The complete source code for implementation of this case is available here.

See Also

© bpm'online 2002-2017.

Did you find this information useful?

How can we improve it?