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:
| 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.
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.
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.
