Data Sources

Blog
Data Sources
Friday, January 23, 2015PrintSubscribe
Creating Project for Touch UI

What is Touch UI?

Touch UI is the new user interface of single page apps (SPA) created with Code On Time database application generator. This responsive user interface is based on jQuery Mobile and adapted to work on both mobile and desktop devices with any screen size. It also integrates popular Bootstrap framework to simplify content creation and content oriented SPAs.

Choose Project Type

Touch UI is available in applications created as Azure Factory, Mobile Factory, Web App Factory, or Web Site Factory project.

End users of your application will not be able to tell its project type. 

Developers will notice that Mobile Factory and Web Site Factory project files are grouped in folders, which allows Microsoft ASP.NET to compile the code dynamically. Web App Factory project has a more complex folder structure and uses a solution file to keep track of the project components. It must be compiled in Visual Studio explicitly. These three project types can be deployed to any physical or hosted Windows Sever.

Azure Factory project type is similar to Web App Factory. It is designed specifically for deployment and hosting in Microsoft Azure as a cloud service.

The most versatile and simplest to maintain project type is Web Site Factory.

Choosing a project type for an application with Touch UI.

Start the app generator and create a new application by choosing the desired project type.

Framework and User Interface

Make sure that application framework is set to .NET Framework 4.5 or higher. Also select Touch UI as the user interface default option.

If you are creating your project with Unlimited edition of the app generator, then your application will support both Touch UI and Desktop UI. The secondary user interface will work with browsers that are not compatible with HTML5. Your app will automatically downgrade to desktop user interface as needed. If you choose Desktop UI as a default user interface option then Touch UI will only be activated on mobile devices.

Note that dual user interface is not supported in other product editions.

Specifying Framework and User Interface for an app with Touch UI.

Click Next until your reach a page that allows configuring a database connection for your project.

Configuring Data Provider

 Code On Time allows creating apps straight from your database or from external data sources such as web services or file system.

The default data provider for the app is Microsoft SQL Server. A variety of other database engines is also supported.

If you are not planning to use a database engine as a source of data then choose Custom Data Provider option in the drop down.

Specifying a data provider for an app with Touch UI.

Click on the button with three dots next to Connection String input to configure the connection string for the selected provider.

Setting Up a Sample Data Set

If you are creating a project for Microsoft SQL Server or Microsoft SQL Server Express then you can configure a sample database that is used in the tutorials.

First make sure to specify your server address. If you have Microsoft SQL Express installed on your computer then make sure to enter “.\sqlexpress” without double quotes in the Server input field.

Enter Northwind in the Database input and click Create button. A confirmation will be displayed when an empty database has been created.

Creating an empty sample database for a project with Touch UI.

Select Northwind  in the drop down under Sample Tables. Click Install to create a sample data set. Wait for the confirmation to be displayed.

Populating empty sample database with data for a project with Touch UI.

Add a security system to your project by clicking Add button under Membership section.

Adding a security system to a project with Touch UI.

The new database with sample data is configured and you can click OK to save settings and see the application connection string parameters.

Controllers, Pages, or Empty Project

Now it is time to decide if you want the app generator to create sample data controllers and pages (single page apps) from the contents of your database.

If you are planning to build a collection of single page apps using jQuery Mobile or Bootstrap then choose only Data Controllers to generate. Consider taking advantage of the automatically created SPAs and choose Data Controllers and Pages. If the pages are not matching your needs then you can always delete them later in Project Designer.

If your plan to define data controllers later then choose Empty Project instead.

Choosing data controller and page creation mode for the app with Touch UI.

Choosing Database Tables

The app generator can created data controllers and SPAs for every table and view found in the database. This will be the case if you proceed to the Next step.

For the purpose of the tutorials click Change  next to “All database tables and views are included in this project” and choose tables listed at the bottom of the next screenshot.

Choosing specific tables to be used for generation of data controllers and SPAs in a Touch UI app.

New database tables and views can be added to a project later. 

