Blog

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
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.

Saturday, August 16, 2014PrintSubscribe
Reports at Attachments in Email Business Rules

Application framework allows generating reports on the server. This capability makes it possible to produce reports as attachments of email business rules.

Consider the following Email Business Rules implementing a simple notification executed in response to Update command in Categories data controller.

Property Value
Command Name Update
Type Email
Phase After
Script
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>

Note the two attachment elements embedded directly in the email message. Each element defines a snippet of XML markup describing the report execution arguments.

The first attachment is produced for Categories data controller. Data is filtered by CategoryID of the modified record. The report is produced for editForm1 view.

The second attachment is produced for Products data controller. Data is filtered by CategoryID and sorted in descending order of Unit Price. The report is produced for grid1 view.

Note the field names of the updated data record referenced in curly braces. Expressions {CategoryName} and {CategoryID} are replaced with the actual values during processing.

Attachment definitions are removed from the email body by application framework. The framework will generate each report using specified arguments and attach the report to the email before it is sent out to the recipient.

Run the application and edit any category. Click OK button to save the changes.

An email business rule will trigger an email notification with two report attachments produced in a Touch UI app created with Code On Time application generator.

The data will be saved after a slight delay.

Check your smart phone device for messages.

An email notification on an Android device generated by an Email Business Rule of an app produced with Code On Time.

This is how the email message may look.

The text of the email notification produced by an Email Business Rule in an app created with Code On Time applicaition generator.

Click on an attachment to see the report data in a PDF reader installed on your device.

An attachment report with Category details displayed in Adobe Reader on an Android device.

An attachment report with a list of Products in changed category displayed in Adobe Reader on an Android device.

Saturday, August 16, 2014PrintSubscribe
Producing Reports in Binary Format

Application end users download the data reports by selecting menu options in the user interface.

Standard reporting options in an app with Touch UI produced with Code On Time application generator.

The report is produced in the requested format on the server and streamed back to the client browser. The report data is automatically filtered and sorted exactly as displayed to the end user.

A report produced in Microsoft Word format by an app with Touch UI created with Code On Time application generator.

Application developers may need to produce a report on the server with arbitrary filters and sort expression in response to the user actions. The report data file may be stored in the database, archived in the file system, or sent as an email attachment. Application framework offers a simple method that allows to do just that.

Consider the following sample business rule.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using MyCompany.Data;
using MyCompany.Handlers;
using System.IO;
using MyCompany.Web;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules : 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 "ProduceReport".
        /// </summary>
        [Rule("r100")]
        public void r100Implementation(string customerID, string companyName, string contactName,
            string contactTitle, string address, string city, string region, string postalCode,
            string country, string phone, string fax)
        {
            // This is the placeholder for method implementation.
            ReportArgs args = new ReportArgs();
            // controller
            args.Controller = "Orders";
            // sort expression
            args.SortExpression = "OrderDate desc";
            // data filter
            args.Filter = new FieldFilter[] {
                new FieldFilter  {
                    FieldName = "CustomerID",
                    Operation = RowFilterOperation.Equal,
                    Value = customerID
                }
            };
            // filter details
            args.FilterDetails = "This report has been produced on the server for customer " + companyName;
            // produce report in binary format
            byte[] reportData = Report.Execute(args);
            // save report to the local file system
            File.WriteAllBytes(Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.pdf"),
                reportData);
            // report the MIME type and file extension that go with the binary data
            Result.ShowAlert("MIME: {0}, Extension: {1}", args.MimeType, args.FileNameExtension);
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Text.RegularExpressions
Imports System.Web
Imports System.Web.Security
Imports MyCompany.Handlers
Imports System.IO
Imports MyCompany.Web

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        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 "ProduceReport".
        ''' </summary>
        <Rule("r100")> _
        Public Sub r100Implementation(ByVal customerID As String, ByVal companyName As String,
                                      ByVal contactName As String, ByVal contactTitle As String,
                                      ByVal address As String, ByVal city As String,
                                      ByVal region As String, ByVal postalCode As String,
                                      ByVal country As String, ByVal phone As String, ByVal fax As String)
            'This is the placeholder for method implementation.
            Dim args As ReportArgs = New ReportArgs()
            ' controller
            args.Controller = "Orders"
            ' sort expression
            args.SortExpression = "OrderDate desc"
            ' data filter
            args.Filter = New FieldFilter() {
                New FieldFilter With {
                    .FieldName = "CustomerID",
                    .Operation = RowFilterOperation.Equals,
                    .Value = customerID
                    }
                }
            ' filter details
            args.FilterDetails = "This report has been produced on the server for customer " + companyName
            ' produce report in binary format
            Dim reportData As Byte() = Report.Execute(args)
            ' save report to the local file system
            File.WriteAllBytes(Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.pdf"),
                reportData)
            ' report the MIME type and file extension that go with the binary data
            Result.ShowAlert("MIME: {0}, Extension: {1}", args.MimeType, args.FileNameExtension)
        End Sub
    End Class
End Namespace

The code is executed in response to a custom action Produce Report selected in the context menu of application.

Custom action used to invoke a 'Code' business rule in an app with Touch UI creatd with Code On Time application builder.

Static method Report.Execute performs a server-side execution of the standard report action. The custom implementation of the “Code” business rule displays details about the produced binary data array.

Information about MIME type and file extension reporting by a business rule after producing a report in PDF format in an app with Touch UI.

This sample saves the report data to My Documents folder of the server computer. Here is the actual report.

This report has been generated by a custom action with the help of Report.Execute method invoked by custom business rule in an app with Touch UI produced with Code On Time.

Instance of a class ReportArgs exposes several properties that control the report rendering on the server.

Property Description
Controller Specifies the name of the data controller.
View Specifies the ID of the data controller view that will be used to produce data.
SortExpression Defines a sort expression that determines the order of data rows passed to the reporting engine for processing.
Filter Defines an array of filters applied to the report data passed to the reporting engine for processing.
FilterDetails Specifies the optional message displayed below the report header in standard reports.
Format Specifies the format of the output. The default format is Pdf. Other options are Word, Excel, and Image.
Template Name Specifies the name of the custom report template. If left blank, then a standard template is automatically created by application framework.
MimeType Indicates the MIME type of the report data produced by Microsoft Report Viewer. Use this property when sending report as an email attachment.
FileNameExtension Indicates the file name extension that matches the data produced by Microsoft Report Viewer. Use this property to provide a correct extension for the file name.