Data access through ORM

PDF
Advanced

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 current user's access permissions, and put the retrieved data to the cache storage.

Retrieving data from the database 

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 this class instance, 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. 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 this class instance, 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 EntitySchemaQuery query's cache 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 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 EntitySchemaQuery query's cache 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 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).

Adding data 

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 this class instance, 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. 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 complement all records returned in its Select subquery.

Modifying data 

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 this class instance, the UPDATE SQL-expression query to the application database will be built.

Deleting data 

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 this class instance, the DELETE SQL-expression query to the application database will be built.

Working with database entities 

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.

Root schema 

The root schema is a schema (i.e., a database table) in relation to which the paths are built to all query columns, including the connected tables' columns.

When building the column paths, the connections are established via the lookup fields. You can build an arbitrary name of a column added to the query as a chain of interconnected items, each of which represents a “context’ of a specific schema linked to the previous schema via an external key.

In general cases, the format of building an arbitrary column from schema N can be represented as follows:

[Schema 1 context].[...].[Schema N context].[Column_name]

Multithreading when working with the database 

Using several streams in working with the database via UserConnection may cause issues in starting synchronization or committing transactions.

Attention. The issue occurs during the work with the database even without direct use of the DBExecutor. For example, it can be used indirectly, through the EntitySchemaQuery.

Attention. As unmanaged resources are used to work with the database, you need to wrap the DBExecutor in the using operator. Another way is to call the Dispose() method explicitly to release resources. More information about the using operator can be found in the documentation.

A source code fragments with incorrect and correct usage of the DBExecutor are given below. You cannot call the DBExecutor instance methods in the parallel threads. The call of the DBExecutor instance methods is performed consistently in one thread.

// Create a parallel thread.
var task = new Task(() => {
    // Using a DBExecutor instance in a parallel thread.
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction();
        //...
        dbExecutor.CommitTransaction();
    }
});
// Running an asynchronous task in a parallel thread.
// The execution of the program in the main thread continues on.
task.Start();
//...
var select = (Select)new Select(UserConnection)
        .Column("Id")
        .From("Contact")
        .Where("Name")
        .IsEqual(Column.Parameter("Supervisor"));
// Using an instance of DBExecutor in the main thread will cause an error, 
// because The instance DBExecutor is already used in a parallel thread.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}
// The first use of the instance DBExecutor 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"));
// Reusing the DBExecutor instance in the main thread.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}

Work with PostgreSQL 

General recommendations:

  1. Avoid using the CREATE OR REPLACE command for creating triggers, views, and functions. Instead, use the DROP … IF EXISTS construction first (you may also use the CASCADE command if needed), use CREATE OR REPLACE.
  2. Use the “public” scheme instead of the “dbo” scheme.
  3. Note that system names are case-sensitive. Remember to wrap the names of tables, columns, and other elements in quotes ("") .
  4. Instead of the MS SQL's BIT type, use the Postgres’s BOOL type. The WHERE "boolColumn" = true construction is redundant for matching a BOOL type field's value. Using WHERE "boolColumn" or WHERE NOT "boolColumn” will suffice.
  5. Postgres allows using a shortened form of explicit conversion, ::TEXT.
  6. String matching is case-sensitive in Postgres. To perform case-insensitive matching, use the iLIKE keyword. Note that this type of matching is significantly slower than the UPPER+LIKE combination. Additionally, the UPPER+LIKE combination has less strict index applicability rules compared to iLIKE.
  7. If there is no implicit typecasting, you can create one using the CREATE CAST command. More information is available in the PostgreSQL documentation.
  8. Postgres lacks a built-in NESTLEVEL function for recursive procedures. Instead, you should use a special parameter in the procedure for storing the current recursion depth.
  9. Use the NAME type instead of the SYSNAME type.
  10. Avoid using empty INSTEAD triggers and create rules instead, for instance:
    CREATE RULE US_VwAdministrativeObjects AS
    ON UPDATE TO "VwAdministrativeObjects"
    DO INSTEAD NOTHING;
    
  11. Implicit type casting from the INT type to the BOOL type does not work for the UPDATE command in Postgres even with the corresponding CAST operator. You must cast an INT value to the BOOL type explicitly.
  12. The methods for formatting string literals are described in detail in the PostgreSQL documentation:
  13. Use the following construction instead of @@ROWCOUNT:
    DECLARE rowsCount BIGINT = 0;
    GET DIAGNOSTICS rowsCount = row_count;
    
  14. Instead of using the following MS SQL construction:
    (CASE WHEN EXISTS (
      SELECT 1
      FROM [SysSSPEntitySchemaAccessList]
      WHERE [SysSSPEntitySchemaAccessList].[EntitySchemaUId] = [BaseSchemas].[UId]
      )
    THEN 1 ELSE 0 END) AS [IsInSSPEntitySchemaAccessList]
    

    use the following PostgreSQL construction
    EXISTS (
      SELECT 1
      FROM "SysSSPEntitySchemaAccessList"
      WHERE "EntitySchemaUId" = BaseSchema."UId"
     ) "IsInSSPEntitySchemaAccessList"
    

    The response field will have a BOOL value.

Data type matching 

Table for Creatio, MS SQL, and PostgreSQL data type matching
Data type in Creatio object designer Data type in MS SQL Data type in PostgreSQL
BLOB VARBINARY BYTEA
Boolean BIT BOOLEAN
Color NVARCHAR CHARACTER VARYING
CRC NVARCHAR CHARACTER VARYING
Currency DECIMAL NUMERIC
Date DATE DATE
Date/Time DATETIME2 TIMESTAMP WITHOUT TIME ZONE
Decimal (0.00000001) DECIMAL NUMERIC
Decimal (0.0001) DECIMAL NUMERIC
Decimal (0.001) DECIMAL NUMERIC
Decimal (0.01) DECIMAL NUMERIC
Decimal (0.1) DECIMAL NUMERIC
Encrypted string NVARCHAR CHARACTER VARYING
File VARBINARY BYTEA
Image VARBINARY BYTEA
Image Link UNIQUEIDENTIFIER UUID
Integer INTEGER INTEGER
Lookup UNIQUEIDENTIFIER UUID
Text (250 characters) NVARCHAR(250) CHARACTER VARYING
Text (50 characters) NVARCHAR(50) CHARACTER VARYING
Text (500 characters) NVARCHAR(500) CHARACTER VARYING
Time TIME TIME WITHOUT TIME ZONE
Unique identifier UNIQUEIDENTIFIER UUID
Unlimited length text NVARCHAR(MAX) TEXT

Binding an SQL scenario to a package 

If you have SQL scripts (including MS SQL-specific ones) bound to a package, create a new script for PostgreSQL that would implement the same features in the PostgreSQL syntax. To do this, add a new PostgreSQL script on the SQL scenarios tab.

Root schema. Building paths to columns
Advanced

The City schema acts as a root schema for all the below examples.

Example 1 

In this case, the column name is specified as Column name in the root schema.

  • Column containing the city name
  • Column name: Name
// Creating an instance of the EntitySchemaQuery request with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the city name to the request.
esqQuery.AddColumn("Name");

// Receiving the text of the resulting sql-request.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 

SELECT
[City].[Name] [Name]
FROM
[dbo].[City] [City]

Example 2 

The column name is configured as follows: Name of lookup column.Name of lookup schema column.

In the resulting query, a Country lookup schema will be connected to the City root schema by the JOIN operator (LEFT OUTER JOIN by default). The connection condition (ON condition of the JOIN operator) is formed as follows:

Name of the connected schema.Id = Name of the root schema.Name of the lookup column that refers to the connected schema + Id

  • Column containing the name of the country, where the city is located
  • Column name: Country.Name
// Creating an instance of the EntitySchemaQuery request with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the country name to the request.
esqQuery.AddColumn("Country.Name");

// Receiving the text of the resulting sql-request.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();  
SELECT
[Country].[Name] [Country.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
  • Name of the contact who added the country of a specific city
  • Column name: Country.CreatedBy.Name
// Creating an instance of the EntitySchemaQuery request with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the name of the contact who added the country of a specific city.
esqQuery.AddColumn("Country.CreatedBy.Name");

// Receiving the text of the resulting sql-request.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();   
SELECT
[CreatedBy].[Name] [CreatedBy.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country].[CreatedById])

Example 3 

The column name is built as follows: Name_of_connected_schema:Column_name_for _linking_the_connecetd_schema:Column_name_for_linking_the_current_schema.

This method of connecting columns suggests applying reverse connections, i.e., the connection of the connected entity lookup column with any of the columns of the primary entity.

If the Id column of the current schema acts as a connecting column, it can be omitted, i.e., the column name will look as follows:

Name_of_the_connected_schema:Name_of_the_column_for_linking_the_connected_schema.

  • Column with the name of the contact, whose page contains the city selected by the query
  • Column name: [Contact:City:Id].Name
// Creating an instance of the EntitySchemaQuery request with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

//  Adding a column with the name of the contact, whose card contains the specified city.
esqQuery.AddColumn("[Contact:City:Id].Name");

// Receiving the text of the resulting sql-request.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 
SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[CityId] = [City].[Id])
Note. Pay your attention that when you build column names with applying reverse connections, the resulting set of records may contain much more records than the table of the primary entity. In the above example, the database might contain dozens of cities and thousands of contacts, whose pages contain one of the read cities.

In the below example, we consider an alternative variant of building a column name containing the name of the contact that added the country of a specific city (see example 2).

  • Column with the name of the contact that added the country of a specific city
  • Column name: Country.[Contact:Id:CreatedBy].Name
// Creating an instance of the EntitySchemaQuery request with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the name of the contact who added the country of a specific city..
esqQuery.AddColumn("Country.[Contact:Id:CreatedBy].Name");

// Receiving the text of the resulting sql-request.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();
SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[Id] = [Country].[CreatedById])
Retrieving data from the database

Examples 

