Friday, May 4, 2012

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

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.

Entering a user-defined filter value in Automatic Search Bar of a web app created with Code on Time application generator

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

The result of execution of the output caching script that invokes the 'CustOrderHist' stored procedure in the Northwind sample created with Code On Time web application generator

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.

The context menu of the column in a grid view of a web application created with Code On Time

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

Modal dialog allows entering a user-defined filter value in a web application created with Code On Time generator

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.

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.

© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at