Blog

Code On Time apps offer the ability to display the results of a stored procedure. Some stored procedures require passing an SQL parameter in order to perform manipulations on the data.

In the Northwind sample database, the [Employee Sales By Country] stored procedure shows total sales amounts grouped by employee, and then by country. It accepts two parameters, @Starting_Date and @Ending_Date to determine the filter.

Let’s create a controller from this stored procedure. By default, the stored procedure will display all records between the years 1970 and 2000. In addition, we will add a custom action that will allow the user to specify the Beginning and Ending dates via a custom confirmation controller.

The picture below shows the confirmation controller form allowing the user to specify parameters for the stored procedure.

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

CREATE procedure [dbo].[Employee Sales by Country] 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT    Employees.Country, 
        Employees.LastName, 
        Employees.FirstName, 
        Orders.ShippedDate, 
        Orders.OrderID, 
        "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Creating the Controller to Display the Stored Procedure

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

Enter a name for the controller.

Press OK to save. Right-click the new controller and press “Generate 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 parameters.

-- debug
DECLARE @Session_BeginningDate datetime, @Session_EndingDate datetime
-- end debug

if (@Session_BeginningDate is null)
    set @Session_BeginningDate = '1970'

if (@Session_EndingDate is null)
    set @Session_EndingDate = '2000'


EXEC [dbo].[Employee Sales by Country]
    @Session_BeginningDate,
    @Session_EndingDate

Press OK to generate the controller.

Setting the Session Variable

Note that the parameters returned from the search dialog will not be cached. These parameters must be saved into a session variable. In the Project Explorer, double-click on the EmployeeSalesByCountry / Business Rules / Select (Sql / Before) – enableResultSet node.

Replace the script with the following:

set @BusinessRules_EnableResultSet = 1
-- Enable caching of the result set. Duration is specified in seconds.
-- set @BusinessRules_ResultSetCacheDuration = 30 

if (@Parameters_BeginningDate is not null)
    set @Session_BeginningDate = @Parameters_BeginningDate

if (@Parameters_EndingDate is not null)
    set @Session_EndingDate = @Parameters_EndingDate

Press OK to save the new script.

Adding Controller To Page

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

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

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

Drop the new page to the right side of Home page node to place it second in the site menu.

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

Adding the Custom Action

Switch back to the Controllers tab in the Project Explorer. Right-click on EmployeeSalesByCountry / Actions / ag3 (ActionBar) – New node, and press New Action.

Specify the following values:

Press OK to save the action.

Creating the Confirmation Controller

Let’s add a controller that will allow the user to specify BeginningDate and EndingDate parameters for the stored procedure.

On the Project Explorer toolbar, press the New Controller icon.

Enter a name for the controller.

Click OK to save the controller. Right-click on FilterEmployeeSales / Fields node, and press New Field.

Define the field as follows:

Save the field, and create a second field with these values:

Save the EndingDate field. The confirmation controller is now complete.

Viewing the Results

On the Project Designer toolbar, press Browse. In the browser window that will open, navigate to the Employee Sales By Country page. Note that all 809 records are displayed.

In the sidebar or context menu, press Filter View action. The page will navigate to a form with the Beginning Date and Ending Date fields. Enter values, and press OK.

Note that the parameters have been passed to the stored procedure and there are only 17 records displayed now.

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 [Employee Sales By Country] stored procedure shows total sales amounts grouped by employee, and then by country. It accepts two parameters, @Starting_Date and @Ending_Date to determine the filter.

Let’s create a controller from this stored procedure and pass parameters to the script via properties in the BusinessRules class of the app.

This picture shows the results of the stored procedure with @Beginning_Date and @Ending_Date parameters returned by a business rule property.

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

CREATE procedure [dbo].[Employee Sales by Country] 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT    Employees.Country, 
        Employees.LastName, 
        Employees.FirstName, 
        Orders.ShippedDate, 
        Orders.OrderID, 
        "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

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.

Enter a name for the controller.

Press OK to save. Right-click the new controller and press “Generate 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 business rule properties as SQL parameters.

-- debug
DECLARE @BusinessRules_BeginningDate datetime,
        @BusinessRules_EndingDate datetime
-- end debug

EXEC [dbo].[Employee Sales by Country]
    @BusinessRules_BeginningDate,
    @BusinessRules_EndingDate

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.

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

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

Drop the new page to the right side of Home page node to place it second in the site menu.

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

Adding Business Rule Property

Let’s create two properties in the BusinessRules class. These properties will return a DateTime value that will be picked up and used by the query to filter the results. If the user is in role “Administrators”, it will display all records between 1970 and 2000. Otherwise, no records will be displayed.

On the Project Designer toolbar, press Browse to first generate the web app. Then, press Develop to open the solution in Visual Studio.

In the Solution Explorer on the right side, right-click on App_Code folder and press Add | Class.

Assign a name of “EmployeeSalesByCountryProperties” and press OK to create the file. Replace the contents of the file with the following:

C#:

using System;

namespace MyCompany.Data
{
    public partial class BusinessRules
    {
        public static DateTime BeginningDate
        {
            get
            {
                if (Controller.UserIsInRole("Administrators"))
                    return new DateTime(1970, 1, 1);
                else 
                    return DateTime.Now;
            }
        }

        public static DateTime EndingDate
        {
            get
            {
                if (Controller.UserIsInRole("Administrators"))
                    return new DateTime(2000, 1, 1);
                else
                    return DateTime.Now;
            }
        }
    }
}

Visual Basic:

Imports Microsoft.VisualBasic

Namespace MyCompany.Data
    Partial Public Class BusinessRules

        Public ReadOnly Property BeginningDate As DateTime
            Get
                If Controller.UserIsInRole("Administrators") Then
                    Return New DateTime(1970, 1, 1)
                Else
                    Return DateTime.Now
                End If
            End Get
        End Property

        Public ReadOnly Property EndingDate As DateTime
            Get
                If Controller.UserIsInRole("Administrators") Then
                    Return New DateTime(2000, 1, 1)
                Else
                    Return DateTime.Now
                End If
            End Get
        End Property

    End Class
End Namespace

Make sure to save the file.

Viewing the Results

Press Ctrl+F5 to start the app without debugging. Log in as an administrator and navigate to the Employee Sales By Country page. Notice that all 809 records are displayed.

Log out, and log in again as a user. Notice that no records are displayed.