Blog: Posts from November, 2008

Data Aquarium Framework now supports context-sensitive lookups.

A common situation in a data entry application may require limiting the possible foreign key values of fields based on other fields in a data table row.

Consider Northwind database table Products. It stores a product inventory. Each product has a category and a supplier. If you where to build a data entry screen for this table then you may soon realize that it would be nice to limit the number of categories displayed to a user based on a supplier selection. Suppliers typically offer a few categories of products and it may improve data entry efficiency if we were to automatically hide categories that do not have any products that were previously purchased from a supplier.

Here is a picture of a product entry screen automatically created by Code OnTime Generator.

Let's implement a context-sensitive category lookup.

We have to create a database view to allow us implementation of category lookup discussed above. Please execute the following script to create view SupplierCategories in the Northwind database.

create view [dbo].[SupplierCategories]
as
select distinct
    "Categories"."CategoryID" "CategoryID"
    ,"Categories"."CategoryName" "CategoryName"
    ,"Suppliers"."SupplierID" "SupplierID"
    ,"Suppliers"."CompanyName" "SupplierCompanyName"
from
"dbo"."Categories" "Categories"
  inner join "dbo"."Products" "Products" on
    "Products"."CategoryID" = "Categories"."CategoryID"
  inner join "dbo"."Suppliers" "Suppliers" on
    "Suppliers"."SupplierID" = "Products"."SupplierID"

The view provide us with all product categories provided by our suppliers.

Next we will create a new data controller descriptor SuppliersCategories.xml as a copy of Categories.xml.

Change command command1 of new data controller descriptor as shown in this snippet.

    <command id="command1" type="Text">
      <text>
        <![CDATA[
select
    "Categories"."CategoryID" "CategoryID"
    ,"Categories"."CategoryName" "CategoryName"
    ,"Categories"."Description" "Description"
    ,"SupplierCategories"."SupplierID" "SupplierID"
    ,"SupplierCategories"."SupplierCompanyName" "SupplierCompanyName"
from "dbo"."Categories" "Categories"
  inner join "dbo"."SupplierCategories" "SupplierCategories" on
    "SupplierCategories"."CategoryID" = "Categories"."CategoryID"
]]>
      </text>
    </command>

Change fields element to provide definitions for fields SupplierID and SupplierCompanyName.

<fields>
  <field name="CategoryID" type="Int32" allowNulls="false" isPrimaryKey="true" label="Category#" readOnly="true" />
  <field name="CategoryName" type="String" allowNulls="false" label="Category Name" />
  <field name="Description" type="String" allowQBE="false" allowSorting="false" label="Description" />
  <field name="SupplierID" type="Int32" label="Supplier#">
  <field name="SupplierCompanyName" type="String" label="Supplier Company Name"/>
</fields>

Change view grid1 to include field SupplierID under alias SupplierCompanyName.

<dataFields>
  <dataField fieldName="CategoryName" columns="15" />
  <dataField fieldName="Description" rows="5" />
  <dataField fieldName="SupplierID" aliasFieldName="SupplierCompanyName"/>
</dataFields>

The purpose of these changes is to allow filtering by SupplierID when grid view grid1 is presented to a user.

Now we are ready to modify the data controller descriptor Products.xml to use SupplierCategories instead of Categories in the lookup definition of field CategoryID.

<field name="CategoryID" type="Int32" label="Category#" contextFields="SupplierID">
  <items style="Lookup" dataController="SupplierCategories" newDataView="createForm1" />
</field>

We have replaced data controller reference from Categories to SupplierCategories.

Another important change is a new attribute contextFields. This attribute specifies a comma-separated list of fields defined in this data controller descriptor that will provide the source of filter values for the fields in the view of SupplierCategories data controller that have matching names when a lookup is displayed. The filter is automatically set as an external, which hides the filter field from end-user. SupplierID is never presented by is used in filtering.

Here is how it looks in a real application. Only two categories of products are available from supplier Exotic Liquids.

Let's select supplier Plutzer Lebensmittelgroßmärkte AG and check out the available categories. Five categories are available for selection.

Context-sensitive lookups are available to premium project subscribers.

Data Aquarium Framework introduces a new feature that allows code-free uploading and downloading of Binary Large Objects to/from a database table column in AJAX style. The feature is based on a generic handler blob.ashx from File Upload premium project.

Here is screen shot from a sample application available at http://dev.codeontime.com/demo/nwblob.

If a BLOB field is storing an image then a thumbnail preview is automatically created. A preview is available in all views. Other types of BLOB are presented by a simple download hyperlink. Views are displaying additional controls that allow BLOB uploading when records are presented in edit or detail mode.

BLOB support is enabled only when you are interacting with existing records. BLOB data cannot be uploaded when creating new records. The internal preview and upload mechanism is based on automatic table row references based on primary keys, which makes uploading of BLOB impossible when records are created.

Original BLOB data can be retrieved from a database with a click of mouse. Here is a screen shot from Adventure Works application that shows side-by-side small and large photographs of products. Original BLOB data other then images will be opened by native applications when downloaded completely by your web browser.

A confirmation message is displayed at the top of the page when a BLOB data has been uploaded successfully.

You can find complete implementation details for all of these features in the project source code.

Making It Work

Generate a Data Aquarium Framework application from Northwind database and open ~/Contollers/Categories.xml data controller descriptor. Find definition of Picture field. It must look like the one below.

<field name="Picture" type="Byte[]" onDemand="true" 
  sourceFields="CategoryID" 
  onDemandHandler="CategoriesPicture" onDemandStyle="Thumbnail" 
  allowQBE="false" allowSorting="false" label="Picture" />

Three attributes are turning on the BLOB support. Attribute sourceFields must list all fields that allow to find the row with a BLOB. This information is passed on to a generic handler blob.ashx located in the root of your web site project. The name of the handler is specified by onDemandHandler attribute. Optional style for an on-demand field is controlled by onDemandStyle attribute.

Generic handler blob.ashx is automatically generated by Code OnTime Generator. All handlers discovered in your database are listed at the top of the file.

Here is how they look in a Northwind application written in C#.

public partial class BlobFactory
{
    
    static BlobFactory()
    {
        // register blob handlers
        RegisterHandler("CategoriesPicture", "\"dbo\".\"Categories\"", "\"Picture\"", 
            new string[] {"\"CategoryID\""}, "Categories Picture", String.Empty);
        RegisterHandler("EmployeesPhoto", "\"dbo\".\"Employees\"", "\"Photo\"", 
            new string[] {"\"EmployeeID\""}, "Employees Photo", String.Empty);
    }
}

You can quickly change these handlers and add the new ones when needed. The parameters of RegisterHandler method are easy to understand.

The first parameter defines a key that is provided as a value for onDemandHandler attribute in data controller descriptors. The second parameter is a fully qualified name of the table that stores BLOB values. The third parameter specifies a column name of a BLOB. A list of key columns of this table follows in the fourth parameter. A user-friendly name of the blob in the fifth parameter is for GUI presentation.

The last parameter defines a content type of a BLOB. If the value is an empty string then the code of generic handler will try to automatically find the content type by treating the value as an image. If you are storing a specific type of data other then image then enter a valid content type that matches the BLOB. For example, if you are storing Microsoft Word documents then use application/msword as content type.