Documentation

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

DataService. Batch queries

Glossary Item Box

General provisions

The DataService web service of bpm'online is a RESTful (Representational State Transfer, REST) service. The 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, 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.

Bach queries

Batch queries are used to minimize requests to DataServise, which improves application performance. Packet query is a collection that contains a custom set of DataService requests. The query data is transferred to DataService via HTTP, with the help of POST by the following URL:

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

The data that comprises a batch query can be passed in different formats. One of the more convenient formats is JSON: The structure of a batch query in JSON format is as follows:

{
    "items": [
        {
            "__type": "[Full qualified name of the query type]",
            //One-time query contents.
            ...
        },
        // Other one-time queries.
        ...
    ]
}

To generate the contents of one-time queries that comprise a batch query, use the following data constants: InsertQuery, SelectQuery, UpdateQuery and DeleteQuery.

Example of using a batch query in a third-party application

Case description

Create a console application that will use DataService to:

  • Add a contact record with the value "John Smith" in the [Full name] column;

  • Change the value of the [Business phone] column to 012 345 67 89 for all contact records that have "John Smith" as the value in the [Full name] column.

Records must be added and modified via a batch query.

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 the project name, for example, DataServiceBatchExample. 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:

// Primary URL of bpm'online application. Must be repoaced with a custom one.
private const string baseUri = @"http://example.bpmonline.com";
// Request string to the Login methid of the AuthService.svc service.
private const string authServiceUri = baseUri + @"/ServiceModel/AuthService.svc/Login";
// Path string for the BatchQuery.
private const string batchQueryUri = baseUri + @"/0/DataService/json/reply/BatchQuery";
// 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 the query to AuthService.svc for user authentication, are available in the "Authenticating external requests to bpm'online services" article.

4. Implement query adding request

Because the batchQueryUri constant declared previously 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 description. Use data contract classes to create separate queries then serialize them in a string.

For a query to add a contact record with the name "John Smith", add the following program code:

// Insert query.
var insertQuery = new InsertQuery()
{
    RootSchemaName = "Contact",
    ColumnValues = new ColumnValues()
    {
        Items = new Dictionary<string, ColumnExpression>()
        {
            {
                "Name",
                new ColumnExpression()
                {
                    ExpressionType = EntitySchemaQueryExpressionType.Parameter,
                    Parameter = new Parameter
                    {
                        Value = "John Smith",
                        DataValueType = DataValueType.Text
                    }
                }
            }
        }
    }
};

For more information on the InsertQuery data contract please see the "DataService. Adding records" article.

To change the value of the [Business phone] column to 012 345 67 89 for all contact records that have "John Smith" value in the [Full name] column, add the following code:

 // Update query.
var updateQuery = new UpdateQuery()
{
    RootSchemaName = "Contact",
    ColumnValues = new ColumnValues()
    {
        Items = new Dictionary<string, ColumnExpression>()
        {
            {
                "Phone",
                new ColumnExpression()
                {
                    ExpressionType = EntitySchemaQueryExpressionType.Parameter,
                    Parameter = new Parameter()
                    {
                        Value = "0123456789",
                        DataValueType = DataValueType.Text
                    }
                }
            }
        }
    },
    Filters = new Filters()
    {
        FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
        Items = new Dictionary<string, Filter>()
        {
            {
                "FilterByName",
                new Filter
                {
                    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
                    ComparisonType = FilterComparisonType.Equal,
                    LeftExpression = new BaseExpression()
                    {
                        ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                        ColumnPath = "Name"
                    },
                    RightExpression = new BaseExpression()
                    {
                        ExpressionType = EntitySchemaQueryExpressionType.Parameter,
                        Parameter = new Parameter()
                        {
                            DataValueType = DataValueType.Text,
                            Value = "John Smith"
                        }
                    }
                }
            }
        }
    }
};

For more information on the UpdateQuery data contract, please see the "DataService. Updating records" article.

After serializing the created instances of the query class, add information about the qualified name of the corresponding data contract to the strings with JSON objects. Compose the string with batch query:

// Serialization of update query class instance in a JSON string.
var jsonInsert = new JavaScriptSerializer().Serialize(insertQuery);
// Inserting query type in a JSON string.
jsonInsert = jsonInsert.Insert(1, @"""__type"": ""Terrasoft.Nui.ServiceModel.DataContract.InsertQuery"",");
// Serialization of instance of the update query class in a JSON string.
var jsonUpdate = new JavaScriptSerializer().Serialize(updateQuery);
// Inserting query type in a JSON string.
jsonUpdate = jsonUpdate.Insert(1, @"""__type"": ""Terrasoft.Nui.ServiceModel.DataContract.UpdateQuery"",");
// Creating batch query.
var json = @"{""items"": [" + jsonInsert + "," + jsonUpdate + "]}";

The next step is to execute the 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, then execute the DataService query and process its result. To do this, add the following source code:

// Converting a JSON object string in a byte array.
byte[] jsonArray = Encoding.UTF8.GetBytes(json);
// Creating an instance of HTTP request.
var batchRequest = HttpWebRequest.Create(deleteQueryUri) as HttpWebRequest;
// Defining request method.
batchRequest.Method = "POST";
// Determining request content.
batchRequest.ContentType = "application/json";
// Adding authentication cookie received earlier to a query.
batchRequest.CookieContainer = AuthCookie;
// Set the ContentLength property of the WebRequest.
batchRequest.ContentLength = jsonArray.Length;

// Adding JSON object to the query contents.
using (var requestStream = batchRequest.GetRequestStream())
{
    requestStream.Write(jsonArray, 0, jsonArray.Length);
}
// Executing HTTP request and getting reply from server.
using (var response = (HttpWebResponse)batchRequest.GetResponse())
{
    // Displaying response in console.
    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
    {
        Console.WriteLine(reader.ReadToEnd());
    }
}
// Application execution delay.
Console.ReadKey();

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?