Business Rules/Logic

Requirements

A minimal membership provider requires a dedicated table to keep track of user names and passwords.

This is a sample “Users” table with “identity” primary key.

SQL:

create table Users
(
     UserID int not null identity primary key,
     UserName nvarchar(128) not null,
     "Password" nvarchar(128) not null
)

Here is how the table may look with a “unique identifier” primary key.

SQL:

create table Users
(
    UserID uniqueidentifier not null default newid() primary key,
    UserName nvarchar(128) not null,
    "Password" nvarchar(128) not null
)

User roles are hardcoded in the minimal Role Provider implementation.

Configuration

Create a table in your database using one of  the scripts specified above.

Select the project name on the start page of the application generator and choose Settings.

Proceed to Authentication and Membership.

Select “Enable custom membership and role providers” option and enter the following configuration settings.

table Users=Users
column [int|uiid] UserID = UserId
column [text] UserName = UserName
column [text] Password = Password

role Administrators = admin
role Users = admin, user
role Everybody = *

The configuration maps logical table Users required for membership provider implementation to the physical database table Users. It also defines three user roles – Administrators, Users, and Everybody.

Generate the project.

The calculation will analyze Order ID and current Freight value:

If the order total is greater than $100, then Freight will be $19.95 flat.
Otherwise, Freight is $3.95.

The Order Form page will allow overriding the Freight value.

In the Project Explorer, double-click on Orders / Fields / Freight (Decimal) field node.

Change the Context Fields:

Press OK to save.

If you implemented an SQL Business Rule to calculate Subtotal and Total, continue reading the next section. If you implemented the C# / Visual Basic Business rule, skip the next section. You may also use JavaScript – skip the next two sections.

SQL Business Rule

In the Project Explorer, double-click on Orders / Business Rules / Calculate (Sql / Execute) node.

Append the script:

select @Subtotal = sum(unitprice * quantity * (1 - discount)) 
from [Order Details] 
where OrderID = @OrderID

set @Total = @Subtotal + @Freight

if (@Freight is not null or @Freight = 0 
    or @Freight = 3.95 or @Freight = 19.95)
begin
    if (@Total > 100)
        set @Freight = 19.95
    else 
        set @Freight = 3.95
end

Press OK to save.

Code Business Rule

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

Append 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)
        {
            object total = null;
            using (SqlText calc = new SqlText(@"select sum(unitprice * 
                    quantity * (1 - discount)) from 
                    [Order Details] where OrderID= @OrderID"))
            {
                calc.AddParameter("@OrderID", orderID);
                total = calc.ExecuteScalar();
            }

            if (DBNull.Value.Equals(total))
            {
                UpdateFieldValue("Subtotal", 0);
                UpdateFieldValue("Total", 0);
            }
            else
            {
                UpdateFieldValue("Subtotal", Convert.ToDecimal(total));
                UpdateFieldValue("Total", Convert.ToDecimal(total) + freight);
            }

            if (!freight.HasValue || freight.Value == 0 ||
            freight.Value == 3.95m || freight.Value == 19.95m)
                if (Convert.ToDecimal(total) > 100)
                    UpdateFieldValue("Freight", 19.95);
                else
                    UpdateFieldValue("Freight", 3.95);
        }
    }
}

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))
            Dim total As Object = Nothing
            Using calc As SqlText = New SqlText(
                    "select sum(unitprice * quantity * (1 - discount)) " +
                    "from [Order Details] where OrderID=@OrderID")
                calc.AddParameter("@OrderID", orderID)
                total = calc.ExecuteScalar()
            End Using
            If DBNull.Value.Equals(total) Then
                UpdateFieldValue("Subtotal", 0)
                UpdateFieldValue("Total", 0)
            Else
                UpdateFieldValue("Subtotal", Convert.ToDecimal(total))
                UpdateFieldValue("Total", Convert.ToDecimal(total) + freight)
            End If
            If Not freight.HasValue Or freight.Value = 0 Or
                freight.Value = 3.95 Or freight.Value = 19.95 Then
                If total >= 100 Then
                    UpdateFieldValue("Freight", 19.95)
                Else
                    UpdateFieldValue("Freight", 3.95)
                End If
            End If
        End Sub
    End Class
End Namespace

Save the file.

JavaScript Business Rule

Double-click on Orders / Business Rules / Calculate (JavaScript / After) node.

Append the following:

[Total] = [Subtotal] + [Freight];


if ([Freight] || [Freight] == 0 || 
    [Freight] == 3.95 || [Freight] == 19.95)
{
    if ([Total] > 100)
    {
        [Freight] = 19.95;
    }
    else 
    {
        [Freight] = 3.95;
    }
}

Press OK to save.

Viewing the Results

On the toolbar, press Browse. Edit an order, and change the Freight to “0”. When you tab away from the field, the freight will be recalculated.

If you change the order details so that the Subtotal is greater than $100, you will see Freight be recalculated to $19.95.