Documentation

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

Retrieving information from database. The Select class

Glossary Item Box

Introduction

The Terrasoft.Core.DB.Select class is used to build queries for 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 an instance implementing the System.Data.IDataReader interface or as a scalar value of the needed type.

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

The Terrasoft.Core.DB.Select class

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

  • public Select(UserConnection userConnection) – creates a class instance with the specified UserConnection.
  • public Select(UserConnection userConnection, CancellationToken cancellationToken) – creates a class instance with the specified UserConnection and the token for canceling execution of the managed flow.
  • public Select(Select source) – creates a class instance that is a clone of the instance passed as an argument.

Properties.

Table 1. Primary properties of the Select class

Property Type Description
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 only unique records.
Columns: Terrasoft.Core.DB.QueryColumnExpressionCollection Collection of query column expressions.
OffsetFetchPaging bool Determines whether the per-page returning of query result is available.
RowsOffset int Number of rows to skip when returning the query result.
QueryKind Terrasoft.Common.QueryKind The query type (see “Separate query pool”).

Methods

Table 2. Primary methods of the Select class

Methods Description
string GetSqlText() Returns the SQL text of the current query.
void BuildSqlText(StringBuilder sb) Generates the query text via the StringBuilder instance.
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 provides a description of 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 result set first record.
TResult ExecuteScalar<TResult>(DBExecutor dbExecutor) Executes the query using the DBExecutor instance. Returns the typed first column of the result set first record.
Select Distinct() Adds the DISTINCT key word 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 (see table 1). 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.

Parameters:

  • schemaName – 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 (see table 3).
  • schemaName – the joined schema name;
  • subSelect – the joined subquery for data selection;
  • subSelectQuery – the joined subquery;
  • sourceExpression – the joined expression.

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

Table 3. Types of joins (the “Terrasoft.Core.DB.JoinType” enumeration)

Join type Description
Inner Inner join.
LeftOuter Left outer join.
RightOuter Right outer join.
FullOuter Full join.
Cross Cross join.

Use cases

You can download the package with the configuration web serice implementing the below described cases using the following link.

The query result handler method used in all the cases:

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);
}

Example 1

Receive the SQL-query text.

public string GetSqlTextExample()
{
    var result = "";
    var select = new Select(UserConnection)
            .Column(Column.Asterisk())
        .From("Contact");
    result = select.GetSqlText();
    return result;
}

Example 2

Select the necessary number of records from the needed table (object schema).

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;
}

Example 3

Select the identifier, the contacts’ names and birth dates (whose birth dates are later than the needed year).

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;
}

Example 4

Select the identifier, the contacts’ names and birth dates (whose birth dates are later than the specified year and those who have the account names populated).

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;
}

Example 5

Select the identifier and all contacts’ names having joined them to the identifiers and names of the corresponding accounts.

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;
}

Example 6

Select the identifiers and names of the the contacts that are primary for the accounts.

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;
}

Example 7

Select the countries and the number of cities in a country, if such number is greater than the specified number.

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;
}

Example 8

Receive the contact identifier by its name.

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;
}
© Creatio 2002-2020.