Transactions

Blog
Transactions
Friday, December 28, 2012PrintSubscribe
Overview of Web Transaction Implementation Strategies

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.

image

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.

image

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.

Friday, December 28, 2012PrintSubscribe
Web Transactions with Staging Tables

The most robust method to ensure separation between “draft” and “committed” data is physical segregation of draft data in dedicated staging tables. When data is ready to be committed, it is moved from draft tables to the primary database tables. For example, orders in the Northwind sample database are stored in Orders and OrderDetails – the primary tables. DraftOrders and DraftOrderDetails tables will be created in order to store draft orders.

Draft Orders and Order Details table schema.

A custom action in DraftOrders controller will copy the draft order and order details to the primary tables, and delete the draft data rows.

Adding the DraftOrders and DraftOrderDetails Tables

Start SQL Server Management Studio. In the Object Explorer window, right-click on Databases / Northwind node, and press New Query.

Creating a new query for Northwind database.

Paste the following script into the query window:

create table dbo.DraftOrders(
    OrderID int IDENTITY(1,1) not null,
    CustomerID nchar(5) null,
    EmployeeID int null,
    OrderDate datetime null,
    RequiredDate datetime null,
    ShippedDate datetime null,
    ShipVia int null,
    Freight money null,
    ShipName nvarchar(40) null,
    ShipAddress nvarchar(60) null,
    ShipCity nvarchar(15) null,
    ShipRegion nvarchar(15) null,
    ShipPostalCode nvarchar(10) null,
    ShipCountry nvarchar(15) null,
    PRIMARY KEY (OrderID)
)
go

alter table dbo.DraftOrders add foreign key(CustomerID)
references dbo.Customers (CustomerID)
go

alter table dbo.DraftOrders  add foreign key(EmployeeID)
references dbo.Employees (EmployeeID)
go

alter table dbo.DraftOrders  add foreign key(ShipVia)
references dbo.Shippers (ShipperID)
go

create table dbo.DraftOrderDetails(
    OrderID int,
    ProductID int,
    UnitPrice money default (0),
    Quantity smallint default (1),
    Discount real default (0),
    primary key (OrderID, ProductID)
)
go

alter table dbo.DraftOrderDetails add foreign key (OrderID)
references dbo.DraftOrders (OrderID)
go

alter table dbo.DraftOrderDetails add foreign key (ProductID)
references dbo.Products (ProductID)
go

This script will create DraftOrders and DraftOrderDetails tables with the same columns as the primary Orders and OrderDetails tables. It also recreates the relationships with lookup tables – Customers, Employees, Shippers, and Products.

Run the script by pressing Execute on the toolbar.

Creating Draft Order Form

Follow the Order Form Sample tutorial to create a draft order form. Make sure to use DraftOrders and DraftOrderDetails instead of Orders and OrderDetails when implementing the form.

Order Form created from the draft tables.

Creating “Submit Order” Action

The next step is to create an action that will move the draft order and draft order details into the original Orders and OrderDetails tables.

In the Project Designer, switch to the Controllers tab. Right-click on DraftOrders / Actions / ag2 (Form) node, and press New Action.

Creating a new action in the form of DraftOrders.

Assign the following values:

Property Value
Command Name Custom
Command Argument SubmitOrder
Header Text Submit Order

Press OK to save. Drop a100 – Custom, SubmitOrder | Submit Order node to the left side of the first action in ag2 to place it first in the group.

Dropping action 'a100' on the left side of 'a1'.     Action 'a100' placed first.

Handling the Action

Right-click on DraftOrders / Business Rules node, and press New Business Rule.

Creating a new business rule.

Assign these values:

Property Value
Type SQL
Command Name Custom
Command Argument SubmitOrder

In the Script property, enter the following:

begin transaction;

begin try
    -- move submitted order from DraftOrders to Orders
    insert into Orders (CustomerID, EmployeeID, OrderDate, 
                        RequiredDate, ShippedDate, ShipVia, 
                        Freight, ShipName, ShipAddress, 
                        ShipCity, ShipRegion, ShipPostalCode, 
                        ShipCountry)
    select CustomerID, EmployeeID, OrderDate, RequiredDate, 
            ShippedDate, ShipVia, Freight, ShipName, 
            ShipAddress, ShipCity, ShipRegion, ShipPostalCode, 
            ShipCountry
    from DraftOrders 
    where OrderID = @OrderID;

    -- find ID of committed order
    declare @NewOrderID int;
    select @NewOrderID = @@IDENTITY;

    -- move submitted order details from DraftOrderDetails to "Order Details"
    insert into "Order Details" (OrderID, ProductID, UnitPrice,
                                Quantity, Discount)
    select @NewOrderID, ProductID, UnitPrice,
            Quantity, Discount
    from DraftOrderDetails
    where OrderID = @OrderID;

    -- delete draft order and order details
    delete from DraftOrderDetails
    where OrderID = @OrderID;

    delete from DraftOrders
    where OrderID = @OrderID;

    commit transaction;

    -- refresh the page
    set @Result_NavigateUrl = 'OrderForm.aspx'
end try
begin catch
    rollback transaction;
    -- display error message
    declare @Error as nvarchar(500);
    select @Error = error_message();
    set @Result_ShowMessage = 'Order has not been submitted. ' + @Error;
end catch

Press OK to save.

