Blog: Posts from December, 2012

Code On Time web applications can be enhanced with custom reports.

Simple Filtered Report

For example, the master-detail report below can be easily created from a Northwind sample web application.

The first step is to either extend the field dictionary of an data controller with denormalization, or create a new data controller using a database view. The report field model will be defined by a view that will be added to the data controller. A custom report template will be generated for this view.

Remove the pre-configured elements from the custom report template, and set up a tablix element to display a table of data. The master fields will be placed in the header and footer of the report. Some of the fields may require some formatting changes to display correctly.

Finally, add a Report action that will trigger the custom report.

Multi-Level Grouping

The report above works fine when the data set is filtered to a specific OrderID. When multiple orders are included in the data set, the report will have invalid data. To solve this problem, consider implementing multi-level grouping, first by CustomerID, and then by OrderID. This multi-purpose report can be rendered with different filtering parameters to produce data sets with multiple orders when needed.

Database engines include built-in mechanisms for transaction management. The application can begin a transaction and have it committed or rolled back. The database engine will ensure that uncommitted data will not be visible to other users.

Web applications cannot take advantage of transaction managers built into database engines. To ensure scalability, web applications maintain a pool of database connections shared by multiple users simultaneously. Transaction managers require that all modifications of data are performed in the context of a single open database connection. A typical web application may handle requests to modify data from a single user by utilizing multiple pooled connections.

Client-Side Caching

Developers can implement client-side caching of requests to update data. The application user modifies several data items on the client and requests to submit changes at once. The entire batch of modifications is sent to the web application server for processing. The server code will parse the batch and perform individual updates using a database transaction.

Users will have to stay on the same web page until the data is submitted. There is a potential for data loss if a browser window is accidentally closed. The data entry sessions must be short.

The application developer has to rely heavily on JavaScript when manipulating data. Every data entry screen becomes a specialty project.

Server-side Data Staging

A substantially more flexible solution is to “stage” data on the server. Data records are submitted straight to the database tables, and marked as “Draft” using various techniques. When the draft data is ready to be included in the main dataset, the status of the data rows is changed to “Committed”.

Users can submit draft data from multiple pages. The data is safely stored in the database instead of volatile browser cache. The drafted data may be perfected over any period of time from multiple devices.

Application developers can take full advantage of SQL when manipulating draft data. Code On Time web applications include mechanisms that make data staging implementation trivial.

Data Staging Strategies

Suppose that you have created an Order Form web application from the Northwind sample database. Each order is stored in two tables: dbo.Orders and dbo.[Order Details]. When a user wants to create a new order, they must first create the order record, and then add a few order details.

The user should have the opportunity to add or change details, or drop the order altogether before committing it to the primary data set.

However, these “draft” orders will be included in all data sets, such as Order Subtotals. It is necessary to segregate the draft order data from the primary data set.

Consider the following methods of draft data staging when implementing web transactions.

1. “Status” Field
   
   This method involves adding a “Status” field to the Orders table that will indicate whether the order is a draft or has been committed. The status of new orders will have the default value of “Draft”. A custom action will be added to the order form to submit the order and change the status to “Commited”.
   
2. Log Table
   
   This method creates a table that holds a log of all draft orders. When a new order is created, a reference will be created in the draft order log table. When the data is submitted, the reference will be deleted from the log table.
   
3. Data Staging Tables
   
   This method requires creation of duplicate “staging” tables that will hold the draft data. When the order is ready to be submitted, it is copied to the original tables and deleted from the staging tables.

The first two methods will use dynamic access control rules in order to prevent committed data from being displayed on the order form, and draft data from being displayed on all other pages.