Database access is provided by a group of server core classes, listed below. Use this class group to perform all basic CRUD operations, account for the access permissions of the current user, and put the retrieved data to the cache storage.

Select

The Terrasoft.Core.DB.Select class is used to build queries for the selection of records from the database tables. As a result of creating and configuring the instance of this class, the SELECT SQL-expression query to the application database will be built. You can add the needed icons, filtering, and restriction conditions to the query. The query results are returned as a class instance that implements the System.Data.IDataReader interface or as a scalar value of the needed type.

When working with the Select class, the current user permissions are not taken into consideration. All records from the application database are available. The data located in the cache repository are not taken into consideration (see the “Repositories. Types and recommendations on use” article). Use the EntitySchemaQuery class to access additional permission control options and Creatio cache repository operation.

EntitySchemaQuery

The Terrasoft.Core.Entities.EntitySchemaQuery class is used to build queries for selecting records in Creatio database tables. As a result of creating and configuring the instance of this class, the SELECT SQL-expression query to the application database will be built. You can add the needed icons, filtering, and restriction conditions to the query.

EntitySchemaQuery implements a mechanism for working with the repository (Creatio cache or user-defined arbitrary repository). When EntitySchemaQuery is performed, the data retrieved from the server database are cached. The query cache can be an arbitrary repository that implements the Terrasoft.Core.Store.ICacheStore interface. By default, the cache of the EntitySchemaQuery query is the session-level Creatio cache (only the data from the current user session are available) with local storage.

For the EntitySchemaQuery queries, you can determine additional settings that specify the parameters for the page-by-page output of query results and parameters for building a hierarchical query. The Terrasoft.Core.Entities.EntitySchemaQueryOptions class is designed for this purpose.

The result of EntitySchemaQuery is the Terrasoft.Nui.ServiceModel.DataContract.EntityCollection instance (the Terrasoft.Core.Entities.Entity class instance collection). Each Entity collection instance represents a string of data returned by the query.

EntitySchemaQuery specifics

1) Support of access permissions

The EntitySchemaQuery data selection query is built taking into consideration the current user’s permissions. The resulting set will only include the data within the range of the current user permissions. Additionally for EntitySchemaQuery, you can manage the conditions for granting permissions to the connected tables available in the query (connected to the query by the JOIN clause). These conditions are defined by the JoinRightState property value of the EntitySchemaQuery instance.

2) Cache mechanism

EntitySchemaQuery implements a mechanism for working with the repository (Creatio cache or user-defined arbitrary repository). When EntitySchemaQuery is performed, the data retrieved from the server database are cached. The query cache can be an arbitrary repository that implements the ICacheStore interface. By default, the cache of the EntitySchemaQuery query is the session-level Creatio cache (only the data from the current user session are available) with local storage. The query cache is defined by the Cache property of the EntitySchemaQuery instance. The cache access permission key is set via the CacheItemName property (example 4).

3) Additional settings of the query

For the EntitySchemaQuery queries, you can determine additional settings that specify the parameters for the page-by-page output of query results and parameters for building a hierarchical query. For this, use the EntitySchemaQueryOptions class.

The EntitySchemaQueryOptions class properties:

• HierarchicalColumnName – name of the column used for building the hierarchical request
• HierarchicalColumnValue – initial value of the hierarchical column that will be used to build the hierarchy
• HierarchicalMaxDepth – maximum level of nesting the hierarchical request
• PageableConditionValues – values of the page-by-page output conditions
• PageableDirection – direction of the page-by-page output
• PageableRowCount – number of page records of the resulting data set returned by the query

The same EntitySchemaQueryOptions instance can be used for receiving results of executing different queries by passing it as a parameter to the GetEntityCollection() method of the corresponding query (EntitySchemaQuery examples).

Insert

The Terrasoft.Core.DB.Insert class is used to build queries for adding records in Creatio database tables. As a result of creating and configuring the instance of this class, the INSERT SQL-expression query to the application database will be built. Execution of the query results in returning the number of involved records.

InsertSelect

The Terrasoft.Core.DB.InsertSelect class is used to build queries for adding records in Creatio database tables. The Terrasoft.Core.DB.Select class instance is used as the source for adding data (see “Retrieving information from database. The Select class”). As a result of creating and configuring the instance of Terrasoft.Core.DB.InsertSelect, the INSERT INTO SELECT SQL-expression query to the application database will be built.

When working with the InsertSelect class, the access permissions are not applied to the added records. No application permissions are applied to such records (including object permissions by operation, records, or columns). The user connection is only used for accessing the database table.

After the InsertSelect query is executed, the database will be complemented with all records returned in its Select. subquery.

Update

The Terrasoft.Core.DB.Update class is used to build queries for modifying records in Creatio database tables. As a result of creating and configuring the instance of this class, the UPDATE SQL-expression query to the application database will be built.

Delete

The Terrasoft.Core.DB.Delete class is used to build queries for deleting records in Creatio database tables. As a result of creating and configuring the instance of this class, the DELETE SQL-expression query to the application database will be built.

Entity

The Terrasoft.Core.Entities.Entity class is designed to provide access to an object that represents a record in the database table. This class can also be used to insert, update, and delete specified records.

See also:

• Root schema. Building paths to columns
• Multi-row data insert. The Insert class
• Multithreading when working with the database. Using the DBExecutor