Premium Database App Builder for Mobile and Desktop

  Blog

Blog
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.

Thursday, July 31, 2014PrintSubscribe
Introduction to Data Controllers in Code On Time

Controllers are the backbone of any Code On Time web app. The Code On Time application framework uses these XML files as a definition for how the application will work and be interacted with by the user.

Structure of a data controller.

Each controller may contain an automatically created command – essentially a list of developer-friendly field definitions that will be used by the application framework to create on-the-fly SQL commands for Create, Read, Update, and Delete operations. A list of fields will be added that match up to the fields in the table. Several default views will be created so that the user can perform CRUD operations, each view containing data fields that bind a field to a view. In addition, a standard set of actions will be added to provide a fully functional action state machine at run-time – users can select, edit, print reports, create RSS feeds, and more. These actions are placed into action groups that determine where the action is displayed in the user interface.

Controllers can be instantiated on a page using data views placed on these pages (not displayed in the above diagram).

There are multiple ways of creating controllers in order to allow users access to data.

1. Automatic Creation From Database

When a Database Connection string is specified during the creation of a web app, the app generator will create a data model of the tables and views specified by the developer. Then, controllers will be composed from the data model with various optimizations that result in a fully functional application out of the box. Provided that the table has a primary key defined in the database, users will be able to insert, update, and delete records without any work on the developer’s part. The developer can then proceed to modify the design of the controller using the Project Designer to their heart’s content.

This is the easiest method to define controllers, as it is all done automatically by the generator. Note that it requires pre-defined tables and views in the database.

2. Generating a Controller from SQL Query

The Project Designer also allows the generation of controllers from a developer-defined SQL query. The script can be of any complexity – feel free to use any combination of UNIONs, JOINs, GROUP BYs, sub-queries, or any other SQL functions to sculpt the result into the correct shape. The picture below shows an example of such a script.

Defining a data controller from an SQL query.

The application generator will take the script result and compose a data model. Like with automatic creation, a controller will be composed with all the necessary elements, including a command.

Data controller elements created using the "Generate From SQL" option.

The controller will, by default, permit users to perform CRUD operations if a primary key was detected. The developer will have to mark the read-only fields, disable actions, and/or override the default commands with custom code or SQL.

3. Generating a Controller Using Business Rules

Another method to create a data controller is by defining a result set from an SQL query. This method can be used to display the results of stored procedures or other functions in the database. The “Define Data Controller” tool in the Project Designer allows specifying the SQL script and will compose the controller automatically. The controller will not have a command, and the Select action will be overridden with two SQL business rules. The first one will set EnableResultSet property to “true”. The application framework will then use the results of the second business rule to compose the result set. Three additional rules will prevent the user from triggering insert, update, and delete actions. An example can be seen below.

Defining a result set from an SQL query.

Once the controller is generated, a default set of views, actions, and business rules will be added.

The generated controller from defining a result set.

4. Generating The Controller From Web Service

If the controller will be used to display a result set from a web service or other programmable source of data, the developer can create the controller and define the fields using the Project Designer. Then, use the “Generate From Fields” option to create all views, data fields, actions, and several code business rules automatically, as shown below.

The views, actions, data fields, and business rules have been generated from the field definitions.

The first code business rule, with ID of “GetData”, will assign to the ResultSet property using an automatically generated method. The method’s default implementation will return an empty data table. It is up to the developer to implement retrieving data from the source (web service, static definition, etc).

The next three business rules will prevent the user from triggering any default Insert, Update, or Delete actions. The developer must implement the code for these actions to have any effect.

5. Creating the Controller Manually

The developer can always choose to create the controller manually in the Project Designer. While this may be more involved with creating the prerequisite elements, such as fields, views, data fields, and actions, the developer can create exactly what they deem necessary. In addition, the developer can define result sets from any source using C# or Visual Basic business rules, affording a greater complexity. It’s even possible to create a web-based file management system using a custom controller by using .NET’s file management classes. They are limited only by their ingenuity.