Blog: Posts from October, 2012

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.

Change the following properties:

Press OK to save.

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

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

SQL Business Rule

Assign these values:

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:

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.

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.

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

Let’s add a Subtotal field to the order. This field will sum up the extended prices of all order details.

In the Project Explorer, switch to Controllers tab. Right-click on Orders / Fields, and press New Field.

The new field’s settings will be:

select sum(UnitPrice*Quantity*(1-Discount)) 
from "Order Details"
where "Order Details".OrderID = Orders.OrderID

Press OK to save the field.

Your application will incorporate this SQL Formula in the SELECT statements composed at runtime. This statement will look similar to:

select *, 
   (
   select sum(UnitPrice*Quantity*(1-Discount)) from "Order Details"
   where "Order Details".OrderID = Orders.OrderID
   ) Subtotal
from 
   "Orders" Orders

This is the output produced when the statement is executed in SQL Management Studio. The actual statement will look more complex, and may include user-defined sorting, filtering, and paging parameters.

We’ll need to bind this field to views, so that the end user can see the field value.

Drop Orders / Fields / Subtotal field node onto Orders / Views / grid1 node.

Drop Subtotal field node onto Orders / Views / editForm1 node.

Drop Subtotal field node onto Orders / Views / createForm1 view node.

Regenerate the application, and select any order on the Order Form page. You will see the Subtotal field showing the sum of extended prices.