Business Rules/Logic

A web app created with Code On Time may function as an application server for a client on any platform.

Let’s create a native Windows Forms application that will allow browsing and searching products available at http://demo.codeontime.com/Northwind/Pages/Products.aspx.

This is how a Quick Find search is performed by the web app. The native Windows client will provide a similar presentation and search facility.

Click on the following link and you will see a response in XML format from the REST service of the web application:
http://demo.codeontime.com/northwind/appservices/Products?_q=40%20biscuit

The response will look similar to this sample.

<?xml version="1.0" encoding="utf-8"?>
<Products totalRowCount="2" pageSize="100" pageIndex="0" rowCount="2">
  <items>
    <item ProductName="Sir Rodney's Marmalade" SupplierID="8" CategoryID="3" 
          QuantityPerUnit="30 gift boxes" UnitPrice="$81.00" UnitsInStock="40" 
          UnitsOnOrder="0" ReorderLevel="0" Discontinued="False" ProductID="20" 
          SupplierCompanyName="Specialty Biscuits, Ltd." 
          CategoryCategoryName="Confections" />
    <item ProductName="Sir Rodney's Scones" SupplierID="8" CategoryID="3" 
          QuantityPerUnit="24 pkgs. x 4 pieces" UnitPrice="$10.00" UnitsInStock="3" 
          UnitsOnOrder="40" ReorderLevel="5" Discontinued="False" ProductID="21" 
          SupplierCompanyName="Specialty Biscuits, Ltd." 
          CategoryCategoryName="Confections" />
  </items>
</Products>

The “_q” parameter represents the Quick Find argument of the web resource. If the parameter is not specified, then the first 100 items are returned. Other parameters for sorting, paging, and filtering are also supported.

The native Windows client of the Northwind application server will construct a similar URL to retrieve data for presentation.

Start Visual Studio and select File | New Project option. Choose Windows Forms Application under Visual C# or Visual Basic template group, enter “ProductBrowser” in the name, and click OK to create project files.

Drop TextBox, Button, and ListView components from toolbox on the design surface of Form1 and have them arranged as shown next.

Use Properties Window to modify the Columns collection of listView1. Create five columns Product Name, Supplier Company Name, Category Name, Quantity per Unit, and Unit Price to capture a few properties of products.

Also set the following properties of listView1 component:

The list view is configured to behave as a virtual view.

If the property VirtualListSize is set to a value other than zero then the virtual list view will request items from the application as needed.

The list view will continuously request items representing a visible subset of data rows as a user scrolls the list.

Right-click Form1 node in the Solution Explorer and select View Code option in the context menu.

Add the highlighted namespaces to the top of code file:

C#:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Xml.XPath;
using System.IO;

namespace ProductBrowser
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    }
}

Visual Basic:

Imports System.Net
Imports System.Xml.XPath
Imports System.IO

Public Class Form1

End Class

The namespaces will link required .NET library components used by Windows native client to communicate with the web app.

Add fields _dataPage, _query, _cache, and method GetDataPage to the definition of class Form1.

C#:

protected XPathNavigator _dataPage;
protected string _query;

private SortedDictionary<int, XPathNavigator> _cache = new SortedDictionary<int, XPathNavigator>();

protected XPathNavigator GetDataPage(int pageIndex)
{
    XPathNavigator result = null;
    if (!_cache.TryGetValue(pageIndex, out result))
    {
        Cursor = Cursors.WaitCursor;
        try
        {
            WebRequest request = WebRequest.Create(String.Format(
                "http://demo.codeontime.com/northwind" +
                "/appservices/Products?_pageIndex={0}&_pageSize=10&_q={1}",
                pageIndex, _query));
            request.Method = "GET";
            using (WebResponse response = request.GetResponse())
            {
                using (Stream stream = response.GetResponseStream())
                    result = new XPathDocument(stream)
                        .CreateNavigator().SelectSingleNode("/Products");
            }
            _cache.Add(pageIndex, result);
        }
        finally
        {
            Cursor = Cursors.Default;
        }
    }
    return result;
}

Visual Basic:

Protected _dataPage As XPathNavigator
Protected _query As String

Private _cache As SortedDictionary(Of Integer, XPathNavigator) =
    New SortedDictionary(Of Integer, XPathNavigator)

Protected Function GetDataPage(pageIndex As Integer) As XPathNavigator
    Dim result As XPathNavigator = Nothing
    If Not (_cache.TryGetValue(pageIndex, result)) Then
        Cursor = Cursors.WaitCursor
        Try
            Dim request As WebRequest = WebRequest.Create(String.Format(
                "http://demo.codeontime.com/northwind" +
                "/appservices/Products?_pageIndex={0}&_pageSize=10&_q={1}",
                pageIndex, _query))
            request.Method = "GET"
            Using response As WebResponse = request.GetResponse()
                Using stream As Stream = response.GetResponseStream()
                    result = New XPathDocument(stream).
                        CreateNavigator().SelectSingleNode("/Products")
                    _cache.Add(pageIndex, result)
                End Using
            End Using
        Finally
            Cursor = Cursors.Default
        End Try
    End If
    Return result
