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.