Blog

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.

Several users have reported the following error when trying to generate a project with Microsoft SQL Server 2008.

System.InvalidOperationException
The Writer is closed or in error state.

The exception has been raised by ADO.NET when trying to extract user-defined types from the database. File [My Documents]\Code OnTime\Library\CodeOnTime.ModelMap.xml defines a collection of queries that are extracting database information and storing it in the PROJECTTYPE.Metadata.xml file in the root of your project. We have changed SQL Server data discovery entry to exclude UserDefinedTypes as shown in this snippet.

<modelMap>
  <provider providerName="System.Data.SqlClient">
    <collection name="*" exclude="Users,Databases,UserDefinedTypes"/>
    <collection name="$TableSchemas"/>
    <query name="Tables">select * from information_schema.tables</query>
    <query name="Columns">select * from information_schema.columns</query>
    .......

This has eliminated the problem. The fix has been deployed and will be automatically downloaded when you start Code OnTime Generator. None of the current projects is supporting user-defined types and this does not affect any of the available features.

If you are creating a brand new project with Microsoft SQL Server 2008 then metadata discovery should work fine if you have installed the update or downloaded the code generator after the publication of this post.

If you did experience this problem then please click on open link next to the name of your project on the main page of code generator. Delete Error.PROJECTTYPE.Metadata.xml and PROJECTTYPE.Metadata.xml files and generate the project again. If you project type is Aquarium Express then the metadata file is DataAquarium.Metadata.xml.

Alternatively you can open your project and navigate to Database Connection page where the error message is displayed. Click on the link 'Click here if your database has changed and you would like to refresh the cached metadata'. This will delete the file with meta data and will guarantee that your project has been generated with the latest database changes.

Please report any errors or suggestions at http://codeontime.com/contactus.aspx. You can download Code OnTime Generator at http://codeontime.com/download.aspx.