Web applications created with Code On Time offer impressive built-in filtering capabilities available to the end user. Just a few mouse clicks and a list of products is custom tailored to match complex filtering requirements.

If the audience of your web application is composed of independent groups of users then the data must be segregated automatically. Each group of users will see only the data that meets certain criteria.

Consider the Orders table from the Northwind sample.

Customers signing in the Northwind web application should only see their own orders.

A simple SELECT statement will filter the list of order by @CustomerID parameter.

select * from Orders where CustomerID = @CustomerID  

The snippet below shows the definition of the Orders data controller command from the Northwind sample.

  <commands>
    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Orders"."OrderID" "OrderID"
    ,"Orders"."CustomerID" "CustomerID"
    ,"Customer"."CompanyName" "CustomerCompanyName"
    ,"Orders"."EmployeeID" "EmployeeID"
    ,"Employee"."LastName" "EmployeeLastName"
    ,"Orders"."OrderDate" "OrderDate"
    ,"Orders"."RequiredDate" "RequiredDate"
    ,"Orders"."ShippedDate" "ShippedDate"
    ,"Orders"."ShipVia" "ShipVia"
    ,"ShipVia"."CompanyName" "ShipViaCompanyName"
    ,"Orders"."Freight" "Freight"
    ,"Orders"."ShipName" "ShipName"
    ,"Orders"."ShipAddress" "ShipAddress"
    ,"Orders"."ShipCity" "ShipCity"
    ,"Orders"."ShipRegion" "ShipRegion"
    ,"Orders"."ShipPostalCode" "ShipPostalCode"
    ,"Orders"."ShipCountry" "ShipCountry"
from "dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"
]]></text>
    </command>

If you paste this query in SQL Server Management Studio and click execute button then the list of orders will come up in the output window.

Your first natural instinct is to stick the “WHERE Orders.CustomerID = @CustomerID” right in the command text. All you need to know is how to provide the parameter value. Isn’t it that sample? The answer is “yes” and “no”.

Code On Time web applications do not use the command text “as-is”.  The application framework uses the command text as a developer-friendly dictionary. The text of the command allows your application locating the “from..” clause, the name of  the “main” table, the SQL expressions hidden behind the field aliases. The application framework uses the command text snippets to put together SELECT, UPDATE, INSERT, and DELET statements at runtime. Notice that SELECT statements are also automatically enhanced with a complex “WHERE …” clause to incorporate the user-defined filters when needed.

Filtering shall be accomplished with the help of the business rules. Your custom filters will be injected by the application framework in the right spot of dynamically created SQL statements and will co-exist with the user-defined criteria.

Let’s consider what it takes to create a programmatic filter.

Click on the name of the Northwind sample project on the start page of the web application generator and select Design project action.

Activate Controllers tab in the Project Explorer and double-click the name of your data controller.

Enter OrdersBusinessRules in the Handler property of the data controller Orders and click OK button.

Click Exit button on the tool bar and proceed to generate the application.

Select the project name on the start page of the generator one more time and  choose Develop project action. Visual Studio of Visual Web Developer will start up.

Press Ctrl+Shift+F when the project loads in the development environment and search for OrdersBuinessRules. Double-click OrdersBusinessRules.cs(vb) in the Find Results window to open the business rules file.

This file is created if it does not exist already. Subsequent code generation will not overwrite your changes.

Enter the method ShowOrdersPlacedByCustomer in the definition of the class.

C#:

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

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {
        [AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")]
        public void FilterOrdersPlacedByCustomer()
        {
            if (!UserIsInRole("Administrators"))
                RestrictAccess("@CustomerID", "AROUT");
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")>
        Public Sub FilterOrdersPlacedByCustomer()
            If (Not UserIsInRole("Administrators")) Then
                RestrictAccess("@CustomerID", "AROUT")
            End If
        End Sub
    End Class
End Namespace

If you sign in with user account user/user123% then you should see the following list of orders.

Administrative user account admin/admin123% will have an unrestricted view of orders stored in the database.

The definition of access control attribute placed above the method name activates the method at runtime. The parameters of the access control attribute determine how the access control is performed.

AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")

The first parameter indicates the name of the data controller. The application framework treats this parameter as a regular expression that is matched against the name of the data controller. You can enter  a blank string as an alternative.  The blank string or “partial match” regular expression works well in shared business rules when the same access control rule is applied to multiple data controllers.

The second parameter is the name of the field that must exist in the data view for the access control rule to be triggered. In our example any of the fields defined in view grid1 can be used as the second parameter of AccessControl attribute.

The third parameter defines the SQL snippet that will be embedded in the SELECT statement produced by application framework at runtime. The application framework replaces any references to the data fields placed in square brackets with the actual expressions defined in data controller command.

For example, the alternative definition of the filtering expression will work exactly the same way if you enter the AccessControl attribute as follows.

AccessControl("Orders", "CustomerID", "Orders.CustomerID = @CustomerID")

The SQL snippet may have any complexity. You can explicitly reference any fields inferred from the “FROM…” clause of the data controller command text. You can reference any data fields defined in the view using square brackets around their names. You can reference any functions supported by your database server. SQL snippet is physically embedded in the SELECT statement composed by the app.

For example, the definition of the access control rule below will limit customer orders to those shipped by United Package.

AccessControl("Orders", "CustomerID", 
            "[CustomerID] = @CustomerID and [ShipViaCompanyName]='United Package'")

The screen shot shows the end result.

The definition of the access control rule below uses SQL expression that starts with the word “SELECT…”.

AccessControl("Orders", "CustomerID", 
            "select CustomerID from Customers where CustomerID = @CustomerID")

If the SQL parameter of the attribute starts with “SELECT” then the application framework will assume that a single column is returned in the output data set and the values of the field CustomerID must be contains in the data set.

The end result composed by the application framework will be equivalent to the following filtering expression.

select * from Orders 
where Orders.CustomerID in (
    select CustomerID from Orders where @CustomerID
)

In this particular example there is little value in using this syntax. Utilize this form of access control rules when you need to match customer ID with more than one value.

If only one field has to be filtered by a programmatic value then you can omit the SQL property in the access control rule definition.

C#:

[AccessControl("Orders", "CustomerID")]
public void FilterOrdersPlacedByCustomer2()
{
    if (!UserIsInRole("Administrators"))
        RestrictAccess("AROUT");
}

Visual Basic:

<AccessControl("Orders", "CustomerID")>
Public Sub FilterOrdersPlacedByCustomer2()
    If (Not UserIsInRole("Administrators")) Then
        RestrictAccess("AROUT")
    End If
End Sub

Notice that you can call the method RestrictAccess multiple times to filter the customer ID by more than one value.

You can define multiple methods with arbitrary names adorned with one or several AccessControl attributes. The method is called by the application framework if the attributes were matched to the runtime conditions. Actual filtering will only take place if you invoke the RestrictAccess method at least once. This allows for programmatically controlled filtering under any imaginable conditions.

Programmatic filters cannot be removed the end users. The single access control rule will impact all pages of your application that reference the same data controller. For example, the following screen shot shows the Orders lookup with the user-defined filter impacted by our access control rule.