Let’s create a simple inventory management application with the help of Code On Time app builder. The app will run in the default web browser on the development machine. The same app will allow the native installation. The app will work in the offline/disconnected mode when configured with the Offline Sync.

This tutorial requires the basic database management skills only. If you know how to create tables and select data with SQL, then jump right in!

Download and install the development web server Microsoft IIS Express and the Code On Time App Builder before you begin.

Database

The app builder supports several database engines. This tutorial will work with any edition of Microsoft SQL Server. Create the new database in the cloud hosted in Microsoft SQL for Azure or your own server on-premises. You may need to contact your database administrator to get this done. Make a note of the database connection parameters, such as the server address, username, and password.

Alternatively get the free version of the powerful Microsoft SQL Server Express and install it on the development machine. You can skip creating the database since it can be done from the app builder.

This is the database schema of the inventory management system that we are implementing.

Building Online App

Online application has its back-end published to a web server. Users interact with the application front-end in browsers. Both are represented by a collection of files stored together in the web server file system. Code On Time creates applications with the complete front-end and back-end code. The development machine plays the role of the server.

Creating Project

Run Code On Time and start creating a new application. Enter Inventory Manager as the project name and press Create. The first page of the project configuration wizard will be displayed. Proceed with the default settings to the Database Connection configuration.

Connection String and Database Configuration

Keep the Sql Server as the Data Provider and click the “more” button next to the Connection String input.

If you are using Microsoft SQL Express as your database engine, then enter .\SQLEXPRESS as the Server address. Press Test to confirm the connection. Enter northwind-db1 in the Database field and press Create to create the empty database.

If you are working with the hosted database engine, then enter its connection parameters. Press Test to confirm the connection and choose your database name from the drop down menu.

Select Northwind under the Sample Tables and proceed to Install. Sample tables will be created and populated with data. Wait for a confirmation message to appear.

Press the Add button under the Membership section to install the security system. Three tables will be created to maintain users, roles, and their relationships in the database. This step is required.

Users must be authenticated and assigned the “Offline” role to work in the offline mode.

Optionally press the Add button under the Content Management System section to install the single-table Content Management System. The table is used in the apps created with Code On Time to keep track of various runtime settings. This step is required if you plan to take advantage of the built-in RESTful API Engine or enable the Single Sign-On with the external OAuth providers.

Press the OK button to finish the configuration of the database and its connection string.

Press Next to configure the data models.

Data Models and App Preview

The sample database schema defines the Suppliers, Products, and Categories tables. These tables are the foundation of the inventory management system that we are building. Now it is the time to define their models.

Developers create the data models from the database entities with the help of the Model Builder. Available entities are automatically discovered by the app builder. Developers can preview the application as soon as the models are defined.

The data models are the building blocks of applications. Create models and preview the app as explained in the Inventory Manager Example.

The preview of the app is displayed in a browser when the app is generated. The app builder will produce a data controller for each of the three models while using them as configuration blueprints. The data controllers are XML files providing the runtime information to the server-side and client-side frameworks of the application (its back-end and front-end).

If you have followed all steps described in the example, then there will be three models and their corresponding controllers in the app. The Products data controller will be in charge of the access to the product inventory. The Suppliers and Categories controllers will have the Products field of the “DataView” type pointing to the namesake controller. The field will provide users with access to the related products.

Your app runs at the localhost address in the default web browser of the development machine. The server-side code is hosted by the development web server Microsoft IIS Express. The app builder launches the instance of this server to preview the app.

Let’s make a few modifications to alter how users are entering data in forms. We will make the data entry appear more natural and help to prepare the application to run in the offline mode.

Data Entry in Online Forms

Navigate to the Suppliers page and begin creating a new supplier. Note that it is not possible to enter the products of a new supplier. There is the Products data field in the createForm1 form of the Suppliers controller. Yet it remains invisible. The app needs to get the primary key of the supplier to display the list of related products. The primary key is not known until the record is posted to the database. This is the reason why the client-side framework hides the products in the New Suppliers form.

Enter the required Company Name and press the Save button. The new row will be inserted in the Suppliers table and automatically selected in the Review Suppliers form. The list of products will be visible since the primary key of the existing row is known to the framework.

By default the data entry commands insert, update, and delete are executed immediately. The supplier row is created in the database table when the New Suppliers form is saved. Related products are added to an existing supplier one at a time in the Review Suppliers form. It is not possible to undo a data entry operation by canceling the parent supplier form.

Data entries are posted immediately upon submission by users. This gives a peace of mind when working with the structured data.

Immediate posting of data does not mirror how we work with the unstructured documents. Multiple changes made in a document or spreadsheet are persisted when users choose to save the file. A more natural mode of entry is to allow adding products when a new supplier is being entered in the system. It is more natural for the supplier and its products to be committed to the database when the form is saved. Users may expect changes to be discarded when the form is canceled.

It is possible to enable the transactional/atomic data entry in apps created with Code On Time. In fact, this is the only method of data entry in the apps working in the “offline” mode.

Atomic Data Entry With Offline Data Processor

Apps created with Code On Time have the built-in Offline Data Processor (ODP). The processor is a part of the client-side framework. It allows atomic data entry in the forms.

