Composing modify data queries
Glossary Item Box
The Update class is used to update data in the bpm'online database.
The Update class constructor uses the following objects as parameters:
- User connection (Update (UserConnection)).
- User connection and schema name in which the data will be updated (Update (UserConnection,String)).
- User connection and query source object (Update (UserConnection,ModifyQuerySource)).
- Other object Update (Update (Update)). This will create a copy of an Update query included in the parameter.
The following are the examples of using the Update class method used to build queries of varying complexity. In each example an Update object is created and then an SQL query is provided that will be generated for each different DBMS (MS SQL).
Example 1
var update = new Update(userConnection, "Contact") .Set("Name", Func.IsNull(Column.SourceColumn("Name"), Column.Parameter("ParameterValue"))); |
UPDATE [dbo].[Contact] SET [Name] = ISNULL([Name], @P1)
|
Example 2
var update = new Update(userConnection, "City") .Set("CreatedById", new Select(userConnection).Top(1) .Column("Id") .From("Contact") .Where("Name").IsEqual(Column.Parameter("Supervisor"))) .Set("ModifiedById", new Select(userConnection).Top(1) .Column("Id") .From("Contact") .Where("Name").IsEqual(Column.Parameter("User1"))); |
UPDATE [dbo].[City] SET [CreatedById] = (SELECT TOP 1 [Id] FROM [dbo].[Contact] WHERE [Name] = @P1), [ModifiedById] = (SELECT TOP 1 [Id] FROM [dbo].[Contact] WHERE [Name] = @P2) |