Документация

Creatio development guide
PDF
Это документация Creatio версии 7.16.0. Мы рекомендуем использовать новую версию документации.

Получение информации из базы данных. Класс Select

Glossary Item Box

Общие сведения

Класс Terrasoft.Core.DB.Select предназначен для построения запросов выборки записей из таблиц базы данных. В результате создания и конфигурирования экземпляра этого класса будет построен запрос в базу данных приложения в виде SQL-выражения SELECT. В запрос можно добавить требуемые колонки, фильтры и условия ограничений. Результаты выполнения запроса возвращаются в виде экземпляра, реализующего интерфейс System.Data.IDataReader, либо скалярного значения требуемого типа.

При работе с классом Select не учитываются права доступа пользователя, использующего текущее соединение. Доступны абсолютно все записи из базы данных приложения. Также не учитываются данные, помещенные в хранилище кэша (см. "Объектная модель хранилищ Creatio"). Если необходимы дополнительные возможности по управлению правами доступа и работе с хранилищем кэша Creatio, следует использовать класс EntitySchemaQuery.

Класс Terrasoft.Core.DB.Select

Полный перечень методов и свойств класса Select, его родительских классов, а также реализуемых им интерфейсов можно найти в документации ".NET библиотеки классов ядра платформы".

Конструкторы

  • public Select(UserConnection userConnection) — создает экземпляр класса с указанным UserConnection.
  • public Select(UserConnection userConnection, CancellationToken cancellationToken) — создает экземпляр класса с указанным UserConnection и токеном отмены выполнения управляемого потока.
  • public Select(Select source) — создает экземпляр класса, являющийся клоном экземпляра, переданного в качестве аргумента.

Свойства

Табл. 1. — Основные свойства класса Select

Свойство Тип Описание
UserConnection Terrasoft.Core.UserConnection Пользовательское подключение, используемое при выполнении запроса.
RowCount int Количество записей, которые вернет запрос после выполнения.
Parameters Terrasoft.Core.DB.QueryParameterCollection Коллекция параметров запроса.
HasParameters bool Определяет наличие параметров у запроса.
BuildParametersAsValue bool Определяет, добавлять ли параметры запроса в текст запроса как значения.
Joins Terrasoft.Core.DB.JoinCollection Коллекция выражений Join в запросе.
HasJoins bool Определяет наличие выражений Join в запросе.
Condition Terrasoft.Core.DB.QueryCondition Условие выражения Where запроса.
HasCondition bool Определяет наличие выражения Where в запросе.
HavingCondition Terrasoft.Core.DB.QueryCondition Условие выражения Having запроса.
HasHavingCondition bool Определяет наличие выражения Having в запросе.
OrderByItems Terrasoft.Core.DB.OrderByItemCollection Коллекция выражений, по которым выполняется сортировка результатов запроса.
HasOrderByItems bool Определяет наличие условий сортировки результатов запроса.
GroupByItems Terrasoft.Core.DB.QueryColumnExpressionCollection Коллекция выражений, по которым выполняется группировка результатов запроса.
HasGroupByItems bool Определяет наличие условий группировки результатов запроса.

IsDistinct

 bool Определяет, должен ли запрос возвращать только уникальные записи.
Columns Terrasoft.Core.DB.QueryColumnExpressionCollection Коллекция выражений колонок запроса.
OffsetFetchPaging bool Определяет возможность постраничного возврата результата запроса.
RowsOffset int Количество строк, которые необходимо пропустить при возврате результата запроса.
QueryKind Terrasoft.Common.QueryKind Тип запроса (см. "Отдельный пул запросов").

Методы

Табл. 2. — Основные методы класса Select

Методы Описание
string GetSqlText() Возвращает SQL-текст запроса.
void BuildSqlText(StringBuilder sb) Формирует текст запроса при помощи экземпляра StringBuilder.
void ResetCachedSqlText() Очищает закэшированный текст запроса.
QueryParameterCollection GetUsingParameters() Возвращает коллекцию параметров, используемых запросом.
void ResetParameters() Очищает коллекцию параметров запроса.

QueryParameterCollection InitializeParameters()

 Инициализирует коллекцию параметров запроса.

IDataReader ExecuteReader(DBExecutor dbExecutor)

 Выполняет запрос, используя экземпляр DBExecutor. Возвращает объект, реализующий интерфейс IDataReader.

IDataReader ExecuteReader(DBExecutor dbExecutor, CommandBehavior behavior)

 Выполняет запрос, используя экземпляр DBExecutor. Возвращает объект, реализующий интерфейс IDataReader. Параметр behavior предоставляет описание результатов запроса и их эффект на базу данных.
