Sunday, October 28, 2012
Order Form Sample–Part 22

The Subtotal field is now present in the application. The field does not reflect changes when new order items are entered. We will add a business rule to the Orders controller to calculate the subtotal.

In the Project Explorer, switch to Controllers tab. Double-click on Orders / Fields / Subtotal node.

Subtotal field node under Orders controller in the Project Explorer.

Change the following properties:

Property New Value
The value of this field is calculated by a business rule expression true
Context Fields OrderDetails

Press OK to save.

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

New Business Rule context menu option for Orders controller.

The business rule can be implemented using SQL or C# / Visual Basic.

SQL Business Rule

Assign these values:

Property Value
Type SQL
Command Name Calculate
Phase Execute
Script
select @Subtotal = sum(unitprice * quantity * (1 - discount)) 
from [Order Details] 
where OrderID = @OrderID

Press OK to save the business rule.

Code Business Rule

Assign the following values:

Property Value
Type C# / Visual Basic
Command Name Calculate
Phase Execute

Press OK to save the business rule.

On the toolbar, press Browse. The business rule placeholder file will be created.

Right-click on Orders / Business Rules / Calculate (Code / Execute) – r101 node, and press Edit Rule in Visual Studio.

Edit Rule in Visual Studio context menu option for a code business rule.

The rule file will open in Visual Studio. Replace the body of the rule with the following code:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {
        
        [Rule("r101")]
        public void r101Implementation(
                    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, 
                    decimal? subtotal)
        {
            using (SqlText calc = new SqlText(@"select sum(unitprice * 
                    quantity * (1 - discount)) from 
                    [Order Details] where OrderID= @OrderID"))
            {
                calc.AddParameter("@OrderID", orderID);
                object total = calc.ExecuteScalar();
                if (DBNull.Value.Equals(total))
                    UpdateFieldValue("Subtotal", 0);
                else
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total));
            }

        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <Rule("r101")> _
        Public Sub r101Implementation( _
                    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, _
                    ByVal subtotal As Nullable(Of Decimal))
            Using calc As SqlText = New SqlText(
                    "select sum(unitprice * quantity * (1 - discount)) " +
                    "from [Order Details] where OrderID=@OrderID")
                calc.AddParameter("@OrderID", orderID)
                Dim total As Object = calc.ExecuteScalar()
                If DBNull.Value.Equals(total) Then
                    UpdateFieldValue("Subtotal", 0)
                Else
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total))
                End If
            End Using

        End Sub
    End Class
End Namespace

This function uses SqlText utility class to create an instance of a query connected to the project’s database.
This simple query selects a sum of UnitPrice multiplied by Quantity multiplied by one minus the
Discount.

Note that SqlText utility class is generated as a part of the code base of your application. It uses the
default database connection string and ADO.NET to execute the query.

Viewing the Results

On the toolbar, press Browse. Navigate to the Order Form page, and select an order. Note the value of Subtotal field.

Orders form with a subtotal value.

Change one of the values in an order detail. The Subtotal will be updated to reflect the changes.

If one of the order details is changed, the subtotal will be updated to reflect the changes.