ASP.NET Membership

Blog
ASP.NET Membership
Saturday, August 16, 2014PrintSubscribe
Sending Emails with SQL Business Rules

Email business rules offer a simple and effective mechanism of creating email notifications for various workflow tasks. For example, a simple notification can be sent out when a new customer account is created. Another example is a notification with attached PDF reports triggered by an update of a product category record.

An email business rule is a static text-based template with placeholders matched to a data controller action command name and execution phase. The template may also include XML-based attachment instructions. A single email notification is generated by application framework when an email business rule is matched to a command activated by application user. Template placeholders are replaced with the field values of the affected data row. If the multiple selection mode is enabled, then a separate notification is generated for each selected data row. Here is an email business rule that produces a notification with two attachments for Categories controller.

An email business rule selected in Project Designer of Code OnTime app generator.

The text of the email business rule script template is shown next. The data value placeholders are highlighted.

Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true

From: "Sales Admin" <your_email_address@gmail.com>
To: RECIPIENT@northwind.com
Subject: Category "{CategoryName}" has changed!

Dear Admin,

This product category has been changed.
See attachment for category product details.

System Monitor

<attachment type="report">
    <name>{CategoryName}</name>
    <controller>Categories</controller>
    <view>editForm1</view>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value>{CategoryID}</value>
        </item>
    </filter>
</attachment>
<attachment type="report">
    <name>{CategoryName} Products</name>
    <controller>Products</controller>
    <sortExpression>UnitPrice desc</sortExpression>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value type="Int32">{CategoryID}</value>
        </item>
    </filter>
</attachment>

If a specific notification cannot be expressed with a static text template, then consider using SQL business rules to compose an email by writing notification text in the programming language of the database engine.  Database programming languages, such as T-SQL of Microsoft SQL Server, offer enough flexibility to compose a notification of any complexity.

SQL business rules are text-based scripts executed by the database engine. The scripts may include references to data controller fields, properties of BusinessRules class associated with the data controller, URL arguments, and session variables. Developers reference any of these resources as parameters that do not have to be explicitly declared in the scripts.

Application framework binds these “pre-defined” parameters to the corresponding resources, executes the script, collects the output parameter values, and ignores any datasets that may have been produced when the script was executed.

A special mechanism exists in the application framework to force it to pay attention to the output of the SQL business rule script. The developer must supply two business rules that are matched to the same command. The first business rule triggers the “awareness” mode of the application framework. The second business rule produces a dataset that will be captured by the framework. Application framework copies the output data to an instance of System.Data.DataTable class and assigns it to BusinessRules.EmailMessages property. The property implementation iterates over the data table rows and treats each row as a notification that must be sent out. The column names of the table, such as “Port”, “To”, “From”, “Subject”, and “Body” allow the framework to compose correct email messages. The message “awareness” mode is automatically turned off when the last email has been sent.

Here is an example of data controller with two SQL business rules that will generate an email with report attachments when a product category has been updated.

A data controller with two SQL business rules that will generate email notifcations in Touch UI application created with Code On Time.

The first SQL business rule is very simple

Property Value
Type SQL
Command Name Update
Phase After
Script
set @BusinessRules_EnableEmailMessages = 1

The script of the this rule assigns value True to the BusinessRules.EnableEmailMessages property of Boolean type and does nothing else. The implementation of the script may do any other type of processing if needed.

The mode of email message awareness is activated now!

The second SQL business rule will produce a singe data row. The script has a “debug” section that declares parameter values used for testing. Application framework will remove this section at runtime  before asking the database server to execute the script. The highlighted fragments are the output column names.

Property Value
Type SQL
Command Name Update
Phase After
Script
-- debug
declare @CategoryID int
declare @CategoryName nvarchar(5)
select @CategoryName = 'Confections', @CategoryID = 1
-- end debug

declare @CrLf as varchar(2)
select @CrLf = char(13)+ char(10)

select 
    -- from 
    'YOUR_EMAIL_ADDRESS@gmail.com' "From",
    -- to
    'RECIPIENT@nothwind.com' "To",
    -- subject
    'Category "' + @CategoryName + '" has changed' "Subject",
    -- body
    'Dear Admin' + @CrLf + @CrLf +
    'This product category has been changed.' + @CrLf +
    'See attachment for category product details.' + @CrLf + @CrLf +
    'System Monitor' + @CrLf  +
    -- attachment 1
    '<attachment type="report">' + 
    '<name>' + @CategoryName + '</name>' +
    '<controller>Categories</controller>' + 
    '<view>editForm1</view>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value>' + cast(@CategoryID as varchar) + '</value>' +
        '</item>' +
    '</filter>' +
    '</attachment>' +    
    -- attachment 2
    '<attachment type="report">' + 
    '<name>Products in ' + @CategoryName + '</name>' +
    '<controller>Products</controller>' + 
    '<sortExpression>UnitPrice desc</sortExpression>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value type="Int32">' + 
