Code On Time: Business Rules/Logic

Blog

Business Rules/Logic

Saturday, May 12, 2012Print Subscribe

Data validation is the single most important component of any data-driven application.

Modern multi-tier web applications introduce a certain level of complexity when it comes to validation. The data travels between multiple tiers: a web browser, application server, and database. Validation can be performed on any application tier and requires a different skill level:

Web Browser tier validation is performed with JavaScript code.
Application Server tier validation is performed with C#/Visual Basic.
Database tier validation is performed with the dialect of SQL supported by the database engine.

Code On Time web applications offer a unique mechanism called SQL Business Rules. These rules exist on the application server tier, are executed by the database engine tier, and affect presentation in the web browser client tier.

Consider the following script written in T-SQL.

-- debug
declare @UnitPrice money                      -- data field value
declare @BusinessRules_PreventDefault bit     -- application tier property
declare @Arguments_CommandName nvarchar(50)   -- application tier property
declare @Result_Focus nvarchar(255)           -- client tier property
declare @Result_ShowViewMessage nvarchar(255) -- client tier property

set @UnitPrice = 10
set @Arguments_CommandName = 'Update'
-- end debug

-- lookup database to find the average price of products
declare @AverageUnitPrice money 
select @AverageUnitPrice = avg(UnitPrice) 
from Products

-- perform validation
if @UnitPrice is not null and @UnitPrice > @AverageUnitPrice
begin
 set @BusinessRules_PreventDefault = 1
 set @Result_Focus = 
    'UnitPrice, You are not authorized to enter ' + 
    'a price higher than the average of ' + cast(@AverageUnitPrice as nvarchar) + '.'
 set @Result_ShowViewMessage = 'Error trying to execute "' + @Arguments_CommandName + 
    '" command.'
end

-- debug
if @BusinessRules_PreventDefault = 1
begin
    print 'BusinessRules_PreventDefault: ' + cast(@BusinessRules_PreventDefault as nvarchar)
    print 'Result_Focus: ' + @Result_Focus
    print 'Result_ShowViewMessage: ' + @Result_ShowViewMessage
end
-- end debug

The script is compatible with the Northwind sample database.

If you execute the script in SQL Management Studio, then the following output will be displayed in the Messages window.

BusinessRules_PreventDefault: 1
Result_Focus: UnitPrice, You are not authorized to enter a price higher
than the average of 28.87.
Result_ShowViewMessage: Error trying to execute "Update" command.

Replace the value of “30” assigned to the Unit Price with “10”, execute the script, and there will be no messages in the output. The validation test has passed.

Start the Project Designer and right-click Products / Business Rules node on the Controllers tab in Project Explorer. Select New Business Rule option.

Enter the following properties and click OK button to save the business rule.

Property	Value
Command Name	Insert\|Update
Type	SQL
Phase	Before
Script	Paste the entire script from above

The hierarchy of the data controller will look as follows.

Click Browse on the designer toolbar. The application will be generated and open in a default web browser. Navigate to Products page and try editing or creating a product with Unit Price higher than $28.87.

The application server tier will not perform the requested Insert or Update if the parameter @BusinessRules_PreventDefault is set to “1” by the business rule.

This is how the error messages are displayed on the client web browser tier when a violation is detected in the form view.

These are the error messages displayed when entering a new product in the data sheet view.

The application framework automatically removes the debug / end debug sections in the SQL Business Rule script. From the database engine prospective, the script looks like this:

-- lookup database to find the average price of products
declare @AverageUnitPrice money 
select @AverageUnitPrice = avg(UnitPrice) 
from Products

-- perform validation
if @UnitPrice is not null and @UnitPrice > @AverageUnitPrice
begin
 set @BusinessRules_PreventDefault = 1
 set @Result_Focus = 
    'UnitPrice, You are not authorized to enter ' + 
    'a price higher than the average of ' + cast(@AverageUnitPrice as nvarchar) + '.'
 set @Result_ShowViewMessage = 'Error trying to execute "' + @Arguments_CommandName + '" command.'
end

Labels: Application Factory, Business Rules/Logic, Web Application Generator, Workflow

Friday, May 11, 2012Print Subscribe

Activating a Stored Procedure With a Custom Search Bar

Application users can input parameters of a stored procedure or web service in a modal confirmation search dialog. This approach works best if a custom result set may be produced with default parameter values. Application automatically prepopulates the output cache table. Users can adjust parameter values on demand by activating Search action with a modal confirmation.

For example, an application can use a stored procedure or a web service to produce the most recent data records without asking a user for an input. A user may need to review the historical records and will activate the search action on their own.

If the application users are always searching for data, then a more natural method of providing the input parameters for the output caching script is via an inline search bar.

Right-click the Region / Cust Order Hist Cache / container1 node in Project Explorer and choose New Data View option.

Enter the following properties and press OK button to create the new data view.

Property	Value
Controller	CustOrderHist_Params
Sequence	1
Show Action Bar	No
Show Action Buttons	None
Startup Action Command Name	New

Select Region / Cust Order Hist Cache / container1 / view1 (CustOrderHist_Cache, grid1) data view and change its properties as shown next.

