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.
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 (see "Recommendations on use of different types of repositories”) Use the EntitySchemaQuery class to access additional permission control options and bpm’online cache repository operation.
The Terrasoft.Core.DB.Select class
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
- 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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
Query GroupBy(string sourceColumnAlias) Query GroupBy(string sourceAlias, string sourceColumnAlias) Query GroupBy(QueryColumnExpression columnExpression) |
Executes grouping of query results. Returns the query instance. Parameters:
|
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:
|
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:
|
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:
|
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 sel = new Select(UserConnection) .Column(Column.Asterisk()) .From("Contact"); result = sel.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 sel = new Select(UserConnection) .Top(top) .Column(Column.Asterisk()) .From(tableName); using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { using (IDataReader dataReader = sel.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 sel = 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 = sel.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 sel = 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 = sel.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 sel = 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 = sel.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 sel = 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 = sel.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 sel = 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.Const(count)) .OrderByDesc("CitiesCount") as Select; using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { using (IDataReader dataReader = sel.ExecuteReader(dbExecutor)) { result = CreateJson(dataReader); } } return result; }
Example 8
Receive the contact identifier by its name.
public string SelectCountryIdByCityName(string CityName) { var result = ""; var sel = new Select(UserConnection) .Column("CountryId") .From("City") .Where("Name").IsEqual(Column.Parameter(CityName)) as Select; result = sel.ExecuteScalar<Guid>().ToString(); return result; }