Select the inventory manager project on the start page of the app builder and choose the Design option. Select the Suppliers page in the Project Explorer on the right-hand side of the screen. Mark the page as “offline” and save. Exit the Designer and proceed to generate the application.

Start creating a new supplier. Products are immediately available for entry in the New Suppliers form. ODP is activated on the “offline” pages when users enter new data items or edit the existing ones. ODP maintains the JSON representation of data on the client. The processor assigns the unique temporary IDs to the primary key fields of new items. It also keeps track of the data entry operations performed by users in forms.

Saving of the top-level form will force the processor to send the log of changes to the application running on the server. The server-side framework begins a database transaction and replays the user data entry operations from the log. If there are no exceptions, then the transaction is committed. Otherwise all changes are rolled back and the error is reported to the user. Users may choose to cancel the top-level form and discard the changed master and detail data items.

Offline Data Processor ensures the atomic data entry for users on the pages marked as “offline”. A complex structured data form turns into an electronic document. Changes are committed to the database when the top-level form is saved.

Follow the steps described above to mark the Categories and Products pages as “offline” in the Project Designer. The atomic data entry will become enabled on the application data-aware pages. Keep in mind that the data items exist in the memory of the app on the client and may not be available to the server-side business rules until the top-level form is saved. Developers will need to write the JavaScript business rules if the client-side calculations on the field values are required.

“Offline” Check-List

Offline Data Processor takes the data “offline” on the online pages already. The client-side components of the app itself must be taken “offline” along with the complete dataset available to users for a true disconnected experience.

The client-side framework consists of HTML pages, fonts, CSS, and JavaScript files. These files are hosted with the application code on the server. Browsers download the client framework files when users navigate to the app pages. The references in a page tell the front end code in the browser which files are required. Apps may load some resources dynamically. A page may also have direct and indirect references to the data controllers.

Here are the things that need to be done to take the app “offline” on any device:

• Download the client-side framework with the app pages and save them locally.
• Download the user data to the device.
• Make browsers to use the downloaded pages and framework files when users navigate the app.
• Intercept the “read” requests and perform them locally on the downloaded copy of user data.
• Intercept the “write” requests and perform them locally as well.
• Maintain the log of data entry operations locally.
• Provide users with the ability to to upload the log of data entry operations to the app on the server.
• Provide users with the ability to refresh the client-side framework files and user data.

Going “Offline” With Offline Sync Add

Going “offline” may appear as a daunting task. Luckily we have a magic solution called Offline Sync. This optional component will turn an online app created with Code On Time into an app that allows users to work in the offline mode. Offline Sync is enabled in the apps created with the Community and Enterprise editions of Code On Time.

The standard user account offline and the Offline role are created in the app database when the membership is enabled. You can designate the “offline” users in the membership manager. Sign in as admin and navigate to the Membership page. Create the Offline role if needed and assign the role to the user account. Save the changes.

Logout and proceed to sign in with the user account. The downloading of the front-end files and account data will commence.

The atomic data entry by users produces a log of transactions when the top-level forms are saved. The log is persisted on the client. The anticipated effect of data entry is simulated on the local copy of data by the offline data processor. Users can search, filter, and sort their data.

The “cloud” icon is displayed on the right side of the toolbar. The icon will change to reflect the status of the network connection and when there are pending changes that were not synchronized with the server. Tap on the icon will start the synchronization process.

The synchronization is always available as the first option in the context menu of the app running in the offline mode when there are no active forms. Users must save or dismiss the active forms to start the synchronization.

Synchronization must be attempted when the device has a solid network connection. The pending changes are sent to the app back-end for processing. Each individual atomic data entry sequence is processed in the context of a database transaction. The failure to commit will stop the synchronization and give users an option to either fix the issue or discard the problematic transaction in the log.

The synchronization prompt provides the Refresh Data option. If the option is enabled, then the entire application and the user account data will be refreshed upon the successful upload of the pending changes. Users must evaluate the duration of the last data refresh since the app will not be available for normal use while the sync is in progress.

The mix of online and offline pages may be available to users in the “Offline” role. The navigation menu options are reduced to the “offline” pages when the network connection is lost.

Users will remain logged in up to 6 months without being required to confirm their identity. Only the explicit logout will remove the user account data from the device.

Users will be automatically logged in whenever they visit the online pages of the app. Online pages always come from the server back-end. Offlines pages are served from the local cache. Developers must be careful when making significant changes to the app that has offline users. Presentation of online and offline pages may become inconsistent if the offline users do not perform the refresh during the synchronization for lengthy periods of time.

Installed vs Browser in Offline Mode

Apps created with Code On Time can be installed directly from browsers to run in the native mode. Installed app looks just like the other native apps and has its own icon on the home screen. The “Offline” users of the natively installed app will experience the disconnected mode just like they would when working with the app in a browser.

The app front-end framework uses the same set of system APIs to ensure consistent behavior across various operating systems. Application is not aware whether it is running in the browser or in the installed mode.

The installed app may have a significantly increased data storage quota allocated by the operating system.

Start building online apps that can be installed natively and run in the offline mode!