Blog: Posts from May, 2009

Blog

Posts from May, 2009

Wednesday, May 6, 2009Print Subscribe

Data controllers of applications created with Data Aquarium Framework define a state machine of action. There are standard actions, such as Edit, Update, New, ExportRss, that are invoking execution of specific functionality supported by the framework’s JavaScript runtime classes. You can define a collection of your own custom commands and supply these commands with arguments specific to your requirements.

Let’s consider the following example.

Generate a Data Aquarium project from Northwind database. Open ~/Controllers/Employees.xml data controller descriptor and modify the sample custom action with header My Command as shown in the snippet below.

<action commandName="Custom" commandArgument="ExportXls,1,abc" 
    headerText="My Command" description="Execute my custom command" />

Create the business rules class Class1 as shown at /blog/2009/02/business-rules-rowbuilder-attribute.html. Make sure to link the class to Employees data controller via handler attribute. Modify the class to include MyCommand method.

C#:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MyCompany.Data;

public class Class1: BusinessRules
{
    [ControllerAction("Employees", "grid1", "Custom", ActionPhase.Execute)]
    protected void MyCommand(int employeeId)
    {
        string[] args = Arguments.CommandArgument.Split(',');
        if (employeeId <= 4)
            Result.NavigateUrl = String.Format(
                "~/Default.aspx?EmployeeID=" + args[1]);
        else
            Result.ShowAlert(Arguments.CommandArgument);
    }
}

VB:

<ControllerAction("Employees", "grid1", "Custom", _
    ActionPhase.Execute)> _
Protected Sub MyCommand(ByVal employeeId As Integer)
    Dim args As String() = Arguments.CommandArgument.Split(",")
    If (employeeId <= 4) Then
        Result.NavigateUrl = String.Format( _
            "~/Default.aspx?EmployeeID=" & CType(args(1), String))
    Else
        Result.ShowAlert(Arguments.CommandArgument)
    End If
End Sub

The method is designed to be invoked when any Custom action is requested from grid1 view. The parameter employeeId will indicate the currently selected employee.

Property Arguments provides access to all information that the framework has to offer about the requested action.

We will split the argument by comma and will request the JavaScript class Web.DataView of the framework to navigate to the requested page and have an employee with ID specified in the second segment of the argument to be displayed on the default page. This will happen only if the selected EmployeeID is less than or equal to 4. Otherwise a simple alert will display the value of the command argument.

Notice that no actual JavaScript code is written here. The framework methods are encapsulating the calls to the client-side library. If you are familiar with JavaScript then use Result.ExecuteOnClient to send custom scripts to the client.

Open the default page of the web site in a browser and select the fifth employee in the list. Request action My Command from the action bar.

As expected, our method MyCommand will be invoked, which will result in alert displayed to a user. This happens since the fifth employee has ID equal to 5.

You can see that the argument is the one specified in the data controller descriptor.

Now, select any employee record above the one in the picture and invoke the same command on the action bar again.

This time the navigation has been performed and employee with ID equal to 1 is filtered. You can find more information about navigational filtering at /blog/2009/04/present-what-you-want.html.

The NavigateUrl can point to any page or generic handler in your application. The handler may render a report or respond in some other way to the requested acti0n.

Labels: Business Rules/Logic, Data Aquarium Framework

Friday, May 1, 2009Print Subscribe

Calculated Fields in Data Controllers

Data Aquarium Framework dynamically parses SELECT statements in commands written in SQL.

The parsing engine is very simplistic and expects a single SELECT statement with FROM, WHERE, and ORDER BY clauses. The last two clauses of SELECT statement are optional. You can create such commands with any query builder or rely on the statements generated automatically by Code OnTime Generator.

The engine is trying to identify all fields that are available in the statement, their aliases, the table name in the FROM clause, the filtering condition and the sort expression. You can include multiple JOIN expressions to de-normalize your data and produce a data set that truly represents your data objects to hide the complexity of the normalized database schema.

The parsed information is used to dynamically create SQL statements capable of paging and sorting of very large data sets. It is also used to generate UPDATE, INSERT, and DELETE statements.

