Documentation

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

Adding data via subqueries. The InsertSelect class

Glossary Item Box

Introduction

The Terrasoft.Core.DB.InsertSelect class is used to build queries for adding records in Creatio database tables. The Terrasoft.Core.DB.Select class instance is used as the source for adding data (see “Retrieving information from database. The Select class”). As a result of creating and configuring the instance of Terrasoft.Core.DB.InsertSelect, the INSERT INTO SELECT SQL-expression query to the application database will be built.

ATTENTION

When working with the InsertSelect class, the access permissions are not applied to the added records. No application permissions are applied to such records (including object permissions by operation, records or columns). User connection is only used for accessing the database table.

NOTE

After the InsertSelect query is executed, the database will be complemented with all records returned in its Select. subquery.

The “Terrasoft.Core.DB.InsertSelect” class

NOTE

Use the “.NET class libraries of platform core” documentation to access the full list of the InsertSelect class methods and properties, its parent classes and the implemented interfaces.

Constructors

  • public InsertSelect(UserConnection userConnection) – creates a class instance with the specified UserConnection.
  • public InsertSelect(InsertSelect source) – creates a class instance that is a clone of the instance passed as an argument

Properties.

Table 1. Primary properties of the class

Property Type Description
UserConnection Terrasoft.Core.UserConnection The current user connection at the moment of executing the query
Source. Terrasoft.Core.DB.ModifyQuerySource Data source
Parameters Terrasoft.Core.DB.QueryParameterCollection Collection of the query parameters
HasParameters bool Determines whether the query has any parameters
BuildParametersAsValue bool Determines whether the query parameters are to be added as values into the query text
Columns: Terrasoft.Core.DB.ModifyQueryColumnValueCollection Collection of values of the query columns
Select. Terrasoft.Core.DB.Select The Terrasoft.Core.DB.Select instance used in the query.

Methods

Table 2. Primary methods of the 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
void SetParameterValue(string name, object value)

Sets the value of the query parameter

Parameters:

  • name – parameter name
  • value – the value

void InitializeParameters()

 Initiates the collection of the query parameters.

int Execute()

 Executes the query. Returns the number of records involved by the query.

int Execute(DBExecutor dbExecutor)

 Executes the query using the DBExecutor instance. Returns the number of records involved by the query.

InsertSelect Into(string schemaName)

InsertSelect Into(ModifyQuerySource source)

Adds the data source to the current query.

Parameters:

  • schemaName – schema name
  • source – the data source

InsertSelect Set(IEnumerable<string> sourceColumnAliases)

InsertSelect Set(params string[] sourceColumnAliases)

InsertSelect Set(IEnumerable<ModifyQueryColumn> columns)

InsertSelect Set(params ModifyQueryColumn[] columns)

Adds a set of columns to the current query, where the values will be added via the subquery. Returns the current InsertSelect instance.

Parameters:

  • sourceColumnAliases – method parameter collection or array containing the column aliases.
  • columns – method parameter collection or array containing the column instances.

InsertSelect FromSelect(Select subSelect)

InsertSelect FromSelect(Query subSelectQuery)

Adds the SELECT clause to the current query.

Parameters:

  • subSelect – selection subquery
  • subSelectQuery – subquery;

Use cases

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

Example 1

Receive the SQL-query text.

public string GetSqlTextExample(string contactName, string accountName)
{
    var result = "";
    var id = Guid.NewGuid();
    var select = new Select(UserConnection)
        .Column(Column.Const(contactName))
        .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insel = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(select);

    result = insel.GetSqlText();
    return result;
}

Example 2

Add the contact with the specified name and account.

public string InsertContactWithAccount(string contactName, string accountName)
{
    contactName = contactName ?? "Unknown contact";
    accountName = accountName ?? "Unknown account";
    
    var id = Guid.NewGuid();
    var select = new Select(UserConnection)
            .Column(Column.Const(contactName))
            .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insel = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(select);

    var affectedRows = insel.Execute();
    var result = $"Inserted new contact with name '{contactName}'" +
                $" and account '{accountName}'." +
                $" Affected {affectedRows} rows.";
    return result;
}

Example 3

Add the contact with the specified name and connect it to all accounts.

public string InsertAllAccountsContact(string contactName)
{
    contactName = contactName ?? "Unknown contact";

    var id = Guid.NewGuid();
    var insel = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(
            new Select(UserConnection)
                .Column(Column.Parameter(contactName))
                .Column("Id")
                .From("Account") as Select);

    var affectedRows = insel.Execute();
    var result = $"Inserted {affectedRows} new contacts with name '{contactName}'";
    return result;
}
© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?