cast(@CategoryID as varchar) +
'</value>' + '</item>' + '</filter>' + '</attachment>' "Body"

Note that STMP parameters, such as Port and Password are omitted from the script. Application framework will use default SMTP settings from the application configuration file. If any of these parameters are needed to be in the message definition, then make sure that the values are included as the corresponding column names (for example, Host, UserName, etc.)

This particular implementation uses @CrLf parameter to inject end-of-line characters in the message. The entire message body is a single concatenated string of text. A real-world implementation may compose the text by iterating through cursors and executing multiple select statements.

If an HTML output is desired then the corresponding tags should be included in the “Body”.

Developers can return any number of “messages” in the output when needed. This may be accomplished by creating a temporary database table and populating it with the messages. The contents of the temporary table are selected when the job is done. Another alternative is to use UNION of several SELECT statements to send a fixed number of messages at once.

This is a sample email notification produced by SQL business rules from the example above.

An email notification with two report attachments produced by Touch UI app created with Code On Time.

Sunday, July 14, 2013PrintSubscribe
Watermark Property

A common practice when developing web applications is to display placeholder text in a field to indicate the expected input to the user. Let’s implement a watermark on the Order Details create form of a sample Northwind web app.

Start the Project Designer. In the Project Explorer, double-click on Customers / Order Details / container1 / view1 (OrderDetails, grid1) / createForm1 / c1 – New Order Details / UnitPrice data field node.

UnitPrice data field in createForm1 of OrderDetails controller.

Make the following change:

Property Value
Columns 30
Watermark Please enter a price per unit for this order.

Save the field. On the toolbar, press Browse.

Navigate to the Order Details page, and create a new record. Note that the watermark is displayed in the Unit Price field.

Watermark text is displayed inside the Unit Price field.

When a user starts entering a value in the field, the watermark text disappears.

The watermark text disappears when the user enters a value.

Wednesday, July 10, 2013PrintSubscribe
Validation with Code Business Rules

Data integrity is a primary concern of any database application. Data constraints must exist on the database and application server level, while client-side validation will make a web app more responsive and user-friendly.

For example, consider the following screenshot of the New Order Details form. End users can select any product and enter any price, quantity, and discount. The database engine will validate the constraints of the table Order Details and raise an exception when invalid data is submitted.

The database will prevent saving of the record if the table constraints are violated.

Let’s implement a code business rule that will perform more complex validation for Order Details controller.

Configuring Fields

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on OrderDetails / Fields / ProductID* (Int32) –> Products node.

ProductID field of OrderDetails controller.

Change the following:

Property Value
Copy UnitPrice=UnitPrice

Press OK to save. Next, double-click on UnitPrice* (Decimal) field node.

UnitPrice field of OrderDetails controller.

Make the following changes:

Property Value
The value of this field is calculated by a business rule expression. true
Context Fields ProductID, UnitPrice, Quantity, Discount

Press OK to save.

Configuring Business Rule

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

Creating a new business rule for OrderDetails controller.

Assign the following values:

Property Value
Type C# / Visual Basic
Command Name Calculate|Insert|Update
Phase Execute

Press OK to save. On the toolbar, press Browse to generate the business rule file.

When complete, right-click on OrderDetails / Business Rules / Calculate|Insert|Update (Code / Execute) – r100 node, and press Edit Rule in Visual Studio.

Editing the rule in Visual Studio.

Replace the code base with the following:

C#:

