Documentation

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

Adding data using subqueries

Glossary Item Box

Examples of using the InsertSelect class to retrieve data using subqueries

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

The example code demonstrates available methods for passing parameters to the query. When developing a project, be aware that parameters originating from the user should never be passed to the Column.Const method, since this can lead to a successful SQL injection attack.

Example 1

Receive the SQL-query text
 
  
public string GetSqlTextExample(string contactName, string accountName)
{
    var result = "";
    var id = Guid.NewGuid();
    var selectQuery = new Select(UserConnection)
        .Column(Column.Parameter(contactName))
        .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(selectQuery);

    result = insertSelectQuery.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 selectQuery = new Select(UserConnection)
            .Column(Column.Parameter(contactName))
            .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(selectQuery);

    var affectedRows = insertSelectQuery.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 insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(
            new Select(UserConnection)
                .Column(Column.Parameter(contactName))
                .Column("Id")
                .From("Account") as Select);

    var affectedRows = insertSelectQuery.Execute();
    var result = $"Inserted {affectedRows} new contacts with name '{contactName}'";
    return result;
}

See also:

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?