Blog: Posts from October, 2012

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
Posts from October, 2012
Sunday, October 28, 2012PrintSubscribe
Order Form Sample–Part 22

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.

Subtotal field node under Orders controller in the Project Explorer.

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.

New Business Rule context menu option for Orders controller.

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.

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

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.

Orders form with a subtotal value.

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

If one of the order details is changed, the subtotal will be updated to reflect the changes.

Friday, October 26, 2012PrintSubscribe
Order Form Sample–Part 21

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.

New Field context menu option for Orders data controller.

The new field’s settings will be:

Property Value
Field Name Subtotal
Type Currency
The value of this field is computed at run-time by SQL Expression True
SQL
select sum(UnitPrice*Quantity*(1-Discount)) 
from "Order Details"
where "Order Details".OrderID = Orders.OrderID
Label Subtotal
Values of this field cannot be edited True
Data Format String c

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.

Simplified statement of 'Subtotal' SQL Formula calculation in SQL Management Studio

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.

Dropping Subtotal field node onto 'grid1' view node.     Subtotal data field node has been created in 'grid1'.

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

Dropping Subtotal field node onto 'editForm1' view node.     Subtotal data field node has been created in 'editForm1'.

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

Dropping Subtotal field node onto 'createForm1' view node.    Subtotal data field node has been created in 'createForm1'.

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

Subtotal data field is present in editForm1 of Orders.

Friday, October 26, 2012PrintSubscribe
Order Form Sample–Part 20

The Order Details grid view displays several unnecessary reference fields, such as Order Customer Company Name, Order Employee Last Name, and Order Ship Via Company Name. We will get rid of these redundant fields. Let’s also display the average Unit Price, total Quantity, average Discount, and total sum of Extended Price at the bottom of the grid.

Order Form details for a selected order. The order details grid view below contains redundant fields.

In the Project Explorer, expand to Order Form / c100 / dv101 / grid1 node. Highlight the data fields OrderCustomerCompanyName, OrderEmployeeLastName, and OrderShipViaCompanyName. Right-click and press Delete.

Deleting three data fields from 'grid1' view of OrderDetails controller.

Now that the redundant data fields have been removed, let’s assign aggregates. Double-click on Order Details / Views / grid1 / UnitPrice.

UnitPrice data field node in 'grid1' view of OrderDetails controller.

Change the Aggregate Function property:

Property New Value
Aggregate Function Average

Press OK to save the data field.

Next, double-click on Quantity data field.

Quantity data field node in 'grid1' view of OrderDetails controller.

Change the following value:

Property New Value
Aggregate Function Sum

Save the data field, and double-click on Discount.

Discount data field node in 'grid1' view of OrderDetails controller.

Change Aggregate Function to “Average”.

Property New Value
Aggregate Function Average

Press OK, and double-click on ExtendedPrice.

ExtendedPrice data field node in 'grid1' view of OrderDetails controller.

Change Aggregate Function to “Sum”.

Property New Value
Aggregate Function Sum

Press OK to save Extended Price data field.

Generate the application, and navigate to Order Form page. Select any order. You will see that the list of order details will not display redundant order information, and displays aggregates at the bottom of the grid.

Order Form page with details of an order displayed. The order details grid view now displays less redundant data fields as well as several averages and sums.