Modifying data. The Update class
Glossary Item Box
Introduction
The Terrasoft.Core.DB.Update class is used to build queries for modifying records in bpm’online database tables. As a result of creating and configuring the instance of this class, the UPDATE SQL-expression query to the application database will be built.
The Terrasoft.Core.DB.Update class
NOTE
Use the “.NET class libraries of platform core” documentation to access the full list of the Update class methods and properties, its parent classes and the implemented interfaces.
Constructors
- public Update(UserConnection userConnection) – creates a class instance using UserConnection
- public Update(UserConnection userConnection, string schemaName) — creates a class instance for schema with the specified name, using UserConnection
- public Update(UserConnection userConnection, ModifyQuerySource source) — creates a class instance for schema with the specified name, using UserConnection
- public Update(Insert 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. |
| Condition | Terrasoft.Core.DB.QueryCondition | Condition of Where expression in the query. |
| HasCondition | bool | Determines whether Where expression is available in the query. |
| Source. | Terrasoft.Core.DB.ModifyQuerySource | The query data source. |
| ColumnValues | Terrasoft.Core.DB.ModifyQueryColumnValueCollection | Collection of values of the query columns |
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. | |
|
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. | |
|
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) |
Adds the initial condition to the current query. Parameters:
|
|
|
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) |
Adds the condition (predicate) to the current query condition using the AND logical operation. Parameters:
|
|
|
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) |
Adds the condition (predicate) to the current query condition using the OR logical operation. Parameters:
|
|
|
Update Set(string sourceColumnAlias, Select subSelect) Update Set(string sourceColumnAlias, Query subSelectQuery) Update Set(string sourceColumnAlias, QueryColumnExpression columnExpression) Update 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 Update instance. Parameters:
|
Use cases
You can download the package with the configuration web serice implementing the below described cases using the following link.
ATTENTION
In most cases, a request for modification should contain the “Where” condition, which specifies which records exactly should be modified. Otherwise, all records will be modified.
Example 1
Receive the SQL-query text.
public string GetSqlTextExample(string oldName, string newName) { var result = ""; var update = new Update(UserConnection, "Contact") .Set("Name", Column.Parameter(newName)) .Where("Name").IsEqual(Column.Parameter(oldName)); result = update.GetSqlText(); return result; }
Example 2
Change the contact name for a new one.
public string ChangeContactName(string oldName, string newName) { var update = new Update(UserConnection, "Contact") .Set("Name", Column.Parameter(newName)) .Where ("Name").IsEqual(Column.Parameter(oldName)); var cnt = update.Execute(); return $"Contacts {oldName} changed to {newName}. {cnt} rows affected."; }
Example 3
Change the user that has modified all existing contact records, to the specified value.
public string ChangeAllContactModifiedBy(string Name) { var update = new Update(UserConnection, "Contact") .Set("ModifiedById", new Select(UserConnection).Top(1) .Column("Id") .From("Contact") .Where("Name").IsEqual(Column.Parameter(Name))); var cnt = update.Execute(); return $"All contacts are changed by {Name} now. {cnt} rows affected."; }
NOTE
In this example, the Where condition refers to the Select request. The Update request does not contain the Where condition, since it is necessary to modify all records.
