Code On Time: Tutorials

Monday, March 12, 2012Print Subscribe

Order Details: Add “Extended Price” Field

Let’s add an Extended Price field to show the final price of each line item in the order. This field will multiply price by quantity, and factor in the discount.

Switch to the Project Explorer, and select the Controllers tab. Expand OrderDetails, and right-click on Fields. Select New Field option.

For the new field, enter the following settings:

Property	Text
Name	ExtendedPrice
Type	Currency
The value of this field is computed at run-time by SQL Expression	True
SQL Formula	OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount)
Label	Extended Price
Data Format String	c
Values of this field cannot be edited	True

Press OK to save the field. The OrderDetails alias used in the SQL Expression field above is referring to command1 of the Order Details controller. The text of this command can be found at Order Details / Commands / command1 node.

Your application will incorporate the SQL Formula in the SELECT statements composed at runtime. The statement will be equivalent to the following simplified example.

select *, 
    OrderDetails.UnitPrice * OrderDetails.Quantity * 
    (1 - OrderDetails.Discount) ExtendedPrice
from "Order Details" OrderDetails

This is the output produced in SQL Management Studio if you try executing the simplified statement.

To make sure that the field will be displayed in the application pages, we’ll bind the field to the data view using data fields. Go back to the field. Select the Data Fields tab. On the action bar, press New | New Tab.

Set the following settings for the data field:

Property	Text
View	createForm1
Category	New Order Details

Press OK to save the data field. Use the same method to create two more data fields:

Property	Text
View	editForm1
Category	Order Details

Property	Text
View	grid1
Category	n/a

Regenerate the application, and navigate to Order Form page. Select an order. For each order detail, you will see a calculated Extended Price field.

Labels: Tutorials, Web Application Generator

Saturday, March 10, 2012Print Subscribe

Order Details: Configuring Lookup Fields

Let’s make similar changes to the Order Details controller. We’ll make the ProductID lookup open in search mode upon creating a new order detail. When selecting a product, the Unit Price will also copy over into the order detail.

Switch to the Designer, and expand to Order Form / c100 / dv101 / createForm1 / c1 – New Order Details / ProductID / ProductID. Double-click on the the ProductID field reference.

Change the following settings:

Property	Text
Data Value Field	ProductID
Data Text Field	ProductName
Copy	UnitPrice=UnitPrice
Search on Start	True
Activate if Blank	True
Lookup window description	Select a product

Press OK to save the field, and press Generate in the top left corner. Wait until the Preview window refreshes with the regenerated web application.

Navigate to the Order Form page, and select any order.

A lookup will open prompting you to select a product.

Select a product, and you will see that Unit Price of the product has been copied into the order detail.

Labels: Tutorials, Web Application Generator

Friday, March 9, 2012Print Subscribe

Sakila Sample Database

A majority of examples rely on the Northwind sample database available for Microsoft SQL Server.

If your backend database is MySQL, then try Sakila sample database instead.

Getting MySQL on Your Computer

Download and install the MySQL Installer for Windows at http://dev.mysql.com/tech-resources/articles/mysql-installer-for-windows.html.

If you already have MySQL installed, then you can skip the above step.

By default, Sakila database comes with the MySQL installation. If you do not have the sample database, then you can download the database scripts at http://downloads.mysql.com/docs/sakila-db.zip.

Creating a Web Application

Run Code On Time web application generator and create a new Web Site Factory project.

Give this project the name of “Sakila” and select the programming language of your choice (C# or Visual Basic.NET).

Click Create to create the project. Press Next to reach the Database Connection page.

Change the Data Provider to “MySql.Data.MysqlClient” and press the button next to the Connection String string field.

On the MySQL Connection page, enter your connection string settings. If you installed MySQL locally, enter:

Server Name	localhost
User Name	root
Password	[Your Password]
Database	Sakila

Press Next until you reach the Authentication and Membership page. MySQL can automatically install support for ASP.NET Membership in your database. Just check the first box on the page to enable membership support.

Press Next until you reach the Themes page. Select “Vantage” theme.

Hold down Shift key and press Next. This shortcut is available on any page of the web application generator wizard, and will skip to the Summary page.

Click Generate to start the code generation.

A web browser window will open up when code generation has been completed.

Start using and customizing your own Sakila sample.

You can reference the same database in other projects whenever you need.

The next screenshot shows the built-in membership manager in an application created from MySQL database.

Labels: MySQL, Tutorials, Web Application Generator