Documentation

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

Building queries for adding data. The Insert class

Glossary Item Box

Introduction

The Terrasoft.Core.DB.Insert class is used to build queries for adding records in Creatio database tables. As a result of creating and configuring the instance of this class, the INSERT SQL-expression query to the application database will be built. Execution of the query results in returning the number of involved records.

ATTENTION

When working with the Insert class, the access permissions are not applied to the added records. User connection is only used for accessing the database table.

The “Terrasoft.Core.DB.Insert” class

NOTE

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

Constructors

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

Properties.

Table 1. Primary properties of the Insert 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
ColumnValues Terrasoft.Core.DB.ModifyQueryColumnValueCollection Collection of values of the query columns
ColumnValuesCollection List<ModifyQueryColumnValueCollection> Collection of column values for adding multiple records (see “Multi-row data insert. The Insert class”)

Methods

Table 2. Primary methods of the Insert 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.

Insert Into(string schemaName)

Insert Into(ModifyQuerySource source)

Adds the data source to the current query.

Parameters:

  • schemaName – schema name
  • source – the data source

Insert Set(string sourceColumnAlias, Select subSelect)

Insert Set(string sourceColumnAlias, Query subSelectQuery)

Insert Set(string sourceColumnAlias, QueryColumnExpression columnExpression)

Insert Set(string sourceColumnAlias, QueryParameter parameter)

Adds a SET clause to the current query for assigning the passed expression or parameter to the column. Returns the current Insert instance.

Parameters:

  • sourceColumnAlias – column alias
  • subSelect – selection subquery
  • subSelectQuery – subquery
  • columnExpression – expression of the column
  • parameter – parameter of the query
Insert Values() Initiates values for adding multiple columns (see “Multi-row data insert. The Insert class”).

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)
{
    var result = "";
    var id = Guid.NewGuid();
    var ins = new Insert(UserConnection)
        .Into("Contact")
        .Set("Id", Column.Parameter(id))
        .Set("Name", Column.Parameter(ContactName));
    result = ins.GetSqlText();
    return result;
}

Example 2

Add the contact with the specified name.

public string InsertContact(string contactName)
{
    contactName = contactName ?? "Unknown contact";
    var ins = new Insert(UserConnection)
        .Into("Contact")
        .Set("Name", Column.Parameter(contactName));
    var affectedRows = ins.Execute();
    var result = $"Inserted new contact with name '{contactName}'. {affectedRows} rows affected";
    return result;
}

Example 3

Add the city with the specified name and connect it to the specified country.

public string InsertCity(string city, string country)
{
    city = city ?? "unknown city";
    country = country ?? "unknown country";

    var ins = new Insert(UserConnection)
        .Into("City")
        .Set("Name", Column.Parameter(city))
        .Set("CountryId",
            new Select(UserConnection)
                    .Top(1)
                    .Column("Id")
                .From("Country")
                .Where("Name")
                    .IsEqual(Column.Parameter(country)));
    var affectedRows = ins.Execute();
    var result = $"Inserted new city with name '{city}' located in '{country}'. {affectedRows} rows affected";
    return result;
}

NOTE

You can find more examples of adding multiple records in the “Multi-row data insert. The Insert class” article.

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?