Dynamic Client Expressions, Page Base, Resetting Cascading Lookup, etc.

This release of Code On Time web application generator includes several bug fixes and enhancements.

• Annotations column rendering in Data Sheet view has been enhanced in all themes.
• It is now again possible to click on a thumbnail of an image field in grid and data sheet view to download the original file stored in the database table column. The first click will activate a row. The second click will initiate the download process.
• Visual Basic.NET version of generated applications will correctly handle extended upload/download utility fields File Name, Content Type, and File Size.
• Check boxes are correctly rendered in form views with templates.
• Read-only and static fields are not causing “Field value is required” error in the user interface when user saves a form even if the field is not set to “Allow Nulls=false”.
• Client library includes significantly improved processing of Client Script expressions used to evaluate conditional styling and control field / category visibility. All data types are handled correctly. You can also use special JavaScript Date class extensions. For example, the client script Date.$within([RequiredDate], 5) will return true if Required Date is within 5 days as of now. The client script Date.$pastDue([ShipDate], [RequiredDate]) will return true if the ship date of an order is greater than required date or if a ship date is not specified and the time to ship the order has passed. This type of expressions may be useful to specify conditional styling calling for attention.
• Some enhancements to the system files of the code generation library were deployed to support the upcoming Azure Factory web application generator project.
• Field property “Copy” now supports expressions in format FieldName=null to support clearing/resetting of cascading lookup fields when the value of the master field has changed. Read about configuring cascading lookups in Code On Time applications at http://blog.codeontime.com/2011/05/cascading-lookups.html.
• All pages of the generated applications are now based on Namespace.Web.PageBase. This partial class will detect “right-to-left” cultures and switch the text flow to right-to-left from standard left-to-right. The class will also handle culture/language selection that will be enabled on the membership bar. User Interface Language selection feature will be available in the upcoming releases.

Cascading Lookups

ASP.NET web applications created with Code On Time application generator offer impressive lookup capabilities. Numerous lookup styles allow configuring sophisticated data lookup scenarios including cascading lookups. Cascading lookups is a data lookup scenario involving more than one lookup field.

Example of Cascading Lookups

Consider the fragment of Adventure Works LT database presented below.

Table SalesOrderHeader has three lookup fields. Field CustomerID references table Customer, fields ShipToAddressID and BillToAddressID are referencing the same table Address. Shipping and billing addresses may be represented by the same or different rows in the table Address. The many-to-many table CustomerAddress links a Customer and an Address.

If a new Sales Order Header is created and a customer is identified and associated with the header then it will be logical to assist end users of your application in selecting a customer address by limiting the list of all addresses to those that are matching the selected customer. Selection of one lookup value (CustomerID) in a row must cascade as a filter to other dependent lookups (ShipToAddressID and BillToAddressID).

Configuring Cascading Lookups

If you generate a new Web Site Factory application from the database and start creating a new Sales Order Header then you will see a page similar to the one presented below. The screen shot shows that a customer has been selected already and shipping and billing addresses are blank.

Click on (select) link in Ship To Address Line1 or Bill To Address Line1 and you will be presented with lookup window that allows selecting an address. The entire collection of addresses stored in the database is available for selection. The screen shot shows that the application user has activated the search bar to find the right address and is trying to find an address using predictive input.

Let’s make the task of selecting an address easier and limit the list of addresses to those that are associated with the user account.

First we will examine lookup configuration of ShipToAddressID and BillToAddressID fields.

Run the web application generator, select the project name and click Design to activate the project Designer.

Select “All Controllers” tab and locate SalesOrderHeader data controller.

Click on the controller name and select Fields tab.

Select ShipToAddressID field and click Edit button.

Scroll to Lookup section of the field properties. The configuration of lookup properties is presented below.

Property Items Data Controller is set to Address, which instructs the generated web application to show the first grid view of Address data controller when a user click on (select) link of the lookup field.

If you inspect configuration of BillToAddressID field then you will find that it is exactly the same. This type of configuration is automatically performed by Code On Time web application generator when a baseline application is constructed straight from your database.

Table Address does not offer any information that will help us to identify the addresses that belong to a specific customer.

