Sort and Filter

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
Friday, May 21, 2010PrintSubscribe
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