Code Generator

Web Site Factory applications can now use declarative sort and filter expressions on data views. This feature allows effecting just-in-time ORDER BY and WHERE clause automatically created by the application framework at runtime without the need to modify the command.

Multiple views can use the same command and offer different sort and filter expression.

Sort Expression

Run Code OnTime Generator and select your project. Click Design button and select the data controller. Activate Views tab and choose grid1. The following screen is presented if you edit the view and enter the sort expression. Sort expression is a list of data field names of this view, each followed by optional asc or desc suffix.

Here is how the grid1 view looks if you enter the sort expression in the application created from the Northwind database.

If you save the changes and preview the data controller in the Test Bench then the initial list of customers will look like then one in the picture.

The actual SQL  statement executed at runtime looks like the one below. Notice that ORDER BY in the common table expression has been expanded with the actual table column names represented by Country and CompanyName fields.

with page_cte__ as (
select
row_number() over (order by "Customers"."Country" ,"Customers"."CompanyName"  desc) as row_number__
,"Customers"."CustomerID" "CustomerID"
,"Customers"."CompanyName" "CompanyName"
,"Customers"."ContactName" "ContactName"
,"Customers"."ContactTitle" "ContactTitle"
,"Customers"."Address" "Address"
,"Customers"."City" "City"
,"Customers"."Region" "Region"
,"Customers"."PostalCode" "PostalCode"
,"Customers"."Country" "Country"
,"Customers"."Phone" "Phone"
from
"dbo"."Customers" "Customers"

)
select * from page_cte__ where row_number__ > 0 and row_number__ <= 5

Filter Expression

Filter expression must be compatible with the back-end database server syntax. Data field names used in filter are automatically expanded into appropriate SQL expressions as defined in command.

Suppose you enter the following filter expression in the Northwind sample.

This filter expression will result in the following expression executed by the framework at runtime.

with page_cte__ as (
select
row_number() over (order by "Customers"."Country" ,"Customers"."CompanyName"  desc) as row_number__
,"Customers"."CustomerID" "CustomerID"
,"Customers"."CompanyName" "CompanyName"
,"Customers"."ContactName" "ContactName"
,"Customers"."ContactTitle" "ContactTitle"
,"Customers"."Address" "Address"
,"Customers"."City" "City"
,"Customers"."Region" "Region"
,"Customers"."PostalCode" "PostalCode"
,"Customers"."Country" "Country"
,"Customers"."Phone" "Phone"
from
"dbo"."Customers" "Customers"

where
(
ContactTitle='Owner')
)
select * from page_cte__ where row_number__ > 0 and row_number__ <= 5

The filter expression is inserted verbatim in the command text.

Filter expressions support parameters. Parameters must be prefixed by "@" or ":" symbol. You must implement a business rules class for the data controller with a property or field that matches the parameter name.

If you modify the filter expression as follows then additional configuration steps are needed.

ContactTitle = @ContactTitleFilter

First you need to define the name of the business rules class for your data controller. We have entered CustomerRules in the field Handler as displayed in the screen shot.

Then you need to generate the application. This will create the business rules class in the file ~/App_Code/Rules/CustomerRules.cs(vb).

The file will not be overwritten if you re-generate the code next time. It is safe to place your custom business logic in it. The sample filter implementation of property ContactTitleFilter in C# is performing conditional filtering of customers by Contact Title.

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

namespace MyCompany.Rules
{
    public partial class CustomerRules : MyCompany.Data.BusinessRules
    {
        public string ContactTitleFilter
        {
            get
            {
                if (Context.User.IsInRole("Administrators"))
                    return "Owner";
                else
                    return "Sales Agent";
            }
        }
    }
}

This is the screen shot of Customers screen presented to user account user/user123%.

Test Bench

Code OnTime Designer has been enhanced with the Test Bench. This tool is designed to speed testing of data controllers without leaving the design environment. Action bar displayed above the settings of a data controller elements now features the “Preview” action.

