Business Rules/Logic

A web application data controller can define sophisticated SQL Business Rules that are automatically engaged during the lifecycle of an application. The rules are written in the SQL dialect supported by the database engine (T-SQL, PL-SQL, etc).

SQL Business Rules can interact with the database tier, application server tier, and client web browser tier of a web app.

If you know SQL, then you are already an expert in creating SQL Business Rules.

This is the example of SQL Business Rule performing validation written in T/SQL (Microsoft SQL Server).

-- validate the data field value
if @Country = 'USA'
begin
 -- tell the application server to skip the execution of update, insert, or delete
 set @BusinessRules_PreventDefault = 1
 -- set the focus to the field "Country" and display an error message
 set @Result_Focus = 
    'Country, You are not authorized to ' +  lower(@Arguments_CommandName) + 
    ', if the Country is equal to "USA".'
-- show an additional information in the message bar at the top of the page
 set @Result_ShowMessage = 
    'Error trying to execute ' + @Arguments_CommandName + ' command.'
end

The screenshot demonstrates the business rule in action.

Take a look at the other example of SQL Business Rules.

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.

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