Code On Time: AJAX

Blog

AJAX

Wednesday, March 23, 2011Print Subscribe

Using $external() Function in Filter Expressions

Code On Time applications support custom functions in view filter expressions. The new function $external() has been introduced in the latest web application generator update. You can use this function to access values in the URLs of the pages and values of fields listed in Context Fields property of lookup fields.

Consider the EmployeeTerritories table from the Northwind sample database.

Any territory can be associated with an employee only once due to the primary key constraint.

Here is the screen shot of the form that allows creating new EmployeeTerritories records.

The employee last name is already selected. If a user clicks on (select) link in Territory Description field then an entire set of 53 territories will be presented.

Let’s make sure the the territories that are already assigned to a selected employee are not available in the list. For example, if we assign a new territory to employee Davolio then the last territory on the first page in the list of territories in the screen shot above, Wilton from Eastern region, is not listed along with a couple of other territories.

Start the code generator, click on the project name, click Design button, and select EmployeeTerritories data controller, click Edit, and enter EmployeeID in the input labeled Context Fields under Dynamic Properties section. Save changes.

From now on, the application will pass the value of the selected EmployeeID to the lookup view Territories.grid1 attached to field TerritoryID. If the lookup view has the field EmployeeID then the rows of Territories will be automatically filtered to match the value of EmployeeID selected in the new record.

Well, there is no field named EmployeeID in the table Territories.

Let’s create a filter expression for grid1 view in data controller Territories. Click on Home link in the bread crumbs of the Designer at the top of the page. Select Territories data controller. Activate Views tab and choose grid1. Click Edit button.

Enter the following in Filter Expression property:

$external('EmployeeID') is null or not TerritoryID in (
select "TerritoryID" from "EmployeeTerritories"
where "EmployeeID" = $external('EmployeeID')
)

Save changes, exit Designer and generate the application, navigate to Employee Territories page, select Davolio in the Employee Last Name field, and click (select) in Territory Description input. You will see a lookup window with 50 records in it. The three records that are already assigned to Ms. Davolio are not presented in the list.

Navigate to dedicated Territories page and observe that 53 records are still available there. If you enter a URL parameter EmployeeID with value of “1” in the address bar of your browser and hit Enter key then 50 records will be displayed as well. Your URL may look as the one below.

http://localhost:36745/Pages/Territories.aspx?EmployeeID=1

The filter expression assigned to grid1 view does not work if there is no external filter passed in the page URL or in the Context Fields of the lookup view. This is guaranteed by the first comparison in the expression.

$external('EmployeeID') is null or . . .

The second part of the filter expression will test the TerritoryID to ensure that it is not matched to any territories that are already present in EmployeeTerritories and uses $external(‘EmployeeID’) to further limit the scope of test.

Notice the use of double quotations around field and table names. Please use the appropriate symbol that works with your database server to ensure that application will not be trying to resolve the name against the dictionary of fields of the data controller.

Here is physical SQL statement executed by your application. Note that parameters @p0 and @p1 will be replaced with the value of the EmployeeID passed in a URL or as a context field.

