Application Factory

Code On Time application generator performs automatic denormalization when constructing application data controllers from tables of a normalized database.

Database architects put a significant effort in creating a normalized database structure. Normalization ensures efficient data storage and maintenance. It is also much easier to extent a normalized database with new tables and columns.

Consider the following subset of tables from the Northwind sample.

Product information is stored in three tables. Raw product data replaces category and supplier information with ID of records stored in Categories and Suppliers tables.

This is not how the application users see the world. Business users want to see the actual category and supplier when looking at a product record instead of a numeric key value.

Application generator composes the following command text stored in the data controller definition.

<dataController name="Products" . . . .>
  <commands>
    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Products"."ProductID" "ProductID"
    ,"Products"."ProductName" "ProductName"
    ,"Products"."SupplierID" "SupplierID"
    ,"Supplier"."CompanyName" "SupplierCompanyName"
    ,"Products"."CategoryID" "CategoryID"
    ,"Category"."CategoryName" "CategoryCategoryName"
    ,"Products"."QuantityPerUnit" "QuantityPerUnit"
    ,"Products"."UnitPrice" "UnitPrice"
    ,"Products"."UnitsInStock" "UnitsInStock"
    ,"Products"."UnitsOnOrder" "UnitsOnOrder"
    ,"Products"."ReorderLevel" "ReorderLevel"
    ,"Products"."Discontinued" "Discontinued"
from "dbo"."Products" "Products"
    left join "dbo"."Suppliers" "Supplier" on 
      "Products"."SupplierID" = "Supplier"."SupplierID"
    left join "dbo"."Categories" "Category" on 
      "Products"."CategoryID" = "Category"."CategoryID"
]]></text>
    </command>
    . . . . . 

This is the partial output of the query executed in SQL Management Studio.

If you run the sample application then the Products page will be presented in a denormalized user-friendly fashion.

The actual query executed by application is not the same as text stored in the data controller definition. In fact, the application framework uses the command text as a developer-friendly dictionary to locate at runtime the expressions behind the field names, the base table, and “join” constructs.

This is the actual query text that matches the screen shot.

with page_cte__ as (
    select
        row_number() over (order by "Products"."ProductID") as row_number__
        ,"Products"."ProductName" "ProductName"
        ,"Products"."SupplierID" "SupplierID"
        ,"Products"."CategoryID" "CategoryID"
        ,"Products"."QuantityPerUnit" "QuantityPerUnit"
        ,"Products"."UnitPrice" "UnitPrice"
        ,"Products"."UnitsInStock" "UnitsInStock"
        ,"Products"."UnitsOnOrder" "UnitsOnOrder"
        ,"Products"."ReorderLevel" "ReorderLevel"
        ,"Products"."Discontinued" "Discontinued"
        ,"Products"."ProductID" "ProductID"
        ,"Supplier"."CompanyName" "SupplierCompanyName"
        ,"Category"."CategoryName" "CategoryCategoryName"
    from
    "dbo"."Products" "Products"
        left join "dbo"."Suppliers" "Supplier" on 
            "Products"."SupplierID" = "Supplier"."SupplierID"
        left join "dbo"."Categories" "Category" on 
            "Products"."CategoryID" = "Category"."CategoryID"

    where
    (
    ("Supplier"."CompanyName"=@p0)
    )
)
select * from page_cte__ 
where 
    row_number__ > @PageRangeFirstRowNumber and 
    row_number__ <= @PageRangeLastRowNumber

Notice the use of parameters that prevent any possibility of SQL injection attack.

The SQL statement also utilizes a common table expression for efficient data retrieval.

You can control the inclusion of fields in the data controllers with the help of denormalization field map.

Code On Time web applications automatically hide the foreign key field of a detail data view on master/detail page if the field is used for relationship filtering.

Here is the screen shot that shows a master/detail relationship between Suppliers and Products in the Northwind sample application. Data rows visible in the list of products are linked to the supplier New Orleans Cajun Delights. The application client library hides the column Products.SupplierID to avoid repeating the same value in each child data row.

Automatic hiding of the foreign key field involved in master/detail filter will maximize the real estate of the page. The side effect of this convenient feature is that it is not possible for a user to change the product supplier by editing a product record in the detail data view. 

A product supplier can be changed if there is a dedicated page of products. Users will have to navigate away to that page. It will also take some time to locate the record in a dedicated view.

Fortunately,  a simple SQL action can be implemented to allow changing the parent of a child record even if the foreign key field is hidden by the master/detail relationship filter.

First, we need to create an action confirmation data controller.

Start the Project Designer and activate the Controllers tab at the top of the designer home page. Select New | New Controller option on the action bar, enter the following properties, and click OK button to save the controller.

Activate the Controllers tab in Project Explorer and right-click the SupplierSelector / Fields node.

Enter the following properties and save the field by clicking OK button.

SelectFieldValue("Context_ProductName")

Create two more fields – SupplierCompanyName and SupplierID.

Properties of the field SupplierCompanyName.

SelectFieldValue("Context_SupplierCompanyName")

Properties of the field SupplierID.

The hierarchy of the SupplierSelector node shall look as follows.

The SupplierSelector data controller allows collecting a new Supplier ID.

Now we need to define a new SQL action in the Products data controller and use SupplierSelector as a modal confirmation form.

Right-click Products / Actions / ag1 (Grid) action group node on the Controllers tab in the Project Explorer and select the New Action option in the context menu.

Enter the following values in the action properties and click OK button to save the changes.

update Products
set SupplierID = @Parameters_SupplierID
where ProductID = @ProductID

Click Browse on the Project Designer toolbar, navigate to the Suppliers page when the application opens in your default web browser. Open the context menu of any product and select Change Supplier option in the context menu of the product data row.

The modal action confirmation will show up. Select a new supplier and click OK button to execute the action.

The product will disappear from the detail view. You will find it liked to the supplier record selected in the action confirmation form.

It is easy to access the values of fields entered in the action confirmation.

Action Change Supplier defines the following SQL script.

update Products
set SupplierID = @Parameters_SupplierID
where ProductID = @ProductID

Simply prefix the field name from the action confirmation data controller with “Parameters_”. The action implementation updates the SupplierID of selected product with the value selected in the modal confirmation.

If you are implementing custom business rules or shared business rules then you can access the values of action parameters as fields with the same “Parameters_” prefix.

Modal confirmation shows the name and current supplier of the selected product. Both fields are configured as read-only and use a similar Code Default expression. Here the expression from the SupplierSelector.SupplierCompanyName field.

SelectFieldValue("Context_SupplierCompanyName")

This code works with both C# and Visual Basic. The prefix “Context_” allows the action confirmation data controller business rules to access the field values of the controller that will be effected by the action. In our example, the name “Context_SupplierCompanyName” is referring to the SupplierCompanyName field of  Products data controller.