void ExecuteReader(ExecuteReaderReadMethod readMethod) Выполняет запрос, вызывая переданный метод делегата ExecuteReaderReadMethod для каждой записи результирующего набора.
TResult ExecuteScalar<TResult>() Выполняет запрос. Возвращает типизированный первый столбец первой записи результирующего набора.
TResult ExecuteScalar<TResult>(DBExecutor dbExecutor) Выполняет запрос, используя экземпляр DBExecutor. Возвращает типизированный первый столбец первой записи результирующего набора.
Select Distinct() Добавляет к SQL-запросу ключевое слово DISTINCT. Исключает дублирование записей в результирующем наборе. Возвращает экземпляр запроса.
Select Top(int rowCount) Устанавливает количество записей, возвращаемых в результирующем наборе. При этом меняется значение свойства RowCount (см. табл. 1). Возвращает экземпляр запроса.
Select As(string 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)

Добавляет выражение, подзапрос или параметр в коллекцию выражений колонок запроса. Возвращает экземпляр запроса.

Параметры:

  • sourceColumnAlias — псевдоним колонки, для которой добавляется выражение;
  • sourceAlias — псевдоним источника, из которого добавляется выражение колонки;
  • subSelect — добавляемый подзапрос выборки данных;
  • subSelectQuery — добавляемый подзапрос;
  • queryCase — добавляемое выражение для оператора Case;
  • queryParameter — добавляемый параметр запроса;
  • columnExpression — выражение, для результатов которого добавляется условие;

Select From(string schemaName)

Select From(Select subSelect)

Select From(Query subSelectQuery)

Select From(QuerySourceExpression sourceExpression)

Добавляет в запрос источник данных. Возвращает экземпляр запроса.

Параметры:

  • schemaName — название схемы;
  • subSelect — подзапрос выборки, результаты которого становятся источником данных для текущего запроса;
  • subSelectQuery — подзапрос, результаты которого становятся источником данных для текущего запроса;
  • sourceExpression — выражение источника данных запроса.

Join Join(JoinType joinType, string schemaName)

Join Join(JoinType joinType, Select subSelect)

Join Join(JoinType joinType, Query subSelectQuery)

Join Join(JoinType joinType, QuerySourceExpression sourceExpression)

Присоединяет к текущему запросу схему, подзапрос или выражение.

Параметры:

  • joinType — тип присоединения (см. табл. 3).
  • schemaName — название присоединяемой схемы;
  • subSelect — присоединяемый подзапрос выборки данных;
  • subSelectQuery — присоединяемый подзапрос;
  • sourceExpression — присоединяемое выражение.

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)

Добавляет к текущему запросу начальное условие.

Параметры:

  • sourceColumnAlias — псевдоним колонки, для которой добавляется условие;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос выборки данных, для результатов которого добавляется условие;
  • subSelectQuery — подзапрос, для результатов которого добавляется условие;
  • columnExpression — выражение, для результатов которого добавляется условие;
  • 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)

К текущему условию запроса добавляет условие (предикат), используя логическую операцию И.

Параметры:

  • sourceColumnAlias — псевдоним колонки, для которой добавляется предикат;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос выборки данных, используемый в качестве предиката;
  • subSelectQuery — подзапрос, используемый в качестве предиката;
  • parameter — параметр, для которого добавляется предикат;
  • columnExpression — выражение, используемое в качестве предиката;
  • 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)

К текущему условию запроса добавляет условие (предикат), используя логическую операцию ИЛИ.

Параметры:

  • sourceColumnAlias — псевдоним колонки, для которой добавляется предикат;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос на выборку данных, используемый в качестве предиката;
  • subSelectQuery — подзапрос, используемый в качестве предиката;
  • parameter — параметр, для которого добавляется предикат;
  • columnExpression — выражение, используемое в качестве предиката;
  • 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)

Выполняет сортировку результатов запроса. Возвращает экземпляр запроса.

Параметры:

  • direction — порядок сортировки;
  • sourceColumnAlias — псевдоним колонки, по которой выполняется сортировка;
  • sourceAlias — псевдоним источника;
  • queryFunction — функция, значение которой используется в качестве ключа сортировки;
  • subSelect — подзапрос на выборку данных, результаты которого используются в качестве ключа сортировки;
  • subSelectQuery — подзапрос, результаты которого используются в качестве ключа сортировки;
  • columnExpression — выражение, результаты которого используются в качестве ключа сортировки.