Table CustomerAddress has a pointer to a Customer and provides a perfect replacement for Address data controller.

By default only the first mandatory field from master table Address is included by the application generator in the data controller CustomerAddress. We suggest defining the following de-normalization map for your project to expand available fields borrowed from master tables in tables CustomerAddress and SalesOrderHeader.

SalesLT.SalesOrderHeader => SalesLT.Customer
LastName
FirstName
CompanyName

SalesLT.CustomerAddress => SalesLT.Customer
CompanyName
LastName
FirstName

SalesLT.CustomerAddress => SalesLT.Address
AddressLine1
AddressLine2
City
StateProvince
CountryRegion
PostalCode

The application in the screen shots was generated with this de-normalization map.

Let’s change the configuration of field ShipToAddressID as follows:

1. Set Data Controller property to “CustomerAddress”.
2. Set Data Value Field property to “AddressID”.
3. Set Data Text Field property to “AddressAddressLine1”.
4. Set Content Fields under Dynamic Properties section to “CustomerID=CustomerID”.

Step (4) will instruct the data controller CustomerAddress to filter data by field CustomerID (field on the left hand side of the equal sign) with the value stored in field CustomerID (field on right hand side of the equal sign) in the current row of Sales Order Header data controller.

This change does not effect the command text of the data controller SalesOrderHeader in any practical way. The lookup configuration will be taken into account only when a user selects a value for ShipToAddressID or BillToAddressID field.

Save the changes and modify the field BillToAddressID by following exactly the same steps.

Generate your project, start creating a new Sales Order Header record, select “Family’s Favorite Bike Shop” customer in Customer Company Name field. Try select a shipping or billing address. Only two records will be available for selection. Both records match the selected customer.

Try changing a customer and observe that a different selection of addresses is presented each time.

Lookup Styles

You can change the style of lookups using the Items Style property. If you change the style of CustomerID, ShipToAddressID, and BillToAddressID then the cascading lookup behavior will still take place.

Lookup field CustomerID is presented  in Lookup and  two address reference fields are presented in Auto-Complete styles below.

A combination of Auto-Complete,  List Box and Radio Button List lookup styles is shown next.

Clearing Dependent Lookup Fields

Most scenarios of cascading lookups will require  a particular combination of master and detail selections in the lookup fields. If a master values has changed then a cascading clearing of dependent lookup fields may be called for. In fact inconsistent selection of master and detail lookup fields may cause data integrity issues.

An extra step is involved in clearing dependent cascading lookup field values.

Select CustomerID master field in Designer and set its “Copy” property as follows:

ShipToAddressID=null
ShipToAddressAddressLine1=null
BillToAddressID=null
BillToAddressAddressLine1=null

The screen shot shows the “Copy” property of CustomerID when opened in Designer.

The primary purpose of “Copy” property is to allow specifying multi-field copy instructions executed upon lookup selection. Read about multi-field lookup at http://blog.codeontime.com/2010/02/multi-field-lookup.html.

If you enter “null” on the right-hand side of the equal sign then the field value will be cleared when the value of the lookup field has changed. In this particular case any changes to CustomerID field must cause clearing of fields ShipToAddressID and BillToAddressID. Both fields are aliased with ShipToAddressAddressLine1 and BillToAddressAddressLine1 fields accordingly. If you configure only the foreign key fields to be reset upon changes of the master field CustomerID then fields will get cleared but their visual representations will remain intact. Therefore we require clearing of alias fields as well.

Property “Copy” provides surgical level of control over resetting of dependent fields in a cascading lookup field groups. If you have more than one level of dependency then make sure to configure clearing of fields on all levels.

Bug Fixes, Just-In-Time Formatting of Numbers

The new update includes the enhanced client library. Any numeric values with data format strings are automatically formatted as soon as a field input control loses its focus.

The following issues were resolved in this release:

