Data Sources

Let’s create a confirmation data controller to allow selecting a Customer ID. The value will be passed to the enhanced output caching script as a parameter.

Click on the Home link in the bread crumbs displayed under the toolbar at the top of the Project Designer. Select Controllers tab and choose New | New Controller option on the action bar.

Enter the following properties and click OK button to create the controller.

Right-click CustOrderHist_Params / Fields node in Project Explorer and select New Field option.

Enter the new field properties as follows.

Click OK button to create the field.

The field node in the Project Explorer will look as follows.

Now it is time to define a Search action in the output cache controller CustOrderHist_Cache . The action will display the confirmation data controller CustOrderHist_Params to a user and cause the CustOrderHist_Cache controller to refresh the data with the collected parameter CustomerID.

Right-click the CustOrderHist_Cache / Actions / ag5 (ActionBar) – Actions  node in Project Explorer and select the New Action option.

Enter the properties of the action and click OK button to save the changes.

Click Browse on the Project Designer toolbar and navigate to Cust Order Hist Cache page.

The new action can be accessed under Actions option of the action bar.

Notice that the Search action has replaced the standard search bar activator icon on the left of the Quick Find on the action bar. The icon will activate the Search action.

If you click the refresh icon in the bottom right corner of the grid view then this will also activate the first available Search action.

Activate the Search action with any of three methods listed above. The application framework will create a modal confirmation data controller.

Select a customer in the lookup window.

Click OK button in “Select a Customer to View History” modal popup. This will confirm the Search action and will the pass the selected Customer ID to the output caching script.

The enhanced output caching script will detect that the parameter @Parameters_CustomerID is not null and will use its value as a filter.

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.