End Function

The implementation of method GetDataPage creates a web request and retrieves the page of data from the web app if the page has not been found in the dictionary of cached data pages. The method parses the XML response received from the web app and stores an instance of XPathNavigator class initialized with a page data in the _cache field. The web request URI incorporates the requested page index and search criteria in _pageIndex and _q parameters accordingly.

Select listView1 in form designer and activate Properties Window, choose Events button, and double click RetrieveVirtualItem event.

Enter the following definition of the method listView1_RetrieveVirtualItem as shown next.

C#:

private void listView1_RetrieveVirtualItem(object sender, RetrieveVirtualItemEventArgs e)
{
    // analyze the current page item range
    int pageIndex = Convert.ToInt32(_dataPage.GetAttribute("pageIndex", String.Empty));
    int pageSize = Convert.ToInt32(_dataPage.GetAttribute("pageSize", String.Empty));
    int rowCount = Convert.ToInt32(_dataPage.GetAttribute("rowCount", String.Empty));
    int firstAvailableRowIndex = pageIndex * pageSize;
    int lastAvailableRowIndex = firstAvailableRowIndex + rowCount - 1;
    // if the current page does not contain the item then retrieve the page from the app
    if (!(firstAvailableRowIndex <= e.ItemIndex && e.ItemIndex <= lastAvailableRowIndex))
    {
        pageIndex = e.ItemIndex / pageSize;
        firstAvailableRowIndex = pageIndex * pageSize;
        _dataPage = GetDataPage(pageIndex);
    }
    // create a virtual list item
    XPathNavigator dataRow = _dataPage.SelectSingleNode(
        String.Format("items/item[{0}]", e.ItemIndex - firstAvailableRowIndex + 1));
    e.Item = new ListViewItem(dataRow.GetAttribute("ProductName", String.Empty));
    e.Item.SubItems.Add(dataRow.GetAttribute("SupplierCompanyName", String.Empty));
    e.Item.SubItems.Add(dataRow.GetAttribute("CategoryCategoryName", String.Empty));
    e.Item.SubItems.Add(dataRow.GetAttribute("QuantityPerUnit", String.Empty));
    e.Item.SubItems.Add(dataRow.GetAttribute("UnitPrice", String.Empty));
}

Visual Basic:

Private Sub listView1_RetrieveVirtualItem(sender As System.Object,
        e As System.Windows.Forms.RetrieveVirtualItemEventArgs) Handles listView1.RetrieveVirtualItem
    ' analyze the current page item range
    Dim pageIndex As Integer = Convert.ToInt32(_dataPage.GetAttribute("pageIndex", String.Empty))
    Dim pageSize As Integer = Convert.ToInt32(_dataPage.GetAttribute("pageSize", String.Empty))
    Dim rowCount As Integer = Convert.ToInt32(_dataPage.GetAttribute("rowCount", String.Empty))
    Dim firstAvailableRowIndex As Integer = pageIndex * pageSize
    Dim lastAvailableRowIndex As Integer = firstAvailableRowIndex + rowCount - 1
    ' if the current page does not contain the item then retrieve the page from the app
    If Not (firstAvailableRowIndex <= e.ItemIndex And e.ItemIndex <= lastAvailableRowIndex) Then
        pageIndex = e.ItemIndex \ pageSize
        firstAvailableRowIndex = pageIndex * pageSize
       _dataPage = GetDataPage(pageIndex)
    End If
    ' create a virtual list item
    Dim dataRow As XPathNavigator = _dataPage.SelectSingleNode(
        String.Format("items/item[{0}]", e.ItemIndex - firstAvailableRowIndex + 1))
    e.Item = New ListViewItem(dataRow.GetAttribute("ProductName", String.Empty))
    e.Item.SubItems.Add(dataRow.GetAttribute("SupplierCompanyName", String.Empty))
    e.Item.SubItems.Add(dataRow.GetAttribute("CategoryCategoryName", String.Empty))
    e.Item.SubItems.Add(dataRow.GetAttribute("QuantityPerUnit", String.Empty))
    e.Item.SubItems.Add(dataRow.GetAttribute("UnitPrice", String.Empty))
End Sub

Consult the sample of XML response at the top of article to understand the method implementation.