Property	New Value
Sequence	2

Save the changes. The Project Explorer tree will look as follows.

The data controller CustOrderHist_Cache has already been configured to use the confirmation data controller CustOrderHist_Params when Search action is activated. The client library of application framework will detect the inline confirmation data controller instance and will use its values as parameters of the Search action.

Click Browse on the Project Designer toolbar and navigate to Cust Order Hist Cache page.

The startup action of the dv100 data view has its Startup Action Command Name set to New. This allows selecting a customer without any extra effort. The data view is also configured to hide the action bar and the form view action buttons.

Select a customer and click on the search activator icon. The enhanced output caching script will detect the parameter value and use it to cache the output produced by the stored procedure CustOrderHist.

Application users can perform Quick Find, adaptive filtering, paging and sorting of the search result without causing any additional stored procedure activity. The output caching script will automatically detect if the data has been cached for longer than 30 seconds and call the stored procedure to refresh the cached custom record set.

The data view dv100 (CustOrderHistCache_Params) #1 is configured to have no action buttons at runtime when forms are rendered. If you set the value of its Show Action Buttons property to Bottom, then the presentation of the form view will change.

Buttons OK and Cancel are the actions Insert when New and Cancel when New. The actions are automatically created by the application framework since there are no action groups explicitly defined in the data controller CustOrderHist_Params.

Right-click the Region / Cust Order Hist Cache / container1 / dv100 (CustOrderHist_Params) #1 / Actions node in Project Explorer and choose New Action Group option.

Create a new action group with the scope of “Form”.

Add an action to the action group ag100 (Form) with the following properties.

Property	Value
Command Name	Search
When Last Command Name	New

The Actions node of the data view dv100 (CustOrderHist_Params) #1 will look as in the next screenshot.

Expand the node view1 (CustOrderHist_Cache, grid1) #2 and locate the action node a100 – Search .

Change its properties as follows.

Property	New Value
Confirmation	_controller=CustOrderHist_Params _shortcut=false

Note that you can also delete the _shortcut parameter. Its default value is false.

Generate the application and navigate to Cust Order Hist Cache page. You will immediately notice the Search button below the parameter form. The Search action shortcut on the left of the Quick Find is gone. It has been replaced with the standard Advanced Search Bar activator.

The Search action defined in CustOrderHist_Params data controller does not have a confirmation. If the action is activated, then the client library of the application will contact every data view on the page with instruction to perform search. The data views will perform search if they do have an action that uses CustOrderHist_Params as a confirmation.

The Search action without a confirmation broadcasts the need to perform a search to all “interested” parties.

Now that the shortcut is hidden, it is possible for end users to take advantage of the advanced search bar to further refine the custom result set produced by the stored procedure CustOrderHist.

Labels: Business Rules/Logic, Data Sources, Web Application Generator

Thursday, May 10, 2012Print Subscribe

Activating a Stored Procedure With a Custom Search Dialog

Let’s create a confirmation data controller to allow selecting a Customer ID. The value will be passed to the enhanced output caching script as a parameter.

Click on the Home link in the bread crumbs displayed under the toolbar at the top of the Project Designer. Select Controllers tab and choose New | New Controller option on the action bar.

Enter the following properties and click OK button to create the controller.

Property	Value
Controller Name	CustOrderHist_Params

Right-click CustOrderHist_Params / Fields node in Project Explorer and select New Field option.

Enter the new field properties as follows.

Property	Value
Name	CustomerID
Type	String
Length	5
Label	Customer
Items Style	Lookup
Data Value Field	CustomerID
Data Text Field	CompanyName
Lookup window description	Select a customer
Search by First Letter	Yes

Click OK button to create the field.

The field node in the Project Explorer will look as follows.

Now it is time to define a Search action in the output cache controller CustOrderHist_Cache . The action will display the confirmation data controller CustOrderHist_Params to a user and cause the CustOrderHist_Cache controller to refresh the data with the collected parameter CustomerID.

Right-click the CustOrderHist_Cache / Actions / ag5 (ActionBar) – Actions node in Project Explorer and select the New Action option.

Enter the properties of the action and click OK button to save the changes.

Property	Value
Command Name	Search
Confirmation	_controller=CustOrderHist_Params _width=500 _title=Select a Customer to View History _shortcut=true

Click Browse on the Project Designer toolbar and navigate to Cust Order Hist Cache page.

The new action can be accessed under Actions option of the action bar.

Notice that the Search action has replaced the standard search bar activator icon on the left of the Quick Find on the action bar. The icon will activate the Search action.

If you click the refresh icon in the bottom right corner of the grid view then this will also activate the first available Search action.

Activate the Search action with any of three methods listed above. The application framework will create a modal confirmation data controller.

Select a customer in the lookup window.

Click OK button in “Select a Customer to View History” modal popup. This will confirm the Search action and will the pass the selected Customer ID to the output caching script.

The enhanced output caching script will detect that the parameter @Parameters_CustomerID is not null and will use its value as a filter.

Labels: Business Rules/Logic, Data Sources, Web Application Generator