The Select class

Introduction

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.

The “Terrasoft.Core.DB.Select“ class

Constructors

public Select((UserConnection userConnection))

Creates a class instance with the specified UserConnection parameter.

---

public Select((UserConnection userConnection, CancellationToken cancellationToken))

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

---

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

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 (see “Separate query pool”).

---

Methods

Table 2. Primary methods of the Select class

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

---

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

Inner

Inner join.

---

LeftOuter

Left outer join.

---

RightOuter

Right outer join.

---

FullOuter

Full join.

---

Cross

Cross join.

See also

• Retrieving data from the database