Business Rules/Logic

Project requirements may dictate that conversion should be performed on field values.

For example, the Customer# field of the Customers table in the Northwind database may need to be converted to uppercase.

However, there is no mechanism preventing users from saving lowercase letters in the field.

Let’s create an SQL Business Rule to perform field value conversion.

Validate on Insert or Update

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

Use the following properties:

set @CustomerID = UPPER(@CustomerID)

Press OK to save the business rule. On the toolbar, press Browse.

Navigate to the Customers page, and edit a record. Insert some mixed-case letters in the Customer# field.

Save the record. The field value will be converted to uppercase.

Validate on Calculate

You can also perform just-in-time value conversion, when the user moves focus away from the field.

Switch back to the Project Designer. In the Project Explorer, double-click on Customers / Business Rule / Insert|Update node.

Change the Command Name property:

Press OK to save the business rule. In order for the calculation to be performed, the field needs to cause a server request.

In the Project Explorer, double-click on Customers / Fields / CustomerID field node.

Make the following changes:

Press OK to save the field. On the toolbar, press Browse.

On the Customers page, edit a record. Insert mixed-case characters into the Customer# field.

Press Tab on your keyboard or click on another field. The client library will convert the value in Customer# field into uppercase.

Expanding the Business Rule

The user may still click on the OK button and save the lowercase characters in the field. Therefore, you may need to combine both Insert|Update and Calculate command handling. The following Command Name will cover all possible methods of changing the field.

The same business logic can be implemented with the help of C#/Visual Basic Business Rules.

The property Context Fields can pass values from the current record to the lookup data view. The value is passed in the format LookupFieldName=FieldNameOfThisView as an external filter. Multiple value mappings can be specified.

If LookupFieldName matches a data field in the lookup view, then an automatic “equals” filter will be applied to the lookup. If the LookupFieldName does not match, then the application framework will not perform filtering. A developer can use the passed external filter field value to create a filter expression or business rule implementing custom filtering.

Let’s create a business rule for a lookup view that takes advantage of values passed in the Context Fields property.

Navigate to the Orders page, and select an order. Create a new order detail, and activate the lookup for ProductID. The Northwind database has 77 products. All products will be available for selection in the Products lookup.

Let’s exclude products already associated with order details of the existing order from this view.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Products controller node.

Change the Handler property:

Press OK to save the controller. Double-click on OrderDetails / Fields / ProductID field node.

Change the Context Fields property:

Press OK to save the field. On the toolbar, press Exit to close the Project Designer, and click Generate.

When complete, click on the project name, and select Develop to open Visual Studio.

In the Solution Explorer of Visual Studio, double-click on ~\App_Code\Rules\ProductsBusinessRules.cs(.vb) file.

Replace the existing code with the following business rule:

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class ProductsBusinessRules : MyCompany.Data.BusinessRules
    {
        protected override void EnumerateDynamicAccessControlRules(string Products)
        {
            FieldValue orderId = SelectExternalFilterFieldValueObject(
                "ExistingOrderID");
            if (orderId != null && orderId.Value != null)
                RegisterAccessControlRule("ProductID",
                    "[ProductID] in (select ProductID from [Order Details] " +
                    "where OrderID = @OrderID)",
                    AccessPermission.Deny,
                    new SqlParam("@OrderID", orderId.Value));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class ProductsBusinessRules
        Inherits MyCompany.Data.BusinessRules
        Protected Overrides Sub EnumerateDynamicAccessControlRules(Products As String)
            Dim orderId As Object = SelectExternalFilterFieldValueObject(
                "ExistingOrderID")
            If (orderId IsNot Nothing AndAlso orderId.Value IsNot Nothing) Then
                RegisterAccessControlRule("ProductID",
                    "[ProductID] in (select ProductID from [Order Details] " +
                    "where OrderID = @OrderID)",
                    AccessPermission.Deny,
                    New SqlParam("@OrderID", orderId.Value))
            End If
        End Sub
    End Class
End Namespace

The business rule tries to locate the external filter field ExistingOrderID. If it is found, and the value is not null, then the business rule will register an access control rule. The access control rule will deny access to products that are matched to the “Order Details”.“ProductID” column if the column OrderID is equal to the value passed in ExistingOrderID filter field.

Save the file, and run the web application.

Navigate to the Orders page, and select an order. Note the number of order details belonging to the order.

Create a new order detail. Click on (select) link in the Product Name lookup.

The Product lookup will open. There will be no products that are already ordered. In the example below, there are only 74 products displayed out of 77 products in the database.