Web Transactions with Log Table

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
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.