Code On Time: Data Sources

Blog

Data Sources

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

Friday, May 4, 2012Print Subscribe

Activating a Stored Procedure With a Master-Detail Relationship

An output caching script can be activated with user-defined filters.

Filters explicitly exist in the configuration of master/detail pages. Frequently, custom result sets are produced with the master ID specified as a parameter of a stored procedure or web service. The output may be presented as a detail view in the user interface.

Let’s place a data view of customer order history produced by CustOrderHist stored procedure on the Customers page in Northwind sample.

Activate the Project Designer and right-click Customers / container2 node. Select New Data View option to create a new data view.

Enter the following properties and click OK button.

Property	Value
Controller	CustOrderHist_Cache
View	grid1
Activator	Tab
Text	Order History
Page Size	5
Filter Source	view1
Filter Field #1	Customer ID
Auto Hide	Container

The output cache controller CustOrderHist_Cache has its SQL Business Rules configured to execute the output caching script to capture the data rows produced by CustOrderHist stored procedure.

Click Browse button on the designer tool bar. A browser window will open. Navigate to the Customers page and select a customer. Switch to the Order History tab to view the customer order history.

If the Order History tab is visible, then the output caching script will execute every time you select a customer in the master grid view or navigate to a master data row in the form view. The stored procedure CustOrderHist will be invoked by the script only if more than 30 seconds have passed since the last selection of the same master data row.

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

Friday, May 4, 2012Print Subscribe

Activating a Stored Procedure With User-Defined Filters

The stored procedure output caching script is configured to execute when an application requires a row count and there are no unexpired data rows in the output cache table matched to the current user.

This is fragment of the script that does the verification of the condition.

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
    . . . . .

The application framework automatically assigns values to the parameters @BusinessRules_RequiresRowCount and @BusinessRules_UserName. The value of the first parameter is set to “1” when a number of available rows must be calculated. The value of the second parameter is assigned to the name of the current application user.

The highlighted parameter @CustomerID_FilterValue is matched to the field CustomerID of the output cache controller. If the data is being filtered by CustomerID, then the user-specified filter value is assigned to the parameter. If there is no user-defined filter then the application framework will assign a NULL value to the parameter.

If you navigate to the dedicated page of the output cache table CustOrderHist_Cache, then you will see no records there. The script specified in the SQL Business Rules of the data controller is triggered but will not cause execution of there stored procedure since there is no filter.

Activate the search bar and enter “ANTON” in the CustomerID field. Notice that there will be no auto-completed values as you type the text.

Hit Enter key and the list of matched products purchased by the customer will show up.

Try entering other customer IDs and activating search. For example, try “AROUT”, “BONAP”, and “BLONP” values. Every time the relevant data rows are displayed. You will also notice that the auto-completion kicks in as your type initial letters of the values.

Clear the filter and sort the customer order history by Product Name. You will see all records that are captured in the cache output table.

You can also initiate a filter directly from the context menu of the Customer ID column.

Select Customer# / Text Filters / Equals option and enter a customer ID.

Hit Enter key to apply the filter. The relevant data rows will populate the output cache table.

It does not matter to the output caching script what filter operation is selected in the context menu of the column Customer ID. You can choose Equals or Does Not End With and the stored procedure will be invoked by the script in both cases.

The chosen filter operation will affect the selection of data from the CustOrderHist_Cache table after the script has been executed. For example, if you choose Does Not Equal and enter a valid customer ID, then you will see the previously cached data rows. None of them will match the entered ID. In fact the relevant data rows have been inserted in the table by the output caching script. The filter operation Does Not Equal hides them from the user.

If you want to fine-tune the output caching script, then consider using the @CustomerID_FilterOperation parameter. The value of the parameter is a string representing the selected filter operation spelled without spaces. For example, the value of the parameter may be equal to “DoesNotContain” or “EndsWith”. The value is equal to NULL, if the field is not filtered by the application user. Use the filter operation as an additional condition for the activation of the stored procedure in the output caching script when needed.

If you activate a Customer ID filter on the page and add a new customer order of any product, then the subsequent filtering of the history by the same Customer ID will most likely reflect that. The caching timeout is set to 30 seconds. It will probably take longer to enter a new order and activate a filter. If you change the script to cache records for a longer period of time, then there will be a period of discrepancy between the cached data and the actual data records. It is up to you to figure the acceptable cache expiration that can be tolerated by business users and processes.

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