• Processing of numeric values entered with percent symbol are correctly converted to  floating values. You can enable Percent data format if you enter letter “p” in the data format string of a numeric field in Designer.
• Source code generated in Visual Basic projects now uses “TypeOf X is Y” expressions to determine if an instance X is of a specific type Y of if an instance X is implementing interface Y. This corrects the issue that was introduced in last update that had to do with various conversion error messages when business rules were invoked.
• Client library now correctly processes read only primary key values that are not auto-incremented. This fixes the problem with Designer that was reported as “Cannot create duplicate object” and was preventing creation of page containers and actions.
• Interactive History is correctly rendered in IE 9 and WebKit browsers.

Make sure to clear the browser cache after re-generating your project.

File Upload / Download

Code On Time applications support direct uploading of external files into the database.

Basics

Consider the following database schema based on the Northwind sample available with Microsoft SQL Server. Table Categories features Picture field of type image. This data type allows capturing binary content. The purpose of this particular field is to capture a picture representing a category as suggested by the field name.

Generate a Web Site Factory web application with this three tables and navigate to Categories page. The screen shot below shows an automatically created page of categories stored in the database. Field Picture is provided with an automatic preview thumbnail.

Users can create new categories and upload category pictures as shown next.

Create a new category record in createForm1 view by selecting New Categories button on action bar.

Save and edit the new category and indicate that you want to upload a file.

Click Browse button and select the image file. File uploading will start immediately.

The file is being uploaded in editForm1.

The file has been uploaded as indicated by the message at the top of the page.

Note that file uploading works only with existing records. The Picture field is available only in editForm1 and grid1 view.

Uploading of binary content is executed asynchronously. If a record does not exist yet then it is not possible to save a file to the database.  Create a new record first, save it, and only at that time upload any files that must be associated with a record.

Code On Time applications will be offering a delayed uploading option that will work with new records by postponing operation until a new record is successfully stored in the database. Creation of  a new record and file uploading will become one smooth operation.

Capturing Extended File Properties

Generated applications do not limit the type of content submitted by end users. If an Adobe PDF file or Microsoft Word document is uploaded instead of an image then the file content will be saved in the database. The thumbnail will not display a preview in that case. If you were to download the file from the application to your hard drive by right-clicking on the thumbnail and selecting Save Target As option then you will notice the following message.

The name of the file is represented as generic CategoriesPicture_27, the file type is unknown, the content type is octet-stream.

The binary file stores the file contents only. The file name and content type are not known.

You can easily remedy the situation by introducing utility fields to capture the file name, content type, and length of the uploaded file. The latter is not strictly needed since the application can determine the size of the file by inspecting the database. File length can be useful if you users need to known the file size prior to downloading the file on their computer.

Change Categories table as shown below. Three new columns complement field Picture in table Categories. We have added columns PictureFileName, PictureContentType, and PictureLength. Make sure to allocate enough space for Content Type field to accommodate very long content types introduced in the latest versions of Microsoft Office.

Refresh the meta data of your project and regenerate the application.

Create a new category, select the category in the grid view and start editing category properties. New utility fields are displayed right under the Picture field.

Upload any document or image.

Notice that the thumbnail of the uploaded file shows the file extension. The utility fields have captured their corresponding properties.

Right-click  the thumbnail, select Save Target As and observe the prompt that may look as follows.

The name of the file is correctly suggested in “Save As” dialog. The type of the file is also correct.

If you click on the thumbnail directly then the file will open in the application associated with the file type on your computer.

We suggest that your further customize the data controller in the project Designer as shown in the next screen shot. 

All utility fields are marked as hidden in createForm1.

Field PictureContentType is “hidden” in editForm1 and grid1.

Field PictureFileName has its Text Mode property set to Static in editForm1 and grid1.

Field PictureLength is “hidden” in editForm1 and Static in view grid1.

These changes will ensure that utility information is captured and only the file name and file size are displayed as read-only values.

Implementing Virtual Fields to Store BLOB/ FILE Content

The new architecture of the framework in Code On Time applications allows full control of the upload / download processing of binary fields with the data controller business rules.

Let’s create a virtual “Picture” field in Products data controller and store the uploaded files in the external file system folder. Such approach may help to reduce the size of the database and provides developers with total control of the process.

To be continued.

Bug Fixes

The following bugs / issues were addressed in this release:

