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.