Friday, August 8, 2014
Passing Session Parameters to Stored Procedures

Code On Time generator allows creating data controllers from the result set of a stored procedure. Some stored procedures use parameters in order to perform operations on the data. In the Northwind sample database, the CustOrderHist stored procedure accepts a CustomerID as a parameter and displays a list of products and totals ordered for that customer.

Let’s create a controller from this stored procedure and pass a session variable as the parameter.

This picture shows the results of the stored procedure with a CustomerID passed as a session variable..

image

The CREATE script for the stored procedure can be seen below.

CREATE PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

Creating the Controller

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Click on the New Controller icon on the toolbar.

Creating a new controller in the project.

Enter a name for the controller.

Property Value
Name CustOrderHist

Press OK to save. Right-click the new controller and press “Generate From SQL…”.

Generating the new controller from SQL.

In the SQL script textbox, paste in the following script. The debug section is removed from the business rule when the application framework executes the script at runtime and declares the session parameters.

-- debug
DECLARE @Session_CustomerID char(5)
-- end debug

DECLARE @CustomerID char(5)

if (@Session_CustomerID is not null)
    set @CustomerID = @Session_CustomerID

EXEC [dbo].[CustOrderHist] @CustomerID

Press OK to generate the controller.

Adding Controller To Page

Next, let’s add the controller to a page. Right-click on the controller and press Copy.

Copying the CustOrdersOrders controller.

Switch to the Pages tab in the Project Explorer. On the toolbar, press the New Page icon.

Adding a new page to the project.

Give a name to the page and press OK to save.

Property Value
Name Customer Order History

Right-click on the page and press Paste to instantiate the controller as a data view on the page.

Pasting onto the Customer Orders page.

Let’s hide the page from the menu by right-clicking on Customer Orders page and pressing Exclude From Menu.

Excluding the Customer Orders page from the menu.

Creating Action to Assign Session Variable

Users will access Customer Order History by a custom action on the Customers page. This action will set the session variable and redirect to the correct page.

In the Project Explorer, switch to the Controllers tab. Right-click on Customers / Actions / ag4 (ActionBar) – Edit/Delete node, and press New Action.

Creating a new action in Customers controller.

Assign the following values:

Property Value
Command Name SQL
Header Text Show Orders
Data 
set @Session_CustomerID = @CustomerID
set @Result_ShowAlert = 'You will be redirected to orders ' +
                        'made by customer "' + @CompanyName + '".'
set @Result_NavigateUrl = 'CustomerOrderHistory.aspx'
When Key Selected Yes

Press OK to save the new action.

Viewing the Results

On the toolbar, press Browse. Navigate to the Customers page, and select a record. Open the context menu by clicking on the top right button, and select “Show Orders” action.

Activating the "Show Orders" action on the Customers page.

A dialog will appear informing the user that they will be redirected.

Alert shown to the user that they will be redirected.

The Customer Order History page will load, and display the relevant order information about the customer using the session variable as a parameter.

image