Select class

Level: advanced

Terrasoft.Core.DB namespace.

The Terrasoft.Core.DB.Select class builds queries to select records in a Creatio database table. As a result of creating and configuring an instance of this class, Creatio will build a SELECT SQL query. You can add columns, filters, and restriction conditions to the query. The query results are returned as an instance that implements the System.Data.IDataReader interface or as a scalar value of the needed type.

Important

When working with the Select class, Creatio does not apply the current user permissions. All Creatio database records are available. Creatio does not use the data in the cache repository as well. If you need additional Creatio permissions and cache repository management options, use the EntitySchemaQuery class.

note

View the entire list of methods and properties of the Select class, its parent classes, as well as the interfaces it implements, in the .NET class library.

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

Properties

UserConnection Terrasoft.Core.UserConnection

The user connection that the query uses.

RowCount int

The number of records to return after the execution.

Parameters Terrasoft.Core.DB.QueryParameterCollection

The query parameter collection.

HasParameters bool

Whether the query has parameters.

BuildParametersAsValue bool

Whether to add the query parameters to the query text as values.

Joins Terrasoft.Core.DB.JoinCollection

The collection of Join query expressions.

HasJoins bool

Whether the query has Join expressions.

Condition Terrasoft.Core.DB.QueryCondition

The Where condition of the query.

HasCondition bool

Whether the query has the Where expression.

HavingCondition Terrasoft.Core.DB.QueryCondition

The Having condition of the query.

HasHavingCondition bool

Whether the query has the Having expression.

OrderByItems Terrasoft.Core.DB.OrderByItemCollection

The collection of expressions by which to sort the query results.

HasOrderByItems bool

Whether the query has order expressions.

GroupByItems Terrasoft.Core.DB.QueryColumnExpressionCollection

The collection of expressions by which to group the query results.

HasGroupByItems bool

Whether the query has grouping expressions.

IsDistinct bool

Whether to return only the distinct records.

Columns Terrasoft.Core.DB.QueryColumnExpressionCollection

The collection of query column expressions.

OffsetFetchPaging bool

Whether to paginate the query results.

RowsOffset int

The number of rows to skip when returning the query result.

QueryKind Terrasoft.Common.QueryKind

The query type.

Methods

void ResetCachedSqlText()

Clears the cached query text.

QueryParameterCollection GetUsingParameters()

Returns the parameter collection that the query uses.

void ResetParameters()

Clears the query parameter collection.

QueryParameterCollection InitializeParameters()

Initializes the query parameter collection.

IDataReader ExecuteReader(DBExecutor dbExecutor)

Executes the query using the DBExecutor instance. Returns the object that implements the IDataReader interface.

Parameters

dbExecutor

The DBExecutor instance required to run the query.

IDataReader ExecuteReader(DBExecutor dbExecutor, CommandBehavior behavior)

Executes the query using the DBExecutor instance. Returns the object that implements the IDataReader interface.

Parameters

behavior	Describes the query results and how they affect the database.
dbExecutor	The DBExecutor instance required to run the query.

void ExecuteReader(ExecuteReaderReadMethod readMethod)

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

Parameters

readMethod

The ExecuteReaderReadMethod delegate method.

TResult ExecuteScalar<tresult>()

Executes the query. Returns the typed first column of the first record in the resulting set.

TResult ExecuteScalar<tresult>(DBExecutor dbExecutor)

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

Parameters

dbExecutor

The DBExecutor instance required to run the query.

Select Distinct()

Adds the DISTINCT keyword to the SQL query. Excludes record duplicates from the resulting set. Returns the query instance.

Select Top(int rowCount)

Sets the number of records to return in the resulting set. Also changes the RowCount property value. Returns the query instance.

Parameters

rowCount

The number of first records in the resulting set.

Select As(string alias)

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

Parameters

alias

The query expression alias.

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 column expression collection of the query. Returns the query instance.

Parameters

sourceColumnAlias	The name of the column for which to add the expression.
sourceAlias	The alias of the source from which to add the column expression.
subSelect	The added data selection subquery.
subSelectQuery	The added subquery.
queryCase	The added expression for the Case operator.
queryParameter	The added query parameter.
columnExpression	The expression for whose results to add the condition.

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	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	The join type.
schemaName	The joinable schema name.
subSelect	The joinable data selection subquery.
subSelectQuery	The joinable subquery.
sourceExpression	The joinable expression.

Available 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	The alias of the schema for which to add the condition.
sourceAlias	The alias of the source.
subSelect	The data selection subquery for whose results to add the condition.
subSelectQuery	The subquery for whose results to add the condition.
columnExpression	The expression for whose results to add the condition.
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	The name of the schema for which to add the predicate.
sourceAlias	The alias of the source.
subSelect	The data selection subquery that serves as a predicate.
subSelectQuery	The subquery that serves as a predicate
parameter	The parameter for which to add the predicate.
columnExpression	The expression that serves 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	The name of the schema for which to add the predicate.
sourceAlias	The alias of the source.
subSelect	The data select subquery that serves as a predicate.
subSelectQuery	The subquery that serves as a predicate
parameter	The parameter for which to add the predicate.
columnExpression	The expression that serves 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)

Sorts the query results. Returns the query instance.

Parameters

direction	The sorting order.
sourceColumnAlias	The alias of the column by which to sort the results.
sourceAlias	The alias of the source.
queryFunction	The function whose value serves as the sorting key.
subSelect	The select subquery whose results serve as the sorting key.
subSelectQuery	The subquery whose results serve as the sorting key.
columnExpression	The expression whose results serve as the sorting 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 to sort the results.
sourceAlias	The alias of the source.
subSelect	The select subquery whose results serve as the sorting key.
subSelectQuery	The subquery whose results serve as the sorting key.
columnExpression	The expression whose results serve as the sorting 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 descending order. Returns the query instance.

Parameters

sourceColumnAlias	The alias of the column by which to sort the results.
sourceAlias	The alias of the source.
subSelect	The select subquery whose results serve as the sorting key.
subSelectQuery	The subquery whose results serve as the sorting key.
columnExpression	The expression whose results serve as the sorting key.

Query GroupBy(string sourceColumnAlias)
Query GroupBy(string sourceAlias, string sourceColumnAlias)
Query GroupBy(QueryColumnExpression columnExpression)

Groups the query results. Returns the query instance.

Parameters

sourceColumnAlias	The alias of the column by which to group the results.
sourceAlias	The alias of the source.
columnExpression	The expression whose results serve as the grouping 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 group condition to the current query. Returns the Terrasoft.Core.DB.QueryCondition instance that represents the group condition for the query parameter.

Parameters

sourceColumnAlias	The alias of the column by which to add the group condition.
sourceAlias	The alias of the source.
subSelect	The select subquery for whose results to add the group condition.
subSelectQuery	The subquery for whose results to add the group condition.
parameter	The query parameter to add the group condition.
columnExpression	The expression that serves as a predicate.

Query Union(Select unionSelect)
Query Union(Query unionSelectQuery)

Merges the results of the passed query with the results of the current query. Excludes duplicates from the resulting set.

Parameters

unionSelect	The selection subquery.
unionSelectQuery	The subquery.

Query UnionAll(Select unionSelect)
Query UnionAll(Query unionSelectQuery)

Merges the results of the passed query with the results of the current query. Does not exclude duplicates from the resulting set.

Parameters

unionSelect	The selection subquery.
unionSelectQuery	Subquery.