Blog: Posts from May, 2012

Blog

Posts from May, 2012

Thursday, May 10, 2012Print Subscribe

Changing Output Caching Script to Use Confirmation Parameters

A web application can activate an output caching script with the built-in filtering capabilities or with a master-detail relationship filter. The former requires a certain effort from a user and the latter works transparently.

You can replace the standard built-in filtering capabilities with a custom search bar or search dialog. Application user will select a customer ID in a dedicated input screen. The selected value will be passed to the output caching script as a parameter.

A custom action with an inline or modal confirmation data controller must be defined in the output cache controller. The output caching script needs to be changed to process the parameter value collected in the confirmation data controller.

Select the CustOrderHist_Cache / Business Rules / Select in grid1 (SQL / Before) business rule node in Project Explorer.

Replace its Script property with the following text and press OK button to save the changes.

/*********************************************************
 The application framework will delete the "Debug" section
 from the business rule. The parameter values will be 
 provided by the application framework at runtime.
*********************************************************/

-- Business Rule Parameters: Debug
declare @BusinessRules_RequiresRowCount int
declare @BusinessRules_UserName nvarchar(40)
declare @CustomerID_FilterValue nvarchar(5)
declare @Parameters_CustomerID nvarchar(5)

set @BusinessRules_RequiresRowCount = 1
set @BusinessRules_UserName = 'username'
set @CustomerID_FilterValue = 'ANTON'
-- Business Rule Parameters: End Debug

/*********************************************************
 This is the actual business rule implementation 
*********************************************************/

if @CustomerID_FilterValue is null
    if @Parameters_CustomerID is not null
    begin
      -- Delete the previously cached data associated with the current user
      -- if it is not matched to the Customer ID selected in the confirmation
      -- data controller.
      delete from CustOrderHist_Cache
      where 
        CacheUserName = @BusinessRules_UserName and 
        CustomerID <> @Parameters_CustomerID
      -- Use the confirmation data controller field value as a filter
      set @CustomerID_FilterValue = @Parameters_CustomerID
    end

if @BusinessRules_RequiresRowCount = 1 
    and @CustomerID_FilterValue is not null
    and not exists (
        select 1 from CustOrderHist_Cache
        where CacheUserName = @BusinessRules_UserName and 
            CustomerID = @CustomerID_FilterValue and
            CacheExpires > getdate()
    )
begin
    -- delete the data cached by the current user if it has expired.
    delete from CustOrderHist_Cache
    where CacheUserName = @BusinessRules_UserName and 
        CustomerID = @CustomerID_FilterValue
    -- create temporary table to capture the custom result set
    create table #CustOrderHist(
        ProductName nvarchar(50),
        Total int
    )
    -- execute the stored procedure and capture the custom result set
    insert into #CustOrderHist
    exec [CustOrderHist] @CustomerID_FilterValue
    -- copy the custom result set to the Output Cache Table
    insert into CustOrderHist_Cache(
        ProductName, Total, CustomerID, 
        CacheID, CacheUserName, CacheExpires)
    select 
        ProductName,                    -- column from #CustOrderHist
        Total,                          -- column from #CustOrderHist
        @CustomerID_FilterValue,        -- stored procedure parameter
        newid(),                        -- generic primary key value
        @BusinessRules_UserName,        -- the application user name
        dateadd(second, 30, getdate())  -- cache expiration date
    from #CustOrderHist
    -- drop the temporary table with the custom result set
    drop table #CustOrderHist
end

The debug section at the top of the script will be removed by the application framework at runtime.

The highlighted parameter @Parameters_CustomerID will have a value of the customer ID selected in the confirmation data controller. If the action that has caused the execute of this SQL Business Rules does not have a modal or inline confirmation, then the parameter value will always remain equal to NULL.

Labels: Business Rules/Logic, Data Sources, Web Application Generator

Tuesday, May 8, 2012Print Subscribe

Feature: Adaptive Filtering

Code On Time web applications offer adaptive filtering out of the box. Adaptive filters limit the list of filtering options according to the current data set and other active filtering options.

For example, consider the Northwind sample web application. Navigate to the Products page. Mouse over the Category Name column header, and activate the dropdown. Click on “Beverages” filter option.

You will see the list of products has been filtered to only display those records with a Category Name of “Beverages”.

Now, filter Supplier Company Name by “Exotic Liquids” using the same method. The combination of filters has produced two matching records.

Activate the dropdown for Category Name again. You will see that the list of categories has been reduced to only those that match the other filtering constraints (Supplier Company Name equals Exotic Liquids).

Clear the Category filter by clicking on the text “Category Name equals Beverages”.

Filters do not require lookup tables. The filter options are drawn from the data set itself. For example, activate the drop down for Reorder Level in the list of Products. If Supplier Company Name is filtered to “Exotic Liquids”, you can see that the only available filtering options are “10” and “25”, which match the current data set (shown above).

You can clear the whole filter by pressing the “x” on the right side of the filter header.

Filters will continue to adapt regardless of the complexity of a filtering operation. For example, activate the dropdown for Category Name, and click on “Filter…” option.

Toggle the checkboxes next to the following categories: “Beverages”, “Dairy Products”, “Meat/Poultry”. Press OK to apply the filter.

Click on text Unit Price in its header to sort the products in descending order of price.

Activate the Unit Price dropdown, and click on Number Filters | Between. Write “10” and “30” in each box, respectively.

Press OK to apply the filter.

Activate the Supplier Company Name dropdown, and click on “Filter…” option. Checkmark several suppliers from the list, and press OK to apply the filter.

Activate the dropdown for Reorder Level and click on “Filter…” option. Choose several reorder levels and press OK to apply the filter.

The filtering options will all be displayed in a filter detail bar underneath the action bar. You may have noticed that as you made each filter, the subsequent filters had less values. This is due to the power of adaptive filtering.

You can also click on the text of any specific filter description to remove that filter.

Labels: User Interface, Web Application Generator

Friday, May 4, 2012Print Subscribe

Rapidly Find Data Using Quick Find

The simplest method to search for data in Code On Time web applications is Quick Find.

Quick Find is located on the left side of the action bar above grid views. It allows searching for values in the visible fields. The application will split the search text by spaces and use each word as a filtering parameter when executing a query.

For example, let’s find any customer that contains the text “owner”. Enter the text in the Quick Find, and press Enter on your keyboard. You can see that we now have a list of records that contain “owner” in the Contact Title field.

Let’s find all owners in Mexico. In the Quick Find box, type “owner mexico”, and press Enter on your keyboard. This will fetch a shorter list. All data rows will contain both words in the fields. For example, “owner” is found in Contact Title, and “mexico” is found in Country.

The order of words in the query does not matter. For example, search for “france owner”. The search will find all three owners from France.

You can also search for numeric values in any field of the record. Switch to the Products page, and search for “40”. The first product has “40” in Units On Order. The second product has a Unit Price of “40”. The third product has “40” in Quantity Per Unit.

Numbers and words can be mixed in the search query. Search for “40 biscuit”. The search query found two records that have those parameters.

The user input is never incorporated in the SQL text, eliminating possibility of an SQL Injection attack. Search for “drop database”. No records will be found, and the database will not be dropped. The application framework will include parameter names in the query and pass the user-entered criteria as parameter values.

You can clear any filter by either clicking on the “x” icon on the right side of the filter detail bar, or by clicking on the filter description.

Developers can also configure “hidden” fields to participate in the Quick Find.

Labels: Security, User Interface, Web Application Generator