|
For the sake of explanation I will use the Northwind database provided with Microsoft SQL Server to
display for each order the products that has been purchased, i.e. the order details. Our sample involves
three tables: Orders (the order header), Order Details and Products (to extract the product name instead
of code found in the Order Details table). For readability I've also created a custom query that joins the
Order Details table and the Products table according to the Product ID field.
The steps taken:
Step 1: Create the Show Table page for the header table (in our case Orders table) using Iron Speed
Designer.
Step 2: Add another column to the Table and include a field value literal that gets its value from the
OrderId field, which is also the foreign key to the Order Details table.
Step 3: Name this Literal as "OrderItems" and use the Code Telescope in Iron Speed Designer to add the
"PreRender" function. The PreRender function is the .NET event that can be used just before the control is being
displayed. You'll notice that two new subroutines have been added to your code: EventHookup_OrderItems_PreRender
and OrderItems_PreRender. We will now add the business logic into OrderItems_ PreRender in order to display the
order details.
Step 4: Our business logic will do the following:
After validating that we do have an Order ID in the record, we retrieve all detail records and
start processing each one of them. As you can see in the code, we're creating an HTML table that
displays the relevant data for each record and summarize the total price of the order. After processing
all records, we add the bottom line to display the total price of products in this order and replace the
Order number value with the new text.
The code will eventually look like this (VB.Net):
Private Sub OrderItems_PreRender(ByVal sender As Object, ByVal e As _
System.EventArgs)
Dim OrderItems() As CompleteProductDetailsRecord
Dim OrderDetailsTable As CompleteProductDetailsQuery = _
CompleteProductDetailsQuery.Instance
Dim OrderDetailsRecord As CompleteProductDetailsRecord
'
' Define the html table header to be displayed in the page
'
Dim NewTextToReplace As String = _
"<table border=1 borderwidth=0><tr><td>Item " & _
"Number</td><td>Product</td><td>Quantity</td><td>Price</td></tr>"
Dim TextLine As String
Dim Price As Decimal = 0
Dim TotalPrice As Decimal = 0
Dim ItemsCounter As Integer = 0
Dim Quantity As Decimal
Dim UnitPrice As Decimal
'
' Verify We actually have the order number
'
If Not IsNothing(Me.OrderItems.Text) And IsNumeric(Me.OrderItems.Text) Then
OrderItems = OrderDetailsTable.GetRecords("OrderId=" & Me.OrderItems.Text)
'
' For each detail record retrieved we display the data and add the line price to the total
'
For Each OrderDetailsRecord In OrderItems
ItemsCounter = ItemsCounter + 1
TextLine = "<tr><td>" & CStr(ItemsCounter) & "</td>"
TextLine = TextLine & "<td>" & _
OrderDetailsRecord.GetProductName_ProductsFieldValue & "</td>"
TextLine = TextLine & "<td>" & _
OrderDetailsRecord.GetQuantity_Order_DetailsFieldValue & "</td>"
Quantity = CDec(OrderDetailsRecord.GetQuantity_Order_DetailsFieldValue)
UnitPrice = CDec(OrderDetailsRecord.GetUnitPrice_Order_DetailsFieldValue)
Price = Quantity * UnitPrice
TextLine = TextLine & "<td>" & CStr(Price) & "</td>"
TotalPrice = TotalPrice + Price
NewTextToReplace = NewTextToReplace & TextLine
Next
'
' Prepare the bottom line row to display total price of order
'
TextLine = "<tr><td>Total</td><td></td><td></td><td>" & CStr(TotalPrice) & _
"</td></tr></table>"
NewTextToReplace = NewTextToReplace & TextLine
'
' Change the literal display to the new text instead of just the order number
'
Me.OrderItems.Text = NewTextToReplace
Else
'
' If no order number was found – we don't display anything
'
Me.OrderItems.Visible = False
End If
End Sub
|
Step 5: That's it! All we need to do now is build our project and test it. For those of you who are
interested, you could apply some more "face lifting" code by introducing other HTML tags such as DIV, border
colors, use of style sheets etc. Our end result looks like the following:
|