Press Next until your arrive to Reporting configuration section of your project.

Reporting

Application framework  creates dynamical reports in PDF, Word, Excel, and TIFF format. Complex master-detail custom reports can also be created.

We recommend enabling reports even if you are creating SPAs with jQuery Mobile or Bootstrap without relying on data presentation capabilities of Touch UI. Reports can be defined on the server and invoked with minimal  JavaScript code via custom user interface.

Enabling reports for an app with Touch UI.

Generating Application

Continue pressing Next and you will reach a page with a summary of data controllers that will be created in your project. The summary will be blank if you are creating a project with a custom data provider.

Summary of data controllers that will be created in Touch UI app by Code On Time application generator.

Click Generate button and wait for the  web browser window to open.

Starting project code generation in Code On Time.

Application generator will create the source code of your project, compile it, launch IIS Express, and start the web browser with the home page address of your app loaded.

Exploring App with Touch UI

Touch UI application is a collection of pages (single page apps) that provide access to content or data. A unified navigation system allows switching between single page apps. The default application will have a single SPA called home with several virtual pages.

The default home page of a single page app created with Code On Time.

Click on Site Map or Instructions to transition to the corresponding virtual page of the SPA. The default transition on Android devices is “fade”. Other mobile devices and desktop computers will use “slide” transition animation.

Proceed to Instructions and click or touch Login button. Enter admin/admin123% as user identity and hit Enter key.

Logging into a Touch UI app created with Code On Time.

SPA Home will reload and three  options will be displayed in the sitemap.

Home SPA of a user with a known identity in a Touch UI app.

Option Membership allows administrators to access user and role manager built into the app.

Buil-in user and role manager in app with Touch UI.

If you have an app created with Data Controller and Pages then the navigation menu will have numerous SPA options.

A navigation system with single page apps automatically created by Code On Time applicaiton generator.

Application SPAs will provide access to data stored in database tables and views. This particular single page app allows managing employees stored in the sample database in Employees table.

A single page app allows managing employees in the application with Touch UI created with Code On Time.

Changing App Settings

Click or touch Menu button on the left side of the application toolbar. Select Settings option in the menu and change the application theme to Dark.

Changing settings of application with Touch UI.

This is how the Membership Manager will look in Dark theme. There are 37 themes to choose from.

An alternative Dark theme activated in an app with Touch UI.

Touch or click Menu button again and select Logout in the slide-out drawer panel. The current SPA will reload.

Logging out of the app with Touch UI.

The original application theme will be displayed. You are now an anonymous user and the default theme will be activated.

If the last SPA has required authorization than that application page will not be loaded. Instead user will be transitioned to the home SPA accessible to anonymous users and asked to log in.

Home SPA will request identification if the user just has logged out of the SPA that required authorizaton in an application with Touch UI.

Many application settings and features are user-configurable and stored in the local database of your browser.

Configuring Default Settings of Touch UI

You can specify default settings of Touch UI for your project such as global display density or label alignments in data-enabled SPAs.

For example, if you notice that transition animations between virtual pages are not smooth enough on the hardware of your end users than consider changing default settings for transitions. Select your project on the start page of the app generator and choose Features in the project settings.

Change features of the app with Touch UI in Code On Time.

Select Touch UI section and set Transitions to None. This will disable transitions between virtual pages of SPAs by default.

Specifying default configuration settings for the Touch UI application.

Application end users can still choose their preference for transitions on their own.

Developers can remove various settings from the user interface with a few lines of JavaScript.

Saturday, August 9, 2014PrintSubscribe
Passing Business Rule Properties 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 [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.

Employee Sales by Country stored procedure results filtered 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.

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 an SQL script.

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.

Copying the EmployeeSalesByCountry controller.

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

Creating a new page in the project.

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 side of Home page node.     Employee Sales By Country page placed after the Home page node in the site menu.

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

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

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.

Adding a new class to the project.

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.

All employee sales are displayed.

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

No employee sales are displayed.

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