Friday, May 21, 2010
Sort and Filter

Web Site Factory applications can now use declarative sort and filter expressions on data views. This feature allows effecting just-in-time ORDER BY and WHERE clause automatically created by the application framework at runtime without the need to modify the command.

Multiple views can use the same command and offer different sort and filter expression.

Sort Expression

Run Code OnTime Generator and select your project. Click Design button and select the data controller. Activate Views tab and choose grid1. The following screen is presented if you edit the view and enter the sort expression. Sort expression is a list of data field names of this view, each followed by optional asc or desc suffix.

Here is how the grid1 view looks if you enter the sort expression in the application created from the Northwind database.

image

If you save the changes and preview the data controller in the Test Bench then the initial list of customers will look like then one in the picture.

image

The actual SQL  statement executed at runtime looks like the one below. Notice that ORDER BY in the common table expression has been expanded with the actual table column names represented by Country and CompanyName fields.

with page_cte__ as (
select
row_number() over (order by "Customers"."Country" ,"Customers"."CompanyName"  desc) as row_number__
,"Customers"."CustomerID" "CustomerID"
,"Customers"."CompanyName" "CompanyName"
,"Customers"."ContactName" "ContactName"
,"Customers"."ContactTitle" "ContactTitle"
,"Customers"."Address" "Address"
,"Customers"."City" "City"
,"Customers"."Region" "Region"
,"Customers"."PostalCode" "PostalCode"
,"Customers"."Country" "Country"
,"Customers"."Phone" "Phone"
from
"dbo"."Customers" "Customers"

)
select * from page_cte__ where row_number__ > 0 and row_number__ <= 5

Filter Expression

Filter expression must be compatible with the back-end database server syntax. Data field names used in filter are automatically expanded into appropriate SQL expressions as defined in command.

Suppose you enter the following filter expression in the Northwind sample.

image

This filter expression will result in the following expression executed by the framework at runtime.

with page_cte__ as (
select
row_number() over (order by "Customers"."Country" ,"Customers"."CompanyName"  desc) as row_number__
,"Customers"."CustomerID" "CustomerID"
,"Customers"."CompanyName" "CompanyName"
,"Customers"."ContactName" "ContactName"
,"Customers"."ContactTitle" "ContactTitle"
,"Customers"."Address" "Address"
,"Customers"."City" "City"
,"Customers"."Region" "Region"
,"Customers"."PostalCode" "PostalCode"
,"Customers"."Country" "Country"
,"Customers"."Phone" "Phone"
from
"dbo"."Customers" "Customers"

where
(
ContactTitle='Owner')
)
select * from page_cte__ where row_number__ > 0 and row_number__ <= 5

The filter expression is inserted verbatim in the command text.

Filter expressions support parameters. Parameters must be prefixed by "@" or ":" symbol. You must implement a business rules class for the data controller with a property or field that matches the parameter name.

If you modify the filter expression as follows then additional configuration steps are needed.

ContactTitle = @ContactTitleFilter

First you need to define the name of the business rules class for your data controller. We have entered CustomerRules in the field Handler as displayed in the screen shot.

image

Then you need to generate the application. This will create the business rules class in the file ~/App_Code/Rules/CustomerRules.cs(vb).

The file will not be overwritten if you re-generate the code next time. It is safe to place your custom business logic in it. The sample filter implementation of property ContactTitleFilter in C# is performing conditional filtering of customers by Contact Title.

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

namespace MyCompany.Rules
{
    public partial class CustomerRules : MyCompany.Data.BusinessRules
    {
        public string ContactTitleFilter
        {
            get
            {
                if (Context.User.IsInRole("Administrators"))
                    return "Owner";
                else
                    return "Sales Agent";
            }
        }
    }
}

This is the screen shot of Customers screen presented to user account user/user123%.

image