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:
|
||||||||||
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. | ||||||||||
ServerESQCache |
ServerESQCache |
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 | EntitySchemaQuery |
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:
|
||||||||||||||||||||||||
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:
|
||||||||||||||||||||||||
MacrosType | EntitySchemaQuery |
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:
|
||||||||||||||||||||||||
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:
|
||||||||||||||||||||||||||||||||
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.