with page_cte__ as (
select
row_number() over (order by "Territories"."TerritoryID") as row_number__
,"Territories"."TerritoryID" "TerritoryID"
,"Territories"."TerritoryDescription" "TerritoryDescription"
,"Territories"."RegionID" "RegionID"
,"Region"."RegionDescription" "RegionRegionDescription"
from
"dbo"."Territories" "Territories"
    left join "dbo"."Region" "Region" on "Territories"."RegionID" = "Region"."RegionID"

where
(
(((@p0) is null or not "Territories"."TerritoryID" in (
   select "TerritoryID" from "EmployeeTerritories"
   where "EmployeeID" = (@p1)
))))
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber and row_number__ <= @PageRangeLastRowNumber

Labels: AJAX, ASP.NET, ASP.NET Code Generator, Data Aquarium Framework, Tips and Tricks, User Interface, Web 2.0, Web App Generator

Tuesday, March 15, 2011Print Subscribe

MySQL Web Applications in Minutes

Code On Time generator creates powerful user-friendly line-of-business web applications straight from MySQL databases with Microsoft Office look and feel.

Here is how you can get started.

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 MyFirstApp as the project name.

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

Select MySQL option in data provider and then either “Click here if you need assistance to build the connection string” or enter the connection to your database directly in Connection String.

Here is the connection configuration screen that will assist in entering a valid connection string. In the screenshot below we have specified a connection to the demo “SAKILA” database available from http://dev.mysql.com/doc/index-other.html.

Test the connection, press OK button and click Next until you arrive to Reporting page.

Enable dynamic and static reporting in your project.

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

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 MySQL option in Provider Name.

You can enter a connection to the project database (sakila database in our example).

You can also point the connection string to a brand new MySQL database to store ASP.NET membership data that can be shared between multiple projects. Note that you have to create the database on your own. For example, you can have MySQL execute “create database users” command. Specify the name of the “users” database in the membership connection string.

The generated application will automatically initialize supporting data structures and stored procedures though MySQL ASP.NET Membership provider. This provider is included with MySQL Connector/NET that you already have on your computer if you are using MySQL with ASP.NET and Microsoft tools.

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 following screen.

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 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 Film page if you select a 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.

Filter some data to try Quick Find and multiple-value adaptive filtering.

Select Report | Adobe PDF option from the action bar of any grid 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.

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 movies database that shows distribution of movie categories by rating.

With a few clicks turn that into a Pivot Chart.

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

Astonishing business features and friendly user interface of generated web application make your MySQL database instantly a heart of the business operations.

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.

Labels: AJAX, ASP.NET Code Generator, MySQL, User Interface, Web 2.0, Web App Generator

Tuesday, March 15, 2011Print Subscribe

MySQL, IIS Express 7.5, Search Bar Improvements

The latest release of Code On Time generator includes significant new features and bug fixes.

Major Features

Code On Time generator now fully supports MySQL. Generate sophisticated web applications with native membership support, amazing end user capabilities such as live Excel data feeds, RSS, and on-the-fly reports.
In an effort to simplify product installation and to offer up-to-date development experience we have integrated support for IIS Express 7.5 in the web application generator. Code On Time generator will automatically use IIS Express to run and test generated applications. IIS Express can be installed on any Windows computer starting with XP and can run side-by-side with the production version of IIS bundled in many versions of Windows.
Search bars are now enhanced to support the new Search property of data fields. The search bars will no longer display “Hidden” data fields in the field options. The following values of the Search property allow precise control of the search bar behavior:
- Default – if the field is no hidden then it is allowed to be displayed in the search bar options. Up to three fields are automatically presented on the search bar by default.
- Required – the field must be presented on the search bar and cannot be left blank. If at least one field is marked as Required or Suggested then only required and suggested fields are displayed in the search bar.
- Suggested – the field is automatically presented in the search bar when a search bar is activated.
- Allowed – the field is allowed to be displayed in the search bar. This value may be useful to expose the “hidden” fields in the search bar.
- Forbidden – the field is not allowed to be displayed in the search bar even if the field is present in the grid / data sheet / tree view.

Bug Fixes and Minor Improvements

The application will correctly hide the tab in a form if there are no visible categories.
Method SqlText.Execute has been renamed to SqlText.ExecuteScalar
SqlText class has been enhanced with several static methods that make it easy to execute an SQL statement with parameters. The name of the methods are Execute, ExecuteNonQuery, and ExecuteScalar. For example,
```
object[] values = MyCompany.Data.SqlText.Execute(
    "select CompanyName, ContactName from Customers where CustomerID = @CustomerID", 
    "ANTON");
```
will return Company Name and Contact Name of a customer with ID = “ANTON”.
An exception raised when registering an activation code has been fixed.
Table of Contents user control now adjusts targets of TreeNode elements if the URL is of a page is spelled as “_blank:….” or starts with any other standard browser window name.
Business rules now support “array” filters. If you enter a view filter as “CustomerID in @CustomerIDList” and implement property CustomerIDList as an array or any enumerable property then the application will expand the filter into “CustomerID in (@p1, @p2,@p3)” if the property is returning three values.
Code generator will automatically remove read-only flag if detected when generated the project protected by a source control system.
Membership Business Rules were refactored. All methods are now virtual and can be overridden when needed.
The bug with conflict detection when read-only fields are present in a view has been resolved.
Mandatory boolean fields now automatically display first option “(select)” instead of “N/A”.
If the property ItemsStyle of a mandatory boolean field is set to “CheckBox” then field will correctly render as a check box.
The speed of metadata processing when generating a project for the first time or when refreshing the metadata to reflect changes in the database is now significantly improved.
Export as CSV will use the List Separator of the client UI culture instead of a fixed “comma” character.
If a form view has more than one column then the form will automatically take enough of real estate of the screen to fit the contents of the form into available space.
All boxes displayed in the search bar now feature an additional CSS class signifying the purpose of the box. The classes are Summary, TaskList, About, and See Also. This makes it possible to permanently hide the boxes from the UI when needed.
An exception is no raised if there is an error in the code formula. Previously the application silently ignored the runtime error making it difficult to detect the problem.
Summary box automatically trims words that are too long to fit in the side bar. This was frequently happening to email addresses and other “long” text sequences with spaces.

Coming Soon

Many new exciting features are coming soon.

Data Sheet view will be available in Premium and Unlimited editions of the product to provide a spreadsheet-style data entry.
Data Pivot capability will allow creating powerful data entry views with side-by-side presentation of categorized values.
Dynamic Access Control List will be available soon in Unlimited edition. Enterprise class dynamic security system will be available to control access to data on a user and role level without modifying application code.
Support for Sybase SQL Anywhere is being prepared for release.
Date Time Picker will be automatically displayed when data format string of a field is G or g.
New “Purpose” property will be introduced in the fields to allow specifying the business purpose of the field, such as Modified On, Created By, Membership User Name, Phone Number, etc. The code generator will automatically create the necessary business rules to support the expressed purpose of the field.
Azure Factory code generation project is being finalized and will be available to create applications working with Microsoft SQL Azure.
Extended support for file uploading is finally coming to conclusion. It will be possible to capture File Name, File Size, File Content Type, and to redirect file storage / retrieval to an external location. Azure Factory applications will use this capability to store uploaded files and annotations in Azure tables.
Tree View will complement Grid View, Data Sheet, Form, and Chart.

Labels: AJAX, ASP.NET, Code Generator, MySQL, Release Notes, Web 2.0, Web App Generator