Business Rules/Logic

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Business Rules/Logic
Sunday, November 4, 2012PrintSubscribe
Minimal Membership Provider for SQL Server

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.

A user table with "identity" primary key allows keeping tracking of user names and passwords

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.

A user table with "unique identifier" primary key allows keeping tracking of user names and passwords

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.

Tuesday, October 30, 2012PrintSubscribe
Order Form Sample–Part 25

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.

Freight field node selected in Orders controller of the Project Explorer.

Change the Context Fields:

Property New Value
Context Fields OrderDetails,Freight

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.

Calculate SQL Business Rule in Orders controller of Project Explorer.

Append the script:

Property New Value
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.

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

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.

Calculate JavaScript Business Rule in Orders controller of Project Explorer.

Append the following:

Property New Value
Script
[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.

When Freight is cleared and Total is below $100, freight is calculated to be $3.95.

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

When Freight is cleared and Total is above $100, freight is calculated to be $19.95.

Tuesday, October 30, 2012PrintSubscribe
Order Form Sample–Part 23

Let’s add a Total field to the Orders controller. This field will add Subtotal and Freight together to show the final cost of the whole order.

Adding the Field

In the Project Explorer, right-click on Orders / Fields node. Press New Field.

New Field context menu option for Orders data controller.

Give this new field the following settings:

Property Value
Name Total
Type Currency
The value of this field is computed at run-time by SQL Expression true
SQL Formula
(select sum(UnitPrice*Quantity*(1-Discount)) 
from "Order Details" 
where "Order Details".OrderID = Orders.OrderID) + Orders.Freight
The value of this field is calculated by a business rule expression true
Label Total
Values of this field cannot be edited True
Data Format String c
Context Fields OrderDetails, Freight

Press OK to save this field.

Binding the Field to Views

Drop Orders / Fields / Total* (Currency, read-only) field node onto Orders / Views / grid1 node.

Dropping Total field node onto 'grid1' view.     Total data field created in view 'grid1'.

Drop Total field node onto Orders / Views / editForm1 view node.

Dropping Total field node onto 'editForm1' view.     Total data field created in view 'editForm1'.

SQL Business Rule

If you used an SQL Business Rule to handle Subtotal, double-click on Orders / Business Rules / Calculate (Sql / Execute) node.

Calculate SQL Business Rule in Orders controller.

Append the following highlighted line to the Script property:

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

set @Total = @Subtotal + @Freight

Code Business Rule

If you are using C# or Visual Basic to handle the Subtotal field, right-click on Orders / Business Rules and press Edit Rule in Visual Studio.

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

Make the following changes to the code base:

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);
                    UpdateFieldValue("Total", 0);
                }
                else
                {
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total));
                    UpdateFieldValue("Total", Convert.ToDecimal(total) + freight);
                }
            }
        }
    }
}

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)
                    UpdateFieldValue("Total", 0)
                Else
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total))
                    UpdateFieldValue("Total", Convert.ToDecimal(total) + freight)
                End If
            End Using
        End Sub
    End Class
End Namespace

JavaScript Business Rule

You can also implement a JavaScript business rule that will use the returned value of Subtotal and sum it with Freight to calculate the value of Total.

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

New Business Rule context menu option for Orders controller.

Assign the following values:

Property Value
Type JavaScript
Command Name Calculate
Phase After
Script
[Total] = [Subtotal] + [Freight];

Viewing the Results

On the toolbar, press Browse to regenerate the application. Navigate to the Order Form page, and select any order. You will see a Total field that sums up Subtotal and Freight.

Total field present in the Orders edit form.

When any of the order detail records are changed, the Subtotal and Total will be updated.

Total field updated when order details change.