private string CreateJson(IDataReader dataReader)
{
    var list = new List<dynamic>();
    var cnt = dataReader.FieldCount;
    var fields = new List<string>();
    for (int i = 0; i < cnt; i++)
    {
        fields.Add(dataReader.GetName(i));
    }
    while (dataReader.Read())
    {
        dynamic exo = new System.Dynamic.ExpandoObject();
        foreach (var field in fields)
        {
            ((IDictionary<String, Object>)exo).Add(field, dataReader.GetColumnValue(field));
        }
        list.Add(exo);
    }
    return JsonConvert.SerializeObject(list);
}
public string GetSqlTextExample()
{
    var result = "";
    var select = new Select(UserConnection)
            .Column(Column.Asterisk())
        .From("Contact");
    result = select.GetSqlText();
    return result;
}
public string SelectColumns(string tableName, int top)
{
    top = top > 0 ? top : 1;
    var result = "{}";
    var select = new Select(UserConnection)
        .Top(top)
        .Column(Column.Asterisk())
        .From(tableName);
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectContactsYoungerThan(string birthYear)
{
    var result = "{}";
    var year = DateTime.ParseExact(birthYear, "yyyy", CultureInfo.InvariantCulture);
    var select = new Select(UserConnection)
            .Column("Id")
            .Column("Name")
            .Column("BirthDate")
        .From("Contact")
        .Where("BirthDate").IsGreater(Column.Parameter(year))
            .Or("BirthDate").IsNull()
        .OrderByDesc("BirthDate")
         as Select;
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectContactsYoungerThanAndHasAccountId(string birthYear)
{
    var result = "{}";
    var year = DateTime.ParseExact(birthYear, "yyyy", CultureInfo.InvariantCulture);
    var select = new Select(UserConnection)
            .Column("Id")
            .Column("Name")
            .Column("BirthDate")
        .From("Contact")
        .Where()
        .OpenBlock("BirthDate").IsGreater(Column.Parameter(year))
            .Or("BirthDate").IsNull()
        .CloseBlock()
        .And("AccountId").Not().IsNull()
        .OrderByDesc("BirthDate")
         as Select;
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectContactsJoinAccount()
{
    var result = "{}";
    var select = new Select(UserConnection)
            .Column("Contact", "Id").As("ContactId")
            .Column("Contact", "Name").As("ContactName")
            .Column("Account", "Id").As("AccountId")
            .Column("Account", "Name").As("AccountName")
        .From("Contact")
        .Join(JoinType.Inner, "Account")
            .On("Contact", "Id").IsEqual("Account", "PrimaryContactId")
         as Select;
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectAccountPrimaryContacts()
{
    var result = "{}";
    var select = new Select(UserConnection)
            .Column("Id")
            .Column("Name")
        .From("Contact").As("C")
        .Where()
            .Exists(new Select(UserConnection)
                            .Column("A", "PrimaryContactId")
                        .From("Account").As("A")
                        .Where("A", "PrimaryContactId").IsEqual("C", "Id"))
         as Select;
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectCountriesWithCitiesCount(int count)
{
    var result = "{}";
    var select = new Select(UserConnection)
            .Column(Func.Count("City", "Id")).As("CitiesCount")
            .Column("Country", "Name").As("CountryName")
        .From("City")
        .Join(JoinType.Inner, "Country")
            .On("City", "CountryId").IsEqual("Country", "Id")
        .GroupBy("Country", "Name")
        .Having(Func.Count("City", "Id")).IsGreater(Column.Parameter(count))
        .OrderByDesc("CitiesCount")
         as Select;
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
    {
        using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
        {
            result = CreateJson(dataReader);
        }
    }
    return result;
}
public string SelectCountryIdByCityName(string CityName)
{
    var result = "";
    var select = new Select(UserConnection)
            .Column("CountryId")
        .From("City")
        .Where("Name").IsEqual(Column.Parameter(CityName)) as Select;
    result = select.ExecuteScalar<Guid>().ToString();
    return result;
}
Retrieving data based on user permissions

Examples 

public string CreateESQ()
{
    var result = "";
    // Receiving an instance of the object schema manager.          
    EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
    // Receiving an instance of the schema that will be the root schema for the created
    // EntitySchemaQuery instance.
    var rootEntitySchema = esqManager.GetInstanceByName("City") as EntitySchema;
    // Creating the EntitySchemaQuery instance that will have
    // rootEntitySchema as the root schema.
    var esqResult = new EntitySchemaQuery(rootEntitySchema);
    // Adding columns that will be selected in the result request.
    esqResult.AddColumn("Id");
    esqResult.AddColumn("Name");
    // Receiving the Select instance associated with the created EntitySchemaQuery request.
    Select selectEsq = esqResult.GetSelectQuery(SystemUserConnection);
    // Receiving the text of the resulting request of the created EntitySchemaQuery instance.
    result = selectEsq.GetSqlText();
    return result;
}
public string CreateESQClone()
{
    var result = "";
    EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
    var esqSource = new EntitySchemaQuery(esqManager, "Contact");
    esqSource.AddColumn("Id");
    esqSource.AddColumn("Name");
    // Creating an EntitySchemaQuery instance that is a clone of the esqSource instance.
    var esqClone = new EntitySchemaQuery(esqSource);
    result = esqClone.GetSelectQuery(SystemUserConnection).GetSqlText();
    return result;
}
public string GetEntitiesExample()
{
    var result = "";
    // Creating a request to the City schema, adding the Name column to the request.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    var colName = esqResult.AddColumn("Name");

    // Running the request to the database and receiving the resulting object collection.
    var entities = esqResult.GetEntityCollection(UserConnection);
    for (int i=0; i < entities.Length; i++) {
        result += entities[i].GetColumnValue(colName.Name).ToString();
        result += "\n";
    }
    
    // Running the request to the database and receiving an object with the specified identifier.
    var entity = esqResult.GetEntity(UserConnection, new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"));
    result += "\n";
    result += entity.GetColumnValue(colName.Name).ToString();
    return result;
}
public Collection<string> UsingCacheExample()
{
    // Creating a request to the City schema, adding the Name column to the request.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    esqResult.AddColumn("Name");

    // Identifying the key that will be used to store the request results in the cache.
    // Creatio cache of the local data cache session level acts as a cache (since the
    // Cache property of the object cannot be identified).
    esqResult.CacheItemName = "EsqResultItem";

    // Collection that will contain the request results.
    var esqCityNames = new Collection<string>();

    // Collection that will contain the cached request results.
    var cachedEsqCityNames = new Collection<string>();

    // Running the requst to the database and receiving the resulting object collection.
    // After running the operation the results will be cached.
    var entities = esqResult.GetEntityCollection(UserConnection);

    // Processing the request results and populating the esqCityNames collection.
    foreach (var entity in entities)
    {
        esqCityNames.Add(entity.GetTypedColumnValue<string>("Name"));
    }

    // Receiving a link to the cache of the esqResult request using the CacheItemName key as a data table in memory.
    var esqCacheStore = esqResult.Cache[esqResult.CacheItemName] as DataTable;

    // Populating the cachedEsqCityNames collection with the values of the request cache.
    if (esqCacheStore != null)
    {
        foreach (DataRow row in esqCacheStore.Rows)
        {
            cachedEsqCityNames.Add(row[0].ToString());
        }
    }
    return cachedEsqCityNames;
}
public Collection<string> ESQOptionsExample()
{
    // Creating a request instance with the City root schema.
    var esqCities = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    esqCities.AddColumn("Name");

    // Creating a request with the Country root schema.
    var esqCountries = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Country");
    esqCountries.AddColumn("Name");

    // Creating an instance of settings to return the first 5 strings by the request.
    var esqOptions = new EntitySchemaQueryOptions()
    {
        PageableDirection = PageableSelectDirection.First,
        PageableRowCount = 5,
        PageableConditionValues = new Dictionary<string, object>()
    };

    // Receiving a city collection that will contain the first 5 cities of the resulting data set.
    var cities = esqCities.GetEntityCollection(UserConnection, esqOptions);

    // Receiving a country collection that will contain the first 5 countries of the resulting data set.
    var countries = esqCountries.GetEntityCollection(UserConnection, esqOptions);
        var esqStringCollection = new Collection<string>();
    foreach (var entity in cities)
    {
        esqStringCollection.Add(entity.GetTypedColumnValue<string>("Name"));
    }
    foreach (var entity in countries)
    {
        esqStringCollection.Add(entity.GetTypedColumnValue<string>("Name"));
    }
    return esqStringCollection;
}
Adding data
Advanced

Examples 

public string GetSqlTextExample(string ContactName)
{
    var result = "";
    var id = Guid.NewGuid();
    var ins = new Insert(UserConnection)
        .Into("Contact")
        .Set("Id", Column.Parameter(id))
        .Set("Name", Column.Parameter(ContactName));
    result = ins.GetSqlText();
    return result;
}
public string InsertContact(string contactName)
{
    contactName = contactName ?? "Unknown contact";
    var ins = new Insert(UserConnection)
        .Into("Contact")
        .Set("Name", Column.Parameter(contactName));
    var affectedRows = ins.Execute();
    var result = $"Inserted new contact with name '{contactName}'. {affectedRows} rows affected";
    return result;
}
public string InsertCity(string city, string country)
{
    city = city ?? "unknown city";
    country = country ?? "unknown country";

    var ins = new Insert(UserConnection)
        .Into("City")
        .Set("Name", Column.Parameter(city))
        .Set("CountryId",
            new Select(UserConnection)
                    .Top(1)
                    .Column("Id")
                .From("Country")
                .Where("Name")
                    .IsEqual(Column.Parameter(country)));
    var affectedRows = ins.Execute();
    var result = $"Inserted new city with name '{city}' located in '{country}'. {affectedRows} rows affected";
    return result;
}
Adding data using subqueries
Advanced

Examples 

public string GetSqlTextExample(string contactName, string accountName)
{
    var result = "";
    var id = Guid.NewGuid();
    var selectQuery = new Select(UserConnection)
        .Column(Column.Parameter(contactName))
        .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(selectQuery);

    result = insertSelectQuery.GetSqlText();
    return result;
}
public string InsertContactWithAccount(string contactName, string accountName)
{
    contactName = contactName ?? "Unknown contact";
    accountName = accountName ?? "Unknown account";
    
    var id = Guid.NewGuid();
    var selectQuery = new Select(UserConnection)
            .Column(Column.Parameter(contactName))
            .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(selectQuery);

    var affectedRows = insertSelectQuery.Execute();
    var result = $"Inserted new contact with name '{contactName}'" +
                $" and account '{accountName}'." +
                $" Affected {affectedRows} rows.";
    return result;
}
public string InsertAllAccountsContact(string contactName)
{
    contactName = contactName ?? "Unknown contact";

    var id = Guid.NewGuid();
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(
            new Select(UserConnection)
                .Column(Column.Parameter(contactName))
                .Column("Id")
                .From("Account") as Select);

    var affectedRows = insertSelectQuery.Execute();
    var result = $"Inserted {affectedRows} new contacts with name '{contactName}'";
    return result;
}
Multi-row data insert
Advanced

Multi-row data insert is available on the Insert class level and is determined by the Values() method.

After calling the Values() method, all subsequent Set() calls fall into a new ColumnsValues instance. A query with several Values() blocks will be built if there is more than one data set in ColumnsValuesCollection.

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 the result, the following SQL inquiry is generated:

SQL-query
--For MSSQL 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

Use specifics 

  1. MS SQL limits the quantity of parameters to 2100 while using Column.Parameter in the Set() expression.
  2. The Insert class cannot independently split a single query into multiple queries if it contains more parameters than necessary. Queries are split by developers themselves.

    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) {
                //...
            }
    });
    
  3. 3. The Insert() class does not validate that the number of columns and Set() conditions match. For example, if there is a resulting SQL-query

    SQL-query
    INSERT INTO [dbo].[Table] (Column1, Column2, Column3)
    Values (1, 2), (1, 2, 3)
    

This creates an exception on the database level. The Insert class is not responsible for a detailed validation. Thus, it only depends on developers.

Modifying data
Advanced

Examples 

public string GetSqlTextExample(string oldName, string newName)
{
    var result = "";
    var update = new Update(UserConnection, "Contact")
        .Set("Name", Column.Parameter(newName))
        .Where("Name").IsEqual(Column.Parameter(oldName));
    result = update.GetSqlText();
    return result;
}
 public string ChangeContactName(string oldName, string newName)
{
    var update = new Update(UserConnection, "Contact")
        .Set("Name", Column.Parameter(newName))
        .Where ("Name").IsEqual(Column.Parameter(oldName));
    var cnt = update.Execute();
    return $"Contacts {oldName} changed to {newName}. {cnt} rows affected.";
}
public string ChangeAllContactModifiedBy(string Name)
{
    var update = new Update(UserConnection, "Contact")
        .Set("ModifiedById", 
            new Select(UserConnection).Top(1)
                .Column("Id")
                .From("Contact")
                .Where("Name").IsEqual(Column.Parameter(Name)));
    var cnt = update.Execute();
    return $"All contacts are changed by {Name} now. {cnt} rows affected.";
}
Note. In this example, the Where condition refers to the Select request. The Update request does not contain the Where condition, since indiscriminate modification is the goal.
Deleting data
Advanced
Attention. In most cases, the deletion request should contain the Where condition, which specifies which records exactly should be deleted. Otherwise, all records will be deleted.
Attention. The example code demonstrates available methods for passing parameters to the query. When developing a project, be aware that parameters originating from the user should never be passed to the Column.Const method, since this can lead to a successful SQL injection attack.

Examples 

public string GetSqlTextExample(string name)
{
    var result = "";
    var delete = new Delete(UserConnection)
        .From("Contact")
        .Where("Name").IsEqual(Column.Parameter(name));
    result = delete.GetSqlText();
    return result;
}
public string DeleteContacts(string name)
{
    var delete = new Delete(UserConnection)
        .From("Contact")
        .Where("Name").IsEqual(Column.Parameter(name));
    var cnt = delete.Execute();
    return $"Contacts with name {name} were deleted. {cnt} rows affected";
}
Working with database entities
Advanced

Examples of using the Entity class to work with the database 


public string GetEntityColumnData()
{
    var result = "";
    // Creating a request to the City schema, adding the Name column to the request.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    var colName = esqResult.AddColumn("Name");
    // Running the request to the database and receiving an object with the specified identifier. You can receive the UId of the object from the browser navigation bar of the open record edit page. 
    var entity = esqResult.GetEntity(UserConnection, new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"));
    // Receiving the value of the object column.
    result += entity.GetColumnValue(colName.Name).ToString();
    return result;
}
public IEnumerable<string> GetEntityColumns()
{
    // Creating the data string object of the City schema (using the schema Id received from the database).
    var entity = new Entity(UserConnection, new Guid("5CA90B6A-93E7-4448-BEFE-AB5166EC2CFE"));
    // Receiving the object with the specified id from the database. You can receive the UId of the object from the browser navigation bar of the open record edit page.
    entity.FetchFromDB(new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"),true);
    // Receiving the name collection of the object columns.
    var result = entity.GetColumnValueNames();
    return result;
}
public bool DeleteEntity()
{
    // Creating a request to the Order schema, adding all schema columns to the request.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Order");
    esqResult.AddAllSchemaColumns();
    // Running the request to the database and receiving the object with the specified id. You can receive the UId of the object from the browser navigation bar of the open record edit page.
    var entity = esqResult.GetEntity(UserConnection, new Guid("e3bfa32f-3fe9-4bae-9332-16c162c51e0d"));
    // Deleting the object from the database.
    entity.Delete();
    // Verification whether the object with the specified Id exists in the database.
    var result = entity.ExistInDB(new Guid("e3bfa32f-3fe9-4bae-9332-16c162c51e0d"));
    return result;
}
public bool UpdateEntity()
{
    // Creating a request to the Order schema, adding all schema columns to the request.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Order");
    esqResult.AddAllSchemaColumns();
    // Running the request to the database and receiving the object with the specified id. You can receive the UId of the object from the browser navigation bar of the open record edit page.
    var entity = esqResult.GetEntity(UserConnection, new Guid("58be5223-715d-4b16-a5c4-e3d4ec0412d9"));
    // Creating a data string object of the OrderStatus schema.
    var statusSchema = UserConnection.EntitySchemaManager.GetInstanceByName("OrderStatus");
    var newStatus = statusSchema.CreateEntity(UserConnection);
    // Receiving the object with specified name from the database.
    newStatus.FetchFromDB("Name", "4. Completed");
    // Assigns a new value to the StatusId column.
    entity.SetColumnValue("StatusId", newStatus.GetTypedColumnValue<Guid>("Id"));
    // Saving the changed object in the database.
    var result = entity.Save();
    return result;
}
public bool InsertEntity(string city, string country)
{
    city = city ?? "unknown city";
    country = country ?? "unknown country";
    var citySchema = UserConnection.EntitySchemaManager.GetInstanceByName("City");
    var entity = citySchema.CreateEntity(UserConnection);
    entity.FetchFromDB("Name", city);
    // Sets default values for the object columns.
    entity.SetDefColumnValues();
    var contryEntity = new Entity(UserConnection, new Guid("09FCE1F8-515C-4296-95CD-8CD93F79A6CF"));
    contryEntity.FetchFromDB("Name", country);
    // Assigns the passed-in city name to the Name column.
    entity.SetColumnValue("Name", city);
    // Assings the UId of the passed-in country to the CountryId UId column.
    entity.SetColumnValue("CountryId", contryEntity.GetTypedColumnValue<Guid>("Id"));
    var result = entity.Save();
    return result;
}
Check the data matching mechanism
Advanced

Case description 

Create a custom UsrEntityMapperConfigsContainer class to check the data matching mechanism through Terrasoft.Configuration.EntityMapper. Implement the data matching logic for the Contact and AppForm objects in this class. Implement a custom configuration service for data matching on the client side of the application. Add a button that will launch the custom configuration service on the edit page of the application form. The result has to be displayed in the browser’s console.

Case implementation algorithm 

1. Create a custom UsrEntityMapperConfigsContainer class for data matching. 

Learn more about the process of creating the Source code schema in the “Create the Source code schema” article.

Property values for the created schema:

  • [Title] — "UsrEntityMapperConfigsContainer".
  • [Name] – "UsrEntityMapperConfigsContainer".
  • [Package] — "Custom" (or a different custom package).

Add the following source code on the Source Code tab of the schema designer:

namespace Terrasoft.Configuration
{
    using System;
    using System.Collections.Generic;
// This class contains mapping settings.
public class UsrEntityMapperConfigsContainer
{
    // Settings for contact and application form mapping.
    public MapConfig ContactToAppFormConfig { get; protected set; }

    public UsrEntityMapperConfigsContainer() {
        this.InitContactToAppFormConfig();
    }
    // Configures the mapping of contact and application form objects.
    protected virtual void InitContactToAppFormConfig() {
        var columns = new Dictionary<string, string>();
        // In this case, the column names of the contact and the application form coincided.
        columns.Add("Surname", "Surname");
        columns.Add("GivenName", "GivenName");
        columns.Add("MiddleName", "MiddleName");
        columns.Add("INN", "INN");
        columns.Add("SpouseSurname", "SpouseSurname");
        columns.Add("SpouseGivenName", "SpouseGivenName");
        columns.Add("SpouseMiddleName", "SpouseMiddleName");
        columns.Add("Spouse", "Spouse");
        var config = new MapConfig {
            SourceEntityName = "Contact",
            Columns = columns,
            RelationEntities = new List<RelationEntityMapConfig>() {
                    new RelationEntityMapConfig() {
                        SourceEntityName = "Contact",
                        ParentColumnName = "Spouse",
                        Columns = new Dictionary<string, string>() {
                            { "Surname", "SpouseSurname" },
                            { "BirthDate", "SpouseBirthDate" }
                        }
                    }
                },
                DetailsConfig = new List<DetailMapConfig>() {
                    new DetailMapConfig() {
                        SourceEntityName = "ContactAddress",
                        DetailName = "RegistrationAddressFieldsDetail",
                        Columns = new Dictionary<string, string>() {
                            { "AddressType", "AddressType" },
                            { "Country", "Country" },
                            { "Region", "Region" }
                        },
                        Filters = new List<EntityFilterMap>() {
                            new EntityFilterMap(){
                                ColumnName = "AddressType",
                                Value = BaseFinanceConst.RegistrationAddressTypeId
                            }
                        }
                    }
                },
                CleanDetails = new List<string>() {
                    "AppFormIncomeDetail"
                }
            };
            this.ContactToAppFormConfig = config;
        }
    }
}

Publish the schema to apply changes.

2. Create a custom configuration service for data matching 

The process of creating a custom configuration service is described in the “Creating a user configuration service” article.

Create the Source Code schema in a custom package. Property values for the created schema:

  • [Title]— "UsrEntityMappingService".
  • [Name] — "UsrEntityMappingService".
  • [Package] — "Custom" (or a different custom package).

Add the following source code on the Source Code tab of the schema designer:

namespace Terrasoft.Configuration
{
using System;
using System.Linq;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using Terrasoft.Core;
using Terrasoft.Core.Factories;
using Terrasoft.Core.Entities;
using Terrasoft.Common;
using System.Web;
using Terrasoft.Web.Common;
using Terrasoft.Nui.ServiceModel.DataContract;
using Terrasoft.Common.Json;
using Terrasoft.Core.Configuration;

    /// Service class for mapping entities and their details.
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class UsrEntityMappingService: BaseService
    {
        private EntityMapper _entityMapper;
        // Returns an EntityMapper instance.
        protected virtual EntityMapper EntityMapper {
            get {
                return _entityMapper ?? (_entityMapper = ClassFactory.Get<EntityMapper>(
                    new ConstructorArgument("userConnection", this.UserConnection)));
            }
        }
        // Returns mapping settings.
        protected virtual MapConfig GetConfig() {
            UsrEntityMapperConfigsContainer mapperConfigsContainer = new UsrEntityMapperConfigsContainer();
            return mapperConfigsContainer.ContactToAppFormConfig;
        }
        // Performs the mapping and returns the result. The main method of service.
        [OperationContract]
        [return: MessageParameter(Name = "result")]
        [WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.Wrapped,
            RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
        public EntityMappingResult GetMappedEntity(string id)
        {
            EntityMappingResult result = new EntityMappingResult();
            try {
                Guid recordId;
                MapConfig config = this.GetConfig();
                EntityResult entityResult = new EntityResult(config);
                result.columns = entityResult.Columns;
                result.details = entityResult.Details;
                if (!Guid.TryParse(id, out recordId)) {
                    return result;
                }
                entityResult = EntityMapper.GetMappedEntity(recordId, config);
                result.columns = entityResult.Columns;
                result.details = entityResult.Details;
                result.Success = true;
            } catch (Exception e){
                result.Success = false;
                result.Exception = e;
            }
            return result;
        }
    }

}

Publish the schema to apply changes.

3. Adding a data mapping button to the application form edit page 

To add a data mapping button, replace the existing Application Form Edit Page schema. The procedure for creating a replacing client schema is covered in the “Create a client schema”.

Add a localizable string to a replacing schema (Fig. 1) with the following properties:

  • [Title] — “Call service”.
  • [Name] — “EntityMappingButtonCaption”.
Fig. 1. Adding a localizable string

Add the following source code on the Source Code tab of the schema designer:

define("AppFormPage", [], function() {
    return {
        entitySchemaName: "AppForm",
        methods: {
            // User service request function.
            requestContactData2: function() {
                var data = {
                    id: this.get("Contact").value
                };
                // A configuration object for passing parameters to the service.
                var config = {
                    serviceName: "UsrEntityMappingService",
                    methodName: "GetMappedEntity",
                    data: data
                };
                // Calling a service.
                this.callService(config, this.parseMappedEntityResponse2, this);
            },
                //Callback-function for outputting the service response to the console.
            parseMappedEntityResponse2: function(response) {
                window.console.log("Response from UsrEntityMappingService", response);
            }
        },
        diff: /**SCHEMA_DIFF*/[
            {
                "operation": "insert",
                "parentName": "ProfileContainer",
                "propertyName": "items",
                "name": "EntityMappingButton",
                "values": {
                    itemType: Terrasoft.ViewItemType.BUTTON,
                    "style": Terrasoft.controls.ButtonEnums.style.GREEN,
                    // Bind the button's title to the localized string of the schema.
                    caption: { bindTo: "Resources.Strings.EntityMappingButtonCaption" },
                    // Bind the button click method-handler.
                    click: { bindTo: "requestContactData2" },
                    "layout": {
                        "column": 0,
                        "row": 2,
                        "colSpan": 24
                    }
                }
            }
        ]/**SCHEMA_DIFF*/
    };
});

Add the configuration object to the diff array. The object will be used to add the data matching button to the application form edit page. The requestContactData2() method is called when the button is pressed, and the UsrEntityMappingService configuration service is called in the method with all necessary parameters. The parseMappedEntityResponse2() callback-function will display the service response in the browser console.

Attention.

Instead of browser console output, you can implement the mechanism of auto completing fields of the application form edit page with the matched values. However, this functionality is already implemented in the requestContactData() and parseMappedEntityResponse() methods of the AppFormPage parent schema.

 

Save the schema to apply changes.

As a result, the button will appear on the application form edit page. When you press the button, the object with mapped data will be displayed in the browser console.

Fig. 2. Case result

Attention.

If the profile edit page is open in the new record creation mode, you must first select or create a contact connected to the created application form. If a contact is not selected, an exception will occur, because This.get ("Contact") returns null.

 

Select class
Advanced

Namespace Terrasoft.Core.DB.

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.

Attention. 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. Use the EntitySchemaQuery class to access additional permission control options and Creatio cache repository operation.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the Select class methods and properties, its parent classes, and the implemented interfaces.

Constructors 

Select(UserConnection userConnection)

Creates a class instance with the specified UserConnection parameter.

Select(UserConnection userConnection, CancellationToken cancellationToken)

Creates a class instance with the specified UserConnection parameter and the cancellation token for managed threads.

Select(Select source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

UserConnection Terrasoft.Core.UserConnection

The current user connection at the moment of executing the query.

RowCount int

The number of records returned by the query after execution.

Parameters Terrasoft.Core.DB.QueryParameterCollection

Collection of the query parameters.

HasParameters bool

Determines whether any query parameters are available.

BuildParametersAsValue bool

Determines whether the query parameters are to be added as values into the query text.

Joins Terrasoft.Core.DB.JoinCollection

The collection of Join expressions in the query.

HasJoins bool

Determines whether Join expressions are available in the query.

Condition Terrasoft.Core.DB.QueryCondition

Condition of Where expression in the query.

HasCondition bool

Determines whether Where expression is available in the query.

HavingCondition Terrasoft.Core.DB.QueryCondition

Condition of Having expression in the query.

HasHavingCondition bool

Determines whether Having expression is available in the query.

OrderByItems Terrasoft.Core.DB.OrderByItemCollection

The collection of expressions by which the query results are sorted.

HasOrderByItems bool

Determines whether the conditions of query result sorting are available.

GroupByItems Terrasoft.Core.DB.QueryColumnExpressionCollection

The collection of expressions by which the query results are grouped.

HasGroupByItems bool

Determines whether the conditions of query result grouping are available.

IsDistinct bool

Determines whether the query should return unique records only.

Columns Terrasoft.Core.DB.QueryColumnExpressionCollection

Collection of query column expressions.

OffsetFetchPaging bool

Determines whether the per-page returning of the query result is available.

RowsOffset int

Number of rows to skip when returning the query result.

QueryKind Terrasoft.Common.QueryKind

The query type.

Methods 

void ResetCachedSqlText()

Clears the cached text of the query.

QueryParameterCollection GetUsingParameters()

Returns the collection of parameters used by the query.

void ResetParameters()

Clears the collection of the query parameters.

QueryParameterCollection InitializeParameters()

Initiates the collection of the query parameters.

IDataReader ExecuteReader(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the object implementing the IDataReader interface.

IDataReader ExecuteReader(DBExecutor dbExecutor, CommandBehavior behavior)

Executes the query using the DBExecutor instance. Returns the object implementing the IDataReader interface. The behavior parameter describes query results and how they affect the database.

void ExecuteReader(ExecuteReaderReadMethod readMethod)

Executes the query by calling the passed over ExecuteReaderReadMethod delegate method for every record of the result set.

TResult ExecuteScalar<TResult>()

Executes the query. Returns the typed first column of the first record of the result set.

TResult ExecuteScalar<TResult>(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the typed first column of the first record of the result set.

Select Distinct()

Adds the DISTINCT keyword to the SQL-query. Excludes record duplicates in the result set. Returns the query instance.

Select Top(int rowCount)

Sets the number of records returned in the result set. The RowCount property value is applied. Returns the query instance.

Select As(string alias)

Adds the alias for the last query expression specified in the argument. Returns the query instance.

Select Column(string sourceColumnAlias)
Select Column(string sourceAlias, string sourceColumnAlias)
Select Column(Select subSelect)
Select Column(Query subSelectQuery)
Select Column(QueryCase queryCase)
Select Column(QueryParameter queryParameter)
Select Column(QueryColumnExpression columnExpression)

Adds an expression, a subquery or a parameter to the query column expression collection. Returns the query instance.

Parameters
sourceColumnAlias an alias of the column for which the expression is added
sourceAlias an alias of the source that the column expression is added from
subSelect the added subquery for data selection
subSelectQuery the added subquery
queryCase the added expression for the Case operator
queryParameter the added parameter query
columnExpression the expression, for whose results the condition is added
Select From(string schemaName)
Select From(Select subSelect)
Select From(Query subSelectQuery)
Select From(QuerySourceExpression sourceExpression)

Adds the data source to the query. Returns the query instance.

Parametersv
schemaName the schema name
subSelect the selection subquery, whose results become the data source for the current query
subSelectQuery the subquery, whose results become the data source for the current query
sourceExpression the expression of the query data source
Join Join(JoinType joinType, string schemaName)
Join Join(JoinType joinType, Select subSelect)
Join Join(JoinType joinType, Query subSelectQuery)
Join Join(JoinType joinType, QuerySourceExpression sourceExpression)

Joins a schema, a subquery or an expression to the current query.

Parameters
joinType join type
schemaName the joined schema name
subSelect the joined subquery for data selection
subSelectQuery the joined subquery
sourceExpression the joined expression
Possible values (Terrasoft.Core.DB.JoinType)
Inner inner join
LeftOuter left outer join
RightOuter right outer join
FullOuter full join
Cross cross join
QueryCondition Where()
QueryCondition Where(string sourceColumnAlias)
QueryCondition Where(string sourceAlias, string sourceColumnAlias)
QueryCondition Where(Select subSelect)
QueryCondition Where(Query subSelectQuery)
QueryCondition Where(QueryColumnExpression columnExpression)
Query Where(QueryCondition condition)

Adds the initial condition to the current query.

Parameters
sourceColumnAlias an alias of the column for which the condition is added
sourceAlias the alias of the source
subSelect a subquery of the data selection, for whose results the condition is added
subSelectQuery the subquery, for whose results the condition is added
columnExpression the expression, for whose results the condition is added
condition the query condition
QueryCondition And()
QueryCondition And(string sourceColumnAlias)
QueryCondition And(string sourceAlias, string sourceColumnAlias)
QueryCondition And(Select subSelect)
QueryCondition And(Query subSelectQuery)
QueryCondition And(QueryParameter parameter)
QueryCondition And(QueryColumnExpression columnExpression)
Query And(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the AND logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
QueryCondition Or()
QueryCondition Or(string sourceColumnAlias)
QueryCondition Or(string sourceAlias, string sourceColumnAlias)
QueryCondition Or(Select subSelect)
QueryCondition Or(Query subSelectQuery)
QueryCondition Or(QueryParameter parameter)
QueryCondition Or(QueryColumnExpression columnExpression)
Query Or(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the OR logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
Query OrderBy(OrderDirectionStrict direction, string sourceColumnAlias)
Query OrderBy(OrderDirectionStrict direction, string sourceAlias, string sourceColumnAlias)
Query OrderBy(OrderDirectionStrict direction, QueryFunction queryFunction)
Query OrderBy(OrderDirectionStrict direction, Select subSelect)
Query OrderBy(OrderDirectionStrict direction, Query subSelectQuery)
Query OrderBy(OrderDirectionStrict direction, QueryColumnExpression columnExpression)

Executes sorting of query results. Returns the query instance.

Parameters
direction sorting order
sourceColumnAlias the alias of the column by which the sorting is performed
sourceAlias the alias of the source
queryFunction the function whose value is used as the sort key
subSelect the data selection subquery, whose results are used as the sort key
subSelectQuery the subquery, whose results are used as the sort key
columnExpression the expression, whose results are used as the sort key
Query OrderByAsc(string sourceColumnAlias)
Query OrderByAsc(string sourceAlias, string sourceColumnAlias)
Query OrderByAsc(Select subSelect)
Query OrderByAsc(Query subSelectQuery)
Query OrderByAsc(QueryColumnExpression columnExpression)

Sorts query results in the ascending order. Returns the query instance.

Parameters
sourceColumnAlias the alias of the column by which the sorting is performed
sourceAlias the alias of the source
subSelect the data selection subquery, whose results are used as the sort key
subSelectQuery the subquery, whose results are used as the sort key
columnExpression the expression, whose results are used as the sort key
Query OrderByDesc(string sourceColumnAlias)
Query OrderByDesc(string sourceAlias, string sourceColumnAlias)
Query OrderByDesc(Select subSelect)
Query OrderByDesc(Query subSelectQuery)
Query OrderByDesc(QueryColumnExpression columnExpression)

Sorts query results in the descending order. Returns the query instance.

Parameters
sourceColumnAlias the alias of the column by which the sorting is performed
sourceAlias the alias of the source
subSelect the selection subquery, whose results are used as the sort key
subSelectQuery the subquery, whose results are used as the sort key
columnExpression the expression, whose results are used as the sort key
Query GroupBy(string sourceColumnAlias)
Query GroupBy(string sourceAlias, string sourceColumnAlias)
Query GroupBy(QueryColumnExpression columnExpression)

Executes grouping of query results. Returns the query instance.

Parameters
sourceColumnAlias the alias of the column by which the grouping is performed
sourceAlias the alias of the source
columnExpression the expression, whose results are used as the group key
QueryCondition Having()
QueryCondition Having(string sourceColumnAlias)
QueryCondition Having(string sourceAlias, string sourceColumnAlias)
QueryCondition Having(Select subSelect)
QueryCondition Having(Query subSelectQuery)
QueryCondition Having(QueryParameter parameter)
QueryCondition Having(QueryColumnExpression columnExpression)

Adds the grouping condition to the current query. Returns the Terrasoft.Core.DB.QueryCondition instance that represents the grouping condition for the query parameter.

Parameters
sourceColumnAlias the alias of the column by which the grouping condition is added
sourceAlias the alias of the source
subSelect the selection subquery, for whose results the grouping condition is added
subSelectQuery the subquery, for whose results the grouping condition is added
parameter the query parameter, for which the grouping condition is added
columnExpression the expression used as a predicate
Query Union(Select unionSelect)
Query Union(Query unionSelectQuery)

Combines the results of the passed query with the results of the current query excluding the duplicates from the result set.

Parameters
subSelect selection subquery
subSelectQuery subquery
Query UnionAll(Select unionSelect)
Query UnionAll(Query unionSelectQuery)

Combines the results of the passes query with the results of the current query. The duplicates are not excluded from the result set.

Parameters
subSelect selection subquery
subSelectQuery subquery
EntitySchemaQuery class
Advanced

Namespace Terrasoft.Core.Entities.

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.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaQuery class.

Constructors 

EntitySchemaQuery(EntitySchema rootSchema)

Creates a class instance, where the EntitySchema passed instance is set as a root schema. The manager of the passed-in instance of the root schema is set as the schema manager.

EntitySchemaQuery(EntitySchemaManager entitySchemaManager, string sourceSchemaName)

Creates a class instance with the specified EntitySchemaManager and the root schema passed as an argument.

EntitySchemaQuery(EntitySchemaQuery source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

Cache Terrasoft.Core.Store.ICacheStore

The query cache.

CacheItemName string

Name of the cache item.

CanReadUncommitedData bool

Determines whether the query results will include the data for which the transaction is not completed.

Caption Terrasoft.Common.LocalizableString

Header.

ChunkSize int

The number of strings in one chunk.

Columns Terrasoft.Core.Entities.EntitySchemaQueryColumnCollection

Collection of columns of the current entity schema query.

DataValueTypeManager DataValueTypeManager

Manager of the data type values.

EntitySchemaManager Terrasoft.Core.Entities.EntitySchemaManager

Entity schema manager.

Filters Terrasoft.Core.Entities.EntitySchemaQueryFilterCollection

Collection of filters of the current entity schema query.

HideSecurityValue bool

Determines whether the values of the encrypted columns will be hidden.

IgnoreDisplayValues bool

Determines whether the displayed column values will be used in the query.

IsDistinct bool

Indicates whether duplicates in the resulting data set should be removed.

IsInherited bool

Indicates whether the query is inherited.

JoinRightState QueryJoinRightLevel

Determines the conditions for applying permissions when using related tables if the schema is managed by records.

Manager Terrasoft.Core.IManager

Schema manager.

ManagerItem Terrasoft.Core.IManagerItem

Manager element.

Name string

Name.

ParentCollection Terrasoft.Core.Entities.EntitySchemaQueryCollection

A collection of queries to which the current request to the object schema belongs.

ParentEntitySchema Terrasoft.Core.Entities.EntitySchema

Parent schema of the query.

PrimaryQueryColumn Terrasoft.Core.Entities.EntitySchemaQueryColumn

The column created from the primary column of the root schema Initialized during the first access.

QueryOptimize bool

Allows using query optimization./p>

RootSchema Terrasoft.Core.Entities.EntitySchema

The root schema.

RowCount int

Number of rows that are returned by the query..

SchemaAliasPrefix string

The prefix used to create schema alias.

SkipRowCount int

Number of rows to skip when returning the query result.

UseAdminRights bool

The parameter that defines whether permissions will be taken into account when constructing a data acquisition request.

UseLocalization bool

Determines whether localizable data will be used.

UseOffsetFetchPaging bool

Determines whether the per-page returning of the query result is available.

UseRecordDeactivation bool

Determines whether data will be excluded from filtering.

Methods 

void AddAllSchemaColumns(bool skipSystemColumns)

The object schema adds all the columns of the root schema in the column collection of the current query.

EntitySchemaQueryColumn AddColumn(string columnPath, AggregationTypeStrict aggregationType, out EntitySchemaQuery subQuery)
void AddColumn(EntitySchemaQueryColumn queryColumn)
EntitySchemaQueryColumn AddColumn(string columnPath)
EntitySchemaQueryColumn AddColumn(EntitySchemaQueryFunction function)
EntitySchemaQueryColumn AddColumn(object parameterValue, DataValueType parameterDataValueType)
EntitySchemaQueryColumn AddColumn(EntitySchemaQuery subQuery)

Creates and inserts a column in the current entity schema query.

Parameters
columnPath path to the schema column in relation to the root schema
aggregationType the type of aggregating function. The enumeration type values of the Terrasoft.Common.AggregationTypeStrict aggregate function are passed as a parameter
subQuery reference to the created subquery placed in the column
queryColumn the EntitySchemaQueryColumn instance to be added to the column collection of the current query
function the EntitySchemaQueryFunction function instance
parameterValue the value of the parameter added to the query as a column
parameterDataValueType the type of parameter value added to the query as a column
void ClearCache()

Clears the cache of the current query.

static void ClearDefCache(string cacheItemName)

Removes the item with the specified cacheItemName name from the cache of the query.

object Clone()

Creates a clone of the current EntitySchemaQuery instance.

EntitySchemaQueryExpression CreateAggregationEntitySchemaExpression(string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType)

Returns expression of the aggregated function with the specified aggregation type from the Terrasoft.Common.AggregationTypeStrict enumeration for the column, located at the leftExprColumnPath specified path.

static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue)
static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue, DataValueType valueType)
static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue, string displayValue, DataValueType valueType)

Creates an expression for the query parameter.

Parameters
parameterValue the type of the parameter
valueType the type of the parameter value
displayValue the parameter displayed value
static IEnumerable CreateParameterExpressions(DataValueType valueType, params object[] parameterValues)
static IEnumerable CreateParameterExpressions(DataValueType valueType, IEnumerable<object> parameterValues)

Creates an expression collection for the query parameters with a certain DataValueType type of data.

static EntitySchemaQueryExpression CreateSchemaColumnExpression(EntitySchemaQuery parentQuery, EntitySchema rootSchema, string columnPath, bool useCoalesceFunctionForMultiLookup, bool useDisplayValue)
static EntitySchemaQueryExpression CreateSchemaColumnExpression(EntitySchema rootSchema, string columnPath, bool useCoalesceFunctionForMultiLookup)
EntitySchemaQueryExpression CreateSchemaColumnExpression(string columnPath, bool useCoalesceFunctionForMultiLookup)

Returns the expression of the entity schema column.

Parameters
parentQuery the entity schema query for which the column expression is created
rootSchema the root schema
columnPath path to the schema column in relation to the root schema
useCoalesceFunctionForMultiLookup indicates whether to use the COALESCE function for the column of the lookup type. Optional parameter, set to false by default
useDisplayValue indicates whether to use the displayed value for the column. Optional parameter, set to false by default
Enumerable CreateSchemaColumnExpressions(params string[] columnPaths)
IEnumerable CreateSchemaColumnExpressions(IEnumerable columnPaths, bool useCoalesceFunctionForMultiLookup)

Returns the collection of column expressions of the entity schema query by the specified columnPaths. collection of paths to columns.

IEnumerable CreateSchemaColumnExpressionsWithoutCoalesce(params string[] columnPaths)

Returns the collection of column expressions of the entity schema query by the specified array of paths to columns. If it is a column of the multilookup type, the COALESCE function does not apply to its values.

static EntitySchemaQueryExpression CreateSchemaColumnQueryExpression(string columnPath, EntitySchema rootSchema, EntitySchemaColumn schemaColumn, bool useDisplayValue)
static EntitySchemaQueryExpression CreateSchemaColumnQueryExpression(string columnPath, EntitySchema rootSchema, bool useDisplayValue)

Returns the expression of the entity schema query by the specified path to column, root schema and schema column instance. You can define which column value type to use in the expression – either the stored value or the displayed value.

EntitySchemaQueryExpression CreateSubEntitySchemaExpression(string leftExprColumnPath)

Returns the expression of entity schema subquery for the column located at the specified leftExprColumnPath path.

EntitySchemaAggregationQueryFunction CreateAggregationFunction(AggregationTypeStrict aggregationType, string columnPath)

Returns the EntitySchemaAggregationQueryFunction aggregation function instance with the specified type of aggregation from the Terrasoft.Common.AggregationTypeStrict enumeration for the column at the specified columnPath path in relation to the root schema.

EntitySchemaCaseNotNullQueryFunction CreateCaseNotNullFunction(params EntitySchemaCaseNotNullQueryFunctionWhenItem[] whenItems)

Returns the instance of the CASE EntitySchemaCaseNotNullQueryFunction function for the specified EntitySchemaCaseNotNullQueryFunctionWhenItem array of condition expressions.

EntitySchemaCaseNotNullQueryFunctionWhenItem CreateCaseNotNullQueryFunctionWhenItem(string whenColumnPath, object thenParameterValue)

Returns an expression instance for the sql construct of the WHEN <Expression_1> IS NOT NULL THEN <Expression_2> view

Parameters
whenColumnPath path to the column that contains the expression of the WHEN clause
thenParameterValue path to the column that contains the expression of the THEN clause
EntitySchemaCastQueryFunction CreateCastFunction(string columnPath, DBDataValueType castType)

Returns an instance of the CAST EntitySchemaCastQueryFunction function for the column expression located at the specified columnPath path relative to the root schema and the specified DBDataValueType target data type.

EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(params string[] columnPaths)
static EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(EntitySchemaQuery parentQuery, EntitySchema rootSchema, params string[] columnPaths)
static EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(EntitySchema rootSchema, params string[] columnPaths)

Returns the function instance returning the first expression that is other than null from the list of arguments for the specified columns.

Parameters
columnPaths array of paths to columns in relation to the root schema
parentQuery query to the entity schema, for which the function instance is created
rootSchema the root schema
EntitySchemaConcatQueryFunction CreateConcatFunction(params EntitySchemaQueryExpression[] expressions)

Returns a function instance for generating a string that is the result of combining the string values of function arguments for the specified EntitySchemaQueryExpression array of expressions.

EntitySchemaDatePartQueryFunction CreateDatePartFunction(EntitySchemaDatePartQueryFunctionInterval interval, string columnPath)

Returns the DATEPART instance of the EntitySchemaDatePartQueryFunction function that determines the date interval specified by the EntitySchemaDatePartQueryFunctionInterval enumeration (month, day, hour, year, week day...) for the value of column located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateDayFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that determines the Day date range for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateHourFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the Hour date for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateHourMinuteFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the Minute date for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateMonthFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the Month date for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateWeekdayFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Week day] date for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateWeekFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the Week date for a column value located at the specified path in relation to the root schema.

EntitySchemaDatePartQueryFunction CreateYearFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the Year date for a column value located at the specified path in relation to the root schema.

EntitySchemaIsNullQueryFunction CreateIsNullFunction(string checkColumnPath, string replacementColumnPath)

Returns the instance of the EntitySchemaIsNullQueryFunction function for columns with values to check and substitute located at specified paths in relation to the root schema.

EntitySchemaLengthQueryFunction CreateLengthFunction(string columnPath)
EntitySchemaLengthQueryFunction CreateLengthFunction(params EntitySchemaQueryExpression[] expressions)

Creating an instance of the LEN function (the function for returning the length of expression) for the column expression at the specified path in relation to the root schema or for the specified expression array.

EntitySchemaTrimQueryFunction CreateTrimFunction(string columnPath)
EntitySchemaTrimQueryFunction CreateTrimFunction(params EntitySchemaQueryExpression[] expressions)

Creating an instance of the TRIM function (the function for removing all leading and trailing whitespace from expression) for the column expression at the specified path in relation to the root schema or for the specified expression array.

EntitySchemaUpperQueryFunction CreateUpperFunction(string columnPath)

Returns an instance of the EntitySchemaUpperQueryFunction function that converts all the argument expression characters into upper case. The column expression located at the specified path relative to the root schema is specified as the argument.

EntitySchemaCurrenddateQueryFunction CreateCurrenddateFunction()

Returns an instance of the EntitySchemaCurrentDateQueryFunction function that defines the current date.

EntitySchemaCurrenddateTimeQueryFunction CreateCurrenddateTimeFunction()

Returns an instance of the EntitySchemaCurrentDateTimeQueryFunction function that returns the current date and time.

EntitySchemaCurrentTimeQueryFunction CreateCurrentTimeFunction()

Returns the instance of the EntitySchemaCurrentTimeQueryFunction function that defines the current time.

EntitySchemaCurrentUserAccountQueryFunction CreateCurrentUserAccountFunction()

Returns the instance of the EntitySchemaCurrentUserAccountQueryFunction function that defines the account Id of the current user.

EntitySchemaCurrentUserContactQueryFunction CreateCurrentUserContactFunction()

Returns the instance of the EntitySchemaCurrentUserContactQueryFunction function that defines the contact Id of the current user.

EntitySchemaCurrentUserQueryFunction CreateCurrentUserFunction()

Returns the instance of the EntitySchemaCurrentUserQueryFunction function that defines the current user.

EntitySchemaQueryFilter CreateExistsFilter(string rightExpressionColumnPath)

Creates the Exists by the specified condition type comparison filter and sets the column expression located at the rightExpressionColumnPath specified path as the testing value.

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, params string[] rightExpressionColumnPaths)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryExpression rightExpression)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryFunction rightExpressionValue)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, int rightValue)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, DateTime rightValue)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, DayOfWeek rightValue)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, int rightValue)IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, DateTime rightValue
)IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, DayOfWeek rightValue)IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQuery rightExpressionValue)
EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, int rightExprParameterValue)
EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, double rightExprParameterValue)
EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrlict leftExprAggregationType, DateTime rightExprParameterValue)
EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, string rightExprParameterValue)
EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, object rightExprParameterValue, out EntitySchemaQuery leftExprSubQuery)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, EntitySchemaQueryMacrosType macrosType, int daysCount)
IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, EntitySchemaQueryMacrosType macrosType, out EntitySchemaQuery leftExprSubQuery, int daysCount)

