Insert, Update, and Delete

Web applications created with Code On Time have a built-in support for Insert, Update, and Delete actions. These actions are invoked when the end user is saving or deleting the existing record, or saving the new one.

The application framework analyses the values that were entered by the user and creates a dynamic SQL statement that will perform a corresponding operation on the base table of the data controller. The framework uses the data controller definition to identify the primary key fields, the base table name, and conflict detection strategy, when creating SQL statements at runtime.  User values are always incorporated in the dynamic SQL statements as parameters to eliminate any possibility of SQL injection attack.

If application business requirements are calling for a more complex processing of insert, update, and delete operations, then SQL action shall be considered.

The most common reason to replace the standard Insert, Update, or Delete action is the need to either update a table other than the base table or to change data rows in multiple tables.

Consider the following example that may be implemented in the Northwind sample.

If a new supplier is entered in the database, then
three generic product records must be created
to speed up the data entry process.

Let’s replace the standard Insert action in the Suppliers data controller with the custom SQL action that takes care of that.

Select the project name on the start page of the application generator and click Design.

Activate Controllers tab and select Suppliers / Actions / ag2 (Form) / a7 - Insert when New node in Project Explorer.

Change the following properties in the action settings and click OK to save changes.

select @SupplierID = SupplierID 
from Suppliers 
where CompanyName = @CompanyName

if @SupplierID is null
begin
    insert into Suppliers (
        CompanyName, ContactName, ContactTitle, 
        "Address", City, Region, PostalCode, Country, 
        Phone, Fax, HomePage)
    values (
        @CompanyName, 
        @ContactName_NewValue,
        @ContactTitle_NewValue,
        @Address, @City, @Region, @PostalCode, @Country,
        @Phone, @Fax, @HomePage
    )

    select @SupplierID = @@IDENTITY

    insert into Products (ProductName, SupplierID, ReorderLevel)
    values ('Product 1', @SupplierID, 100)
    insert into Products (ProductName, SupplierID, ReorderLevel)
    values ('Product 2', @SupplierID, 100)
    insert into Products (ProductName, SupplierID, ReorderLevel)
    values ('Product 3', @SupplierID, 100)
end

Select Suppliers / Actions / ag2 (Form) / a13 –Select, editForm1 when Insert action node. The purpose of this action is to select the newly created record in the read-only mode using form view editForm1. Let’s change this action to be invoked when the action a7 – SQL when New | OK has been successfully executed on the server.

Change the properties of the action a13 as follows.

The customized actions a7 and a13 displayed in Project Explorer are shown next.

Click Browse button on the Designer toolbar to start the application in your default web browser. Start creating a new supplier record.

Enter “Tokyo Traders” as the company name and hit OK button.

Our SQL script is “smart”. We are making sure that duplicates are not entered in the database. The user will see the existing supplier record when that happens.

Now enter a new supplier with the company name “New Supplier”.

Click OK button to trigger the custom action a7. Application framework will locate the value of the action property Data specified in the data controller definition. The framework will bind the field values passed from the client web browser to the matching parameters detected in the SQL script.

Notice that parameters @ContactTitle_NewValue and @ContactName_NewValue will be bound to the new field values entered by the end user. In fact, all values are new and there is no need for explicit entering of the “_NewValue” suffix. The suffix can be useful when you are implementing an SQL action that must manipulate the field values of an existing data record. Use “_OldValue” suffix to reference the original field values when needed.

The SQL script also assigns the identity value of the new record to the @SupplierID parameter. The framework marks all “field” parameters as Input/Output. The application will pass the new value of SupplierID back to the client library.

This is how the Suppliers page will look when the action has been processed on the server.

The client library receives notification about the action result from the server and performs an iteration of the data controller action state machine in the web browser. It tries to match the next action that corresponds to a7. It happens to be the action a13. The client library invokes the action a13, which causes the new record to be selected in editForm1 view. Three products with generic names are available for modification in the child data view linked to the Products data controller.

Our example uses the standard INSERT statement. The SQL scripts entered in the Data property of SQL action can make use of any facilities provided by the database server including transactions, stored procedures, cursors, etc.

Implementation of a replacement for Update action must follow exactly the same rules. We suggest that you come up with your own example to try it out.

Let’s consider a sample implementation of Delete action. The first thing that comes to mind is an action that automatically deletes the supplier and all linked products.

Select Suppliers / Actions / ag2 (Form) / a2 – Delete action node on the Controllers tab in Project Explorer and change its properties as follows.

delete from Products
where SupplierID = @SupplierID

delete from Suppliers 
where SupplierID = @SupplierID

set @SupplierID = null

Click Browse button and select a supplier on the Suppliers page. Click Delete button and you will see the confirmation.

If you confirm the action then the linked products and supplier record will be deleted.

Notice the line in the script that assigns NULL value to the parameter @SupplierID.

set @SupplierID = null

This will cause the client library to “forget” the last selected record. Child data views linked to the list of suppliers will become hidden and the selected record summary will disappear from the sidebar.