Data Controllers / Actions / SQL
Business Logic

Complex business logic can be implemented either with custom business rules or with the help of SQL action.

Consider the following example.

End user selects an order in a sample Northwind web application.
Multiple line items (order details) must be discounted with a single
user interface command.

Master-detail page in a web app created with Code On Time web application generator

Let’s add the user interface command.

Select the project on the start page of application generator and click Design.

Right-click Orders / Actions / ag1 (Grid) action group node on the Controllers tab in the Project Explorer and select New Action.

Creating a new action in 'Grid' scope in a Code On Time web application

Enter the following values in the action properties and click OK button.

Property Value
Command Name SQL
Header Text Standard Discount
Data
update "Order Details"
set Discount = 0
where 
    OrderID = @OrderID and Discount = 0.1
    
update "Order Details"
set Discount = cast(Discount as decimal(3,2)) + 0.01
where 
    OrderID = @OrderID and Discount < 0.1
Confirmation The discount will be increased by 1%.

Click Browse on the Project Designer toolbar, navigate to the Orders page, select an existing order and choose Standard Discount option in the context menu of the order data row.

Discounting order line items with 'SQL' action

Confirm the action and the standard discount of 1% will be added to each line item that have a discount less than 10%. If you keep discounting the order details, then the discount will revert to 1% after reaching 10%.

Data view Order Details is shown after execution of SQL action Standard Discount four times.  The standard discount of 4% has been assigned to all line items that have the discount less than 10%.

Data view 'Order Details' after execution of SQL action 'Standard Discount' defined in 'Orders' data controller.  The standard discount of 4% has been assigned to all line items that have the discount less than 10% by selecting 'Standard Discount' option four times.

You can affect multiple rows with the same definition of SQL action if you enable multiple selection in the Orders data view.

First, add a new action to the action group Orders / Actions / ag4 (ActionBar) – Edit/Delete.

Adding a new action to a 'flat' action group in a Code On Time web application

Set its properties as follows and click OK button.

Property Value
Command Name SQL
Header Text Discount
Data
update "Order Details"
set Discount = 0
where 
    OrderID = @OrderID and Discount = 0.1
    
update "Order Details"
set Discount = cast(Discount as decimal(3,2)) + 0.01
where 
    OrderID = @OrderID and Discount < 0.1
Confirmation The discount will be increased by 1% in all selected orders.
When Client Script
this.get_selectedValues().length > 0

The JavaScript expression in the When Client Script property is evaluated at runtime in the web browser to enable the Discount option on the action bar. The Discount option becomes visible when at least one item is selected.

Note that the SQL script is identical to the script of action placed in the group with Grid scope. You may want to consider creating a stored procedure and specifying an expression in the Data property of both actions similar to the the one shown below:

execute sp_IncreaseOrderDiscount @OrderID

Next, enable multiple data row selection mode for Customers / Orders / container1 / view1 (Orders, grid1) data view node in Project Designer.

A master data view selected in Project Explorer

Property New Value
Selection Mode Multiple

Browse to the order page of the Northwind sample application and select multiple orders.

Click Discount option on the action bar to apply a discount to the details of selected orders.

A custom 'SQL' action on the action bar of an app created with Code On Time web application generator

The application framework is aware of multiple selection and will perform one SQL action for each selected primary key value. 

Remember that application end users always have an option to go over each line item to rapidly change the Discount in the Data Sheet view by hand.

Data Sheet view in a Code On Time web application