using System;
using System.Data;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrderDetailsBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r100")]
        public void r100Implementation(int? orderID,
            string orderCustomerID,
            string orderCustomerCompanyName,
            string orderEmployeeLastName,
            string orderShipViaCompanyName,
            FieldValue productID,
            string productProductName,
            string productCategoryCategoryName,
            string productSupplierCompanyName,
            FieldValue unitPrice,
            short? quantity,
            float? discount)
        {
            // 1. If the collected values are not valid then do not enforce the rule.
            //    The client library will instruct the user to correct the input.
            bool success = this.ValidateInput();
            string commandName = this.Arguments.CommandName;
            string triggerFieldName = this.Arguments.Trigger;

            // 2. Reset the base price for calculation of price limits 
            //    if the product selection has changed or if an existing
            //    data row has been selected for editing
            FieldValue oldProductID = (FieldValue)this.GetProperty("Session_ProductID"); 
            if (oldProductID == null || productID.Value.ToString() != oldProductID.Value.ToString())
            {
                decimal basePrice = Convert.ToDecimal(unitPrice.Value);
                if (!triggerFieldName.Equals("ProductID"))
                    basePrice = Convert.ToDecimal(unitPrice.OldValue);
                this.SetProperty("Session_UnitPrice", basePrice);
                this.SetProperty("Session_ProductID", productID);
                if (triggerFieldName.Equals("ProductID"))
                {
                    quantity = 1;
                    UpdateFieldValue("Quantity", quantity);
                    discount = 0;
                    UpdateFieldValue("Discount", discount);
                    Result.Focus("Quantity");
                }
            }
            // 3. Adjusting base price for an existing record
            decimal originalUnitPrice = (decimal)this.GetProperty("Session_UnitPrice");
            if (originalUnitPrice == null || originalUnitPrice == 0)
            {
                originalUnitPrice = Convert.ToDecimal(unitPrice.OldValue);
                this.SetProperty("Session_UnitPrice", originalUnitPrice);
            }
            // 4. validate Unit Price field
            if (success)
            {
                decimal minPrice = originalUnitPrice;
                decimal maxPrice = originalUnitPrice * (decimal)1.05;
                success = unitPrice != null;
                if (!success)
                    Result.Focus("UnitPrice", "Please enter the price.");
                else
                {
                    decimal price = Convert.ToDecimal(unitPrice.Value);
                    success = minPrice <= price && price <= maxPrice;
                    if (!success)
                        Result.Focus("UnitPrice", "This price must be between {0:c} and {1:c}", 
                            minPrice, maxPrice);
                }
            }
            // 5. validate Quantity field
            if (success)
            {
                success = quantity != null && quantity > 0;
                if (!success)
                    Result.Focus("Quantity", "The quantity must be greater than zero.");
            }
            // Validate Discount field
            if (success)
            {
                // 6. If value > 1, then convert value to percentage
                if (discount > 1)
                {
                    discount = discount / 100;
                    UpdateFieldValue("Discount", discount);
                }
                // 7. Confirm that Discount is between 0.00 and 0.99
                success = discount != null && discount >= 0.00 && discount <= 0.99;
                if (!success)
                    Result.Focus("Discount", 
                        "The discount must be between 0.00 and 0.99 (0% - 99%).");
            }
            // 8. Wrapping Up
            if (commandName == "Calculate" || !success)
                this.PreventDefault();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Data

Namespace MyCompany.Rules
    
    Partial Public Class OrderDetailsBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r100")> _
        Public Sub r100Implementation(ByVal orderID As Nullable(Of Integer),
                                      ByVal orderCustomerID As String,
                                      ByVal orderCustomerCompanyName As String,
                                      ByVal orderEmployeeLastName As String,
                                      ByVal orderShipViaCompanyName As String,
                                      ByVal productID As Nullable(Of Integer),
                                      ByVal productProductName As String,
                                      ByVal productCategoryCategoryName As String,
                                      ByVal productSupplierCompanyName As String,
                                      ByVal unitPrice As FieldValue,
                                      ByVal quantity As Nullable(Of Short),
                                      ByVal discount As Nullable(Of Single))
            ' 1. If the collected values are not valid then do not enforce the rule.
            '    The client library will instruct the user to correct the input.
            Dim success As Boolean = Me.ValidateInput()
            Dim commandName As String = Me.Arguments.CommandName
            Dim triggerFieldName As String = Me.Arguments.Trigger

            ' 2. Reset the base price for calculation of price limits 
            '    if the product selection has changed or if an existing
            '    data row has been selected for editing
            If Me.GetProperty("Session_ProductID") Is Nothing OrElse
                productID.Value.ToString() <> Me.GetProperty("Session_ProductID").ToString() Then
                Dim basePrice As Decimal = Convert.ToDecimal(unitPrice.Value)
                If Not triggerFieldName.Equals("ProductID") Then
                    basePrice = Convert.ToDecimal(unitPrice.OldValue)
                End If
                Me.SetProperty("Session_UnitPrice", basePrice)
                Me.SetProperty("Session_ProductID", productID)
                If triggerFieldName.Equals("ProductID") Then
                    quantity = 1
                    UpdateFieldValue("Quantity", quantity)
                    discount = 0
                    UpdateFieldValue("Discount", discount)
                    Result.Focus("Quantity")
                End If
            End If
            ' 3. Adjusting base price for an existing record
            Dim originalUnitPrice As Nullable(Of Decimal) =
                CDec(Me.GetProperty("Session_UnitPrice"))
            If originalUnitPrice Is Nothing OrElse originalUnitPrice = 0 Then
                originalUnitPrice = Convert.ToDecimal(unitPrice.OldValue)
                Me.SetProperty("Session_UnitPrice", originalUnitPrice)
            End If
            ' 4. validate Unit Price field
            If success Then
                Dim minPrice As Decimal = originalUnitPrice
                Dim maxPrice As Decimal = originalUnitPrice * CDec(1.05)
                success = unitPrice IsNot Nothing
                If Not success Then
                    Result.Focus("UnitPrice", "Please enter the price.")
                Else
                    Dim price As Decimal = Convert.ToDecimal(unitPrice.Value)
                    success = minPrice <= price AndAlso price <= maxPrice
                    If Not success Then
                        Result.Focus("UnitPrice",
                                     "This price must be between {0:c} and {1:c}",
                                     minPrice, maxPrice)
                    End If
                End If
            End If
            ' 5. validate Quantity field
            If success Then
                success = quantity IsNot Nothing AndAlso quantity > 0
                If Not success Then
                    Result.Focus("Quantity", "The quantity must be greater than zero.")
                End If
            End If
            ' Validate Discount field
            If success Then
                ' 6. If value > 1, then convert value to percentage
                If discount > 1 Then
                    discount = discount / 100
                    UpdateFieldValue("Discount", discount)
                End If
                ' 7. Confirm that Discount is between 0.00 and 0.99
                success = discount IsNot Nothing AndAlso discount >= 0.0 AndAlso discount < 1
                If Not success Then
                    Result.Focus("Discount",
                                 "The discount must be between 0.00 and 0.99 (0% - 99%).")
                End If
            End If
            ' 8. Wrapping Up
            If commandName = "Calculate" OrElse Not success Then
                Me.PreventDefault()
            End If
        End Sub
    End Class
End Namespace

The business rule is written in C# or Visual Basic. The fields are present in the arguments of the method. Changing the data type of the argument to FieldValue allows accessing additional properties of the field.

Click Browse button on the toolbar, sign in as admin/admin123% and navigate to Customers / Order Details page.

The business rule will be engaged as soon as a new record is being entered or an existing one is modified. The business rule will react to changes to the fields specified in the Context Fields property of UnitPrice when Calculate action is raised. The field UnitPrice depends on itself. It does not matter which application field is used to trigger the Calculate action. The only field that cannot trigger the calculation is ProductID. The Context Fields of a lookup will provide filtering information for the lookup window. Any field names in the Context Fields of ProductID will make the client library treat them as context filters, which will prevent users from seeing any products in the lookup window.

Business Rules provide a true abstraction level and separation from the user interface. The business rule is not actively selecting user interface elements to collect input values, which allows flexible modification of the presentation and the rule itself. The same business rule can service any number of data controller views regardless of the view types.

There are eight distinct steps in the business rule.

1. Initial Input Validation

The client library automatically performs basic validation of the entered values. The rule declares success variable, which is used to determine if any special validation needs to be applied to the fields.

The command causing the rule to execute is stored in commandName variable. The possible values are Calculate, Insert, or Update.

The rule also makes a note of the field that has triggered the business rule. The trigger field name will be known only when Calculate action is raised. The possible triggers are ProductID, UnitPrice, Quantity, and Discount.

This is an example of a basic validation that does not require custom coding.

Basic validation performed on the client.

2. Determination of The “Base” Price

The rule will memorize the last ProductID and UnitPrice in the properties of the data view instantiated on the browser page. The base price is used to ensure that users do not enter lesser values and to prevent the price inflation by more than 5%. If the product has just been changed, then the rule will reset values of fields Quantity and Discount and set the focus on the Quantity field.

This screen shot shows the form after the product selection.

The unit price is copied from the Product.

3.  Adjusting Base Price for Existing Records

If the user is editing an existing record then the old (original) value of the UnitPrice is memorized in the data view properties.

4. Price Validation

The business rule figures the minimum and maximum price that can be entered by the user. If the price is blank, then the user is instructed to enter a value. If the price is out of range, then the user is forced to correct the problem.

This is the price validation in the form view.

Validation is performed on the unit price.

This is how the price validation is performed in the data sheet view.

Validation performed in data sheet view.

5. Quantity Validation

The rule ensures that a positive Quantity is entered.

Validation to ensure positive quantity.

6.  Automatic Conversion of Discount

The Discount column in Order Details table has the Single type. There is also a database constraint that requires the value to be between zero and one. The business rule will help the user by automatically converting the discount to a fraction of 100.

A user has entered a value greater than one in this screenshot.

Discount is converted to a percentage.

The value is automatically converted when the user leaves the field.

7. Discount Range Validation

If the end user is trying to enter a particularly high discount that remains out of range even after automatic conversion, then the validation error is displayed.

Validation is performed on Discount field.

8. Wrapping Up

The default behavior of the client library is to send the collected values to the server for processing by application. The business rule will prevent this from occurring. The business rule prevents server-side execution of Calculate action. It is also preventing server-side processing of Insert and Update if the validation has not been successful.