The implementation makes an assumption that a page of XML data is stored in the _dataPage instance of XPathNavigator. The property e.ItemIndex specifies the index of the item that the virtual list view needs. The code tries to determine if the current data page contains the item. If the item is not in current page _dataPage, then the application will retrieve a page of data from the web app by invoking GetDataPage method. The method will either find the page in the cache or reach out to the web app with a web request for data.

Next, the event handler will create an instance of a ListViewItem. Its sub-items represent the values displayed in the columns of the list view. The instance of the ListViewItem is assigned to e.Item property.

At this point the entire mechanism of populating the list view with data has been implemented.

The only remaining step is to collect user input for the search criteria and to assign a value to the VirtualListSize property of listView1.

Activate the design surface of Form1 and double-click the Find button. This will create an event handler that shall be defines as explained below.

C#:

private void button1_Click(object sender, EventArgs e)
{
    // assign the search criteria
    _query = textBox1.Text;
    // remove any previously cached data pages
    _cache.Clear();
    // get the first page of data with index "zero"
    _dataPage = GetDataPage(0);
    // figure the total number of rows in the virtual listview
    int totalRowCount = Convert.ToInt32(_dataPage.GetAttribute("totalRowCount", String.Empty));
    listView1.VirtualListSize = totalRowCount;
}

Visual Basic:

Private Sub button1_Click(sender As System.Object, e As System.EventArgs) Handles button1.Click
    ' assign the search criteria
    _query = textBox1.Text
    ' remove any previously cached data pages
    _cache.Clear()
    ' get the first page of data with index "zero"
    _dataPage = GetDataPage(0)
    ' figure the total number of rows in the virtual listview
    Dim totalRowCount As Integer = Convert.ToInt32(_dataPage.GetAttribute("totalRowCount", String.Empty))
    listView1.VirtualListSize = totalRowCount
End Sub

The implementation of button1_Click method copies the search criteria from the text box in the field _query.  Then it clears the previously cached data pages and obtains the first page of data. Last, the method will figure the size of the virtual list.

In less than a hundred lines of code a remarkable functionality has been brought to life.

Press F5 to start the Northwind Product Browser for Windows. Click the Find button.

Try scrolling the list of products. The Windows client will make at the most 8 requests to retrieve the 77 products from the Northwind database sample. The page size in this implementation is deliberately set to 10 items to ensure that a client-server interaction does occur upon scrolling.  If the page size is not specified then the default page size of 100 will be used.

Enter “40 biscuit” in the text box textBox1 and hit the button one more time. The two products matching the search criteria will be retrieved. Compare the result to the web app screenshot at the top.

The same technique of data paging can be used in any application that supports XML or JSON data formats. That includes Java applications and mobile native Android and iPhone/iPad apps.

Let’s implement a custom action that will apply a discount to all line items of a specific order. Orders are stored in the table Orders, and line items are stored in Order Details.

Adding a Custom Action

First, an action needs to be defined in the action group of a data controller.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Actions / ag4 (ActionBar) – Edit/Delete action group node, and press New Action.

Assign the following values:

Press OK to save the action.

The Confirmation property references a data controller called “OrderDiscount”. This data controller does not exist in the project – we will create this confirmation data controller from scratch and have it configured to collect the Discount value from the user. The data controller will not be based on any database table or view.

Creating the Confirmation Controller

On the Project Explorer toolbar, press the New Controller icon.

Give this controller a name:

Press OK to save.

Right-click on OrderDiscount / Fields node, and press New Field.

Assign the following values:

True

Press OK to save.

Add another field with these values:

Save the field.

Generate the app and select an order. On the action bar, press the Apply a Discount action.

A modal form with two empty fields, Customer Company Name and Discount, will be displayed.

Let’s populate Customer Company Name with the name of the company associated with the selected order and initialize Discount with the average discount of the order details.

In the Project Explorer, right-click on OrderDiscount / Business Rules node, and press New Business Rule.

Assign the following values:

set @CustomerCompanyName = @Context_CustomerCompanyName

select @Discount = avg(Discount) 
from "Order Details"
where OrderID = @Context_OrderID

Press OK to save the business rule.

The parameter @CustomerCompanyName refers to the CustomerCompanyName field of the confirmation data controller.

The parameter @Context_CustomerCompanyName refers to the CustomerCompanyName field of the Orders data controller.

The application framework will pass the script for execution to the database engine when the action Custom / ApplyDiscount is activated by the user. The result is shown in the next screenshot.

If the user clicks OK, nothing will happen. The application does not know what to do with a “Custom” action with argument of “ApplyDiscount”.

Creating an SQL Business Rule to Handle the Action

Now that an action and controller are in place to capture the user input, a business rule needs to be created to apply the specified discount to all OrderDetails data rows associated with the selected order.

