Data Controllers / Creation
From SQL Query

 Code On Time generator offers the ability to compose controllers from the results of custom SQL scripts.

The script can range in complexity from performing a simple “select *” from a table to any complicated SQL expression to combine fields from multiple tables.

Here is an example of a result set produced by an arbitrary SQL query.

The result of the SQL query can be viewed in the browser.

In this example, we use a script that displays fields from tables Customers, Orders, Order Details, and Products by using joins on the foreign keys CustomerID, OrderID, and ProductID. The query can be seen below.

SELECT CompanyName, OrderDate, ProductName, Quantity 
    FROM Customers 
        JOIN Orders 
            JOIN [Order Details] 
                JOIN Products 
                ON [Order Details].ProductID = Products.ProductID 
            ON Orders.OrderID = [Order Details].OrderID 
        ON Customers.CustomerID = Orders.CustomerID

Example output can be seen in the following picture.

Results of the query.

Let’s generate a data controller for this query.

Start the web app generator and activate 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 Explorer.

Give the controller a name:

Property Value
Name ItemsOrderedByCustomer

Press OK to save the new controller. In the Project Explorer, right-click on the new controller and press “Generate From SQL…”.

Defining the new ItemsOrderedByCustomer controller

The Define Data Controller window will open. Paste in the script at the beginning of this article. Press Verify and the result will be seen in the data grid, as in the picture below:

Verifying the results of the query in the "Define Data Controller" window.

Select “Command Text” radio button. This will use the SQL script as a command and will take advantage of the application framework’s ability to compose SQL statements on the fly. SQL formulas will be defined for each field.

The “Base Table Name” property will be used in any dynamically created Insert and Update statements. Leave this property blank for now.

Keep default settings and press OK to define the data controller. The window will close and the Project Explorer will refresh with added fields, views, data fields, actions, as well as the command or business rules.

The data controller has been defined and now has a command.

Next, we will need to create a page and bind the controller to the page with a data view. Right-click on the controller and press Copy.

Copying the 'ItemsOrderedByCustomer' data controller.

Switch to the Pages tab in the Project Explorer. Click on the New Page icon.

Creating a new page in the web app.

Assign a name.

Property Value
Name ItemsOrderedByCustomer

Press OK to save the page. Drag the new page in the Project Explorer to the right of Home page node to place it second in the site menu.

Dropping the 'Items Ordered By Customer' page to right side of Home page node.     The 'Items Ordered by Customer' page is now second in the app menu.

Right-click on the new page and press Paste to bind the controller to the page.

Pasting into the 'Items Ordered by Customer' page.     The controller has been bound to the page with a data view.

On the toolbar, press Browse to generate the app and open it in the default browser.

The result of the query will be visible on the page. The user can sort, filter, and view data.