Monday, September 15, 2008
Server-Side Data Filters

Data Aquarium Framework and Aquarium Express applications are featuring user-defined adaptive filtering. Users can select any combination of field values in dynamic menus of grid view columns to find the data they need.

Take a look at a live example of Northwind database web application available at http://dev.codeontime.com/demo/northwind. The screen shot displays a list of orders filtered down to employee with last name Fuller. User is about to apply an additional filter to the column Ship Via Company Name.

image

If you want your users to see only a subset of data then define alternative data views based on queries that are limiting data in the SQL query in the foundation of each views.  The advantage of this approach is an ability to quickly define a data subset limited by a known criteria. Dynamic parameter values are not allowed in the query.

What if you want to limit the list of orders to those placed by currently signed-in employee only?

New feature available in Data Aquarium Framework allows to define a reusable data filter class used by linked data controllers automatically whenever a corresponding data retrieval operation requires a  filter. Let's see how it works.

Generate a web site code with Data Aquarium Framework project  and Northwind database.  Open this web site in Visual Studio 2008 or free Visual Web Developer 2008 Express Edition. Add Class1 to App_Code folder and enter the following code.

Data filter written in C#:

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

public class Class1 : IDataFilter
{
    void IDataFilter.Filter(SortedDictionary<string, object> filter)
    {
        filter.Add("EmployeeID", new int[] { 2, 1 });
        filter.Add("ShipVia", 1);
    }
}

Here is VB.NET version of this class:

Imports System.Collections.Generic
Imports MyCompany.Data

Public Class Class1
    Implements IDataFilter

    Public Sub Filter(ByVal filter As SortedDictionary(Of String, Object)) _
        Implements MyCompany.Data.IDataFilter.Filter
        filter.Add("EmployeeID", New Object() {2, 1})
        filter.Add("ShipVia", 1)
    End Sub
End Class

Open data controller descriptor ~/Controllers/Orders.xml and  add an attribute dataFilterType as shown below:

<dataController 
  name="Orders" conflictDetection="overwriteChanges" 
  label="Orders" xmlns="urn:schemas-codeontime-com:data-aquarium" 
  dataFilterType="Class1">

If you run the web site and select Orders in the drop down of the default page then a view like the one below is displayed.

image

Only eight pages of orders are available. All of them are limited to those placed by employees Fuller and Davolio. All of these orders were shipped via Speedy Express.  If you look at the sample code then you will notice that we are supplying filter values for EmployeeID field as an array with two values, while the ShipVia field is filtered by a single value only.

A quick and easy enhancement will make your view less busy. In our example only one shipper is displayed. You can hide this column from user interface by adding an additional hidden attribute to dataField element in the data controller descriptor ~/Controllers/Orders.xml.

<dataField fieldName="ShipVia" aliasFieldName="ShipViaCompanyName"  hidden="true"/>

In a real-world scenario you will likely use HttpContext.Current.User.Identity property or HttpContext.Current.User.IsInRole method to determine the scope of data that you want to include in the result set displayed to a currently signed in user. Make sure to add System.Web namespace to the list of namespaces in your data filter to get access to HttpContext.Current property.

What do you do if the number of filter values is greater than just a few? In this case you might want to consider creating a database table that holds sets of filter values matched with user identities or roles and then join this table into SQL queries defined in corresponding data controller descriptors. Implement a server-side data filter to provide an ID or a role of current user.

If the name of the data filter field is not matched with any of the fields in the result set then the value is ignored. You can link the same filter to multiple data controllers to take advantage of centralized processing architecture of Data Aquarium Framework.