Saturday, March 17, 2012

Total and Subtotal: Business Rule for Subtotal

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 and add a code expression for Subtotal. This code expression will use the rule to calculate the subtotal.

In the Project Explorer, switch to Controllers tab. Double-click on the Orders controller. In the Handler field, type:

OrdersBusinessRules

Adding 'OrdersBusinessRules' Handler to 'Orders' Controller

Press OK to save the controller, exit the Designer, and Generate the project. The business rule placeholder file will be created.

When generation is complete, click on the project name in the web application generator and press Develop. Microsoft Visual Studio or Visual Web Developer will open. In the Solution Explorer on the right side of the screen, double-click on App_Code /Rules / OrdersBusinessRules.cs(vb).

OrdersBusinessRules.cs Rule file in OrderForm project

Paste in the following code:

C#:

using MyCompany.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {
        public decimal CalculateOrderDetailsTotal(int? orderID)
        {
            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))
                    return 0;
                else
                    return Convert.ToDecimal(total);
            }
        }
    }
}

VB:

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

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules
        Public Function CalculateOrderDetailsTotal(
                            ByRef orderID As Nullable(Of Integer)) As 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
                    Return 0
                Else
                    Return Convert.ToDecimal(total)
                End If
            End Using
        End Function
    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.

Save the file, and switch back to the application generator. Select the project name, and press Design. In the Project Explorer, switch to Controllers. Double-click on Orders / Fields / Subtotal. Make the following changes:

Property Text
The value of this field is calculated by a business rule expression True
Code Formula
CalculateOrderDetailsTotal(orderID)
Context Fields OrderDetails

Code Formula for 'Subtotal' field
'Context Fields' property of Orders / Fields / Subtotal

Press OK to save the field. The Subtotal field on the Order Form will now update to reflect any changes made to the order details.

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.


© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at http://codeontime.com