Right-click on Orders / Business Rules node, and press New Business Rule.

Use these values:

-- apply discount to order details
update "Order Details"
set Discount = @Parameters_Discount
where OrderID = @OrderID

-- force refresh of child views
set @Result_RefreshChildren = 1

Press OK to save.

The first statement in the SQL script will update [Order Details].[Discount] column where the OrderID matches the selected order. The value of the discount is referenced by @Parameters_Discount parameter.

The second statement instructs the client library to refresh the child data views of the master data view Orders. This will cause Order Details data view to reflect the updated discount.

On the Project Designer toolbar, press Browse. Navigate to the Orders page and select an order. A list of related order details will be displayed below. Take note of the discounts of the order details.

On the action bar of Orders grid view, press Apply a Discount. The confirmation modal popup will appear, displaying the current Customer Company Name and the average discount. Enter a discount of “.25”.

Press OK, and the specified discount will be applied to all records in Order Details table that belong to the selected order.

Creating “Code” Business Rule to Handle the Action

Instead of using SQL, you may also create a code business rule written in C# or Visual Basic to handle the calculation.

If you have implemented the previous SQL business rule, you will need to delete it.

Right-click on Orders / Business Rules / Custom, ApplyDiscount (Sql / Execute) - r100 business rule node and press Delete.

Right-click on Orders / Business Rules node and press New Business Rule.

Assign these values:

Save the rule. “Code” business rules do not have a script stored in the data controller definition file. A code file must be created in the project. The application generator will create an initial “empty” business rule code file as soon as the project is generated.

On the Project Designer toolbar, press Generate.

When complete, right-click on Orders / Business Rules / Custom, ApplyDiscount (Code / Execute) – r100 node and press Edit Rule in Visual Studio.

The file will be opened in Visual Studio. The generator has created a template for the business rule. Replace the existing code with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {

        /// <summary>
        /// This method will execute in any view for an action
        /// with a command name that matches "Custom" and argument that matches "ApplyDiscount".
        /// </summary>
        [Rule("r100")]
        public void r100Implementation(
                    int? orderID,
                    string customerID,
                    string customerCompanyName,
                    int? employeeID,
                    string employeeLastName,
                    DateTime? orderDate,
                    DateTime? requiredDate,
                    DateTime? shippedDate,
                    int? shipVia,
                    string shipViaCompanyName,
                    decimal? freight,
                    string shipName,
                    string shipAddress,
                    string shipCity,
                    string shipRegion,
                    string shipPostalCode,
                    string shipCountry)
        {
            // This is the placeholder for method implementation.
            using (SqlText applyDiscount = new SqlText(
                "update [Order Details] " +
                "set Discount = @Discount " +
                "where OrderID = @OrderID"))
            {
                applyDiscount.AddParameter("@Discount", SelectFieldValue("Parameters_Discount"));
                applyDiscount.AddParameter("@OrderID", orderID);
                applyDiscount.ExecuteNonQuery();
            }
            Result.RefreshChildren();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports MyCompany.Rules

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        ''' <summary>
        ''' This method will execute in any view for an action
        ''' with a command name that matches "Custom" and argument that matches "ApplyDiscount".
        ''' </summary>
        <Rule("r100")> _
        Public Sub r100Implementation( _
                    ByVal orderID As Nullable(Of Integer), _
                    ByVal customerID As String, _
                    ByVal customerCompanyName As String, _
                    ByVal employeeID As Nullable(Of Integer), _
                    ByVal employeeLastName As String, _
                    ByVal orderDate As Nullable(Of DateTime), _
                    ByVal requiredDate As Nullable(Of DateTime), _
                    ByVal shippedDate As Nullable(Of DateTime), _
                    ByVal shipVia As Nullable(Of Integer), _
                    ByVal shipViaCompanyName As String, _
                    ByVal freight As Nullable(Of Decimal), _
                    ByVal shipName As String, _
                    ByVal shipAddress As String, _
                    ByVal shipCity As String, _
                    ByVal shipRegion As String, _
                    ByVal shipPostalCode As String, _
                    ByVal shipCountry As String)
            'This is the placeholder for method implementation.
            Using applyDiscount As SqlText = New SqlText(
                    "update [Order Details] " +
                    "set Discount = @Discount " +
                    "where OrderID = @OrderID"
                    )
                applyDiscount.AddParameter("@Discount", SelectFieldValue("Parameters_Discount"))
                applyDiscount.AddParameter("@OrderID", orderID)
                applyDiscount.ExecuteNonQuery()
            End Using
            Result.RefreshChildren()
        End Sub
    End Class
End Namespace

Save the file, and refresh the web page. The Assign a Discount action will function in exactly the same way as the version with the SQL business rule.