Blog: Posts from June, 2012

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
Posts from June, 2012
Wednesday, June 27, 2012PrintSubscribe
Converting Field Values with C# and Visual Basic

The CustomerID field from the Customers controller should have all values formatted in uppercase.

CustomerID field of the Customers controller.

Let’s create a business rule in C# or Visual Basic to convert all user input to uppercase.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Customers controller node.

Customers controller in the Project Explorer hierarchy.

Change the Handler property:

Property New Value
Handler CustomersRule

Press OK to save the controller. In the Project Explorer, double-click on Customers / Fields / CustomerID field node.

CustomerID field of Customers controller.

Make the following changes:

Property New Value
The value of this field is calculated by a business rule expression. True
Context Fields CustomerID

Exit the designer and generate the application.

Click on the project name, and press Design. Visual Studio will open the project.

In the Solution Explorer, double-click on ~\App_Code\Rules\CustomersRule.cs(vb) file.

CustomersRule business rule file in the Solution Explorer.

Replace the sample code base 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 CustomersRule : MyCompany.Data.BusinessRules
    {
        [ControllerAction("Customers", "Insert", ActionPhase.Before)]
        [ControllerAction("Customers", "Update", ActionPhase.Before)]
        [ControllerAction("Customers", "Calculate", ActionPhase.Execute)]
        public void CalculateCustomerID(string customerID)
        {
            UpdateFieldValue("CustomerID", customerID.ToUpper());
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Namespace MyCompany.Rules
    Partial Public Class CustomersRule
        Inherits MyCompany.Data.BusinessRules
        <ControllerAction("Customers", "Insert", ActionPhase.Before)>
        <ControllerAction("Customers", "Update", ActionPhase.Before)>
        <ControllerAction("Customers", "Calculate", ActionPhase.Execute)>
        Public Sub CalculateCustomerID(ByVal customerID As String)
            UpdateFieldValue("CustomerID", customerID.ToUpper())
        End Sub
    End Class
End Namespace

Save the file, and switch to the web application. Navigate to the Customers page, and create a new record. Type in lowercase letters for Customer# field.

CustomerID field with lowercase letters.

Press Tab or click on another area of the page. The text in Customer# will be converted to uppercase.

The lowercase text in CustomerID field has been converted to uppercase.

The same business logic can be implemented with the help of SQL Business Rules.

Wednesday, June 27, 2012PrintSubscribe
Browsing Project Configuration Elements

The picture shows the Project Designer window of a Northwind web application.

Code On Time Project Designer window.

The screen is split into two parts.

The flexible left side of the window holds the Project Configuration Browser. This window initially displays a list of pages, with other tabs that display Controllers, User Controls, All Commands, All Fields, All Views, and All Data Fields

The resizable right side holds the Project Explorer. It displays a logical hierarchy of the application configuration elements.

The options on the Project Browser toolbar allows the developer to generate the app, develop in Visual Studio,  open the folder, or exit the Project Designer.

The Generate, Browse, Develop, Open, and Exit actions on the Project Browser toolbar.

The Generate button will generate and open the application in the Preview window. The Browse button will generate and open the application in the default web browser. The generated application is stored locally and executed by IIS Express.

The Develop button will open the solution in Visual Studio. If it is already open, the button will switch to Visual Studio.

The Exit button will close the Designer and return to the project settings.

The Project Browser displays lists of project configuration elements. The lists offer the same Quick Find, sorting, and filtering capabilities that are available in Code On Time web applications. Notice that the Project Browser runs locally and only displays the project configuration stored on the hard drive without interacting with any web server.

Filter and sort operations available in all data views.

Clicking on a link in the first column will navigate to the properties form of the selected item.

Property edit form for a page.

There may be additional tabs at the top of the window that allow access to related project configuration elements. The next screenshot shows Containers, Data Views, and Controls associated with Customers page.

Tabs at the top of the view display available elements that belong to the selected element.

The context menu Sync command selects the relevant element in the Project Explorer window on the right side of the Designer.

Sync command on the context menu.     Container1 synced from the designer window.

Clicking on links in the breadcrumbs will navigate directly to the parent project configuration element. The Home link will lead to the Home page of the Project Configuration Browser. Pressing OK or Cancel on the main tab will navigate back up one level.

Breadcrumbs enable navigation back up to parent project configuration elements.

The Project Explorer, on the right side of the Designer, displays a full hierarchy of project configuration elements across three tabs: Pages, Controllers, and User Controls.

The Project Explorer window.

The toolbar at the top of the Project Explorer offers context-sensitive commands. The buttons Sync, Navigate To, and Home are always displayed.

The first button, Sync, will synchronize the Project Explorer hierarchy node with the project configuration element selected in the Project Browser window.

Synchronize with Designer button on Project Explorer toolbar.

The second button, Navigate To, opens a search window that will help find a project configuration element by name or property value.

Navigate To button on Project Explorer toolbar.

The third button will display a list of top-level items in the Project Browser. The list depends on the active tab in the Project Explorer. For example, if the button is pressed when Controllers tab is active, a list of controllers will be displayed in the Project Browser window.

List of Controllers button on Project Explorer toolbar.

The fourth button will allow creating a new data controller, user control, or top-level page.

New Controller button on Project Explorer toolbar.

Additional options may be displayed depending on the selected node.

Show Value Text Items button on Project Explorer toolbar.

Double-clicking on a node will open the properties form for that project configuration element in the Designer window.

Properties form in Designer window for Controller

If you select a project configuration element in Project Explorer and click OK button to save the changes, then the same element will remain selected in the form. If you Cancel changes, then the original properties will be displayed in the form.

Multiple elements of the same type can be selected in the hierarchy. Many elements in the Project Explorer have Cut, Copy, and Paste commands available in the context menu. Users can drag and drop project configuration elements to modify the project configuration hierarchy. Drag and drop commands will work on multiple nodes.

Context menu commands available for Pages project configuration element node.

Monday, June 25, 2012PrintSubscribe
SQL Upsert

Upsert is an insert operation with detection of duplicates. The application will update the existing record when a duplicate is detected instead of creating a new record instance. Modern database engines support a dedicated SQL command Merge that performs this function. Applications created with Code On Time offer an elegant mechanism of SQL Business Rules that allows creating flexible and configurable Merge operations.

Let’s implement upsert for the Products table of the Northwind sample. The picture below shows two duplicate records of the product “Chai”.

New Chai product entered into the list.

The upsert implementation will detect an attempt to insert a duplicated product, and will update the values of the existing one.

Delete the duplicate “Chai” record from the list of Products, and paste the following query into SQL Server Management Studio:

-- debug
declare @ProductID int
declare @ProductName nvarchar(40) = 'Chai'
declare @SupplierID int
declare @CategoryID int
declare @QuantityPerUnit nvarchar(20)
declare @UnitPrice money = 42
declare @UnitsInStock smallint
declare @UnitsOnOrder smallint
declare @ReorderLevel smallint
declare @Discontinued bit
declare @BusinessRules_PreventDefault int
-- end debug

select @ProductID = ProductID
from Products
where ProductName = @ProductName

if @ProductID is not null
begin 
    set @BusinessRules_PreventDefault = 1
    update Products
    set 
        SupplierID = IsNull(@SupplierID,SupplierID),
        CategoryID = IsNull(@CategoryID,CategoryID),
        QuantityPerUnit = IsNull(@QuantityPerUnit,QuantityPerUnit),
        UnitPrice = IsNull(@UnitPrice,UnitPrice),
        UnitsInStock = IsNull(@UnitsInStock,UnitsInStock),
        UnitsOnOrder = IsNull(@UnitsOnOrder,UnitsOnOrder),
        ReorderLevel = IsNull(@ReorderLevel,ReorderLevel),
        Discontinued = IsNull(@Discontinued,Discontinued)
    where ProductID = @ProductID
end

The sample debug parameters represent values of a new product where Product Name is “Chai”, and Unit Price is “42”. The script will try to find an existing product that matches the Product Name. If a match is found, then the application framework is instructed to prevent executing the default Insert command. This is accomplished by setting the value of @BusinessRules_PreventDefault parameter to true. Then the new record values will be used to update the existing product. If a new column value is null, then the current column value is preserved with the help of IsNull function.

Execute the query. A message will state that 1 row was affected. Go back to the application and refresh the view, and you will see that the query has only updated the existing “Chai” record.

New Chai product only updates the existing record.

Let’s integrate this script in a new business rule of Products controller.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Products / Business Rules node, and select New Business Rule option.

Create New Business Rule for Products controller.

Use the following properties.

Property Value
Command Name Insert
Type SQL
Phase Before
Script

Copy and paste the script from above.

This business rule will execute before every Insert command.

The application framework will ignore the debug section. You can keep the debug section in the script or take it out – it will not make any difference.

Press OK to save the new business rule. On the toolbar, click Browse option.

Navigate to the Products page. Create a new product with the following field values:

Field Value
Product Name Chang
Category Name Condiments
Unit Price 99

Instead of creating the new record, the application updated the existing product. It is automatically selected in the user interface.

When creating a new record with the same name, the field values will be applied to the existing record.

This business rule will also work with the Import action. Create a spreadsheet, and paste in the following table:

Product Name Supplier Company Name Unit Price
Alice Mutton   120
Aniseed Syrup Tokyo Traders 44
ABCDE   42

Save the spreadsheet as a CSV (Comma delimited) file type.

Save file as a CSV.

On the action bar of the web application, select Actions | Import from File option.

Import From File action on the Products list.

Make sure that the columns are matching, and press Import.

Make sure the field map is correct, and press Import.

Confirm the operation. Sort by the Product Name field by clicking on the header text. Two of the records already existed, and Unit Price and Supplier Company Name for these records has been updated. The “ABCDE” Product Name was unique, so the row was added.

Records with matching Product Name are updated thanks to Upsert implementation.

There are advantages of using SQL Business Rules over the standard Merge operation. If additional processing is required or duplicate records have to be staged in a separate table, then business rules provide more flexibility over the insert/update combination offered by Merge.

Continue to Maximum Value Count