Try testing the action – it will properly move the draft order and details into the Orders and OrderDetails tables. If any errors occur, the transaction will be rolled back and an error message will be displayed to the end user at the top of the web page.

Thursday, December 27, 2012PrintSubscribe
Web Transactions with Log Table

One method to implement separation of “draft” and “committed” data is to add a log table to the database. This table will store a list of references to data that has not been committed. For example, new orders created in a database will be referenced in the dedicated DraftOrderLog table. The application will ensure that draft orders are only visible on the order entry page. When a user submits an order, the reference to it will be removed from the log table.

Adding Log Table

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind node, and press New Query.

Creating a new query for Northwind database.

Paste in the following query:

create table DraftOrderLog
(
    OrderID int not null primary key,
    Created datetime default getdate()
)
go

The query will create the “DraftOrderLog” table with two columns. Column “OrderID” will record the ID of the draft order, and column “Created” will reflect the date when the order was logged.

On the toolbar, press Execute to run the query.

Controlling Display of Draft Orders

Start the web app generator. Select the project name and click Settings. Press Business Logic Layer and enable shared business rules. Click Finish and regenerate the project.

Enabling shared business rules for the project.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders controller, and press Edit Handler in Visual Studio.

Editing the shared business rule handler in Visual Studio.

The shared business rule file will open in Visual Studio. Replace the existing code with the following:

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class SharedBusinessRules : MyCompany.Data.BusinessRules
    {
        
        public SharedBusinessRules()
        {
        }

        protected override void EnumerateDynamicAccessControlRules(string controllerName)
        {
            if (Context.Request.UrlReferrer != null)
            {
                if (Context.Request.UrlReferrer.ToString().ToLower().Contains("orderform.aspx"))
                    RegisterAccessControlRule("OrderID", 
                        "select OrderID from DraftOrderLog", 
                        AccessPermission.Allow);
                else
                    RegisterAccessControlRule("OrderID", 
                        "select OrderID from DraftOrderLog", 
                        AccessPermission.Deny);
            }
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class SharedBusinessRules
        Inherits MyCompany.Data.BusinessRules

        Public Sub New()
            MyBase.New()
        End Sub

        Protected Overrides Sub EnumerateDynamicAccessControlRules(controllerName As String)
            If Context.Request.UrlReferrer <> Nothing Then
                If Context.Request.UrlReferrer.ToString().ToLower().Contains("orderform.aspx") Then
                    RegisterAccessControlRule("OrderID",
                                              "select OrderID from DraftOrderLog",
                                              AccessPermission.Allow)
                Else
                    RegisterAccessControlRule("OrderID",
                                              "select OrderID from DraftOrderLog",
                                              AccessPermission.Deny)
                End If
            End If
        End Sub
    End Class
End Namespace

The implementation will conditionally register a dynamic access control rule that will be applied to a view of any data controller with an OrderID data field. If the user is interacting with the ~/Pages/OrderForm.aspx application page, then only data with OrderID that matches a record in the DraftOrderLog table will be included in the returned data set. All other pages will show data that is not linked to a logged order.

Save the file.

Adding Business Rule to Update Log Table

Switch back to the Project Designer. Right-click on Orders / Business Rules node, and press New Business Rule.

Creating a new business rule for Orders controller.

Assign the following values:

Property Value
Type SQL
Command Name Insert
Phase After
Script
insert into DraftOrderLog (OrderID)
values (@OrderID)

The business rule will insert a reference to the new order in the DraftOrderLog table. Press OK to save the business rule.

Adding “Submit Order” Action

Right-click on Orders / Actions / ag2 (Form) node, and press New Action.

Creating a new action in action group 'ag2'.

Give this action the following properties:

Property Value
Command Name Custom
Command Argument SubmitOrder
Header Text Submit Order

Press OK to save. Drop a101 – Custom, SubmitOrder | Submit Order node to the left side of a100 – Report | Order Report to place it first on the form.

Dropping action 'a101' on the left side of 'a100'.     Action 'a101' has been placed first in the heirarchy.

Right-click on Orders / Business Rules node, and press New Business Rule.

Creating a new business rule for Orders controller.

Assign these values:

Property Value
Type SQL
Command Name Custom
Command Argument SubmitOrder
Phase Execute
Script
delete from DraftOrderLog
where OrderID = @OrderID

set @Result_NavigateUrl = 'OrderForm.aspx'

This business rule will remove the reference to a submitted order from the DraftOrderLog table when the Submit Order action is activated. The browser will be instructed to navigate to ~/Pages/OrderForm.aspx page.

Press OK to save the business rule.

Viewing the Results

On the toolbar, press Browse. Navigate to the Order Form, and create a new order. Notice that only the new draft order is listed.

Only draft orders are displayed on the Order Form page.

Navigate to the Orders page. All orders except the draft order are displayed.

Draft orders are not displayed on the Orders page.

The draft order will not be visible in any data controller based on a database view that relates to orders. For example, the page Reports | Order Subtotals does not display the new order.

Draft orders not displayed on Order Subtotals report.

Switch back to the Order Form page, and select the draft order. Activate the Submit Order button.

Activating the 'Submit Order' button.

The application will refresh the page and display an empty list of orders.

Order Form list contains no draft orders.

The submitted order will now appear on Orders page.

Submitted order is displayed in the list of orders on the Orders page.

It will also appear on pages linked to data controllers related to orders.

Submitted order is now displayed in Order Subtotals data controller based on database view.