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
Wednesday, February 13, 2013PrintSubscribe
“Filter Expression” Property of Views

The “Filter Expression” property allows limiting the records displayed in a specific view by an SQL compatible logical expression.

The default Orders grid view in a sample Northwind web application displays all orders.

List of all orders.

Let’s create a new view by the name of “Orders This Month” that will only display orders placed in the current month.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Views / grid1 node, and press Copy.

Copying 'grid1' view of Orders controllers.

Right-click on Views, and press Paste. A duplicate of view “grid1” will be created.

Pasting onto Views node of Orders controller.      Duplicate of 'grid1' view, called 'v100' has been created.

The properties screen for the new view will be open in the Project Browser. Make the following changes:

Property New Value
Label Orders This Month
Filter Expression $thismonth(OrderDate)

Press OK to save. Double-click on Orders / Views / grid1 node.

View 'grid1' of Orders controller.

Change the label:

Property New Value
Label All Orders

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

Navigate to the Orders page. The All Orders view will continue to display an unfiltered list of orders.

The default grid view of Orders displaying an unfiltered list.

Use the View Selector dropdown in the top-right corner to switch to the Orders This Month view. Only orders placed in the current month will be displayed.

A list of orders filtered in the current month.

When the command is configured in order to retrieve data from the database, the Filter Expression is parsed by the application framework and any standard filter operators will be replaced by the SQL equivalent. SQL compatible logical expressions may also be used. In the example above, the filter is passed to the application framework as the following:

$thismonth(OrderDate)

The framework converts the filter to the following where expression.

"Orders"."OrderDate" between @p0 and @p1

This expression is then inserted into the select command and passed to the server.

with page_cte__ as (
select
row_number() over (order by "Orders"."OrderID") as row_number__
,"Orders"."CustomerID" "CustomerID"
,"Orders"."EmployeeID" "EmployeeID"
,"Orders"."OrderDate" "OrderDate"
,"Orders"."RequiredDate" "RequiredDate"
,"Orders"."ShippedDate" "ShippedDate"
,"Orders"."ShipVia" "ShipVia"
,"Orders"."Freight" "Freight"
,"Orders"."ShipName" "ShipName"
,"Orders"."ShipAddress" "ShipAddress"
,"Orders"."ShipCity" "ShipCity"
,"Orders"."OrderID" "OrderID"
,"Customer"."CompanyName" "CustomerCompanyName"
,"Employee"."LastName" "EmployeeLastName"
,"ShipVia"."CompanyName" "ShipViaCompanyName"
from
"dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"

where
(
("Orders"."OrderDate" between @p0 and @p1)
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber 
    and row_number__ <= @PageRangeLastRowNumber
Wednesday, February 6, 2013PrintSubscribe
SQLAnywhere 12 Sample Web Application

When Sybase SQLAnywhere 12 database server is installed, it will come with a sample database called “demo. Let’s create a Web Site Factory application from this database.

Installing SQLAnywhere 12

First, download SAP Sybase SQL Anywhere 12 for Windows x64 from the Sybase website if you have not done so already.

Download the file and follow instructions to install the database server.

Creating the Web App

Start Code On Time web application generator. At the bottom of the page, click on Create a new web application link. On the New Project screen, select Web Site Factory.

Selecting Web Site Factory project to create.

Assign a name of “SQLAnywhereDemo”, pick the programming language of your choice, and press Create.

Assigning a name and language, and creating the project.

Press Next to reach the Database Connection page. From the Data Provider dropdown, select “.NET Framework Data Provider for SQL Anywhere 12”. Paste in your connection string in the field below.

Selecting the data provider and pasting in the connection string.

Press Next twice to reach the Reporting page. Check the box to enable reporting.

Enabling reporting for the generated web application.

The next page allows configuration of Custom Membership and Role Providers. Leave the default settings and press Next until you reach the Theme page. Select “Vantage” from the list.

Selecting the 'Vantage' theme.

Hold down Shift key and press Next to skip to the Summary page. Press Generate.

Summary page for the SQL Anywhere project.

When generation finishes, the web application will open in the default browser.

Default Products page for SqlAnywhere demo database web application.

Tuesday, February 5, 2013PrintSubscribe
DB2 Sample Web Application

The database server DB2 Express-C, provided by IBM, comes with a database called “SAMPLE”. Let’s use Code On Time web application generator to create a web app straight from this database.

Installing and Configuring DB2

The first step is to download the  DB2 Express-C 10.1.2 for Windows 64-bit from the IBM website.

When the download is complete, run the executable. Follow the steps to install DB2 on your computer.

Creating a Web Application

Start the web app generator and click on the Create a new web application link on the start page. On the New Project screen, select Web Site Factory.

Creating a new Web Site Factory application.

Assign a name of “DB2Sample”, select your preferred programming language, and press Create.

Creating a new project called 'DB2Sample'.

Preserve the default Namespace and Framework, and press Next.

Namespace and Framework page of Code On Time project wizard.

From the Data Provider dropdown, select IBM DB2 .NET Data Provider.

Creating a DB2 database connection string.

To the right of the Connection String field, click on the “…” button to activate the Connection String Assistant.

If you are using a locally installed database, use the following configuration:

Database: SAMPLE

If you want to connect to a local database as a different user, use Server of “127.0.0.1” or “localhost”. If you are using a remote database, model your configuration after the one below:

Server: [IP Address]:[Port]
Database: SAMPLE
User name: [User name]
Password: [Password]

Press Test to confirm that the settings are configured correctly.

The connection succeeds when tested.

Press OK to insert the connection string into the field.

Configured connection string inserted into the 'Connection String' field.

Press Next twice to reach the Reporting page. Check the box to enable reporting.

Enabling reporting for the Web Site Factory web application.

The next page allows configuration of Custom Membership and Role Providers. Leave the default settings and press Next until you reach the Theme page. Select “Summer” from the list box.

Selecting the 'Summer' theme in the Project Wizard.

Hold down Shift key and press Next to skip to the Summary page. Press Generate to create the web application.

The Summary screen of the Project Wizard.

When generation is complete, the web application will open in the default browser.

Default Employee page of a generated web application from SAMPLE DB2 database.

Continue to Custom Filter Panel