Transactions

Labels
AJAX(112) Apple(1) Application Builder(242) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) 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(11) 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) DotNetNuke(12) EASE(20) Email(6) Features(99) Firebird(1) Form Builder(14) Globalization and Localization(6) Hypermedia(2) Installation(4) 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(15) OAuth(5) OAuth Scopes(1) OAuth2(6) Offline(14) Oracle(10) PKCE(1) PostgreSQL(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(163) Reports(48) REST(26) RESTful(21) RESTful Workshop(13) RFID tags(1) SaaS(7) Security(75) SharePoint(12) SPA(5) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(81) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(331) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(39) Web.Config(9) Workflow(28)
Archive
Blog
Transactions
Thursday, May 30, 2019PrintSubscribe
VS 2019, Multi File Upload, Command Line Mode
Code On Time release 8.7.11.0 brings support for Visual Studio 2019, Multi-File Upload in Touch UI applications, and new command line options that allow creating apps with automated scripts.

Start using Visual Studio 2019 with your projects. The new development environment is fully integrated in the app generator. If you do have an existing project and want to migrate to the latest tools from Microsoft, then select the app on the start page of the generator and choose Open option. File Explorer will show the project files. Either delete the file with *.sln extension and regenerate your app or right-click the solution file, open it with Visual Studio 2019, select the solution file item in Solution Explorer, and press Ctrl+S.

Multi-file Upload is now available in Touch UI if you add Upload action to a compatible data controller. End users of your app will be able to choose multiple files to upload. The app will create a database record for each file, have the file submitted to the server, and persist it to the first BLOB field.


Command Line support is now available in the app generator.

Enter the following in the command line to generate a database app that works on mobile devices and in the web browser:

codeontime -Generate "c:\apps\myapp3" -DbConnection "Data Source=;Initial Catalog=northwind-cmd;Integrated Security=True;" -run

Build mobile and web database apps in seconds in command line. Use generated apps to validate your ideas, prototype data input forms, enter sample data, and much more. Apps built with Code On Time are metadata-driven. Create you own customization tools to invoke codeontime.exe in command line mode. The upcoming Code On Time v9 will be using command line mode extensively since the entire development environment is incorporated directly in the apps.


The following features and fixes also included in this release:
  • (Model Builder) All words in field labels are capitalized when a model is created.
  • (Touch UI) Optimized packaging of JSON properties of FieldValue object instances to minimize footprint for offline/disconnected logging.
  • (Client Library) Values of static Check Box List are correctly displayed when more than one item is selected.
  • (Framework) Blob adapter configuration is not included in the JSON controller descriptor.
  • (Touch UI) Selection of files in BLOB input will make the input focused.
  • (Framework) New TemporaryFileStream class is used for temporary storage of BLOB content.
  • (Touch UI) Method $app.confim support chained alternative execution of code with promises when confirmation is canceled.
  • (Offline Sync) Concurrent uploading of BLOBs is implemented with optional reconciliation of failed to upload blobs.
  • (Offline Sync) End user can opt to sync without data refresh. The option will remain selected until data refresh is explicitly requested. This makes possible working offline and only uploading changes to the server.
  • (Framework) Batch Editing of many-to-many fields works correctly when the primary key field is explicitly included in the view.
  • (Framework) Batch Editing will not erase values of many-to-many fields that are not selected in the Batch Edit form.
  • (Framework) Surveys accept inline functions and non-string values as "Visible When" and "Read Only When" expressions in questions and topics.
  • (Framework) Signature prompt is vertically aligned to the middle.
  • (Touch UI) Download icon is not displayed anymore next to "DOWNLOAD" button.
  • (Offline Sync) Added support for signatures.
  • (Touch UI) Placing $none in "Notify" property of action will prevent notification from being displayed.
  • (ODP) Completed implementation of thumbnail production on the client.
  • (Model Builder) New implementation of multi-level construction of Copy property of lookups for both 1-to-Many and 1-to-1 relationships.
  • (Classic) Implementation of BLOB uploading is now moved to Offline Data Processor.
  • (Framework) Download cookie is set on the server only when specified in the request.
  • (Framework) Azure Blob Adapter now uses HTTPS by default.
  • (Touch UI) Fixed signature resizing.
  • (Model Builder) "Enter" key in the property of the last field will post changes and re-select the same field property.
  • (Localization) Italian localization contributed by Massimo Ciurleo.
  • (ODP) Signatures in child data views are fully supported in transactional mode (when odp is enabled).
  • (Project Wizard) New "Addons" section in Features.
  • (Framework) Filter expression in the model will not cause errors when advanced search is executed.
  • (Framework) Default transaction scope is "sequence",
  • (Offline Sync) If transaction scope is "sequence" then ODP-assigned sequence is overridden with an offline sequence number. If transaction scope is explicitly set to "all", then the sequence is assigned to 0 for all transactions in the log.
  • (Touch UI) Button "driving directions" correctly composes Google Maps query based on fields tagged as map-latitude and map-longitude.
  • (Client Library)  Failed blob will be abandoned since there is not much that the user can do. The row was either inserted or updated already. The blob was rejected by the server. Abandoning of blob eliminates infinite loop of submission.
  • (Framework) A registration record for on-demand fields with blank "On Demand Handler" is not generated in Blob.generated.cs(vb) to prevent duplicate "empty" handlers when more than one such field is defined.
  • (Classic) Rich Text editor correctly sets the value produced in custom editors.
  • (Classic UI) Data views correctly synchronize with the inserted record.
  • (Touch UI) Forced notifications without text will not cause an empty alert displayed when ui.notification.enabled = false.
  • (Framework) Generated data access object has a unique name to prevent clashing with parameters created from access control rules.
  • (Framework) New property ApplicationServices.DisplayName returns application name. The value is derived either from the default app name or from the value stored in appName property in ~/touch-settings.json.
  • (Framework) Method ApplicationServices.ValidateBlobAccess ensures that user can access the row that contains the BLOB field. The field must also be accessible to the user. Otherwise access to blob is denied.
  • (Touch UI) Context menu options for child data views that were defined in their own containers are not displayed anymore. Use fields of DataView type to display child data instead. Previously visible context options have caused exceptions at runtime.
  • (Framework) Method NodeSetCollection.SetTag correctly sets tags for both data fields and views in both singular and chained calls.
  • (Touch UI) Inline editing in child data views will not cause identity fields to be marked as "Modified" and allow entering rows without errors
  • (Touch UI) Refactored panel opening and closing.
Our next goal is to release Offline Data Processor and Offline Sync in update 8.9.0.0 due out by the end of June 2019. We are skipping 8.8.0.0 release numbering since both features are being released together. Cloud On Time for Android will be out in July 2019. The new roadmap has been prepared and will be unveiled soon!


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.