SQL Server

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.

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.

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.

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.

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

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

 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.

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.

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.

Give the controller a name:

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

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:

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.

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.

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

Assign a name.

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.

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

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.