• Read-only primary key field values are now passed from the client to the server with both properties NewValue and OldValue set to the original value of the primary key field. This ensures that Value property of a FieldValue class instance is returning the correct result. The change is required to bring the read-only primary key fields in compliance with the new processing model for read-only fields. The enhancement also ensures that annotations will not raise an exception when a new annotation is saved.
• Data Sheet view will now render a “pretty” annotation column.
• Free version of Code On Time will allow creating projects with up to ten database tables or views.

Extended BLOB Processing

This update of Code On Time web application generator introduces a major enhancement of file upload/download feature and several bug fixes.

Make sure to refresh the browser cache after re-generating your application.

Bug Fixes / Enhancements

• Data Sheet view rendering has been improved. Users will notice that sometimes column header of a selected cell is not rendered in “bold” font. This is happening only if assigning a “bold” font will cause the column to become wider. This cosmetic change makes it easier to work with the data sheet.
• Tab and Shit+Tab keys will always skip read-only fields in the data sheet view. You can still select a read-only cell with a mouse click or with arrow keys.
• Dates are now displayed exactly as stored in the database regardless of the time zone of the client browser. ASP.NET Ajax client library automatically adjusts dates to the browser’s time zone, which creates logical problems with date and time processing. Data serialization enhancement ensures that difference between the time zone of the web server and the time zone of the browser is ignored.
• ASP.NET SQL Server Session State is now fully supported. This important feature allows running generated applications on web farms. Read about configuring SQL Server Session State at http://support.microsoft.com/kb/317604. You need to create Web.Config modification instructions for your application to enable the session state as described at http://blog.codeontime.com/2010/03/customizing-webconfig.html.
• Form views no longer hide the top row of buttons regardless of the number of fields in a data view.
• Business rules processor will only try to convert values if ICovertible interface is supported.
• Microsoft SQL Server membership registration has been changed to remove LocalSqlServer connection string prior to the new registration of a connection string with the same name. This allows generating applications for SQL Server when the latest Oracle / NET library is installed on the same computer.
• Import processor now implements BeforeProcess and AfterProcess. The class has been refactored in a partial ImportProcessor based on class ImportProcessorBase. This allows creating a new code file in your project with implementation of new virtual methods if you need to execute custom business logic before of after the import process.
• The bug with field map processing when Import command is selected in IE 9 and Chrome has been corrected.
• WebKit (Chrome and Safari) browsers will no longer display a modal popup information box stating that the “GetPage method has failed..”. This was happening when user were selecting a menu option while the page was waiting for a response from the server.
• Read-Only fields are now always correctly passed to business rules. FieldValue class now  returns NewValue if a field is read-only or modified, which makes it possible to “see” the read-only value in a business rule method. Client library has been changed to enable value conversion on the client. Read-only values of non-string type are passed as their native representation to the server instead of being passed as strings.

File Upload/Download

The application framework of generated web applications allows enhanced processing of BLOB (Binary Large Object) fields. The framework will capture Content Type, Length, and File Name automatically if utility fields matching the BLOB field name are present.

This can be done by simply implementing additional fields in your database. For example, if you modify the Norwind.dbo.Categories table as shown below, refresh the metadata of your project and re-generate the project then the utility fields PictureFileName, PictureContentType, and PictureLength will automatically capture the corresponding properties of an uploaded file.

