Access data directly

Level: intermediate

The back-end core components provide the following database access options:

• access using the ORM model
• direct access

We recommend using the ORM model to access data. That said, direct access to the database is also implemented in the back-end core components. Learn more about running database queries using the ORM model: Access data through ORM.

This article covers direct database queries.

Use the following classes to manage data directly:

• Terrasoft.Core.DB.Select builds queries to retrieve records from the database table.
• Terrasoft.Core.DB.Insert builds queries to add records to the database table.
• Terrasoft.Core.DB.InsertSelect builds queries to add records to the database table based on the results of the queries to retrieve records from the database table.
• Terrasoft.Core.DB.Update builds queries to modify records in the database table.
• Terrasoft.Core.DB.UpdateSelect builds queries to modify records in the database table based on the results of the queries to retrieve records from the database table.
• Terrasoft.Core.DB.Delete builds queries to delete records from the database table.
• Terrasoft.Core.DB.DBExecutor builds and runs complex database queries. For example, queries that contain several nested filters, various join combinations, etc.

Important

The Terrasoft.Core.DB.CustomQuery class is deprecated and will be removed in upcoming releases. We do not recommend using this class.

Retrieve data from the database

Use the following classes to retrieve data from the database:

• Terrasoft.Core.DB.Select retrieves data by accessing the database directly.
• Terrasoft.Core.Entities.EntitySchemaQuery retrieves data using the ORM model. Learn more about running database queries using the Terrasoft.Core.Entities.EntitySchemaQuery class: Access data through ORM.

The purpose of the Terrasoft.Core.DB.Select class is to build queries to select records from database tables. After you create and configure the class instance, Creatio will build a SELECT query to the Creatio database. You can add icons, filters, and restriction conditions to the query.

The Terrasoft.Core.DB.Select class major features:

• The resulting query does not apply the access permissions of the current user. The user can access every database table and record.
• The resulting query does not use the cache repository data.

As result of the query, is an instance that implements the System.Data.IDataReader interface or a scalar value of the corresponding type.

If you need to build queries to select database records that apply the access permissions of the user and use the cache repository data, use the Terrasoft.Core.Entities.EntitySchemaQuery class.

Add data to the database

Use the following classes to add data to the database:

• Terrasoft.Core.DB.Insert
• Terrasoft.Core.DB.InsertSelect

Add data in bulk

The purpose of the Terrasoft.Core.DB.Insert class is to build queries to add records to database tables. After you create and configure the class instance, Creatio will build an INSERT query to the Creatio database.

As result of the query, is the number of records added using the query.

The class implements multiline insertion via the Values() method. After calling the Values() method, all subsequent Set() method calls fall into the new ColumnsValues instance. If the ColumnsValuesCollection collection contains more than one dataset, Creatio will build a query that includes several Values() blocks.

Multiline insertion example

new Insert(UserConnection)
    .Into("Table")
    .Values()
    .Set("Column1", Column.Parameter(1))
    .Set("Column2", Column.Parameter(1))
    .Set("Column3", Column.Parameter(1))
    .Values()
    .Set("Column1", Column.Parameter(2))
    .Set("Column2", Column.Parameter(2))
    .Set("Column3", Column.Parameter(2))
    .Values()
    .Set("Column1", Column.Parameter(3))
    .Set("Column2", Column.Parameter(3))
    .Set("Column3", Column.Parameter(3))
    .Execute();

As a result, Creatio will generate the SQL query.

SQL query

-- For Microsoft SQL or PostgreSQL
INSERT INTO [dbo].[Table] (Column1, Column2, Column3)
VALUES (1, 1, 1),
    (2, 2, 2),
    (3, 3, 3)

-- For Oracle
INSERT ALL
    into Table (column1, column2, column3) values (1, 1, 1)
    into Table (column1, column2, column3) values (2, 2, 2)
    into Table (column1, column2, column3) values (3, 3, 3)
SELECT * FROM dual

The special aspects of multiline data insertion:

• Microsoft SQL supports up to 2100 parameters when using Column.Parameter in the Set() expression.

• If your query contains more parameters, the developer must divide the query into subqueries since Terrasoft.Core.DB.Insert does not support this option.

  Example

  IEnumerable <IEnumerable <ImportEntity>> GetImportEntitiesChunks(IEnumerable <ImportEntity> entities, IEnumerable <ImportColumn> keyColumns) {
      var entitiesList = entities.ToList();
      var columnsList = keyColumns.ToList();
      var maxParamsPerChunk = Math.Abs(MaxParametersCountPerQueryChunk / columnsList.Count + 1);
      var chunksCount = (int) Math.Ceiling(entitiesList.Count / (double) maxParamsPerChunk);
      return entitiesList.SplitOnParts(chunksCount);
  }
  
  var entitiesList = GetImportEntitiesChunks(entities, importColumns);
  entitiesList.AsParallel().AsOrdered()
      .ForAll(entitiesBatch => {
          try {
              var insertQuery = GetBufferedImportEntityInsertQuery();
              foreach(var importEntity in entitiesBatch) {
                  insertQuery.Values();
                  SetBufferedImportEntityInsertColumnValues(importEntity, insertQuery,
                      importColumns);
                  insertQuery.Set("ImportSessionId", Column.Parameter(importSessionId));
              }
              insertQuery.Execute();
          } catch (Exception e) {
              //...
          }
      });

