Stored Procedure

Labels
AJAX(112) Apple(1) Application Builder(242) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(11) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) DotNetNuke(12) EASE(20) Email(6) Features(99) Firebird(1) Form Builder(14) Globalization and Localization(6) Hypermedia(2) Installation(4) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(15) OAuth(5) OAuth Scopes(1) OAuth2(6) Offline(14) Oracle(10) PKCE(1) PostgreSQL(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(163) Reports(48) REST(26) RESTful(21) RESTful Workshop(13) RFID tags(1) SaaS(7) Security(75) SharePoint(12) SPA(5) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(81) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(331) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(39) Web.Config(9) Workflow(28)
Archive
Blog
Stored Procedure
Saturday, August 9, 2014PrintSubscribe
Passing Parameter to Stored Procedure using a Custom Search Dialog

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 confirmation controller form allows the user to select a beginning and ending date to pass to 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.

Creating a new controller.

Enter a name for the controller.

Property Value
Name EmployeeSalesByCountry

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

Generating the 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 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.

Selecting the 'enableResultSet' business rule from the EmployeeSalesByCountry controller.

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.

Copying the 'EmployeeSalesByCountry' controller.

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

Adding a page to the app.

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

Property Value
Name Employee Sales By Country

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

Dropping a page to the right of the Home page node.     Employee sales by country page is now second in the site menu.

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

Pasting onto the 'Employee Sales By Country' page.     The EmployeeSalesByCountry controller has been instantiated as a 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.

Creating a new action in the 'EmployeeSalesByCountry' controller.

Specify the following values:

Property Value
Command Name Search
Header Text Filter View
Confirmation

_controller=FilterEmployeeSales
_title=Select the Beginning and Ending Dates

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.

Adding a new controller to the project.

Enter a name for the controller.

Property Value
Name

FilterEmployeeSales

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

Adding a new field to the 'FilterEmployeeSales' controller.

Define the field as follows:

Property Value
Name BeginningDate
Type DateTime

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

Property Value
Name EndingDate
Type DateTime

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.

All 809 records are displayed on the 'Employee Sales By Country' page.

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.

The confirmation controller form allows the user to select a beginning and ending date to pass to the stored procedure.

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

The BeginningDate and EndingDate parameters have been set by the confirmation controller.

Friday, August 8, 2014PrintSubscribe
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

Friday, August 8, 2014PrintSubscribe
Passing URL 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. For example, the EmployeeSalesByCountry stored procedure in the sample Northwind database takes two parameters (@Beginning_Date and @Ending_Date) in order to filter the returned result by the two dates.

Let’s allow the controller to accept URL parameters to set the @Beginning_Date and @Ending_Date for the stored procedure, as shown in the picture.

Controller generated from stored procedure accepts URL arguments as parameters.

The stored procedure definition 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

Let’s create the data controller with an SQL query that will take the parameters from the URL if present. Otherwise, it will use default values. Our script will specify defaults that allow all records to be seen. If necessary, the default values could be set to the same date in order to return zero records when URL parameters are not specified.

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

Adding a new controller.

Enter a name for the controller.

Property Value
Name EmployeeSalesByCountry

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

Generating the controller from SQL query.

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 URL parameters.

-- debug
declare @Url_BeginningDate datetime,
        @Url_EndingDate datetime
-- end debug

declare @Beginning_Date datetime,
        @Ending_Date datetime

if (@Url_BeginningDate is not null)
    set @Beginning_Date = @Url_BeginningDate
else 
    set @Beginning_Date = '1970-01-01'

if (@Url_EndingDate is not null)
    set @Ending_Date = @Url_EndingDate
else 
    set @Ending_Date = '2000-01-01'

EXEC dbo.[Employee Sales by Country] @Beginning_Date, @Ending_Date

Press OK to generate the controller.

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

Copying the EmployeeSalesByCountry 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 EmployeeSalesByCountry

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

Dropping the page to the right side of Home page node.     The "Employee Sales By Country" page has been placed second in the site menu.

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

Pasting onto the Employee Sales By Country page.     The EmployeeSalesByCountry controller has been instantiated as a data view on the page.

On the toolbar, press Browse. The page will open in your default browser. Note that all 809 items are displayed.

All 809 items are displayed on the page before the URL parameter has been specified.

In the URL bar of the browser, replace everything after “.aspx” with the following:

?BeginningDate=1996-07-01&EndingDate=1996-08-01

Press Enter. Notice that only 17 items are displayed.

The URL arguments are passed as parameters for the stored procedure.

The next steps may be to create a Navigate action that redirects to this page and sets the URL parameters.