If you click on the action then Code OnTime Generator will automatically create a test site based on Web Site Factory premium project. The test project will feature a single page named after the currently selected data controller. A browser window will open automatically as soon as the project has been generated. If this is your first time invoking the Test Bench then it will take some time to generate the initial code of the test site. Subsequent requests to preview data controllers will run much faster and will allow quickly testing changes without generating the entire project.

This illustration shows the Preview action on the action bar of the data view category list of Employees data controller from the Northwind sample. We just have changed the Floating attribute of Employees category to Yes.

We click on the Preview button and the Test Bench web site is opened in a new browser window in just a few seconds. If we select an employee then the following presentation will be rendered.

Denormalization Field Map

The code generator will automatically discover relationships between data objects in your database and create the corresponding business objects by denormalizing the data object relationships.

For example, sample database Northwind features Products and Suppliers tables. Products  table refers to Suppliers via foreign key that links dbo.Products.SupplierID and dbo.Suppliers.SupplierID fields. The code generator will automatically include the first most appropriate field from the Supliers table to complement dbo.Products.SupplierID field. The automatically selected field is Suppliers.CompanyName, which is included into Products business object as SupplierCompanyName.

You will probably agree that this make sense. Sometimes the code generator will select a field that does not make much sense at all. You can assist the code generator by supplying a denormalization field map on Business Logic Layer page of the project configuration wizard.

Here is a sample denormalization field map:

dbo.Products => dbo.Suppliers
ContactName
ContactTitle
Fax
Phone

dbo.Employees=>dbo.Employees
LastName
FirstName

dbo.Orders=>dbo.Employees
LastName
FirstName

dbo.Order Details=>dbo.Products
UnitPrice

This map will ensure that ContactName, ContactTitle, Fax, and Phone are included into Products business object and any other business objects that include references to the products.

For example, OrderDetails business object will also include ContactName, ContactTitle, Fax and Phone of the product supplier with the appropriate prefix (ProductSupplieContactName, ProductSupplierContactTitle, etc.)  in front of the field name.

Employees business object is based on a self-referring Employees data object. The foreign key name is ReporsTo. The business object will have ReportsToLastName and ReportsToFirstName fields automatically included in its field roster by the code generator.

Another option of denormalization is to use Code OnTime Designer to add the appropriate formula fields directly to the data controllers. The advantage of using a denormalization map is that it will save you a bunch of mouse clicks in the Designer. The advantage of using the Code OnTime Designer for denormalization purposes is that explicitly declared formula fields can include custom expressions that go beyond a simple field reference delivered through the map.

Relationship Discovery

If you are missing a foreign key in a database or opted not to have an explicit foreign key for some other reasons then you can use Relationship Discovery mechanism to enter the missing relationships, which will help the code generator to construct the correctly denormalized business objects.

Here is an example:

foreign key dbo.Products(SupplierID) references dbo.Suppliers(SupplierID)

foreign key dbo.Orders(CustomerID) references dbo.Customers(CustomerID)

foreign key dbo.Order Details(OrderID) references dbo.Orders(OrderID)

foreign key dbo.Order Details(ProductID) references dbo.Products(ProductID)

Any child/parent relationship specified in relationship discovery overrides the corresponding physical foreign keys automatically discovered in the database. You can set the discovery depth to zero to take complete control over the fields that will be automatically included into composed business objects during denormalization process.

If there is a relationship specified in Relationship Discovery then the discovery depth is ignored when the code generator is following the relationship during denormalization.

The relationship discovery will be updated in the future to support primary key definitions, which will allow to automatically build business objects based on database views.

Auto-Generated Fields in Grid Views

Standard grid views created by the code generator automatically will include ten first fields that are suitable for display to end users. You can change this number on the Features page of the project configuration wizard as shown on the picture below.

You can use Code OnTime Designer to re-arrange any data fields that were automatically generated.