• The developer must confirm the column number matches the Set() condition number since Terrasoft.Core.DB.Insert does not support this option. The mismatch will create an exception on the database level.

Add data from the selection

The purpose of the Terrasoft.Core.DB.InsertSelect class is to build queries to add records to the database tables based on the data of the queries to retrieve records from the database table. As such, the class uses the Terrasoft.Core.DB.Select class instance as a data source. After you create and configure the class instance, Creatio will build an INSERT INTO SELECT query to the Creatio database.

The resulting query built using the Terrasoft.Core.DB.InsertSelect class does not apply the current user's access permissions to the corresponding records. The class uses the user connection only to access the database table.

As a result of the query, Creatio will add the records retrieved in the Select query to the database.

Modify the database data

Use the following classes to modify the database data:

• Terrasoft.Core.DB.Update
• Terrasoft.Core.DB.UpdateSelect

Modify data in bulk

The purpose of the Terrasoft.Core.DB.Update class is to build queries to modify records in the database tables. After you create and configure the class instance, Creatio will build an UPDATE query to the Creatio database.

Modify data based on the selection

The purpose of the Terrasoft.Core.DB.UpdateSelect class is to build queries to modify records in the database tables based on the data of the queries to retrieve records from the database table. As such, the class uses the Terrasoft.Core.DB.Select class instance as a data source. After you create and configure the class instance, Creatio will build an UPDATE FROM query to the Creatio database.

As a result of the query, Creatio will modify the records retrieved in the Select query in the database table.

Delete data from the database

The Terrasoft.Core.DB.Delete class deletes data from the database.

The purpose of the Terrasoft.Core.DB.Delete class is to build queries to delete records from the database tables. After you create and configure the class instance, Creatio will build a DELETE query to the Creatio database.

Use multithreading for database management

Multithreading is the use of several parallel threads to send database queries via UserConnection.

The Terrasoft.Core.DB.DBExecutor class supports multithreading. The class implements building and running several database queries in a single transaction. A single DBExecutor instance is available per user. The user cannot create new instances.

Multithreading may lead to synchronization issues related to transaction start and confirmation. The issue can occur even if you use the DBExecutor indirectly. For example, through EntitySchemaQuery.

If you use the Terrasoft.Core.DB.DBExecutor class, you must wrap the creation of the DBExecutor instance in the using operator. This is required since Creatio uses unmanaged resources to manage the database. You can also call the Dispose() method to free up resources. Learn more about using the using operator: official vendor documentation (Microsoft).

Call the dbExecutor.StartTransaction method to start the transaction. Call dbExecutor.CommitTransaction or dbExecutor.RollbackTransaction to end the transaction. If the execution goes beyond the scope of the using block and you do not call the dbExecutor.CommitTransaction method, the transaction will roll back automatically.

Important

If you run several queries in a single transaction, pass dbExecutor to Execute, ExecuteReader, ExecuteScalar methods.

View the source code snippets that use DBExecutor below. Do not call the methods of the DBExecutor instance in parallel threads.

Correct DBExecutor use example

/* Start using the DBExecutor instance in the main thread. */
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    dbExecutor.StartTransaction();
    //...
    dbExecutor.CommitTransaction();
}
//...
var select = (Select)new Select(UserConnection)
    .Column("Id")
    .From("Contact")
    .Where("Name")
    .IsEqual(Column.Parameter("Supervisor"));
/* Continue using the DBExecutor instance in the main thread. */
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}

Incorrect DBExecutor use example

/* Create a parallel thread. */
var task = new Task(() => {

    // Start using the DBExecutor instance in a parallel thread.
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction();
        //...
        dbExecutor.CommitTransaction();
    }
});

/* Run an asynchronous task in the parallel thread. Continue executing the program in the main thread. */
task.Start();
//...
var select = (Select)new Select(UserConnection)
    .Column("Id")
    .From("Contact")
    .Where("Name")
    .IsEqual(Column.Parameter("Supervisor"));

/* If you use the DBExecutor instance in the main thread, this will lead to an error since the parallel thread is already using the DBExecutor instance. */
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}

---

See also

Data access through ORM

Data storage and cache

---

Resources

using operator (official Microsoft documentation)