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.
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.
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.
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%.
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.
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.
| 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.
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.
