Blog

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, January 3, 2013PrintSubscribe
Adding a Database View

Database views are composed of a result set for a query accessible as a virtual table. This query is computed from other tables in the database. Code On Time web application generator creates read-only data controllers from views. It is possible to add update and insert capabilities to these controllers.

Let’s create a new database view in the Northwind database based on Employees table, add this view to a project, and enable editing.

Creating the View

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

Creating a new query for Northwind database.

Paste in the following script:

create view EmployeeList as
select 
    Employees.EmployeeID, 
    Employees.LastName + ', ' + Employees.FirstName FullName,
    Employees.Title, 
    Employees.TitleOfCourtesy,
    (select COUNT(*) 
     from Orders 
     where EmployeeID = Employees.EmployeeID
    ) NumberOfOrders,
    Employees.BirthDate,
    Employees.HireDate,
    Employees."Address",
    Employees.City,
    Employees.Region,
    Employees.PostalCode,
    Employees.Country,
    Employees.HomePhone,
    Employees.Extension
from Employees

The script will create a view composed of several columns from the Employees table. In addition, the FirstName and LastName columns will be concatenated into the FullName column, and a count of orders will be displayed in NumberOfOrders column.

On the toolbar, press Execute to run the script.

Adding View to Project

Start the web application generator. Click on the project name, and press Refresh. Check the box next to the view dbo.EmployeeList, and confirm the refresh.

Adding 'dbo.EmployeeList' to the project.

On the Summary page, press Design to activate the Project Designer.

Defining Primary Key

The new controller is based on a database view and does not have a primary key explicitly defined. This means that individual rows cannot be selected or edited. Specifying a primary key from within the Project Designer will enable this functionality.

In the Project Explorer, switch to the Controllers tab. Double-click on EmployeeList / Fields / EmployeeID* (Int32, read-only) node.

Field 'EmployeeID' of EmployeeList controller.

Mark the field as a primary key.

Property New Value
Defines the primary key. true

Press OK to save the field.

Defining Base Table Name

In order to ensure that the Employees table will be updated when a user changes a record in the EmployeeList controller, the command must have the Table Name property explicitly specified.

Double-click on EmployeeList / Commands / command1 (Text) node.

'Command1' of EmployeeList controller.

Specify a table.

Property New Value
Table Name dbo.Employees

Press OK to save changes.

Next, right-click on EmployeeList / Commands / command1 (Text) and select Reset to Baseline. This will ensure that any changes to the database view will be reflected in the command text when the data controller is refreshed.

Resetting command text to the baseline for 'EmployeeList' controller.

Viewing the Results

On the toolbar, press Browse. Navigate to Reports | Employee List page.

Click on Actions | Show in Data Sheet to activate data sheet mode. Double-click a cell and change the value.

Changing the value of a field in Employee List controller.

Press Enter key to save.

Saving the changed value.

Navigate to the Employees page. Note that the corresponding row of Employees database table has been updated.

The employee record has been updated.

Thursday, January 3, 2013PrintSubscribe
Restrict Access to Actions with “Roles” Property

It is possible to restrict access to actions by a specified list of roles. If the current user is not in the list of roles, the action will not be displayed in the user interface.

For example, the Actions | Export to Spreadsheet option in the Orders controller is available to all registered users by default.

The action 'Export to Spreadsheet' is available in the Orders controller.

Let’s restrict access to this action to only users with roles of “Administrators” or “Sales Manager”.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Orders / Actions /ag5 (ActionBar) – Actions / a3 – ExportRowset node.

Action 'a3' in action group 'ag5' of Orders controller.

Change the Roles property:

Property Value
Roles Administrators, Sales Manager

Press OK to save. On the toolbar, press Browse.

Log in with the standard user account (user / user123%) and navigate to the Orders page. The Export to Spreadsheet action will not longer be available.

The action 'Export to Spreadsheet' is no longer available in the Orders controller.

Log out, and log in again with the administrative account (admin / admin123%). The action will be available.

Wednesday, January 2, 2013PrintSubscribe
Custom Business Rule for Duplicate Action

The default behavior of Duplicate action is to execute the New action and automatically copy values from the current row into the fields of the record. If it is necessary to change the default behavior, a custom business rule must be used.

A user can duplicate a customer using the Duplicate context menu option in Customers grid.

Duplicate context menu action for a row in Customers grid.

The create form will be opened. Fields will be populated using the values of the original record.

Duplicate causes the New Customers form to open with the same values as the original record.

Let’s add a business rule that will add an asterisk (*) to the Customer# field.

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

Creating a new business rule for Customers controller.

Assign these values:

Property Value
Type JavaScript
Command Name New
Phase After
Script
[CustomerID] = [CustomerID] + '*';

Press OK to save. On the toolbar, press Browse.

Navigate to the Customers page. Activate the context menu for a row and press Duplicate.

Activating 'Duplicate' context menu option for a Customers record.

The Customer# value will be appended with an asterisk.

The 'CustomerID' field is appended with an asterisk.

This Duplicate action is only client-side. The data is only saved to the server when the user presses OK.

More complex duplication calculations that require database interaction can be implemented with the use of REST (Representational State Transfer) or with the use of SQL Business Rules.