CREATE TABLE [dbo].[Categories](
    [CategoryID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](15) NOT NULL,
    [Description] [ntext] NULL,
    [Picture] [image] NULL,
    [PictureLength] [int] NULL,
    [PictureFileName] [nvarchar](50) NULL,
    [PictureContentType] [nvarchar](80) NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

If you upload a file of non-image format (PDF, Word Document, etc.) and try downloading the file then the application will return the correct content type and file name to the browser.

Important: Make sure to allocate enough length for <BLOB_FIELD_NAME>ContentType field to ensure that Microsoft Office documents will have their content type captured correctly. The content types of the latest Microsoft Office documents are extremely long.

If is also possible to process files through business rules. The tutorial will be available shortly.

Coming Soon

We have finally completed the necessary templates to roll out Azure Factory project. Premium and Unlimited edition users will be able to begin generating Azure Factory applications  starting with the next update.

Owners of Unlimited edition will be able to create enterprise-class applications with EASE (Enterprise Application Services Engine). The engine will include two major components – Dynamic Access Control List and Status Transitions.

We will be releasing a new project DotNetNuke Factory that will allow incorporating Code On Time applications in DotNetNuke web sites shortly thereafter.

ASP.NET 3.5 Membership, SQL Anywhere, DataViewLookup

The following features and bug fixes are included in this release of Code On Time web application generator:

• Enhancements to Tab and Enter key handling in Data Sheet view when the focus is in the last column. Pressing either one of these keys in edit mode will post the record and advance to the next row. If the row is in “new” mode then the keys will post the new row and start another new row.
• Support for Sybase SQL Anywhere is now incorporated in the web application generator. Try the link even if you are not developing with SQL Anywhere. The post showcases several cool features of web applications created with Code On Time.
• Export to CSV has been enhanced to include the utf-8 signature in the data stream. This ensures that Microsoft Excel will understand extended symbols including multi-byte characters. You can export data as CSV file if you select Actions|Download option above any grid or data sheet view.
• Globalization settings in web.config of generated applications are now including utf-8 file encoding attribute.
• Data Format String is now exposed in Designer in the list of fields of a data controller.
• Recent releases have are based on the April 2011 release of Ajax Control Toolkit. The new toolkit has broken membership functionality in ASP.NET 3.5 applications. Login / logout links were not displayed. The new releases fixes this problem.
• Component  aquarium:DataViewLookup has been fixed and works correctly. You can use this component as a replacement for standard ASP.NET “list” controls in custom forms. The component  will render a lookup input control with all advanced features available in integrated lookups. Read about it at http://blog.codeontime.com/2009/04/objectdatasource-vs-controllerdatasourc.html under section “Filtering”.

Support for capturing of extended properties in file upload / download has been postponed until 5/7/2011. The next update will also include several improvements to Data Sheet view and enhancements to time zone handling.

SQL Anywhere Web Applications in Minutes

Code On Time announces support for SQL Anywhere 12.  Generate powerful user-friendly line-of-business web applications straight from SQL Anywhere 12 databases with Microsoft Office look and feel. The following instructions are written with an assumption that you have installed SQL Anywhere software on your computer.

INSTALLATION

Download the code generator at http://codeontime.com/download.aspx. The installation program will also install Microsoft.NET 4.0, IIS Express 7.5, and Microsoft Report Viewer 2010. These components are available at no cost and fully supported by Microsoft.

The code generator will use IIS Express to run generated web applications on your computer. It can be installed alongside the production version of IIS available in several versions of Microsoft Windows.

The report viewer component will render will render dynamically created reports in PDF, Word, Excel, and TIFF formats.

Follow installation instructions and click on Code OnTime Generator shortcut created on the desktop after installation.

GENERATING AN APPLICATION

Start new Web Site Factory project and enter SQL Anywhere Demo as the project name.

Click Next until your reach Data Connection page in the project wizard.

Select SQL Anywhere option in data provider and enter the following connection string.

userid=dba;password=sql;

Enable dynamic and static reporting in your project.

Click next and you will see Authentication and Membership page of the project wizard.

Follow instructions at http://blog.codeontime.com/2011/05/sql-anywhere-membership-configuration.html to configure ASP.NET Membership in your database.

Select the check box titled “Enable support for ASP.NET Membership with membership bar user manager”.

Select the check box “Membership will use a standalone database that already exist”. Select SQL Anywhere option in Provider Name.

Enter the same connection string

userid=dba;password=sql;

You can also point the connection string to another SQL Anywhere database to store ASP.NET membership data that can be shared between multiple projects. Make sure that configuration of the database has been performed prior to generating the application.

Click Next several times until your see a list of data controllers created from your database.

Now you are ready to generate an application and see it in action.

Click next and wait for the application to be displayed in your web browser. If the browser page comes out blank then simply give it a few moments and hit Refresh button. This may happen if your computer is busy and IIS Express is still getting ready to start the generated web application.

You should see the screen similar to the one in the picture.

USING GENERATED WEB APPLICATION

Make sure to keep the code generator running. Code On Time will automatically shut down the started IIS Express instances if you close the web application generator window.

Sign-in using one of the user accounts automatically created by application.  We suggest that you sign in as admin / admin123% . This user account is authorized to see the membership manager.

Click around and play with the generated pages.

Here is the screenshot of the Customers page if you select a customer record. Master record is shown in edit mode with the detail records tabbed at the bottom. Several other standard page layout are available. You can use the project Designer to create custom layouts. Click on the link to see an example of a custom Order Form.

Note that detail records can be edited in modal forms if you are using a commercial edition of the web application generator.

Here is the screen shot of the Products page display in Data Sheet view that allows spreadsheet-style editing of data. Data Sheet view does not require any external components and is available in Premium and Unlimited editions of Code On Time.

Select Report | Adobe PDF option from the action bar of any grid or data sheet view and take a look at PDF printout of your data with custom filters displayed in the report header.

Select Actions | View RSS Feed and subscribe to the feed to be notified by your RSS feed reader when new movies with the filtering criteria become available. How needs email anymore?

Try some cool data analysis features that allow extending your application and data to the end-user desktops enabling safe and efficient data delivery to business users.

Select Actions | Export to Spreadsheet to export data for analysis.

A prompt will show up to warn you that some data is being downloaded. Internet Explorer 9 and other major browser display a prompt at the bottom of the screen.

Press Open button to open the file. This will start Microsoft Excel. You will see a warning about potential security concern.

We are downloading data from our own application – there is no risk involved. Press Enable button to continue.

The data will be downloaded into a new Worksheet. You may be asked to identify yourself. Enter admin/admin123% or any other valid user account registered in the application membership database.

Select Insert on the ribbon and click PivotTable button.

Confirm the creation of a data range.

Now you are good to go. Here is the pivot view of the sales order items that shows distribution of product sales by employees. With a few clicks add a Pivot Chart for added impact.

Within minutes business users can make sense of their data and have amazing dashboards built in the tool they know best –Microsoft Excel.

The data feed embedded into the spreadsheet is live. Users can save the spreadsheet on the hard drive.

To refresh the data users can open the spreadsheet and select Refresh button on the Data tab of the ribbon.

Users will be prompted to re-enter the user name and password. The user’s identity will be verified against the database and the data feed will be refreshed.

Conclusion

Within minutes you can turn your SQL Anywhere 12 database into interactive treasure trove of data that can be placed on Internet or Intranet server.

Other available database connectivity options are MySQL, Microsoft SQL Server, and Oracle.

Review code customization techniques at http://codeontime.com/tutorials.aspx to learn how to use application designer to enhance the application and how to write custom business rules if the standard features of the generated application need to be enhanced.

SQL Anywhere Membership Configuration

You can find detailed instructions about development of applications with SQL Anywhere 12 and ASP.NET at http://www.sybase.com/detail?id=1080238.

This tutorial outlines the steps required to configure ASP.NET Membership features in a database that will become a part of web application created with Code On Time web application code generator.

Start Windows Explorer and navigate to “C:\Program Files\SQL Anywhere 12\Assembly\V2” folder.

Start SASetupAspNet.exe configuration utility.

Select the language for the ASP.NET security schema configuration.

Connect to the database. The screen shot below shows how to connect to a default demo database created by SQL Anywhere installation program. Enter dsn=SQL Anywhere 12 Demo and test the connection.

Add Membership and Roles to the configuration. Select other options if you are planning to use profiles, web part personalization, and health monitoring.

Finish configuration of your database.

If you are performing configuration on Windows 7 then you may see the following dialog at the end of the configuration process. Ignore the warning and select “This program installed correctly”.

Deploying Generated Applications to Winhost

Learn how to deploy your Web Site Factory and Web App Factory applications to the popular hosting provider http://www.winhost.com.

Includes instructions for web applications working with Microsoft SQL Server and MySQL databases.

http://codeontime.com/Documents/CB-Deploying_Your_App_To_WinHost.pdf