Query OrderByAsc(string sourceColumnAlias)

Query OrderByAsc(string sourceAlias, string sourceColumnAlias)

Query OrderByAsc(Select subSelect)

Query OrderByAsc(Query subSelectQuery)

Query OrderByAsc(QueryColumnExpression columnExpression)

Выполняет сортировку результатов запроса в порядке возрастания. Возвращает экземпляр запроса.

Параметры:

  • sourceColumnAlias — псевдоним колонки, по которой выполняется сортировка;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос на выборку данных, результаты которого используются в качестве ключа сортировки;
  • subSelectQuery — подзапрос, результаты которого используются в качестве ключа сортировки;
  • columnExpression — выражение, результаты которого используются в качестве ключа сортировки.

Query OrderByDesc(string sourceColumnAlias)

Query OrderByDesc(string sourceAlias, string sourceColumnAlias)

Query OrderByDesc(Select subSelect)

Query OrderByDesc(Query subSelectQuery)

Query OrderByDesc(QueryColumnExpression columnExpression)

Выполняет сортировку результатов запроса в порядке убывания. Возвращает экземпляр запроса.

Параметры:

  • sourceColumnAlias — псевдоним колонки, по которой выполняется сортировка;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос выборки, результаты которого используются в качестве ключа сортировки;
  • subSelectQuery — подзапрос, результаты которого используются в качестве ключа сортировки;
  • columnExpression — выражение, результаты которого используются в качестве ключа сортировки.

Query GroupBy(string sourceColumnAlias)

Query GroupBy(string sourceAlias, string sourceColumnAlias)

Query GroupBy(QueryColumnExpression columnExpression)

Выполняет группировку результатов запроса. Возвращает экземпляр запроса.

Параметры:

  • sourceColumnAlias — псевдоним колонки, по которой выполняется группировка;
  • sourceAlias — псевдоним источника;
  • columnExpression — выражение, результаты которого используются в качестве ключа группировки.

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)

Добавляет в текущий запрос групповое условие. Возвращает экземпляр Terrasoft.Core.DB.QueryCondition, представляющий групповое условие для параметра запроса.

Параметры:

  • sourceColumnAlias — псевдоним колонки, по которой добавляется групповое условие;
  • sourceAlias — псевдоним источника;
  • subSelect — подзапрос выборки, для результатов которого добавляется групповое условие;
  • subSelectQuery — подзапрос, для результатов которого добавляется групповое условие;
  • parameter — параметр запроса, для которого добавляется групповое условие;
  • columnExpression — выражение, используемое в качестве предиката;

Query Union(Select unionSelect)

Query Union(Query unionSelectQuery)

Объединяет результаты переданного запроса с результатами текущего запроса, исключая дубликаты из результирующего набора.

Параметры:

  • subSelect — подзапрос выборки;
  • subSelectQuery — подзапрос.

Query UnionAll(Select unionSelect)

Query UnionAll(Query unionSelectQuery)

Объединяет результаты переданного запроса с результатами текущего запроса. Дубликаты из результирующего набора не исключаются.

Параметры:

  • subSelect — подзапрос выборки;
  • subSelectQuery — подзапрос.

Табл. 3. — Типы присоединений (перечисление Terrasoft.Core.DB.JoinType)

Тип присоединения Описание
Inner Внутреннее соединение.
LeftOuter Левое внешнее соединение.
RightOuter Правое внешнее соединение.
FullOuter Полное соединение.
Cross Перекрестное соединение.

Примеры использования

Пакет с конфигурационным веб-сервисом, реализующим приведенные ниже примеры, можно скачать по ссылке.

Метод обработки результатов запроса, используемый во всех примерах:

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

Пример 1

Получить текст SQL запроса.

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

Пример 2

Выбрать необходимое количество записей из требуемой таблицы (схемы объекта).

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

Пример 3

Выбрать идентификатор, имя и дату рождения контактов, дата рождения которых позже требуемого года.

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

Пример 4

Выбрать идентификатор, имя и дату рождения контактов, дата рождения которых позже заданного года и у которых указан контрагент.

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

Пример 5

Выбрать идентификатор и имя всех контактов, присоединив к ним идентификаторы и названия соответствующих контрагентов.

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

Пример 6

Выбрать идентификатор и имя контактов, являющихся основными для контрагентов.

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

Пример 7

Выбрать страны и количество городов в стране, если количество городов больше указанного.

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

Пример 8

Получить идентификатор контакта по его имени.

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