Creates a query filter for selecting records according to specified conditions.

Parameters
comparisonType comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration
leftExpressionColumnPath path to the column that contains the left side expression of the filter
leftExpression expression on the left side of the filter
leftExprAggregationType the type of aggregating function
leftExprSubQuery parameter that returns a subquery for the left side expression of the filter (if it is not equal to null) or a subquery for the first expression on the right side of the filter (if the left-side expression of the filter is equal to null)
rightExpressionColumnPaths array of column paths that contain the right side expressions of the filter
rightExpression expression on the right side of the filter;
rightExpressionValue instance of the expression function on the right side of the filter (the EntitySchemaQueryFunction parameter type) or a subquery expression on the right side of the filter (the EntitySchemaQuery parameter type)
rightValue value processed by the macro in the right side of the filter
rightExprParameterValue parameter value, to which the aggregate function on the right side of the filter is applied
macrosType a type of macro from the Terrasoft.Core.Entities.EntitySchemaQueryMacrosType enumeration
daysCount value to which the macro from the right side of the filter is applied. Optional parameter, default value is 0
IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)
IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)
IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, string leftExpressionColumnPath, IEnumerable<object> rightExpressionParameterValues, bool useDisplayValue)
static IEntitySchemaQueryFilterItem CreateFilterWithParameters(EntitySchemaQuery parentQuery, EntitySchema rootSchema, FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)
static IEntitySchemaQueryFilterItem CreateFilterWithParameters(EntitySchema rootSchema, FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)

