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

DataService. Reading records

Glossary Item Box

General provisions

The DataService web service of Creatio 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.

With the use of the DataService, the data can be automatically configured in various data formats such as XML, JSON, HTML, CSV, and JSV. The data structure is determined by data contracts. A complete list of data contracts used by the DataService, can be found in the "DataService" 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)://[Creatio application address]/[Configuration number]/dataservice/[Data fromat]/reply/SelectQuery
// URL example of the POST query to read data from DataService.
http(s)://example.creatio.com/0/dataservice/json/reply/SelectQuery

The SelectQuery data contract has a complex hierarchical structure with a number of nesting levels. In the Creatio 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.

See Also

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?