Data controller requires that all fields that are displayed in views are enumerated in /dataController/fields/field node. Field list provides information about data types, primary keys, support for update and the need to have a non-empty value in the fields. All of these field properties are assisting the framework in generating dynamic SQL statements.

Let’s consider a few methods of introducing calculated fields that can be used by the framework to provide reach data presentation.

METHOD 1: Simple Calculated Fields

Here is sample command for Employees table in Northwind database.

    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Employees"."EmployeeID" "EmployeeID"
    ,"Employees"."LastName" "LastName"
    ,"Employees"."FirstName" "FirstName"
    ,("Employees"."LastName" + ', ' + "Employees"."FirstName") "FullName"
    ,"Employees"."Title" "Title"
    ,"Employees"."TitleOfCourtesy" "TitleOfCourtesy"
    ,"Employees"."BirthDate" "BirthDate"
    ,"Employees"."HireDate" "HireDate"
    ,"Employees"."Address" "Address"
    ,"Employees"."City" "City"
    ,"Employees"."Region" "Region"
    ,"Employees"."PostalCode" "PostalCode"
    ,"Employees"."Country" "Country"
    ,"Employees"."HomePhone" "HomePhone"
    ,"Employees"."Extension" "Extension"
    ,"Employees"."Photo" "Photo"
    ,"Employees"."Notes" "Notes"
    ,"Employees"."ReportsTo" "ReportsTo"
    ,"ReportsTo"."LastName" "ReportsToLastName"
    ,"Employees"."PhotoPath" "PhotoPath"
from "dbo"."Employees" "Employees"
    left join "dbo"."Employees" "ReportsTo" on 
        "Employees"."ReportsTo" = "ReportsTo"."EmployeeID"
]]></text>

This is the list of corresponding fields.

<fields>
  <field name="EmployeeID" type="Int32" allowNulls="false" isPrimaryKey="true" 
        label="Employee#" readOnly="true" />
  <field name="FullName" type="String" allowNulls="true" label="Full Name" 
        readOnly="true"/>
  <field name="LastName" type="String" allowNulls="false" label="Last Name" />
  <field name="FirstName" type="String" allowNulls="false" label="First Name" />
  <field name="Title" type="String" label="Title" />
  <field name="TitleOfCourtesy" type="String" label="Title Of Courtesy" />
  <field name="BirthDate" type="DateTime" label="Birth Date" />
  <field name="HireDate" type="DateTime" label="Hire Date" />
  <field name="Address" type="String" label="Address" />
  <field name="City" type="String" label="City" />
  <field name="Region" type="String" label="Region" />
  <field name="PostalCode" type="String" label="Postal Code" />
  <field name="Country" type="String" label="Country" />
  <field name="HomePhone" type="String" label="Home Phone" />
  <field name="Extension" type="String" label="Extension" />
  <field name="Photo" type="Byte[]" onDemand="true" sourceFields="EmployeeID" 
        onDemandHandler="EmployeesPhoto" onDemandStyle="Thumbnail" allowQBE="false" 
        allowSorting="false" label="Photo" />
  <field name="Notes" type="String" allowQBE="false" allowSorting="false" label="Notes" />
  <field name="ReportsTo" type="Int32" label="Reports To">
    <items style="Lookup" dataController="Employees" newDataView="createForm1" />
  </field>
  <field name="ReportsToLastName" type="String" readOnly="true" 
        label="Reports To Last Name" />
  <field name="PhotoPath" type="String" label="Photo Path" />
</fields>

These definitions were automatically produced by Code OnTime Generator but you should have little difficulty in making your own changes when needed.

You have probably noticed that FullName field was entered manually in SQL text and in the list of fields. The value of this field is a composition of LastName and FirstName.

This is how the field looks when displayed in a browser.

The same field is immediately displayed when you select an employee in the lookup field. Notice the value Employee Last Name field.

Similar modifications can be done to other related data controllers.