Creates a parameterized filter for selecting records matching certain conditions.

Parameters
parentQuery parent query for which the filter is being created
rootSchema the root schema
comparisonType comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration
useDisplayValue defines the column value type that is used in the filter: true for the displayed value false – for the stored value;
leftExpressionColumnPath path to the column that contains the left side expression of the filter
rightExpressionParameterValues collection of parameter expressions on the right side of the filter
IEntitySchemaQueryFilterItem CreateIsNotNullFilter(string leftExpressionColumnPath)

Creates the comparison filter of the Is not “null” in database type and, as the tested value, sets the column expression located at the path specified in the leftExpressionColumnPath parameter.

IEntitySchemaQueryFilterItem CreateIsNullFilter(string leftExpressionColumnPath)

Creates the comparison filter of the Is “null” in database type and, as the testing condition, sets the column expression located at the path specified in the leftExpressionColumnPath parameter.

EntitySchemaQueryFilter CreateNotExistsFilter(string rightExpressionColumnPath)

Creates the Does not exist by the specified condition type comparison filter and sets the column expression located at the rightExpressionColumnPath specified path as the tested value.

DataTable GetDataTable(UserConnection userConnection)

Returns the result of the current query execution to the object schema as a data table in memory using UserConnection.

static int GetDayOfWeekNumber(UserConnection userConnection, DayOfWeek dayOfWeek)

Returns the sequence number of the week day for the System.DayOfWeek entity taking into account local settings.

Entity GetEntity(UserConnection userConnection, object primaryColumnValue)

Returns an Entity instance by the primaryColumnValue primary key using UserConnection.

EntityCollection GetEntityCollection(UserConnection userConnection, EntitySchemaQueryOptions options)
EntityCollection GetEntityCollection(UserConnection userConnection)

Returns the Entity instance collection representing the results of executing the current query using UserConnection and the specified additional EntitySchemaQueryOptions query settings.

EntitySchema GetSchema()

Returns the EntitySchema entity schema instance of the current EntitySchemaQuery instance.

Select GetSelectQuery(UserConnection userConnection)Select GetSelectQuery(UserConnection userConnection, EntitySchemaQueryOptions options)

Returns a data selection query instance using UserConnection and the specified additional settings of the EntitySchemaQueryOptions query.

EntitySchemaQueryColumnCollection GetSummaryColumns()
EntitySchemaQueryColumnCollection GetSummaryColumns(IEnumerable<string> columnNames)

Returns the collection of expressions of the query columns for which total values are calculated.

Entity GetSummaryEntity(UserConnection userConnection, EntitySchemaQueryColumnCollection summaryColumns)
Entity GetSummaryEntity(UserConnection userConnection)
Entity GetSummaryEntity(UserConnection userConnection, IEnumerable<string> columnNames)
Entity GetSummaryEntity(UserConnection userConnection, params string[] columnNames)

Returns an Entity instance for the result returned by the total value selection query.

Parameters
userConnection system user connection
summaryColumns collection of the query columns, whose totals are selected
columnNames column name collection
Select GetSummarySelectQuery(UserConnection userConnection, EntitySchemaQueryColumnCollection summaryColumns)
Select GetSummarySelectQuery(UserConnection userConnection)
Select GetSummarySelectQuery(UserConnection userConnection, IEnumerable<string> columnNames)
Select GetSummarySelectQuery(UserConnection userConnection, params string[] columnNames)

Builds a query for the selection of total values for a specified column collection of the EntitySchemaQuery current instance.

Parameters
userConnection system user connection
summaryColumns collection of the query columns, whose totals are selected
columnNames column name collection
T GetTypedColumnValue(Entity entity, string columnName)

Returns the typed column value with columnName from the Entity passed instance.

void LoadDataTableData(UserConnection userConnection, DataTable dataTable)
void LoadDataTableData(UserConnection userConnection, DataTable dataTable, EntitySchemaQueryOptions options)

Loads the result of executing the current query to the entity schema into the System.Data.DataTable entity using UserConnection and the EntitySchemaQueryOptions specified additional query settings.

void RemoveColumn(string columnName)

Removes the columnName column from the current query column collection.

void ResetSchema()

Clears the schema of the EntitySchemaQuery current instance.

void ResetSelectQuery()

Clears the select query for the current entity schema query.

void SetLocalizationCultureId(System.Guid cultureId)

Sets the identifier of the local culture.

Insert class
Advanced

The Namespace Terrasoft.Core.DB.

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.

Attention. When working with the Insert class, the access permissions are not applied to the added records. The user connection is only used for accessing the database table.
Note. Use the “.NET class libraries of platform core” documentation to access the full list of the Insert class methods and properties, its parent classes, and the implemented interfaces.

Constructors 

Entity(UserConnection userConnection)

Creates a new Entity class instance for the set UserConnection.

Insert(UserConnection userConnection)

Creates a class instance with the specified UserConnection parameter.

