Code On Time: Business Rules/Logic

Blog

Business Rules/Logic

Wednesday, January 2, 2013Print Subscribe

Duplicating a Record with SQL Business Rule

The default Duplicate action available in Code On Time web apps performs a client-side duplication. When the action is activated, the create form will be opened and the fields will be populated using values from the duplicated record. The new record will only be saved to the database when the user presses OK.

Sometimes it is necessary for more complex operations to be performed when a record is duplicated. For example, when a master record is duplicated, the child records should be duplicated as well. This will require a server-side duplication. A custom action will be implemented – when the user activates this action, the master and detail records will be copied and saved to the server. The order will then be displayed in the edit form.

Let’s implement a master-detail duplication with Orders and Order Details controllers in the Northwind database.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Actions / ag1 (Grid) node, and press New Action.

Assign the following values:

Property	Value
Command Name	Custom
Command Argument	DuplicateOrder
Header Text	Duplicate Order
Confirmation	Are you sure you want to duplicate this order?

Press OK to save the action. Right-click on Orders / Business Rules node, and press New Business Rule.

Use the following:

Property	Value
Type	SQL
Command Name	Custom
Command Argument	DuplicateOrder
Phase	Execute

In the Script property, enter the following:

-- duplicate the order
insert into Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, 
                    ShippedDate, ShipVia, Freight, ShipName, ShipAddress, 
                    ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
select CustomerID, EmployeeID, getdate(), RequiredDate, null, ShipVia, 
        Freight, ShipName, ShipAddress, ShipCity, ShipRegion, 
        ShipPostalCode, ShipCountry
from Orders
where OrderID = @OrderID

-- find ID of duplicated order
declare @NewOrderID int;
select @NewOrderID = @@IDENTITY;

-- duplicate order details
insert into "Order Details" (OrderID, ProductID, UnitPrice, 
                            Quantity, Discount)
select @NewOrderID, ProductID, UnitPrice, Quantity, Discount
from "Order Details"
where OrderID = @OrderID

-- open edit form for duplicate order
set @Result_NavigateUrl = 'Orders.aspx?OrderID=' + cast(@NewOrderID as nvarchar)
                          + '&_controller=Orders&_commandName=Edit'
                          + '&_commandArgument=editForm1'

The business rule will first duplicate the order. The OrderDate will be assigned the current date, and RequiredDate will be set to null. Then, the ID of the new order will be acquired using the @@IDENTITY function. The order details will be duplicated using the new ID. Finally, the client library will be prompted to navigate to open the new order using URL parameters. Press OK to save the business rule. On the toolbar, press Browse.

Navigate to the Orders page. Using the context menu on a row, activate the Duplicate Order option.

The order and associated order details will be duplicated, and it will be displayed in the edit form.

Labels: Business Rules/Logic, Master/Detail, User Interface, Web Application Generator

Sunday, December 30, 2012Print Subscribe

Changing Values on Edit

Client-side business rules allow changing the value of a field without having to save it to the database. This way, the user can either approve the value, change it, or leave the original data intact.

Let’s add 7 days to the Order Date of an order when a user starts editing.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Business Rules node, and press New Business Rule.

Assign the following values:

Property	Value
Type	JavaScript
Command Name	Edit
Phase	After
Script	var d = [OrderDate]; if (this.dataView().get_isEditing() && d != null) { d.setDate(d.getUTCDate() + 7); d.setHours(0); [OrderDate] = d; }

The business rule will get the current date value of OrderDate, add 7 days, and assign the value.

Press OK to save. On the toolbar, press Browse. Navigate to the Orders page. Note the Order Date of a specific record.

Start editing the the record. The Order Date will be moved forward by 7 days from the original value.

If the user presses Cancel at this time, the Order Date will not be changed. If the user saves the record, the new value will be applied.

Labels: Application Builder, Business Rules/Logic, Designer, Web Application Generator

Saturday, December 29, 2012Print Subscribe

On-Demand Handler Property

The Products table in the Northwind database includes a foreign key reference to CategoryID column of Categories table. Code On Time generator adds the CategoryName field in the Products controller through automatic denormalization. In addition to the CategoryName field, let’s denormalize the Picture field in order to include it in the Products grid.

Start the web application generator. Click on the project name, and select Settings. Click on Business Logic Layer option. Switch to the Denormalization Map section, and paste in the following:

dbo.Products => dbo.Categories
Picture

Press Finish to skip to the Summary page, and then press Refresh. Check the box next to Products controller, and continue to refresh the project.

On the Summary page, click on Design to bring up the Project Designer.

In the Project Explorer, switch to the Controllers tab. Double-click on Products / Fields / CategoryPicture (Byte[], read-only) field node.

Change the following values:

Property	New Value
On Demand Handler	CategoriesPicture

This will prompt the application framework to populate the field using the same handler that populates the Picture field in Categories controller. Press OK to save the field.

Drop Products / Fields / CategoryPicture (Byte[], read-only) field node onto Products / Views / grid1.

On the toolbar, press Browse. Navigate to the Products page. The Category Picture column will now be populated with images.

Labels: Application Builder, Business Rules/Logic, Web Application Generator