The back-end core components provide the following database access options:
- access using the ORM model
- direct access
We recommend using the ORM model to access data. That said, direct access to the database is also implemented in the back-end core components. Learn more about running database queries using the ORM model: Access data through ORM.
This article covers direct database queries.
Use the following classes to manage data directly:
- Terrasoft.Core.DB.Select builds queries to retrieve records from the database table.
- Terrasoft.Core.DB.Insert builds queries to add records to the database table.
- Terrasoft.Core.DB.InsertSelect builds queries to add records to the database table based on the results of the queries to retrieve records from the database table.
- Terrasoft.Core.DB.Update builds queries to modify records in the database table.
- Terrasoft.Core.DB.UpdateSelect builds queries to modify records in the database table based on the results of the queries to retrieve records from the database table.
- Terrasoft.Core.DB.Delete builds queries to delete records from the database table.
- Terrasoft.Core.DB.DBExecutor builds and runs complex database queries. For example, queries that contain several nested filters, various join combinations, etc.
Retrieve data from the database
Use the following classes to retrieve data from the database:
- Terrasoft.Core.DB.Select retrieves data by accessing the database directly.
- Terrasoft.Core.Entities.EntitySchemaQuery retrieves data using the ORM model. Learn more about running database queries using the Terrasoft.Core.Entities.EntitySchemaQuery class: Access data through ORM.
The purpose of the Terrasoft.Core.DB.Select class is to build queries to select records from database tables. After you create and configure the class instance, Creatio will build a SELECT query to the Creatio database. You can add icons, filters, and restriction conditions to the query.
The Terrasoft.Core.DB.Select class major features:
- The resulting query does not apply the access permissions of the current user. The user can access every database table and record.
- The resulting query does not use the cache repository data.
As result of the query, is an instance that implements the System.Data.IDataReader interface or a scalar value of the corresponding type.
If you need to build queries to select database records that apply the access permissions of the user and use the cache repository data, use the Terrasoft.Core.Entities.EntitySchemaQuery class.
Add data to the database
Use the following classes to add data to the database:
- Terrasoft.Core.DB.Insert
- Terrasoft.Core.DB.InsertSelect
Add data in bulk
The purpose of the Terrasoft.Core.DB.Insert class is to build queries to add records to database tables. After you create and configure the class instance, Creatio will build an INSERT query to the Creatio database.
As result of the query, is the number of records added using the query.
The class implements multiline insertion via the Values() method. After calling the Values() method, all subsequent Set() method calls fall into the new ColumnsValues instance. If the ColumnsValuesCollection collection contains more than one dataset, Creatio will build a query that includes several Values() blocks.
As a result, Creatio will generate the SQL query.
The special aspects of multiline data insertion:
- Microsoft SQL supports up to 2100 parameters when using Column.Parameter in the Set() expression.
-
If your query contains more parameters, the developer must divide the query into subqueries since Terrasoft.Core.DB.Insert does not support this option.
- The developer must confirm the column number matches the Set() condition number since Terrasoft.Core.DB.Insert does not support this option. The mismatch will create an exception on the database level.
Add data from the selection
The purpose of the Terrasoft.Core.DB.InsertSelect class is to build queries to add records to the database tables based on the data of the queries to retrieve records from the database table. As such, the class uses the Terrasoft.Core.DB.Select class instance as a data source. After you create and configure the class instance, Creatio will build an INSERT INTO SELECT query to the Creatio database.
The resulting query built using the Terrasoft.Core.DB.InsertSelect class does not apply the current user's access permissions to the corresponding records. The class uses the user connection only to access the database table.
As a result of the query, Creatio will add the records retrieved in the Select query to the database.
Modify the database data
Use the following classes to modify the database data:
- Terrasoft.Core.DB.Update
- Terrasoft.Core.DB.UpdateSelect
Modify data in bulk
The purpose of the Terrasoft.Core.DB.Update class is to build queries to modify records in the database tables. After you create and configure the class instance, Creatio will build an UPDATE query to the Creatio database.
Modify data based on the selection
The purpose of the Terrasoft.Core.DB.UpdateSelect class is to build queries to modify records in the database tables based on the data of the queries to retrieve records from the database table. As such, the class uses the Terrasoft.Core.DB.Select class instance as a data source. After you create and configure the class instance, Creatio will build an UPDATE FROM query to the Creatio database.
As a result of the query, Creatio will modify the records retrieved in the Select query in the database table.
Delete data from the database
The Terrasoft.Core.DB.Delete class deletes data from the database.
The purpose of the Terrasoft.Core.DB.Delete class is to build queries to delete records from the database tables. After you create and configure the class instance, Creatio will build a DELETE query to the Creatio database.
Use multithreading for database management
Multithreading is the use of several parallel threads to send database queries via UserConnection.
The Terrasoft.Core.DB.DBExecutor class supports multithreading. The class implements building and running several database queries in a single transaction. A single DBExecutor instance is available per user. The user cannot create new instances.
Multithreading may lead to synchronization issues related to transaction start and confirmation. The issue can occur even if you use the DBExecutor indirectly. For example, through EntitySchemaQuery.
If you use the Terrasoft.Core.DB.DBExecutor class, you must wrap the creation of the DBExecutor instance in the using operator. This is required since Creatio uses unmanaged resources to manage the database. You can also call the Dispose() method to free up resources. Learn more about using the using operator in the official Microsoft documentation.
Call the dbExecutor.StartTransaction method to start the transaction. Call dbExecutor.CommitTransaction or dbExecutor.RollbackTransaction to end the transaction. If the execution goes beyond the scope of the using block and you do not call the dbExecutor.CommitTransaction method, the transaction will roll back automatically.
View the source code snippets that use DBExecutor below. Do not call the methods of the DBExecutor instance in parallel threads.
In most cases, a modification query must contain the Where condition, which specifies the exact records to modify. If you do not specify the Where condition, Creatio will modify all records.
Example 1
Example 2
The Where condition refers to the Select query. The Update query does not contain the Where condition since the goal is to modify all records.
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.UpdateSelect class builds queries to select records in a Creatio database table. As a result of creating and configuring an instance of this class, Creatio will build a SELECT SQL query. You can add columns, filters, and restriction conditions to the query. The query results are returned as an instance that implements the System.Data.IDataReader interface or as a scalar value of the needed type.
Constructors
Creates a class instance with the specified UserConnection parameter.
Creates a class instance with the specified UserConnection parameter and the cancellation token for managed threads.
Creates a class instance that is a clone of the instance passed as the argument.
Properties
The user connection that the query uses.
The number of records to return after the execution.
The query parameter collection.
Whether the query has parameters.
Whether to add the query parameters to the query text as values.
The collection of Join query expressions.
Whether the query has Join expressions.
The Where condition of the query.
Whether the query has the Where expression.
The Having condition of the query.
Whether the query has the Having expression.
The collection of expressions by which to sort the query results.
Whether the query has order expressions.
The collection of expressions by which to group the query results.
Whether the query has grouping expressions.
Whether to return only the distinct records.
The collection of query column expressions.
Whether to paginate the query results.
The number of rows to skip when returning the query result.
The query type.
Methods
Clears the cached query text.
Returns the parameter collection that the query uses.
Clears the query parameter collection.
Initializes the query parameter collection.
Executes the query using the DBExecutor instance. Returns the object that implements the IDataReader interface.
dbExecutor | The DBExecutor instance required to run the query. |
Executes the query using the DBExecutor instance. Returns the object that implements the IDataReader interface.
behavior | Describes the query results and how they affect the database. |
dbExecutor | The DBExecutor instance required to run the query. |
Executes the query by calling the passed ExecuteReaderReadMethod delegate method for every record of the resulting set.
readMethod | The ExecuteReaderReadMethod delegate method. |
Executes the query. Returns the typed first column of the first record in the resulting set.
Executes the query using the DBExecutor instance. Returns the typed first column of the first record in the resulting set.
dbExecutor | The DBExecutor instance required to run the query. |
Adds the DISTINCT keyword to the SQL query. Excludes record duplicates from the resulting set. Returns the query instance.
Sets the number of records to return in the resulting set. Also changes the RowCount property value. Returns the query instance.
rowCount | The number of first records in the resulting set. |
Adds the last query expression alias specified in the argument. Returns the query instance.
alias | The query expression alias. |
Adds an expression, a subquery, or a parameter to the column expression collection of the query. Returns the query instance.
sourceColumnAlias | The name of the column for which to add the expression. |
sourceAlias | The alias of the source from which to add the column expression. |
subSelect | The added data selection subquery. |
subSelectQuery | The added subquery. |
queryCase | The added expression for the Case operator. |
queryParameter | The added query parameter. |
columnExpression | The expression for whose results to add the condition. |
Adds the data source to the query. Returns the query instance.
schemaName | The schema name. |
subSelect | The selection subquery whose results become the data source for the current query. |
subSelectQuery | The subquery whose results become the data source for the current query. |
sourceExpression | The expression of the query data source. |
Joins a schema, a subquery, or an expression to the current query.
joinType | The join type. |
schemaName | The joinable schema name. |
subSelect | The joinable data selection subquery. |
subSelectQuery | The joinable subquery. |
sourceExpression | The joinable expression. |
Inner | Inner join. |
LeftOuter | Left outer join. |
RightOuter | Right outer join. |
FullOuter | Full join. |
Cross | Cross join. |
Adds the initial condition to the current query.
sourceColumnAlias | The alias of the schema for which to add the condition. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery for whose results to add the condition. |
subSelectQuery | The subquery for whose results to add the condition. |
columnExpression | The expression for whose results to add the condition. |
condition | The query condition. |
Adds the condition (predicate) to the current query condition using the AND logical operation.
sourceColumnAlias | The name of the schema for which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter for which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Adds the condition (predicate) to the current query condition using the OR logical operation.
sourceColumnAlias | The name of the schema for which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data select subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter for which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Sorts the query results. Returns the query instance.
direction | The sorting order. |
sourceColumnAlias | The alias of the column by which to sort the results. |
sourceAlias | The alias of the source. |
queryFunction | The function whose value serves as the sorting key. |
subSelect | The select subquery whose results serve as the sorting key. |
subSelectQuery | The subquery whose results serve as the sorting key. |
columnExpression | The expression whose results serve as the sorting key. |
Sorts query results in the ascending order. Returns the query instance.
sourceColumnAlias | The alias of the column by which to sort the results. |
sourceAlias | The alias of the source. |
subSelect | The select subquery whose results serve as the sorting key. |
subSelectQuery | The subquery whose results serve as the sorting key. |
columnExpression | The expression whose results serve as the sorting key. |
Sorts query results in descending order. Returns the query instance.
sourceColumnAlias | The alias of the column by which to sort the results. |
sourceAlias | The alias of the source. |
subSelect | The select subquery whose results serve as the sorting key. |
subSelectQuery | The subquery whose results serve as the sorting key. |
columnExpression | The expression whose results serve as the sorting key. |
Groups the query results. Returns the query instance.
sourceColumnAlias | The alias of the column by which to group the results. |
sourceAlias | The alias of the source. |
columnExpression | The expression whose results serve as the grouping key. |
Adds the group condition to the current query. Returns the Terrasoft.Core.DB.QueryCondition instance that represents the group condition for the query parameter.
sourceColumnAlias | The alias of the column by which to add the group condition. |
sourceAlias | The alias of the source. |
subSelect | The select subquery for whose results to add the group condition. |
subSelectQuery | The subquery for whose results to add the group condition. |
parameter | The query parameter to add the group condition. |
columnExpression | The expression that serves as a predicate. |
Merges the results of the passed query with the results of the current query. Excludes duplicates from the resulting set.
unionSelect | The selection subquery. |
unionSelectQuery | The subquery. |
Merges the results of the passed query with the results of the current query. Does not exclude duplicates from the resulting set.
unionSelect | The selection subquery. |
unionSelectQuery | Subquery. |
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.Insert class builds queries to add records to Creatio database tables. As a result of creating and configuring an instance of this class, Creatio will build an INSERT SQL query. The query returns the number of records involved.
Constructors
Creates a new Entity class instance for the set UserConnection user connection.
Creates a class instance with the specified UserConnection.
Creates a class instance that is a clone of the instance passed as the argument.
Properties
The user connection that the query uses.
The data source.
The query parameter collection.
Whether the query has parameters.
Whether to add the query parameters to the query text as values.
The collection of query column values.
The collection of column values required to add records in bulk.
Methods
Clears the cached query text.
Returns the parameter collection that the query uses.
Clears the query parameter collection.
Sets the value of the query parameter
name | The parameter name. |
value | The value. |
Initializes the query parameter collection.
Executes the query. Returns the number of records that the query involved.
Executes the query using the DBExecutor instance. Returns the number of records that the query processed.
Adds a data source to the current query.
schemaName | The schema name. |
source | The data source. |
Adds a SET clause to the current query. Required to assign the passed expression or parameter to the column. Returns the current Insert instance.
sourceColumnAlias | The column alias. |
subSelect | The select subquery. |
subSelectQuery | The subquery. |
columnExpression | The column expression. |
parameter | The query parameter. |
Initializes the values required to add records in bulk.
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.InsertSelect class builds queries to add records to Creatio database tables. The Terrasoft.Core.DB.Select class instance serves as a data source. As a result of creating and configuring an instance of Terrasoft.Core.DB.InsertSelect, Creatio will build an INSERT INTO SELECT SQL query.
Constructors
Creates a class instance with the specified UserConnection.
Creates a class instance that is a clone of the instance passed as the argument.
Properties
The user connection that the query uses.
The data source.
The query parameter collection.
Whether the query has parameters.
Whether to add the query parameters to the query text as values.
The collection of query column values.
The Terrasoft.Core.DB.Select instance that the query uses.
Methods
Clears the cached query text.
Returns the parameter collection that the query uses.
Clears the query parameter collection.
Sets the value of the query parameter
name | The parameter name. |
value | The value. |
Initializes the query parameter collection.
Executes the query. Returns the number of records that the query involved.
Executes the query using the DBExecutor instance. Returns the number of records that the query involved.
Adds the data source to the current query.
schemaName | The schema name. |
source | The data source. |
Adds a set of columns to the current query. The subquery assigns values to the columns. Returns the current InsertSelect instance.
sourceColumnAliases | A method parameter collection or array, which contains the column aliases. |
Columns | A method parameter collection or array, which contains the column instances. |
Adds the SELECT clause to the current query.
subSelect | The select subquery. |
subSelectQuery | The subquery. |
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.Update class builds queries to modify records in Creatio database tables. As a result of creating and configuring an instance of this class, Creatio will build an UPDATE SQL query.
Constructors
Creates a class instance using UserConnection.
Creates a class instance for the specified schema using UserConnection.
Creates a class instance for the specified data source using UserConnection.
Creates a class instance that is a clone of the instance passed as the argument.
Properties
The user connection that the query uses.
The Where condition of the query.
Whether the query has a Where expression.
The query data source.
The collection of query column values.
Methods
Clears the cached query text.
Returns the parameter collection that the query uses.
Executes the query. Returns the number of records that the query involved.
Executes the query using the DBExecutor instance. Returns the number of records that the query processed.
Adds the initial condition to the current query.
sourceColumnAlias | The alias of the schema to which to add the condition. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery to whose results to add the condition. |
subSelectQuery | The subquery to whose results to add the condition. |
columnExpression | The expression to whose results to add the condition. |
Condition | The query condition. |
Adds the condition (predicate) to the current query condition using the AND logical operation.
sourceColumnAlias | The name of the schema to which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter to which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Adds the condition (predicate) to the current query condition using the OR logical operation.
sourceColumnAlias | The name of the schema to which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data select subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter to which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Adds a SET clause to the current query. Required to assign the passed expression or parameter to the column. Returns the current Update instance.
sourceColumnAlias | The column alias. |
subSelect | The select subquery. |
subSelectQuery | The subquery. |
columnExpression | The column expression. |
parameter | The query parameter. |
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.UpdateSelect class builds queries to modify records in a Creatio database table. The Terrasoft.Core.DB.Select class instance serves as a data source. As a result of creating and configuring the instance of the UpdateSelect class, Creatio will build an UPDATE FROM SQL query.
Terrasoft.Core.DB namespace.
The Terrasoft.Core.DB.Delete class builds queries to delete records from Creatio database tables. As a result of creating and configuring an instance of this class, Creatio will build a DELETE SQL query.
Constructors
Creates a class instance using UserConnection.
Creates a class instance that is a clone of the instance passed as the argument.
Properties
The user connection that the query uses.
The Where condition of the query.
Whether the query has a Where expression.
The query data source.
Methods
Clears the cached query text.
Returns the parameter collection that the query uses.
Executes the query. Returns the number of records that the query involved.
Executes the query using the DBExecutor instance. Returns the number of records that the query involved.
Adds the initial condition to the current query.
sourceColumnAlias | The alias of the schema to which to add the condition. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery to whose results to add the condition. |
subSelectQuery | The subquery to whose results to add the condition. |
columnExpression | The expression to whose results to add the condition. |
condition | The query condition. |
Adds the condition (predicate) to the current query condition using the AND logical operation.
sourceColumnAlias | The name of the schema to which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data selection subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter to which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Adds the condition (predicate) to the current query condition using the OR logical operation.
sourceColumnAlias | The name of the schema to which to add the predicate. |
sourceAlias | The alias of the source. |
subSelect | The data select subquery that serves as a predicate. |
subSelectQuery | The subquery that serves as a predicate |
parameter | The parameter to which to add the predicate. |
columnExpression | The expression that serves as a predicate. |
condition | The query condition. |
Adds the data source to the current query. Returns the current Delete instance.
schemaName | The schema name (tables, views). |
source | The data source. |
The Terrasoft.Core.DB.QueryFunction class implements the expression function.
The following classes implement the expression function:
- QueryFunction. The base class of the expression function.
- AggregationQueryFunction. Implements the aggregate expression function.
- IsNullQueryFunction. Replaces null values with the replacing expression.
- CreateGuidQueryFunction. Implements the expression function for a new ID.
- CurrentDateTimeQueryFunction. Implements the expression function for the current date and time.
- CoalesceQueryFunction. Returns the first non-null expression from the argument list.
- DatePartQueryFunction. Implements the expression function for a part of a Date/Time type value.
- DateAddQueryFunction. Implements the expression function for the date, calculated by adding the period to the date.
- DateDiffQueryFunction. Implements the expression function for the difference between the dates, calculated by subtracting the dates.
- CastQueryFunction. Casts the argument expression to the specified data type.
- UpperQueryFunction. Converts the argument expression characters to uppercase.
- CustomQueryFunction. Implements a custom function.
- DataLengthQueryFunction. Calculates bytes used to represent the expression.
- TrimQueryFunction. Removes whitespaces from both ends of the expression.
- LengthQueryFunction. Returns the expression length.
- SubstringQueryFunction. Retrieves a part of the string.
- ConcatQueryFunction. Creates a string by merging the string arguments of the function.
- WindowQueryFunction. Implements an SQL window function.
QueryFunction class
The Terrasoft.Core.DB namespace.
The base class of the expression function.
Methods
Returns the expression that negates the value of the passed function.
operand | The expression function. |
Overloads the operator that negates the passed expression function.
operand | The expression function. |
Returns the expression that adds the passed expression functions.
leftOperand | The left operand in the addition operation. |
rightOperand | The right operand in the addition operation. |
Overloads the operator that adds two expression functions.
leftOperand | The left operand in the addition operation. |
rightOperand | The right operand in the addition operation. |
Returns the expression that subtracts the right expression function from the left function.
leftOperand | The left operand in the subtraction operation. |
rightOperand | The right operand in the subtraction operation. |
Overloads the operator that subtracts the right expression function from the left function.
leftOperand | The left operand in the subtraction operation. |
rightOperand | The right operand in the subtraction operation. |
Returns the expression that multiplies the passed expression functions.
leftOperand | The left operand in the multiplication operation. |
rightOperand | The right operand in the multiplication operation. |
Overloads the operator that multiplies two expression functions.
leftOperand | The left operand in the multiplication operation. |
rightOperand | The right operand in the multiplication operation. |
Returns the expression that divides the left expression function by the right function.
leftOperand | The left operand in the division operation. |
rightOperand | The right operand in the division operation. |
Overloads the operator that divides the expression functions.
leftOperand | The left operand in the division operation. |
rightOperand | The right operand in the division operation. |
Makes a copy of the current QueryFunction instance.
Generates the query string using the passed StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Returns the collection of query column expressions for the current query function.
Returns the query column expression for the current query function.
AggregationQueryFunction class
Terrasoft.Core.DB namespace.
The class implements the aggregate expression function.
Constructors
Initializes a new AggregationQueryFunction instance.
Initializes a new AggregationQueryFunction instance with the specified type of the aggregate function for the specified column expression.
aggregationType | The aggregate function type. |
expression | The column expression to apply the aggregate function. |
Initializes a new AggregationQueryFunction instance with the specified type of the aggregate function for the column expression.
aggregationType | The aggregate function type. |
expression | The column expression to apply the aggregate function. |
Initializes a new AggregationQueryFunction instance that is a clone of the passed aggregate expression function.
source | The aggregate function of the AggregationQueryFunction expression to clone. |
Properties
The aggregate function type.
The aggregate function scope.
The function argument expression.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current AggregationQueryFunction instance.
Sets the To All Values scope for the current aggregate function.
Sets the To Unique Values scope for the current aggregate function.
IsNullQueryFunction class
Terrasoft.Core.DB namespace.
The class replaces null values with the replacing expression.
Constructors
Initializes a new IsNullQueryFunction instance.
Initializes a new IsNullQueryFunction instance for the check expression and the replacing expression.
checkExpression | The expression to compare to null. |
replacementExpression | The expression the function returns if checkExpression is null. |
Initializes a new IsNullQueryFunction instance that is a clone of the passed expression function.
source | The aggregate function of the IsNullQueryFunction expression to clone. |
Properties
The function argument expression to compare to null.
The function argument expression to return if the check expression is null.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current IsNullQueryFunction instance
CreateGuidQueryFunction class
Terrasoft.Core.DB namespace.
The class implements the expression function for a new ID.
Constructors
Initializes a new CreateGuidQueryFunction instance.
Initializes a new CreateGuidQueryFunction instance that is a clone of the passed function.
source | The CreateGuidQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Clones the current CreateGuidQueryFunction instance.
CurrentDateTimeQueryFunction class
The Terrasoft.Core.DB namespace.
The class implements the expression function for the current date and time.
Constructors
Initializes a new CurrentDateTimeQueryFunction instance.
Initializes a new CurrentDateTimeQueryFunction instance that is a clone of the passed function.
source | The CurrentDateTimeQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Creates a clone of the current CurrentDateTimeQueryFunction instance.
CoalesceQueryFunction class
Terrasoft.Core.DB namespace.
The class returns the first non-null expression from the argument list.
Constructors
Initializes a new CoalesceQueryFunction instance.
Initializes a new CoalesceQueryFunction instance that is a clone of the passed function.
source | The CoalesceQueryFunction function to clone. |
Initializes a new CoalesceQueryFunction instance for the passed collection of column expressions.
expressions | The collection of query column expressions. |
Initializes a new CoalesceQueryFunction instance for the passed array of column expressions.
expressions | The array of query column expressions. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Creates a clone of the current CoalesceQueryFunction instance.
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
DatePartQueryFunction class
Terrasoft.Core.DB namespace.
The class implements the expression function for a part of a Date/Time type value.
Constructors
Initializes a new instance of the DatePartQueryFunction class.
Initializes a new DatePartQueryFunction instance with the set expression of the Date/Time type column and the specified date part.
interval | The date part. |
expression | The expression of the Date/Time type column. |
Initializes a new DatePartQueryFunction instance that is a clone of the passed function.
source | The DatePartQueryFunction function to clone. |
Properties
The expression of the function argument.
The date part that the function returns.
Whether to use the Universal Coordinated Time (UTC) offset relative to the set local time.
The UTC offset.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current DatePartQueryFunction instance.
DateAddQueryFunction class
Terrasoft.Core.DB namespace.
The class implements the expression function for the date, calculated by adding the period to the date.
Constructors
Initializes a new instance of the DateAddQueryFunction class.
Initializes a DateAddQueryFunction instance with the specified parameters.
interval | The date part to add the period. |
number | The value to add to interval. |
expression | The expression of the column that contains the original date. |
Initializes a DateAddQueryFunction instance that is a clone of the passed function.
source | The instance of the DateAddQueryFunction function to clone. |
Properties
The expression of the column that contains the original date.
The date part to add the period.
The period to add.
The expression that contains the period to add.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current DateAddQueryFunction instance.
DateDiffQueryFunction class
Terrasoft.Core.DB namespace.
The class implements the expression function for the difference between the dates, calculated by subtraction.
Constructors
Initializes a DateDiffQueryFunction instance with the specified parameters.
interval | The date interval unit. |
startDateExpression | The expression of the column that contains the start date. |
endDateExpression | The expression of the column that contains the end date. |
Initializes a DateDiffQueryFunction instance that is a clone of the passed function.
source | The instance of the DateDiffQueryFunction to clone. |
Properties
The expression of the column that contains the start date.
The expression of the column that contains the end date.
The date difference’s measurement unit that the function returns.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current DateDiffQueryFunction instance.
CastQueryFunction class
Terrasoft.Core.DB namespace.
The class casts the argument expression to the specified data type.
Constructors
Initializes a new CastQueryFunction instance with the specified column expression and target data type.
expression | The query column expression. |
castType | The target data type. |
Initializes a new CastQueryFunction instance that is a clone of the passed function.
source | The CastQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current CastQueryFunction instance.
UpperQueryFunction class
Terrasoft.Core.DB namespace.
The class converts the argument expression characters to uppercase.
Constructors
Initializes a new instance of the UpperQueryFunction class.
Initializes a new UpperQueryFunction instance for the specified column expression.
expression | The query column expression. |
Initializes a new UpperQueryFunction instance that is a clone of the passed function.
source | The UpperQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current UpperQueryFunction instance.
CustomQueryFunction class
Terrasoft.Core.DB namespace.
The class implements a custom function.
Constructors
Initializes a new instance of the CustomQueryFunction class.
Initializes a new CustomQueryFunction instance for the specified function and passed collection of column expressions.
functionName | The name of the function. |
expressions | The collection of query column expressions. |
Initializes a new CustomQueryFunction instance for the specified function and passed array of column expressions.
functionName | The name of the function. |
expressions | The array of query column expressions. |
Initializes a new CustomQueryFunction instance that is a clone of the passed function.
source | The CustomQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the specified parameters to the collection.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current CustomQueryFunction instance.
DataLengthQueryFunction class
Terrasoft.Core.DB namespace.
The class calculates bytes used to represent the expression.
Constructors
Initializes a new instance of the DataLengthQueryFunction class.
Initializes a new DataLengthQueryFunction instance for the specified column expression.
expression | The query column expression. |
Initializes a new DataLengthQueryFunction instance for the specified column expression.
columnNameExpression | The query column expression. |
Initializes a new DataLengthQueryFunction instance that is a clone of the passed function.
source | The DataLengthQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current DataLengthQueryFunction instance.
TrimQueryFunction class
Terrasoft.Core.DB namespace.
The class removes whitespaces from both ends of the expression.
Constructors
Initializes a new TrimQueryFunction instance for the specified column expression.
expression | The query column expression. |
Initializes a new TrimQueryFunction instance that is a clone of the passed function.
source | The TrimQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current TrimQueryFunction instance.
LengthQueryFunction class
Terrasoft.Core.DB namespace.
The class returns the length of the expression.
Constructors
Initializes a new instance of the LengthQueryFunction class.
Initializes a new LengthQueryFunction instance for the specified column expression.
expression | The query column expression. |
Initializes a new LengthQueryFunction instance that is a clone of the passed function.
source | The LengthQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current LengthQueryFunction instance.
SubstringQueryFunction class
Terrasoft.Core.DB namespace.
The class retrieves a part of the string.
Constructors
Initializes a new SubstringQueryFunction instance for the specified column expression, starting position and the substring length.
expression | The query column expression. |
start | The starting position of the substring. |
length | The length of the substring. |
Initializes a new SubstringQueryFunction instance that is a clone of the passed function.
source | The SubstringQueryFunction function to clone. |
Properties
The expression of the function argument.
The starting position of the substring.
The length of the substring.
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current SubstringQueryFunction instance.
ConcatQueryFunction class
Terrasoft.Core.DB namespace.
The class creates a string by merging the string arguments of the function.
Constructors
Initializes a new ConcatQueryFunction instance for the passed expression collection.
expressions | The collection of query column expressions. |
Initializes a new ConcatQueryFunction instance that is a clone of the passed function.
source | The ConcatQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current ConcatQueryFunction instance.
WindowQueryFunction class
The Terrasoft.Core.DB namespace.
The class implements an SQL window function.
Constructors
Implements an SQL window function.
innerFunction | The nested function. |
Implements an SQL window function.
innerFunction | The nested function. |
partitionByExpression | The expression that separates the query. |
orderByExpression | The expression that sorts the query. |
Initializes a new WindowQueryFunction instance that is a clone of the passed function.
source | The WindowQueryFunction function to clone. |
Methods
Generates the query string using the specified StringBuilder instance and the DBEngine query builder.
sb | The StringBuilder instance required to create the query string. |
dbEngine | The instance of the database query builder. |
Adds the passed collection of parameters to the function arguments.
resultParameters | A collection of query parameters to add to the function arguments. |
Creates a clone of the current WindowQueryFunction instance.