Insert(Insert source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

UserConnection Terrasoft.Core.UserConnection

The current user connection at the moment of executing the query.

Source Terrasoft.Core.DB.ModifyQuerySource

Data source.

Parameters Terrasoft.Core.DB.QueryParameterCollection

Collection of the query parameters.

HasParameters bool

Determines whether the query has any parameters.

BuildParametersAsValue bool

Determines whether the query parameters are to be added as values into the query text.

ColumnValues Terrasoft.Core.DB.ModifyQueryColumnValueCollection

Collection of values of the query columns.

ColumnValuesCollection List<ModifyQueryColumnValueCollection>

Collection of column values for adding multiple records.

Methods 

void ResetCachedSqlText()

Clears the cached text of the query.

QueryParameterCollection GetUsingParameters()

Returns the collection of parameters used by the query.

void ResetParameters()

Clears the collection of the query parameters..

void SetParameterValue(string name, object value)

Sets the value of the query parameter.

Parameters
name parameter name
value the value
void InitializeParameters()

Initiates the collection of the query parameters.

int Execute()

Executes the query. Returns the number of records involved by the query.

int Execute(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the number of records involved by the query.

Insert Into(string schemaName)Insert Into(ModifyQuerySource source)

Adds the data source to the current query.

Parameters
schemaName the schema name
source the data source
Insert Set(string sourceColumnAlias, Select subSelect)
Insert Set(string sourceColumnAlias, Query subSelectQuery)
Insert Set(string sourceColumnAlias, QueryColumnExpression columnExpression)
Insert Set(string sourceColumnAlias, QueryParameter parameter)

Adds a SET clause to the current query for assigning the passed expression or parameter to the column. Returns the current Insert instance.

Parameters
sourceColumnAlias column alias
subSelect selection subquery
subSelectQuery subquery
columnExpression the expression of the column
parameter parameter of the query
Insert Values()

Initiates values for adding multiple columns.

InsertSelect class
Advanced

The Namespace Terrasoft.Core.DB.

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. 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.

Attention. 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.
Note. After the InsertSelect query is executed, the database will be complemented with all records returned in its Select. subquery.
Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the InsertSelect class.

Constructors 

InsertSelect(UserConnection userConnection)

Creates a class instance with the specified UserConnection parameter.

InsertSelect(InsertSelect source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

UserConnection Terrasoft.Core.UserConnection

The current user connection at the moment of executing the query.

Source Terrasoft.Core.DB.ModifyQuerySource

Data source.

Parameters Terrasoft.Core.DB.QueryParameterCollection

Collection of the query parameters.

HasParameters bool

Determines whether the query has any parameters.

BuildParametersAsValue bool

Determines whether the query parameters are to be added as values into the query text.

Columns Terrasoft.Core.DB.ModifyQueryColumnValueCollection

Collection of values of the query columns.

Select Terrasoft.Core.DB.Select

The Terrasoft.Core.DB.Select instance used in the query.

Methods 

void ResetCachedSqlText()

Clears the cached text of the query.

QueryParameterCollection GetUsingParameters()

Returns the collection of parameters used by the query.

void ResetParameters()

Clears the collection of the query parameters.

void SetParameterValue(string name, object value)

Sets the value of the query parameter.

Parameters
name parameter name
value the value
void InitializeParameters()

Initiates the collection of the query parameters.

int Execute()

Executes the query. Returns the number of records involved by the query.

int Execute(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the number of records involved by the query.

InsertSelect Into(string schemaName)
InsertSelect Into(ModifyQuerySource source)

Adds the data source to the current query.

Parameters
schemaName the schema name
source the data source
InsertSelect Set(IEnumerable<sdling> sourceColumnAliases)
InsertSelect Set(params sdling[] sourceColumnAliases)
InsertSelect Set(IEnumerable<ModifyQueryColumn> columns)
InsertSelect Set(params ModifyQueryColumn[] columns)
	

Adds a set of columns to the current query, where the values will be added via the subquery. Returns the current InsertSelect instance.

Parameters
sourceColumnAliases method parameter collection or array containing the column aliases
columns method parameter collection or array containing the column instances
InsertSelect FromSelect(Select subSelect)
InsertSelect FromSelect(Query subSelectQuery)

Adds the SELECT clause to the current query.

Parameters
subSelect selection subquery
subSelectQuery subquery
Update class
Advanced

The Namespace Terrasoft.Core.DB.

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.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods and properties, parent classes, and implemented interfaces of the Update class.

Constructors 

Update(UserConnection userConnection)

Creates a class instance using UserConnection.

Update(UserConnection userConnection, string schemaName)

Creates a class instance for the specified schema using UserConnection.

Update(UserConnection userConnection, ModifyQuerySource source)

Creates a class instance for the specified data source using UserConnection.

Update(Insert source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

UserConnection Terrasoft.Core.UserConnection

The current user connection at the moment of executing the query.

Condition Terrasoft.Core.DB.QueryCondition

Condition of Where expression in the query.

HasCondition bool

Determines whether Where expression is available in the query.

Source Terrasoft.Core.DB.ModifyQuerySource

The query data source.

ColumnValues Terrasoft.Core.DB.ModifyQueryColumnValueCollection

Collection of values of the query columns.

Methods 

void ResetCachedSqlText()

Clears the cached text of the query.

QueryParameterCollection GetUsingParameters()

Returns the collection of parameters used by the query.

int Execute()

Executes the query. Returns the number of records involved by the query.

int Execute(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the number of records involved by the query.

QueryCondition Where()
QueryCondition Where(string sourceColumnAlias)
QueryCondition Where(string sourceAlias, string sourceColumnAlias)
QueryCondition Where(Select subSelect)
QueryCondition Where(Query subSelectQuery)
QueryCondition Where(QueryColumnExpression columnExpression)
Query Where(QueryCondition condition)

Adds the initial condition to the current query.

Parameters
sourceColumnAlias an alias of the column for which the condition is added
sourceAlias the alias of the source
subSelect a subquery of the data selection, for whose results the condition is added
subSelectQuery the subquery, for whose results the condition is added
columnExpression the expression, for whose results the condition is added
condition the query condition
QueryCondition And()
QueryCondition And(string sourceColumnAlias)
QueryCondition And(string sourceAlias, string sourceColumnAlias)
QueryCondition And(Select subSelect)
QueryCondition And(Query subSelectQuery)
QueryCondition And(QueryParameter parameter)
QueryCondition And(QueryColumnExpression columnExpression)
Query And(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the AND logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
QueryCondition Or()
QueryCondition Or(string sourceColumnAlias)
QueryCondition Or(string sourceAlias, string sourceColumnAlias)
QueryCondition Or(Select subSelect)
QueryCondition Or(Query subSelectQuery)
QueryCondition Or(QueryParameter parameter)
QueryCondition Or(QueryColumnExpression columnExpression)
Query Or(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the OR logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
Update Set(string sourceColumnAlias, Select subSelect)
Update Set(string sourceColumnAlias, Query subSelectQuery)
Update Set(string sourceColumnAlias, QueryColumnExpression columnExpression)
Update Set(string sourceColumnAlias, QueryParameter parameter)

Adds a SET clause to the current query for assigning the passed expression or parameter to the column. Returns the current Update instance.

Parameters
sourceColumnAlias column alias
subSelect selection subquery
subSelectQuery subquery
columnExpression expression of the column
parameter parameter of the query
Delete class
Advanced

Namespace Terrasoft.Core.DB

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.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the Delete class.

Constructors 

Delete(UserConnection userConnection)

Creates a class instance using UserConnection.

Delete(Delete source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

UserConnection Terrasoft.Core.UserConnection

The current user connection at the moment of executing the query.

Condition Terrasoft.Core.DB.QueryCondition

Condition of Where expression in the query.

HasCondition bool

Determines whether Where expression is available in the query.

Source Terrasoft.Core.DB.ModifyQuerySource

The query data source.

Methods 

void ResetCachedSqlText()

Clears the cached text of the query.

QueryParameterCollection GetUsingParameters()

Returns the collection of parameters used by the query.

int Execute()

Executes the query. Returns the number of records involved by the query.

int Execute(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the number of records involved by the query.

QueryCondition Where()QueryCondition Where(string sourceColumnAlias)
QueryCondition Where(string sourceAlias, string sourceColumnAlias)
QueryCondition Where(Select subSelect)
QueryCondition Where(Query subSelectQuery)
QueryCondition Where(QueryColumnExpression columnExpression)
Query Where(QueryCondition condition)

Adds the initial condition to the current query.

Parameters
sourceColumnAlias an alias of the column for which the condition is added
sourceAlias the alias of the source
subSelect a subquery of the data selection, for whose results the condition is added
subSelectQuery the subquery, for whose results the condition is added
columnExpression the expression, for whose results the condition is added
condition the query condition
QueryCondition And()
QueryCondition And(string sourceColumnAlias)
QueryCondition And(string sourceAlias, string sourceColumnAlias)
QueryCondition And(Select subSelect)
QueryCondition And(Query subSelectQuery)
QueryCondition And(QueryParameter parameter)
QueryCondition And(QueryColumnExpression columnExpression)
Query And(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the AND logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
QueryCondition Or()
QueryCondition Or(string sourceColumnAlias)
QueryCondition Or(string sourceAlias, string sourceColumnAlias)
QueryCondition Or(Select subSelect)
QueryCondition Or(Query subSelectQuery)
QueryCondition Or(QueryParameter parameter)
QueryCondition Or(QueryColumnExpression columnExpression)
Query Or(QueryCondition condition)

Adds the condition (predicate) to the current query condition using the OR logical operation.

Parameters
sourceColumnAlias an alias of the column, for which the predicate is added
sourceAlias the alias of the source
subSelect the data selection subquery used as a predicate
subSelectQuery the subquery used as a predicate
parameter parameter that the predicate is added to
columnExpression the expression used as a predicate
condition the query condition
Delete From(string schemaName)
Delete From((ModifyQuerySource source)

Adds the data source to the current query. Returns the current Delete instance.

Parameters
schemaName schema name (tables, views)
source the data source
Entity class
Advanced

Namespace Terrasoft.Core.Entities.

The Terrasoft.Core.Entities.Entity class is designed to provide access to an object that represents a record in the database table.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the Entity class.

Constructors 

public Entity(UserConnection userConnection)

Creates a new Entity class instance for the set UserConnection.

public Entity(UserConnection userConnection, Guid schemaUId)

Creates a new Entity class instance for the set UserConnection and the schema set by the schemaUId identifier.

public Entity(Entity source)

Creates a class instance that is a clone of the instance passed as an argument.

Properties 

ChangeType EntityChangeType

The type of changing the entity status (added, modified, deleted, unchanged).

EntitySchemaManager EntitySchemaManager

An instance of the entity schema manager.

EntitySchemaManagerName string

Name of the entity schema manager.

HasColumnValues bool

Determines whether an object has at least one column.

HierarchyColumnValue Guid

Value of column of the parent record relationship for hierarchical objects.

InstanceUId Guid

Object instance identifier.

IsDeletedFromDB bool

Determines whether the object is deleted from the database.

IsInColumnValueChanged bool

Determines whether the handling of the ColumnValueChanged event is performed.

IsInColumnValueChanging bool

Determines whether the handling of the ColumnValueChanging event is performed.

IsInDefColumnValuesSet bool

Determines whether the handling of the DefColumnValuesSet event is performed.

IsInDeleted bool

Determines whether the handling of the Deleted event is performed.

IsInDeleting bool

Determines whether the handling of the Deleting event is performed.

IsInInserted bool

Determines whether the handling of the Inserted event is performed.

IsInInserting bool

Determines whether the handling of the Inserting event is performed.

IsInLoaded bool

Determines whether the handling of the Loaded event is performed.

IsInLoading bool

Determines whether the handling of the Loading event is performed.

IsInSaved bool

Determines whether the handling of the Saved event is performed.

IsInSaveError bool

Determines whether the handling of the SaveError event is performed.

IsInSaving bool

Determines whether the handling of the Saving event is performed.

IsInUpdated bool

Determines whether the handling of the Updated event is performed.

IsInUpdating bool

Determines whether the handling of the Updating event is performed.

IsInValidating bool

Determines whether the handling of the Validating event is performed.

IsSchemaInitialized bool

Determines whether the entity schema is initialized.

LicOperationPrefix string

The prefix of the operation that is being licensed.

LoadState EntityLoadState

State of the object loading.

PrimaryColumnValue Guid

Initial column identifier.

PrimaryDisplayColumnValue string

Initial column value for displaying.

Process Process

The embedded process of an object.

Schema EntitySchema

An instance of the entity schema.

SchemaName string

Object schema name.

StoringState StoringObjectState

Object status (modified, added, deleted, unchanged).

UseAdminRights bool

Determines whether permissions will be taken into account when inserting, updating, deleting and receiving data.

UseDefRights bool

Determines whether the default object permissions should be used.

UseLazyLoad bool

Determines whether to use the initial lazy loading of object data.

UserConnection UserConnection

User connection.

ValidationMessages EntityValidationMessageCollection

Collection of the messages output when validating an object.

ValueListSchemaManager ValueListSchemaManager

An instance of the object enumerations manager.

ValueListSchemaManagerName string

Name of the manager for object enumerations.

Methods 

void AddDefRights()void AddDefRights(Guid primaryColumnValue)
void AddDefRights(IEnumerable<Guid> primaryColumnValues)

Sets the default permissions for the given object.

virtual object Clone()

Creates a clone of the current Entity instance.

Insert CreateInsert(bool skipLookupColumnValues)

Creates a query to insert data.

Update CreateUpdate(bool skipLookupColumnValues)

Creates a query to update data.

virtual bool Delete()
virtual bool Delete(object keyValue)

Deletes the object record from the database. The keyValue parameter determines the initial key of a record.

bool DeleteWithCancelProcess()

Deletes the object record from the database and cancels the launched process.

static Entity DeserializeFromJson(UserConnection userConnection, string jsonValue)

Creates an Entity type object using userConnection and populates the field values from the specified string of the JSON jsonValue format.

bool ExistInDB(EntitySchemaColumn conditionColumn, object conditionValue)
bool ExistInDB(string conditionColumnName, object conditionValue)
bool ExistInDB(object keyValue)
bool ExistInDB(Dictionary<string,object> conditions)

Determines whether a record matching the given condition of the conditionValue query to the conditionColumn object schema column or with the specified keyValue initial key exists in the database.

bool FetchFromDB(EntitySchemaColumn conditionColumn, object conditionValue, bool useDisplayValues)
bool FetchFromDB(string conditionColumnName, object conditionValue, bool useDisplayValues)
bool FetchFromDB(object keyValue, bool useDisplayValues)
bool FetchFromDB(Dictionary<string,object> conditions, bool useDisplayValues)
bool FetchFromDB(EntitySchemaColumn conditionColumn, object conditionValue, IEnumerable<EntitySchemaColumn> columnsToFetch, bool useDisplayValues)
bool FetchFromDB(string conditionColumnName, object conditionValue, IEnumerable<string>columnNamesToFetch, bool useDisplayValues)

By the specified condition, loads the object from the database.

Parameters
conditionColumn the column, for which the selection condition is specified
conditionColumnName the name of the column, for which the selection condition is specified
conditionValue the value of the condition column for the selected data
columnsToFetch the list of columns to be selected
columnNamesToFetch the list of column names to be selected
conditions set of conditions for filtering the selection of object records
keyValue the key field value
useDisplayValues indicates that the query returns the primary display values. If the parameter is true, the query will return the primary display values
bool FetchPrimaryColumnFromDB(object keyValue)

By the set condition, keyValue loads the object with the initial column from the database.

bool FetchPrimaryInfoFromDB(EntitySchemaColumn conditionColumn, object conditionValue)
bool FetchPrimaryInfoFromDB(string conditionColumnName, object conditionValue)

By the set condition, loads an object with initial columns including the initial display column from the database.

byte[] GetBytesValue(string valueName)

Returns the value of the specified object column as a byte array.

IEnumerable<EntityColumnValue> GetChangedColumnValues()

Returns the name collection of the object properties that have been modified.

string GetColumnDisplayValue(EntitySchemaColumn column)

Returns the value for display of the object property that matches the specified column of the entity schema.

object GetColumnOldValue(string valueName)
object GetColumnOldValue(EntitySchemaColumn column)

Returns the previous value of the specified object property.

virtual object GetColumnValue(string valueName)
virtual object GetColumnValue(EntitySchemaColumn column)

Returns the value of the object column with the specified name that matches the passed column of the object schema.

IEnumerable<string> GetColumnValueNames()

Returns the collection of object column names.

virtual bool GetIsColumnValueLoaded(string valueName)
bool GetIsColumnValueLoaded(EntitySchemaColumn column)

Returns whether the specified property of an object is loaded.

virtual MemoryStream GetStreamValue(string valueName)

Returns the value of the passed object schema column converted into the System.IO.MemoryStream type instance.

virtual TResult GetTypedColumnValue<TResult>(string valueName)
TResult GetTypedColumnValue<TResult>(EntitySchemaColumn column)

Returns the typed value of the object property that matches the specified column of the entity schema.

TResult GetTypedOldColumnValue<TResult>(string valueName)
TResult GetTypedOldColumnValue<TResult>(EntitySchemaColumn column)

Returns the typed previous value of the entity property that matches the specified column of the entity schema.

virtual bool InsertToDB(bool skipLookupColumnValues, bool validateRequired)

Adds an entry of the current object to the database taking into account the passed parameters:

Parameters
skipLookupColumnValues parameter that determines whether the columns of the lookup type are to be added to the database. If the parameter is true, the columns of the lookup type will not be added to the base. Default value – false
validateRequired parameter that determines the necessity of validating the required values. Default value – true
bool IsColumnValueLoaded(string valueName)
bool IsColumnValueLoaded(EntitySchemaColumn column)

Determines whether the value of the object property with the specified name is loaded.

virtual bool Load(DataRow dataRow)
virtual bool Load(DataRow dataRow, Dictionary<string,string> columnMap)
virtual bool Load(IDataReader dataReader)
virtual bool Load(IDataReader dataReader, IDictionary<string,string> columnMap)
virtual bool Load(object dataSource)
virtual bool Load(object dataSource, IDictionary<string,string> columnMap)

Populates the object with the passed data.

Parameters
dataRow the System.Data.DataRow instance from which the data is loaded to the object
dataReader the System.Data.IDataReader instance from which the data is loaded to the object
dataSource the System.Object instance from which the data is loaded to the object
columnMap object properties populated with data
void LoadColumnValue(string columnValueName, IDataReader dataReader, int fieldIndex, int binaryPackageSize)
void LoadColumnValue(string columnValueName, IDataReader dataReader, int fieldIndex)
void LoadColumnValue(string columnValueName, object value)
void LoadColumnValue(EntitySchemaColumn column, object value)

Loads the value from the passed instance for the property with the specified name.

Parameters
columnValueName name of the object property
column object schema column
dataReader the System.Data.IDataReader instance from which the property value is loaded
fieldIndex index of the field loaded from System.Data.IDataReader
binaryPackageSize size of the loaded value
value value of the property that is being loaded
static Entity Read(UserConnection userConnection, DataReader dataReader)

Returns the current property value of the Entity type from the output stream.

void ReadData(DataReader reader)
void ReadData(DataReader reader, EntitySchema schema)

Reads data from the object schema and saves them in the specified object of the System.Data.IDataReader type.

void ResetColumnValues()

Cancels changes for all object properties.

void ResetOldColumnValues()

Reverts all object properties to previous values.

bool Save(bool validateRequired)

Saves the object to the database. The validateRequired parameter determines the necessity of validating the required values. Default value – true.

static string SerializeToJson(Entity entity)

Converts the entity object into a JSON format string.

virtual void SetBytesValue(string valueName, byte[] streamBytes)

Sets the passed value of the System.Byte type for the specified object property.

bool SetColumnBothValues(EntitySchemaColumn column, object value, string displayValue)
bool SetColumnBothValues(string columnValueName, object value, string displayColumnValueName, string displayValue)

Sets the passed value and displayValue to the object property matching the specified schema column.

bool SetColumnValue(string valueName, object value)
bool SetColumnValue(EntitySchemaColumn column, object value)

Sets the passed value to the specified schema column.

void SetDefColumnValue(string columnValueName, object defValue)
void SetDefColumnValue(string columnValueName)

Sets the property with the specified name to the default value.

void SetDefColumnValues()

Sets default values for all object properties.

bool SetStreamValue(string valueName, Stream value)

Sets the passed value of the System.IO.Stream type for the specified object property.

virtual bool UpdateInDB(bool validateRequired)

Updates the object record in the database. The validateRequired parameter determines the necessity of validating the required values. Default value – true.

bool Validate()

Verifies if the required fields are populated.

static void Write(DataWriter dataWriter, Entity entity, string propertyName)
static void Write(DataWriter dataWriter, Entity entity, string propertyName, bool couldConvertForXml)

Records the value of the Entity type to the output stream with the specified name.

Parameters
dataWriter instance of the Terrasoft.Common.DataWriter class that provides methods for sequential recording of values to the output stream
entity value for record of the Entity type
propertyName name of the object
couldConvertForXml allows converting for XML-serialization
void Write(DataWriter dataWriter, string propertyName)

Records the value of the Entity type to the output stream with the specified name.

void WriteData(DataWriter writer)
void WriteData(DataWriter writer, EntitySchema schema)

Records to the output stream for the specified object schema.

Events 

event EventHandler<EntityColumnAfterEventArgs> ColumnValueChanged

Event handler used after modifying the value of an entity column.

The event handler receives an argument of the EntityColumnAfterEventArgs type.

The EntityColumnAfterEventArgs properties that provide information referring to the event:

  • ColumnValueName;
  • DisplayColumnValueName.
event EventHandler<EntityColumnBeforeEventArgs> ColumnValueChanging

Event handler used before modifying the value of an entity column.

The event handler receives an argument of the EntityColumnBeforeEventArgs type.

The EntityColumnBeforeEventArgs properties that provide information referring to the event:

  • ColumnStreamValue;
  • ColumnValue<;/li>
  • ColumnValueName;
  • DisplayColumnValue;
  • DisplayColumnValueName.
event EventHandler<EventArgs> DefColumnValuesSet

Event handler used after setting default values for the object fields.

event EventHandler<EntityAfterEventArgs> Deleted

Event handler used after deleting an object.

The event handler receives an argument of the EntityAfterEventArgs type.

The EntityAfterEventArgs properties that provide information referring to the event:

  • ModifiedColumnValues;
  • PrimaryColumnValue.
event EventHandler<EntityBeforeEventArgs> Deleting

Event handler used before deleting an object.

The event handler receives an argument of the EntityBeforeEventArgs type.

The EntityBeforeEventArgs properties that provide information referring to the event:

  • AdditionalCondition;
  • IsCanceled;
  • KeyValue.
event EventHandler<EntityAfterEventArgs> Inserted

Event handler used after inserting an object.

The event handler receives an argument of the EntityAfterEventArgs type.

The EntityAfterEventArgs properties that provide information referring to the event:

  • ModifiedColumnValues;
  • PrimaryColumnValue.
event EventHandler<EntityBeforeEventArgs> Inserting

Event handler used before inserting an object.

The event handler receives an argument of the EntityBeforeEventArgs type.

The EntityBeforeEventArgs properties that provide information referring to the event:

  • AdditionalCondition;
  • IsCanceled;
  • KeyValue.
event EventHandler<EntityAfterLoadEventArgs> Loaded

Event handler used after loading an object.

The event handler receives an argument of the EntityAfterLoadEventArgs type.

The EntityAfterLoadEventArgs properties that provide information referring to the event:

  • ColumnMap;
  • DataSource.
event EventHandler<EntityBeforeLoadEventArgs> Loading

Event handler used before loading an object.

The event handler receives an argument of the EntityBeforeLoadEventArgs type.

The EntityBeforeLoadEventArgs properties that provide information referring to the event:

  • ColumnMap;
  • DataSource;
  • IsCanceled.
event EventHandler<EntityAfterEventArgs> Saved

Event handler used after saving an object.

The event handler receives an argument of the EntityAfterEventArgs type.

The EntityAfterEventArgs properties that provide information referring to the event:

  • ModifiedColumnValues;
  • PrimaryColumnValue.
event EventHandler<EntitySaveErrorEventArgs> SaveError

Event handler used when an error occurs while saving an object.

The event handler receives an argument of the EntitySaveErrorEventArgs type.

The EntitySaveErrorEventArgs properties that provide information referring to the event:

  • Exception;
  • IsHandled.
event EventHandler<EntityBeforeEventArgs> Saving

Event handler used before saving an object.

The event handler receives an argument of the EntityBeforeEventArgs type.

The EntityBeforeEventArgs properties that provide information referring to the event:

  • AdditionalCondition;
  • IsCanceled;
  • KeyValue.
event EventHandler<EntityAfterEventArgs> Updated

Event handler used after updating an object.

The event handler receives an argument of the EntityAfterEventArgs type.

The EntityAfterEventArgs properties that provide information referring to the event:

  • ModifiedColumnValues;
  • PrimaryColumnValue.
event EventHandler<EntityBeforeEventArgs>Updating

Event handler used before updating an object.

The event handler receives an argument of the EntityBeforeEventArgs type.

The EntityBeforeEventArgs properties that provide information referring to the event:

  • AdditionalCondition;
  • IsCanceled;
  • KeyValue.
event EventHandler<EntityValidationEventArgs> Validating

Event handler used when validating an object.

The event handler receives an argument of the EntityValidationEventArgs type.

The EntityValidationEventArgs properties that provide information referring to the event:

  • Messages.
EntityMapper class
Advanced

The Terrasoft.Configuration.EntityMapper class is a utility configuration class that stored in the FinAppLending package of the Lending product. EntittyMapper allows to map data of one Entity with another using rules defined in the configuration file. Using the approach of mapping the data of different entities avoids the appearance of a monotonous code.

The idea of mapping the data of different entities is implemented in the following classes:

  • EntityMapper – implements the mapping logic.
  • EntityResult – defines the resulting type of the mapped entity.
  • MapConfig – a set of mapping rules.
  • DetailMapConfig – used to set up a list of mapping rules of the details and entities connected with them.
  • RelationEntityMapConfig – contains rules for mapping connected entities.
  • EntityFilterMap – a filter for database query.

The EntityMapper class 

The Namespace Terrasoft.Configuration.

Class implements the mapping logic.

Methods 

virtual EntityResult GetMappedEntity(Guid recId, MapConfig config)

Returns mapped data for two Entity objects.

Parameters
recId GUID records in the database
config an instance of the MapConfig class, which is a set of mapping rules
virtual Dictionary<string, object> GetColumnsValues(Guid recordId, MapConfig config, Dictionary<string, object> result)

Gets the main entity from the database and matches its columns and values according to the rules specified in the config object.

Parameters
recordId GUIDrecodr in the database
config an instance of the MapConfig class, which is a set of mapping rules
result a dictionary of columns and their values of the mapped entity
virtual Dictionary<string, object> GetRelationEntityColumnsValues(List<RelationEntityMapConfig> relations, Dictionary<string, object> dictionaryToMerge, string columnName, Terrasoft.Nui.ServiceModel.DataContract.LookupColumnValue entitylookup)

Gets the related entities from the database and matches them to the main entities.

Parameters
relations a list of rules for obtaining related records
dictionaryToMerge a dictionary with columns and theirs values
columnName name of the parent column
entitylookup an object that contains name and Id of the record in the database

The EntityResult class 

The Namespace Terrasoft.Configuration.

Class defines the resulting type of the mapped entity.

Properties 

Columns Dictionary<string, object>

A dictionary of main entity column names and their values.

Details Dictionary<string, List<Dictionary<string, object>>>

A dictionary of the detail names with the list of their columns and values.

The MapConfig class 

The Namespace Terrasoft.Configuration.

Class sets of mapping rules.

Properties 

SourceEntityName string

Entity name in the database.

Columns Dictionary<string, object>

A dictionary with the names of columns of one entity and compared columns of another entity.

DetailsConfig List<DetailMapConfig>

A list of configuration objects with rules for details.

CleanDetails List<string>

A list of detail names for cleaning their values.

RelationEntities List<RelationEntityMapConfig>

List of configuration objects with rules for mapping related records with the main entity.

The DetailMapConfig class 

The Namespace Terrasoft.Configuration.

Class used to set up a list of mapping rules of the details and entities connected with them.

Properties 

DetailName string

Detail name (The ensure the uniqueness of detail instance).

SourceEntityName string

Entity name in the database.

Columns Dictionary<string, object>

A dictionary with the names of columns of one entity and compared columns of another entity.

Filters List<EntityFilterMap>

A list of configuration objects with filtration rules for more accurate selections from the database.

RelationEntities List<RelationEntityMapConfig>

List of configuration objects with rules for mapping related records with the main entity.

The RelationEntityMapConfig class 

The Namespace Terrasoft.Configuration.

Class contains rules for mapping connected entities.

Properties 

ParentColumnName string

The name of the parent column, which, when found, will trigger the logic for obtaining and mapping the entity data.

SourceEntityName string

Entity name in the database.

Columns Dictionary<string, object>

A dictionary with the names of columns of one entity and compared columns of another entity.

Filters List<EntityFilterMap>

A list of configuration objects with filtration rules to refine selections from the database.

RelationEntities List<RelationEntityMapConfig>

List of configuration objects with rules for mapping related records with the main entity.

The EntityFilterMap class 

The Namespace Terrasoft.Configuration.

Class filters for database query.

Properties 

ColumnName string

The name of the column, which when found, will start the filtering logic.

Value object

The value to compare to.

EntitySchemaQueryFunction class
Advanced

The Terrasoft.Core.Entities.EntitySchemaQueryFunction class implements the expression function.

The expression function is implemented in the following classes:

  • EntitySchemaQueryFunction – the base class of expression of the entity schema query.
  • EntitySchemaAggregationQueryFunction – implements the aggregate function of the expression.
  • EntitySchemaIsNullQueryFunction – replaces null values with the replacement expression.
  • EntitySchemaCoalesceQueryFunction – returns the first not null expression from the list of arguments.
  • EntitySchemaCaseNotNullQueryFunctionWhenItem – class that describes the condition expression of the CASE SQL operator.
  • EntitySchemaCaseNotNullQueryFunctionWhenItems – collection of condition expressions of the CASE SQL operator.
  • EntitySchemaCaseNotNullQueryFunction – returns one value from the set of possible values depending on the specified conditions.
  • EntitySchemaSystemValueQueryFunction – returns the expression of the system value.
  • EntitySchemaCurrentDateTimeQueryFunction – implements the function for current date and time expression.
  • EntitySchemaBaseCurrentDateQueryFunction – base class of the expression function for the base date.
  • EntitySchemaCurrentDateQueryFunction – implements the function for current date and time expression.
  • EntitySchemaDateToCurrentYearQueryFunction – implements the function that converts the date expression to the same date of the current year.
  • EntitySchemaBaseCurrentDateTimeQueryFunction – implements the function for current date and time expression.
  • EntitySchemaDatePartQueryFunction – implements the function for the datepart of the Date/Time type.
  • EntitySchemaCastQueryFunction – casts the argument expression to the specified data type.
  • EntitySchemaUpperQueryFunction – converts the argument expression characters to uppercase.
  • EntitySchemaTrimQueryFunction – removes whitespaces from both ends of the expression.
  • EntitySchemaLengthQueryFunction – returns the length of the expression.
  • EntitySchemaConcatQueryFunction – returns a string resulting from merging the string arguments of the function.
  • EntitySchemaWindowQueryFunction – implements an SQL window function.

The EntitySchemaQueryFunction class 

Namespace Terrasoft.Core.Entities

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the EntitySchemaQueryFunction class.

Methods 

abstract QueryColumnExpression CreateQueryColumnExpression(DBSecurityEngine dbSecurityEngine)

For the current function, gets the query column expression that is generated taking into account the specified access rights.

Parameters
dbSecurityEngine a Terrasoft.Core.DB.DBSecurityEngine object that defines access rights
abstract DataValueType GetResultDataValueType(DataValueTypeManager dataValueTypeManager)

Gets the data type of the output returned by the function, using the passed-in data type manager.

Parameters
dataValueTypeManager data type manager
abstract bool GetIsSupportepataValueType(DataValueType dataValueType)

Indicates whether the output of the function has the specified data type.

Parameters
dataValueType the type of data
abstract string GetCaption()

Gets the caption of the expression function.

virtual EntitySchemaQueryExpressionCollection GetArguments()

Gets the collection of expressions of function arguments.

void CheckIsSupportepataValueType(DataValueType dataValueType)

Verifies that the output of the function has the specified data type. Otherwise, an exception is thrown.

Parameters
dataValueType the type of data

The EntitySchemaAggregationQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaAggregationQueryFunction class.

Constructors 

EntitySchemaAggregationQueryFunction(EntitySchemaQuery parentQuery)

Initializes the EntitySchemaAggregationQueryFunction instance of the specified aggregate function type for the specified query to the object schema.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaAggregationQueryFunction(AggregationTypeStrict aggregationType, EntitySchemaQuery parentQuery)

Initializes the EntitySchemaAggregationQueryFunction instance of the specified aggregate function type for the specified query to the object schema.

Parameters
aggregationType the type of aggregating function
parentQuery query against the schema of the entity that contains the function
EntitySchemaAggregationQueryFunction(AggregationTypeStrict aggregationType, EntitySchemaQueryExpression expression, EntitySchemaQuery parentQuery)

Initializes a new EntitySchemaAggregationQueryFunction instance for the specified type of aggregate function, expression, and query to the object schema.

Parameters
aggregationType the type of aggregating function
expression a query expression
parentQuery query against the schema of the entity that contains the function
EntitySchemaAggregationQueryFunction(EntitySchemaAggregationQueryFunction source)

Initializes a new EntitySchemaAggregationQueryFunction instance that is a clone of the passed aggregate expression function instance.

Parameters
source instance of the expression aggregate function whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

AggregationType AggregationTypeStrict

The type of aggregating function.

AggregationEvalType AggregationEvalType

The scope of the aggregate function.

Expression EntitySchemaQueryExpression

The expression of the aggregate function argument.

Methods 

override void WriteMetaData(DataWriter writer)

Serializes the aggregate function, using the specified Terrasoft.Common.DataWriter instance.

Parameters
writer the Terrasoft.Common.DataWriter instance used for serialization
override QueryColumnExpression CreateQueryColumnExpression(DBSecurityEngine dbSecurityEngine)

For the aggregate function, gets the query column expression that is generated taking into account the specified access rights.

Parameters
dbSecurityEngine a Terrasoft.Core.DB.DBSecurityEngine object that defines access rights
override EntitySchemaQueryExpressionCollection GetArguments()

Gets the collection of expressions of the aggregate function arguments.

override DataValueType GetResultDataValueType(DataValueTypeManager dataValueTypeManager)

Gets the data type of the output returned by the aggregate function, using the specified data type manager.

Parameters
dataValueTypeManager data type manager
override bool GetIsSupportepataValueType(DataValueType dataValueType)

Indicates whether the output of the aggregate function has the specified data type.

Parameters
dataValueType the type of data
override string GetCaption()

Gets the caption of the expression function.

override object Clone()

Creates a clone of the current EntitySchemaAggregationQueryFunction instance.

EntitySchemaAggregationQueryFunction All()

Sets the To All Values scope for the current aggregate function.

EntitySchemaAggregationQueryFunction Distinct()

Sets the To Unique Values scope for the current aggregate function.

The EntitySchemaIsNullQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaIsNullQueryFunction class.

Constructors 

EntitySchemaIsNullQueryFunction(EntitySchemaQuery parentQuery)

Initializes the EntitySchemaIsNullQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaIsNullQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression checkExpression, EntitySchemaQueryExpression replacementExpression)

Initializes a new EntitySchemaIsNullQueryFunction instance for the specified query to the object schema, validated expression and substitute expression.

Parameters
parentQuery query against the schema of the entity that contains the function
checkExpression the expression to check for being null
replacementExpression the expression returned by the function if checkExpression is null
EntitySchemaIsNullQueryFunction(EntitySchemaIsNullQueryFunction source)

Initializes a new EntitySchemaIsNullQueryFunction instance that is a clone of the passed expression function.

Parameters
source an instance of the EntitySchemaIsNullQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

CheckExpression EntitySchemaQueryExpression

Expression of the function argument to check for being equal to the null value.

ReplacementExpression EntitySchemaQueryExpression

Expression of the function argument that is returned if the check expression is equal to null.

Methods 

override void WriteMetaData(DataWriter writer)

Serializes the expression function, using the passed DataWriter instance.

Parameters
writer the DataWriter instance used for serializing the expression function
override QueryColumnExpression CreateQueryColumnExpression(DBSecurityEngine dbSecurityEngine)

For the current function, gets the query column expression that is generated taking into account the specified access rights.

Parameters
dbSecurityEngine a Terrasoft.Core.DB.DBSecurityEngine object that defines the access rights
override EntitySchemaQueryExpressionCollection GetArguments()

Gets the collection of expressions of function arguments.

override DataValueType GetResultDataValueType(DataValueTypeManager dataValueTypeManager)

Gets the data type of the output returned by the function, using the passed-in data type manager.

Parameters
dataValueTypeManager data type manager

The EntitySchemaCoalesceQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCoalesceQueryFunction class.

Constructors 

EntitySchemaCoalesceQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaCoalesceQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaCoalesceQueryFunction(EntitySchemaCoalesceQueryFunction source)

Initializes a new EntitySchemaCoalesceQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCoalesceQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expressions EntitySchemaQueryExpressionCollection

Collection of expressions of function arguments.

HasExpressions bool

Indicates whether at least one item exists in the collection of expressions of the function arguments.

Methods 

override bool GetIsSupportepataValueType(DataValueType dataValueType)

Indicates whether the output of the function has the specified data type.

Parameters
dataValueType the type of data

The EntitySchemaCaseNotNullQueryFunctionWhenItem class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the EntitySchemaCaseNotNullQueryFunctionWhenItem class.

Constructors 

EntitySchemaCaseNotNullQueryFunctionWhenItem()

Initializes a new instance of the EntitySchemaCaseNotNullQueryFunctionWhenItem class.

EntitySchemaCaseNotNullQueryFunctionWhenItem(EntitySchemaQueryExpression whenExpression, EntitySchemaQueryExpression thenExpression)

Initializes the EntitySchemaCaseNotNullQueryFunctionWhenItem instance for the specified expressions of the WHEN and THEN clauses.

Parameters
whenExpression expression of the WHEN condition clause
thenExpression expression of the THEN condition clause
EntitySchemaCaseNotNullQueryFunctionWhenItem(EntitySchemaCaseNotNullQueryFunctionWhenItem source)

Initializes the EntitySchemaCaseNotNullQueryFunctionWhenItem instance that is a clone of the passed function.

Parameters
source the EntitySchemaCaseNotNullQueryFunctionWhenItem whose clone is being created

Properties 

WhenExpression EntitySchemaQueryExpression

Expression of the WHEN clause.

ThenExpression EntitySchemaQueryExpression

Expression of the THEN clause.

The EntitySchemaCaseNotNullQueryFunctionWhenItems class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the EntitySchemaCaseNotNullQueryFunctionWhenItems class.

Constructors 

EntitySchemaCaseNotNullQueryFunctionWhenItems()

Initializes an EntitySchemaCaseNotNullQueryFunctionWhenItems instance.

EntitySchemaCaseNotNullQueryFunctionWhenItems(EntitySchemaCaseNotNullQueryFunctionWhenItems source)

Initializes a new EntitySchemaCaseNotNullQueryFunctionWhenItems instance that is a clone of the passed collection of conditions.

Parameters
source the collection of conditions whose clone is being created

The EntitySchemaCaseNotNullQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCaseNotNullQueryFunction class.

Constructors 

EntitySchemaCaseNotNullQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaCaseNotNullQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaCaseNotNullQueryFunction(EntitySchemaCaseNotNullQueryFunction source)

Initializes a new EntitySchemaCaseNotNullQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCaseNotNullQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

WhenItems EntitySchemaCaseNotNullQueryFunctionWhenItems

Collection of conditions of the expression function.

HasWhenItems bool

Indicates whether the function has at least one condition.

ElseExpression EntitySchemaQueryExpression

Expression of the ELSE clause.

Methods 

void SpecifyQueryAlias(string queryAlias)

For the current expression function, defines the specified alias in the resulting SQL query.

Parameters
queryAlias alias to define for the current function

The EntitySchemaSystemValueQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaSystemValueQueryFunction class.

Properties 

QueryAlias string

The alias of the function in the SQL query.

SystemValueName string

Name of the system value.

The EntitySchemaBaseCurrentDateQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaBaseCurrentDateQueryFunction class.

Properties 

SystemValueName string

Name of the system value.

Offset int

The offset.

The EntitySchemaDateToCurrentYearQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaDateToCurrentYearQueryFunction class.

Constructors 

EntitySchemaDateToCurrentYearQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaDateToCurrentYearQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaDateToCurrentYearQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression expression)

Initializes the new EntitySchemaDateToCurrentYearQueryFunction instance for the specified entity schema query and passed date expression.

Parameters
parentQuery query against the schema of the entity that contains the function
expression a query expression
EntitySchemaDateToCurrentYearQueryFunction(EntitySchemaDateToCurrentYearQueryFunction source)

Initializes a new EntitySchemaDateToCurrentYearQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaDateToCurrentYearQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expression EntitySchemaQueryExpression

The expression of the function arguments.

The EntitySchemaCurrentTimeQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCurrentTimeQueryFunction class.

Constructors 

EntitySchemaCurrentTimeQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaCurrentTimeQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaCurrentTimeQueryFunction(EntitySchemaCurrentTimeQueryFunction source)

Initializes a new EntitySchemaCurrentTimeQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCurrentTimeQueryFunction whose clone is being created

Properties 

SystemValueName string

Name of the system value.

The EntitySchemaCurrentUserQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCurrentUserQueryFunction class.

Constructors 

EntitySchemaCurrentUserQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaCurrentUserQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaCurrentUserQueryFunction(EntitySchemaCurrentUserQueryFunction source)

Initializes a new EntitySchemaCurrentUserQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCurrentUserQueryFunction whose clone is being created

Properties 

SystemValueName string

Name of the system value.

The EntitySchemaCurrentUserAccountQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCurrentUserAccountQueryFunction class.

Constructors 

EntitySchemaCurrentUserAccountQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaCurrentUserAccountQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaCurrentUserAccountQueryFunction(EntitySchemaCurrentUserAccountQueryFunction source))

Initializes a new EntitySchemaCurrentUserAccountQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCurrentUserAccountQueryFunction whose clone is being created

Properties 

SystemValueName string

Name of the system value.

The EntitySchemaUpperQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaUpperQueryFunction class.

Constructors 

EntitySchemaUpperQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaUpperQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaUpperQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression expression)

Initializes the new EntitySchemaUpperQueryFunction instance for the specified entity schema query and passed date expression.

Parameters
parentQuery query against the schema of the entity that contains the function
expression a query expression
EntitySchemaUpperQueryFunction(EntitySchemaUpperQueryFunction source)

Initializes a new EntitySchemaUpperQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaUpperQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expression EntitySchemaQueryExpression

The expression of the function arguments.

The EntitySchemaTrimQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaTrimQueryFunction class.

Constructors 

EntitySchemaTrimQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaTrimQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaTrimQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression expression)

Initializes the new EntitySchemaTrimQueryFunction instance for the specified entity schema query and passed date expression.

Parameters
parentQuery query against the schema of the entity that contains the function
expression a query expression
EntitySchemaTrimQueryFunction(EntitySchemaTrimQueryFunction source)

Initializes a new EntitySchemaTrimQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaTrimQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expression EntitySchemaQueryExpression

The expression of the function arguments.

The EntitySchemaLengthQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaLengthQueryFunction class.

Constructors 

EntitySchemaLengthQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaLengthQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaLengthQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression expression)

Initializes the new EntitySchemaLengthQueryFunction instance for the specified entity schema query and passed date expression.

Parameters
parentQuery query against the schema of the entity that contains the function
expression a query expression
EntitySchemaLengthQueryFunction(EntitySchemaLengthQueryFunction source)

Initializes a new EntitySchemaLengthQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaLengthQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expression EntitySchemaQueryExpression

The expression of the function arguments.

The EntitySchemaCastQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaCastQueryFunction class.

Constructors 

EntitySchemaCastQueryFunction(EntitySchemaQuery parentQuery, DBDataValueType castType)

Initializes a new EntitySchemaCastQueryFunction instance for the specified query to the schema of the object with the specified target data type.

Parameters
parentQuery query against the schema of the entity that contains the function
castType the target data type
EntitySchemaCastQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression expression, DBDataValueType castType)

Initializes a new EntitySchemaCastQueryFunction instance with the specified expression and target data type.

Parameters
parentQuery query against the schema of the entity that contains the function
expression a query expression
castType the target data type
EntitySchemaCastQueryFunction(EntitySchemaCastQueryFunction source)

Initializes a new EntitySchemaCastQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaCastQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expression EntitySchemaQueryExpression

The expression of the function argument.

CastType DBDataValueType

The target data type.

The EntitySchemaConcatQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaConcatQueryFunction class.

Constructors 

EntitySchemaConcatQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaConcatQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaConcatQueryFunction(EntitySchemaQuery parentQuery, EntitySchemaQueryExpression[] expressions)

Initializes a new EntitySchemaConcatQueryFunction instance for the specified array of expressions and entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
expressions array of expressions
EntitySchemaConcatQueryFunction(EntitySchemaConcatQueryFunction source)

Initializes a new EntitySchemaConcatQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaConcatQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

Expressions EntitySchemaQueryExpressionCollection

Collection of expressions of function arguments.

HasExpressions bool

Indicates whether at least one item exists in the collection of expressions of the function arguments.

The EntitySchemaWindowQueryFunction class 

Namespace Terrasoft.Core.Entities.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaWindowQueryFunction class.

Constructors 

EntitySchemaWindowQueryFunction(EntitySchemaQuery parentQuery)

Initializes the new EntitySchemaWindowQueryFunction instance for the specified entity schema query.

Parameters
parentQuery query against the schema of the entity that contains the function
EntitySchemaWindowQueryFunction(EntitySchemaQueryExpression function, EntitySchemaQuery esq)

Initializes the new EntitySchemaWindowQueryFunction instance for the specified entity schema query.

Parameters
function nested query function
esq the expression of the entity schema query
EntitySchemaWindowQueryFunction(EntitySchemaQueryExpression function, EntitySchemaQuery esq, EntitySchemaQueryExpression partitionBy = null, EntitySchemaQueryExpression orderBy = null)

Initializes the new EntitySchemaWindowQueryFunction instance for the specified entity schema query.

Parameters
function nested query function
parentQuery query against the schema of the entity that contains the function
partitionBy the expression for splitting the query
orderBy the expression for sorting the query
EntitySchemaWindowQueryFunction(EntitySchemaQueryFunction source)

Initializes a new EntitySchemaWindowQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaQueryFunction whose clone is being created
EntitySchemaWindowQueryFunction(EntitySchemaWindowQueryFunction source)

Initializes a new EntitySchemaWindowQueryFunction instance that is a clone of the passed function.

Parameters
source the EntitySchemaWindowQueryFunction whose clone is being created

Properties 

QueryAlias string

The alias of the function in the SQL query.

InnerFunction EntitySchemaQueryExpression

The function to apply.

PartitionByExpression EntitySchemaQueryExpression

Split by expression.

OrderByExpression EntitySchemaQueryExpression

Sort by expression.

QueryFunction class
Advanced

The Terrasoft.Core.DB.QueryFunction class implements the expression function.

The expression function is implemented in the following classes:

  • QueryFunction – a base class of the expression function.
  • AggregationQueryFunction – implements the aggregate function of the expression.
  • IsNullQueryFunction – replaces null values with the replacement experession.
  • CreateGuidQueryFunction – implements the function for generating a new identifier.
  • CurrentDateTimeQueryFunction – implements the function for current date and time expression.
  • CoalesceQueryFunction – returns the first not null expression from the list of arguments.
  • DatePartQueryFunction – implements the function for the date part of the Date/Time type.
  • DateAddQueryFunction – implements the function for the date expression calculated by adding the specified period to the specified date.
  • DateDiffQueryFunction – implements the function for calculating the delta between two specified dates.
  • CastQueryFunction – casts the argument expression to the specified data type.
  • UpperQueryFunction – converts the argument expression characters to uppercase.
  • CustomQueryFunction – implements a custom function.
  • DataLengthQueryFunction – calculates the number of bytes used to represent the expression.
  • TrimQueryFunction – removes whitespaces from both ends of the expression.
  • LengthQueryFunction – returns the length of the expression.
  • SubstringQueryFunction – returns a part of the string.
  • ConcatQueryFunction – returns a string resulting from merging the string arguments of the function.
  • WindowQueryFunction – implements an SQL window function.

The QueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the QueryFunction class.

Methods 

static QueryColumnExpression Negate(QueryFunction operand)

Gets the expression that negates the value of the passed-in function.

Parameters
operand the function of the expression
static QueryColumnExpression operator -(QueryFunction operand)

Overloads the operator of negating the passed-in expression function.

Parameters
operand the function of the expression
static QueryColumnExpression Add(QueryFunction leftOperand, QueryFunction rightOperand)

Gets the arithmetic addition expression of the passed-in expression functions.

Parameters
leftOperand the left operand in the addition operation
rightOperand the right operand in the addition operation
static QueryColumnExpression operator +(QueryFunction leftOperand, QueryFunction rightOperand)

Overloads the operator of adding two expression functions.

Parameters
leftOperand the left operand in the addition operation
rightOperand the right operand in the addition operation
static QueryColumnExpression Subtract(QueryFunction leftOperand, QueryFunction rightOperand)

Returns the expression of subtraction of the passed-in right expression function from the passed-in left expression function.

Parameters
leftOperand the left operand in the subtraction operation
rightOperand the right operand in the subtraction operation
static QueryColumnExpression operator -(QueryFunction leftOperand, QueryFunction rightOperand)

Reloads the operator of subtraction of the passed-in right expression function from the passed-in left expression function.

Parameters
leftOperand the left operand in the subtraction operation
rightOperand the right operand in the subtraction operation
static QueryColumnExpression Multiply(QueryFunction leftOperand, QueryFunction rightOperand)

Gets the expression of multiplying the passed-in expression functions.

Parameters
leftOperand the left operand in the multiplication operation
rightOperand the right operand in the multiplication operation
static QueryColumnExpression operator *(QueryFunction leftOperand, QueryFunction rightOperand)

Overloads the operator of multiplying two expression functions.

Parameters
leftOperand the left operand in the multiplication operation
rightOperand the right operand in the multiplication operation
static QueryColumnExpression Divide(QueryFunction leftOperand, QueryFunction rightOperand)

Returns the expression of dividing the passed-in left expression function by the passed-in right expression function.

Parameters
leftOperand the left operand in the division operation
rightOperand the right operand in the division operation
static QueryColumnExpression operator /(QueryFunction leftOperand, QueryFunction rightOperand)

Overloads the operator of dividing two expression functions.

Parameters
leftOperand the left operand in the division operation
rightOperand the right operand in the division operation
abstract object Clone()

Creates a copy of the current QueryFunction instance.

abstract void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the passed StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
virtual void AddUsingParameters(QueryParameterCollection resultParameters)

Adds the passed-in collection of parameters in the function arguments.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
QueryColumnExpressionCollection GetQueryColumnExpressions()

Gets the collection of the query column expression for the current query function.

QueryColumnExpression GetQueryColumnExpression()

Gets the query column expression for the current query function.

The AggregationQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the AggregationQueryFunction class.

Constructors 

AggregationQueryFunction()

Initializes a new instance of the AggregationQueryFunction class.

AggregationQueryFunction(AggregationTypeStrict aggregationType, QueryColumnExpression expression)

Initializes a new AggregationQueryFunction instance with the specified type of the aggregate function for the specified column expression.

Parameters
aggregationType the type of aggregating function
expression the column expression to which the aggregate function is applied
AggregationQueryFunction(AggregationTypeStrict aggregationType, IQueryColumnExpressionConvertible expression)

Initializes a new AggregationQueryFunction instance with the specified type of the aggregate function for the specified column expression.

Parameters
aggregationType the type of aggregating function
expression the column expression to which the aggregate function is applied
AggregationQueryFunction(AggregationQueryFunction source)

Initializes a new AggregationQueryFunction instance that is a clone of the passed aggregate function of the expression.

Parameters
source the AggregationQueryFunction expression aggregate function whose clone is being created

Properties 

AggregationType AggregationTypeStrict

The type of the aggregate function.

AggregationEvalType AggregationEvalType

The scope of the aggregate function..

Expression QueryColumnExpression

The expression of the function argument.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds the passed-in collection of parameters in the function arguments.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current AggregationQueryFunction instance.

AggregationQueryFunction All()

Sets the [To All Values] scope for the current aggregate function.

AggregationQueryFunction Distinct()

Sets the [To Unique Values] scope for the current aggregate function.

The IsNullQueryFunction class 

Namespace Terrasoft.Core.DB.

Note.Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the IsNullQueryFunction class.

Constructors 

IsNullQueryFunction()

Initializes a new IsNullQueryFunction instance.

IsNullQueryFunction(QueryColumnExpression checkExpression, QueryColumnExpression replacementExpression)
IsNullQueryFunction(IQueryColumnExpressionConvertible checkExpression, IQueryColumnExpressionConvertible replacementExpression)

Initializes a new IsNullQueryFunction instance for the specified validated expression and substitute expression.

Parameters
checkExpression the expression to check the null value
replacementExpression the expression returned by the function if checkExpression is null
IsNullQueryFunction(IsNullQueryFunction source)

Initializes a new IsNullQueryFunction instance that is a clone of the passed expression function.

Parameters
source the IsNullQueryFunction expression aggregate function whose clone is being created

Properties 

CheckExpression QueryColumnExpression

The expression of the function argument to check the null value.

ReplacementExpression QueryColumnExpression

The expression of the function argument that is returned if the check expression is equal to null.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds the passed-in collection of parameters in the function arguments.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current IsNullQueryFunction instance.

The CreateGuidQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the CreateGuidQueryFunction class.

Constructors 

CreateGuidQueryFunction()

Initializes a new CreateGuidQueryFunction instance.

CreateGuidQueryFunction(CreateGuidQueryFunction source)

Initializes a new CreateGuidQueryFunction instance. The instance is a clone of the passed function.

Parameters
source the CreateGuidQueryFunction whose clone is being created

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override object Clone()

Clones the current CreateGuidQueryFunction instance.

The CurrentDateTimeQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, parent classes, and implemented interfaces of the CurrentDateTimeQueryFunction class.

Constructors 

CurrentDateTimeQueryFunction()

Initializes a new CurrentDateTimeQueryFunction instance.

CurrentDateTimeQueryFunction(CurrentDateTimeQueryFunction source)

Initializes a new CurrentDateTimeQueryFunction instance. The instance is a clone of the passed function.

Parameters
source the CurrentDateTimeQueryFunction whose clone is being created

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override object Clone()

Creates a clone of the current CurrentDateTimeQueryFunction instance.

The CoalesceQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the CoalesceQueryFunction class.

Constructors 

CoalesceQueryFunction()

Initializes a new CoalesceQueryFunction instance.

CoalesceQueryFunction(CoalesceQueryFunction source)

Initializes a new CoalesceQueryFunction instance. The instance is a clone of the passed function.

Parameters
source the CoalesceQueryFunction whose clone is being created
CoalesceQueryFunction(QueryColumnExpressionCollection expressions)

Initializes a new CoalesceQueryFunction instance for the passed collection of column expressions.

Parameters
expressions a collection of query column expressions
CoalesceQueryFunction(QueryColumnExpression[] expressions)
CoalesceQueryFunction(IQueryColumnExpressionConvertible[] expressions)

Initializes a new CoalesceQueryFunction instance for the passed array of column expressions.

Parameters
expressions an array of expressions of query columns

Properties 

Expressions QueryColumnExpressionCollection

Collection of expressions of function arguments.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override object Clone()

Creates a clone of the current CoalesceQueryFunction instance.

override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments

The DatePartQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the DatePartQueryFunction class.

Constructors 

DatePartQueryFunction()

Initializes a new instance of the DatePartQueryFunction class.

DatePartQueryFunction(DatePartQueryFunctionInterval interval, QueryColumnExpression expression)
DatePartQueryFunction(DatePartQueryFunctionInterval interval, IQueryColumnExpressionConvertible expression)

Initializes a new DatePartQueryFunction instance with the specified expression of the column of the Date/Time type and the specified date part.

Parameters
interval the date part
expression the expression of the column of the Date/Time type
DatePartQueryFunction(DatePartQueryFunction source)

Initializes a new DatePartQueryFunction instance that is a clone of the passed function.

Parameters
source the DatePartQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

Interval DatePartQueryFunctionInterval

The date part returned by the function.

UseUtcOffset bool

Using the coordinated universal time (UTC) offset from the specified local time.

UtcOffset int?

Using the coordinated universal time (UTC).

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current DatePartQueryFunction instance.

The DateAddQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the DatePartQueryFunction class.

Constructors 

DateAddQueryFunction()

Initializes a new instance of the DateAddQueryFunction class.

DateAddQueryFunction(DatePartQueryFunctionInterval interval, int number, QueryColumnExpression expression)
DateAddQueryFunction(DatePartQueryFunctionInterval interval, IQueryColumnExpressionConvertible numberExpression, IQueryColumnExpressionConvertible expression)
DateAddQueryFunction(DatePartQueryFunctionInterval interval, int number, IQueryColumnExpressionConvertible expression)

Initializes the DateAddQueryFunction instance with the specified parameters.

Parameters
interval the date part to which the time span is added
number the value added to interval
expression the expression of the column with the original date
DateAddQueryFunction(DateAddQueryFunction source)

Initializes the DateAddQueryFunction instance that is a clone of the passed function.

Parameters
source an instance of the CoalesceQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

Expression of the column containing the original date.

Interval DatePartQueryFunctionInterval

Datepart to which the time span is added.

Number int

The time span to add.

NumberExpression QueryColumnExpression

Expression containing the time period to be added.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current DateAddQueryFunction instance.

The DateDiffQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the DatePartQueryFunction class.

Constructors 

DateDiffQueryFunction(DateDiffQueryFunctionInterval interval, QueryColumnExpression startDateExpression, QueryColumnExpression endDateExpression)
DateDiffQueryFunction(DateDiffQueryFunctionInterval interval, IQueryColumnExpressionConvertible startDateExpression, IQueryColumnExpressionConvertible endDateExpression)

Initializes the DateDiffQueryFunction instance with the specified parameters.

Parameters
interval the unit for measuring the date difference
startDateExpression an expression of the column with the start date
endDateExpression an expression of the column with the end date
DateDiffQueryFunction(DateDiffQueryFunction source)

Initializes the DateDiffQueryFunction instance that is a clone of the passed function.

Parameters
source an instance of the CoalesceQueryFunction whose clone is being created

Properties 

StartDateExpression QueryColumnExpression

An expression of the column containing the start date.

EndDateExpression QueryColumnExpression

An expression of the column containing the end date.

Interval DateDiffQueryFunctionInterval

Unit of measurement of the date difference returned by function.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current DateDiffQueryFunction instance.

The CastQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the CastQueryFunction class.

Constructors 

CastQueryFunction(QueryColumnExpression expression, DBDataValueType castType)
CastQueryFunction(IQueryColumnExpressionConvertible expression, DBDataValueType castType)

Initializes a new CastQueryFunction instance with the specified column expression and target data type.

Parameters
expression a query column expression
castType the target data type
CastQueryFunction(CastQueryFunction source)

Initializes a new CastQueryFunction instance that is a clone of the passed function.

Parameters
source the CastQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

CastType DBDataValueType

The target data type.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current CastQueryFunction instance.

The UpperQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the UpperQueryFunction class.

Constructors 

UpperQueryFunction()

Initializes a new instance of the UpperQueryFunction class.

UpperQueryFunction(QueryColumnExpression expression)
UpperQueryFunction(IQueryColumnExpressionConvertible expression)

Initializes a new UpperQueryFunction instance for the specified column expression.

Parameters
expression a query column expression
UpperQueryFunction(UpperQueryFunction source)

Initializes a new UpperQueryFunction instance that is a clone of the passed function.

Parameters
source the UpperQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current UpperQueryFunction instance.

The CustomQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the CastQueryFunction class.

Constructors 

CustomQueryFunction()

Initializes a new instance of the CustomQueryFunction class.

CustomQueryFunction(string functionName, QueryColumnExpressionCollection expressions)

Initializes a new CustomQueryFunction instance for the specified function and passed collection of column expressions.

Parameters
functionName the function name
expressions a collection of query column expressions
CustomQueryFunction(string functionName, QueryColumnExpression[] expressions)
CustomQueryFunction(string functionName, IQueryColumnExpressionConvertible[] expressions)

Initializes a new CustomQueryFunction instance for the specified function and passed array of column expressions.

Parameters
functionName the function name
expressions an array of expressions of query columns
CustomQueryFunction(CustomQueryFunction source)

Initializes a new CustomQueryFunction instance that is a clone of the passed function.

Parameters
source the CustomQueryFunction whose clone is being created

Properties 

Expressions QueryColumnExpressionCollection

Collection of expressions of function arguments.

FunctionName string

The name of the function.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

Adds specified parameters to a collection.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current CustomQueryFunction instance.

The DataLengthQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the DataLengthQueryFunction class.

Constructors 

DataLengthQueryFunction()

Initializes a new instance of the DataLengthQueryFunction class.

DataLengthQueryFunction(QueryColumnExpression expression)

Initializes a new DataLengthQueryFunction instance for the specified column expression.

Parameters
expression a query column expression
DataLengthQueryFunction(IQueryColumnExpressionConvertible columnNameExpression)

Initializes a new DataLengthQueryFunction instance for the specified column expression.

Parameters
columnNameExpression a query column expression
DataLengthQueryFunction(DataLengthQueryFunction source)

Initializes a new DataLengthQueryFunction instance that is a clone of the passed function.

Parameters
source the DataLengthQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current DataLengthQueryFunction instance.

The TrimQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the CastQueryFunction class.

Constructors 

TrimQueryFunction(QueryColumnExpression expression)
TrimQueryFunction(IQueryColumnExpressionConvertible expression)

Initializes a new TrimQueryFunction instance for the specified column expression.

Parameters
expression a query column expression
TrimQueryFunction(TrimQueryFunction source)

Initializes a new TrimQueryFunction instance that is a clone of the passed function.

Parameters
source the TrimQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current TrimQueryFunction instance.

The LengthQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the DataLengthQueryFunction class.

Constructors 

LengthQueryFunction()

Initializes a new instance of the LengthQueryFunction class.

LengthQueryFunction(QueryColumnExpression expression)
LengthQueryFunction(IQueryColumnExpressionConvertible expression)

Initializes a new LengthQueryFunction instance for the specified column expression.

Parameters
expression a query column expression
LengthQueryFunction(LengthQueryFunction source)

Initializes a new LengthQueryFunction instance that is a clone of the passed function.

Parameters
source the LengthQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current LengthQueryFunction instance.

The SubstringQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the SubstringQueryFunction class.

Constructors 

SubstringQueryFunction(QueryColumnExpression expression, int start, int length)
SubstringQueryFunction(IQueryColumnExpressionConvertible expression, int start, int length)

Initializes a new SubstringQueryFunction instance for the specified column expression, starting position and the length of the substring.

Parameters
expression a query column expression
start the start index of the substring
length the length of the substring
SubstringQueryFunction(SubstringQueryFunction source)

Initializes a new SubstringQueryFunction instance that is a clone of the passed function.

Parameters
source the SubstringQueryFunction whose clone is being created

Properties 

Expression QueryColumnExpression

The expression of the function argument.

StartExpression QueryColumnExpression

The start index of the substring.

LengthExpression QueryColumnExpression

The length of the substring.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current SubstringQueryFunction instance.

The ConcatQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the ConcatQueryFunction class.

Constructors 

ConcatQueryFunction(QueryColumnExpressionCollection expressions)

Initializes a new ConcatQueryFunction instance for the passed expression collection.

Parameters
expressions a collection of query column expressions
ConcatQueryFunction(ConcatQueryFunction source)

Initializes a new ConcatQueryFunction instance that is a clone of the passed function.

Parameters
source the ConcatQueryFunction whose clone is being created

Properties 

Expressions QueryColumnExpressionCollection

Collection of expressions of function arguments.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current ConcatQueryFunction instance.

The WindowQueryFunction class 

Namespace Terrasoft.Core.DB.

Note. Use the “.NET class libraries of platform core” documentation to access the full list of the methods, properties, parent classes, and implemented interfaces of the WindowQueryFunction class.

Constructors 

WindowQueryFunction(QueryFunction innerFunction)

Implements an SQL window function.

Parameters
innerFunction nested function
WindowQueryFunction(QueryFunction innerFunction, QueryColumnExpression partitionByExpression = null, QueryColumnExpression orderByExpression = null) : this(innerFunction)

Implements an SQL window function.

Parameters
innerFunction nested function
partitionByExpression expression for separating the query
orderByExpression expression for sorting the query
WindowQueryFunction(WindowQueryFunction source) : this( source.InnerFunction, source.PartitionByExpression, source.OrderByExpression)

Initializes a new WindowQueryFunction instance that is a clone of the passed function.

Parameters
source the WindowQueryFunction whose clone is being created

Properties 

InnerFunction QueryFunction

The function to apply.

PartitionByExpression QueryColumnExpression

Split by expression.

OrderByExpression QueryColumnExpression

Sort by expression.

Methods 

override void BuildSqlText(StringBuilder sb, DBEngine dbEngine)

Generates the query text, using the specified StringBuilder instance and the DBEngine query builder.

Parameters
sb StringBuilder instance used to create query text
dbEngine the instance of the database query builder
override void AddUsingParameters(QueryParameterCollection resultParameters)

In the function arguments adds the passed-in collection of parameters.

Parameters
resultParameters the collection of query parameters that are added in the function arguments
override object Clone()

Creates a clone of the current WindowQueryFunction instance.