Blog: Posts from September, 2012

When the Allow Nulls property on a field is marked as false, the client library will prevent the update or insertion of a blank value.

The application generator will set Allow Nulls to “false” for all fields based on mandatory table columns when the baseline application is constructed.

For example, the Employees table does not allow null values in First Name field, but does allow null values for Title field. An asterisk (*) is placed next to the field name to indicate required fields.

If the value is cleared in First Name field and user attempts to save the record, the client library will prevent the update and display a message next to the field.

Let’s allow nulls in First Name and prevent nulls in Title.

Start the Project Designer. Switch to the controllers tab in the Project Explorer and double-click on Employees / Fields / FirstName field node.

Enable Allow Nulls:

Press OK to save the field. Notice that the asterisk (*) next to the FirstName field node has been removed to indicate that the field is no longer required.

Double-click on Employees / Fields / Title field node.

Mark the field as required:

Press OK to save. On the Project Designer toolbar, press Browse.

Navigate to the Employees page and edit the details of a record. The First Name field is not marked as required, but the Title is.

Clear the values from both First Name and Title fields, and try to save the record by pressing OK. A message will be displayed next to the Title field.

Enter a value in Title and save again. This time the action will be passed to the server. However, the FirstName column in Employees table is still marked as required in the database. An error message will be raised by the database engine and displayed at the top of the page. Changes will not be persisted.

Let’s implement a custom action that will apply a discount to all line items of a specific order. Orders are stored in the table Orders, and line items are stored in Order Details.

Adding a Custom Action

First, an action needs to be defined in the action group of a data controller.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Actions / ag4 (ActionBar) – Edit/Delete action group node, and press New Action.

Assign the following values:

Press OK to save the action.

The Confirmation property references a data controller called “OrderDiscount”. This data controller does not exist in the project – we will create this confirmation data controller from scratch and have it configured to collect the Discount value from the user. The data controller will not be based on any database table or view.

Creating the Confirmation Controller

On the Project Explorer toolbar, press the New Controller icon.

Give this controller a name:

Press OK to save.

Right-click on OrderDiscount / Fields node, and press New Field.

Assign the following values:

True

Press OK to save.

Add another field with these values:

Save the field.

Generate the app and select an order. On the action bar, press the Apply a Discount action.

A modal form with two empty fields, Customer Company Name and Discount, will be displayed.

Let’s populate Customer Company Name with the name of the company associated with the selected order and initialize Discount with the average discount of the order details.

In the Project Explorer, right-click on OrderDiscount / Business Rules node, and press New Business Rule.

Assign the following values:

set @CustomerCompanyName = @Context_CustomerCompanyName

select @Discount = avg(Discount) 
from "Order Details"
where OrderID = @Context_OrderID

Press OK to save the business rule.

The parameter @CustomerCompanyName refers to the CustomerCompanyName field of the confirmation data controller.

The parameter @Context_CustomerCompanyName refers to the CustomerCompanyName field of the Orders data controller.

The application framework will pass the script for execution to the database engine when the action Custom / ApplyDiscount is activated by the user. The result is shown in the next screenshot.

If the user clicks OK, nothing will happen. The application does not know what to do with a “Custom” action with argument of “ApplyDiscount”.

Creating an SQL Business Rule to Handle the Action

Now that an action and controller are in place to capture the user input, a business rule needs to be created to apply the specified discount to all OrderDetails data rows associated with the selected order.

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

Use these values:

-- apply discount to order details
update "Order Details"
set Discount = @Parameters_Discount
where OrderID = @OrderID

-- force refresh of child views
set @Result_RefreshChildren = 1

Press OK to save.

The first statement in the SQL script will update [Order Details].[Discount] column where the OrderID matches the selected order. The value of the discount is referenced by @Parameters_Discount parameter.

The second statement instructs the client library to refresh the child data views of the master data view Orders. This will cause Order Details data view to reflect the updated discount.

On the Project Designer toolbar, press Browse. Navigate to the Orders page and select an order. A list of related order details will be displayed below. Take note of the discounts of the order details.

On the action bar of Orders grid view, press Apply a Discount. The confirmation modal popup will appear, displaying the current Customer Company Name and the average discount. Enter a discount of “.25”.

Press OK, and the specified discount will be applied to all records in Order Details table that belong to the selected order.

Creating “Code” Business Rule to Handle the Action

Instead of using SQL, you may also create a code business rule written in C# or Visual Basic to handle the calculation.

If you have implemented the previous SQL business rule, you will need to delete it.

Right-click on Orders / Business Rules / Custom, ApplyDiscount (Sql / Execute) - r100 business rule node and press Delete.

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

Assign these values:

Save the rule. “Code” business rules do not have a script stored in the data controller definition file. A code file must be created in the project. The application generator will create an initial “empty” business rule code file as soon as the project is generated.

On the Project Designer toolbar, press Generate.

When complete, right-click on Orders / Business Rules / Custom, ApplyDiscount (Code / Execute) – r100 node and press Edit Rule in Visual Studio.

The file will be opened in Visual Studio. The generator has created a template for the business rule. Replace the existing code with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {

        /// <summary>
        /// This method will execute in any view for an action
        /// with a command name that matches "Custom" and argument that matches "ApplyDiscount".
        /// </summary>
        [Rule("r100")]
        public void r100Implementation(
                    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)
        {
            // This is the placeholder for method implementation.
            using (SqlText applyDiscount = new SqlText(
                "update [Order Details] " +
                "set Discount = @Discount " +
                "where OrderID = @OrderID"))
            {
                applyDiscount.AddParameter("@Discount", SelectFieldValue("Parameters_Discount"));
                applyDiscount.AddParameter("@OrderID", orderID);
                applyDiscount.ExecuteNonQuery();
            }
            Result.RefreshChildren();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports MyCompany.Rules

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        ''' <summary>
        ''' This method will execute in any view for an action
        ''' with a command name that matches "Custom" and argument that matches "ApplyDiscount".
        ''' </summary>
        <Rule("r100")> _
        Public Sub r100Implementation( _
                    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)
            'This is the placeholder for method implementation.
            Using applyDiscount As SqlText = New SqlText(
                    "update [Order Details] " +
                    "set Discount = @Discount " +
                    "where OrderID = @OrderID"
                    )
                applyDiscount.AddParameter("@Discount", SelectFieldValue("Parameters_Discount"))
                applyDiscount.AddParameter("@OrderID", orderID)
                applyDiscount.ExecuteNonQuery()
            End Using
            Result.RefreshChildren()
        End Sub
    End Class
End Namespace

Save the file, and refresh the web page. The Assign a Discount action will function in exactly the same way as the version with the SQL business rule.