The calculated expressions must remain simple. Otherwise you are risking to confuse the powerful but rather simple-minded SQL parser of the framework. High performance comes at a price!

Method 2: Use Data Views For Complex Calculations

Complex formulas can be easily hidden in the views.

For example, create the SQL view as shown in example.

create view EmployeeView as
    select 
        EmployeeID, 
        LastName + ', ' + FirstName as FullName
    from 
        Employees

Modify the command from the previous sample to look like the one below. Notice the changes in field FullName and joined view EmployeeView.

    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Employees"."EmployeeID" "EmployeeID"
    ,"Employees"."LastName" "LastName"
    ,"Employees"."FirstName" "FirstName"
    ,EmployeeView.FullName
    ,"Employees"."Title" "Title"
    ,"Employees"."TitleOfCourtesy" "TitleOfCourtesy"
    ,"Employees"."BirthDate" "BirthDate"
    ,"Employees"."HireDate" "HireDate"
    ,"Employees"."Address" "Address"
    ,"Employees"."City" "City"
    ,"Employees"."Region" "Region"
    ,"Employees"."PostalCode" "PostalCode"
    ,"Employees"."Country" "Country"
    ,"Employees"."HomePhone" "HomePhone"
    ,"Employees"."Extension" "Extension"
    ,"Employees"."Photo" "Photo"
    ,"Employees"."Notes" "Notes"
    ,"Employees"."ReportsTo" "ReportsTo"
    ,"ReportsTo"."LastName" "ReportsToLastName"
    ,"Employees"."PhotoPath" "PhotoPath"
from "dbo"."Employees" "Employees"
    left join "dbo"."Employees" "ReportsTo" on 
         "Employees"."ReportsTo" = "ReportsTo"."EmployeeID"
    inner join EmployeeView on 
         Employees.EmployeeID = EmployeeView.EmployeeID
]]></text>
    </command>

Run the program and observe that the result is identical.

Additional views can be linked to the main (updatable) table in the FROM clause and allow use of calculated values. This approach is preferred to the previous one since it brings the business logic into the database where it belongs.

Method 3: The Power of RowBuilder

Business rules provide a complete control over the field value calculation when you need it. You can find an example of RowBuilder attribute usage in business rules at /blog/2009/02/business-rules-rowbuilder-attribute.html.

Everything has a price though. High performance user-defined sorting and filtering is supported in the framework via dynamic SQL statements. Calculated values provided by business rules are not a part of SQL operations and thus make it impossible to have these great features in the user interface.

The previous two methods do provide these capabilities as you can see here.

Fields calculated by methods marked with RowBuilder attribute will not sort or filter.

Method 4: Calculated Database Fields.

Modern database servers support server-side calculated fields to provide maximum efficiency. For example, Microsoft SQL Server supports computed fields. The screen shot below shows the computed field FullName as presented in SQL Management Studio.

Here is the Northwind sample that shows the computed field in action. Notice that the field is automatically recognized by Code OnTime Generator and marked as read-only.

The advantage of server-side calculated fields is that such fields are also recognized in any related objects. Here is the order management view that shows the computed Employee Full Name field. You can view sort, filter, lookup, and do any other user interface operation that you can do with any other standard field.

Method 5: Calculate stored Values With business Rules

You may choose to create placeholder database fields and calculate the value of such fields with the help of business rules by marking methods with ControllerAction attribute.

The placeholder calculated fields are stored in your database but are not designed to be changed by users. Make sure to mark such fields as read-only.

This method provides you with enough control to do just about anything when calculating the values and preserves your ability to allow user-defined sorting and filtering. You can augment your calculations with stored procedures, business logic written in other languages, call external web services… The list can go on.

Read about ControllerAction attribute at /blog/2009/02/business-rules-controlleraction.html.

Conclusion

Numerous options to introduce calculated values are supported in Data Aquarium Framework applications. Methods 4 and 5 are most flexible. Use server-side calculated/computed fields defined as SQL expressions or create physical placeholder fields and develop custom business rules with the power of .NET Framework.

Labels: Business Rules/Logic, Data Aquarium Framework