ASP.NET 3.5

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.

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.

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.

A common requirement in many data management applications is to compliment data manually entered by users with some calculated values. You may be required to perform some complicated financial computations, lookup additional field values in the database according to some custom logic, or automatically supply audit trail by recording the time stamps and user ID in the database records. Some of these tasks can be encapsulated in database table triggers. If this is not an option then it is time to create a custom action handler.

We will illustrate this with the unit price lookup by improving on the custom action handler example presented in Aquarium Express Primer.

The primer is demonstrating a lookup of unit price for inserted order details records of Northwind database. Basically it is suggesting to allow insertion of the order details record without a price and executing a follow-up update of the new record. This is resulting in two database operations - SQL insert will be followed by update statement.

We will improve on that by supplying a unit price prior to the insertion of order details record.

Open ~/Controllers/OrderDetails.aspx and change the definition of createForm1 as follows:

<view id="createForm1" type="Form" commandId="command1" label="New Order Details">
  <headerText>Please fill this form and click OK button to create a new order details record. Click Cancel to return to the previous screen.</headerText>
  <categories>
    <category headerText="New Order Details">
      <description>Complete the form. Make sure to enter all required fields.</description>
      <dataFields>
        <dataField fieldName="OrderID" aliasFieldName="OrderCustomerID" />
        <dataField fieldName="ProductID" aliasFieldName="ProductProductName" />
        <dataField fieldName="UnitPrice" dataFormatString="c" columns="15" hidden="true"/>
        <dataField fieldName="Quantity" columns="15" />
        <dataField fieldName="Discount" columns="15" />
      </dataFields>
    </category>
  </categories>
</view>

The primer suggests removing the UnitPrice data field from view createForm1. We are restoring the data field and instead marking it up as "hidden". Hidden data fields are not rendered in the standard user interface when displayed in a web browser, which meets our objective as outline in the primer. On the other hand, hidden fields are available for manipulation in custom action handlers. Note that you can still display any hidden fields if you are using custom form templates.

Now you can remove the price lookup code from method AfterSqlActon in Class1.

Create a new method BeforeSqlAction as shown in example.

Here is C# implementation of BeforeSqlAction.

protected override void BeforeSqlAction(ActionArgs args, ActionResult result)
{
    if (args.CommandName == "Insert" && args["UnitPrice"].Value == null)
    {
        double price = 0;
        using (SqlText findPrice = new SqlText(
            "select UnitPrice from Products where ProductId = @ProductID"))
        {
            findPrice.AddParameter("@ProductId", args["ProductID"].Value);
            price = Convert.ToDouble(findPrice.ExecuteScalar());
        }
        args["UnitPrice"].NewValue = price;
        args["UnitPrice"].Modified = true;
    }
}

VB.NET implementation looks very much the same.

Protected Overrides Sub BeforeSqlAction(ByVal args As MyCompany.Data.ActionArgs, _
                                            ByVal result As MyCompany.Data.ActionResult)
        If args.CommandName = "Insert" And args("UnitPrice").Value Is Nothing Then
            Dim price As Double = 0
            Using findPrice As SqlText = New SqlText( _
                "select UnitPrice from Products where ProductId = @ProductID")
                findPrice.AddParameter("@ProductID", args("ProductID").Value)
                price = Convert.ToDouble(findPrice.ExecuteScalar())
            End Using
            args("UnitPrice").NewValue = price
            args("UnitPrice").Modified = True
        End If
    End Sub

As you can see we are finding the price of the ordered product and then assigning it to the new value of UnitPrice in the argument. It is important to mark the field value as modified. Otherwise the field will not be included in the SQL statement generated by data controller.

Custom action handlers allow complex calculations to be implemented in a high level language such as C# or VB.NET, which may be the only option if calculations involve resources outside of the database server. The same custom action handler is uniformly used in all pages of your application that are displaying the data with the help of the controllers that are referring to your custom action handler via actionHandlerType attribute. Custom action handlers become centralized stateless business logic layer rule repositories of your Web 2.0 applications written with ASP.NET and AJAX.