ASP.NET Data Binding (reloaded)

1
ASP.NET Data Binding(reloaded)

• Dino Esposito
• http//weblogs.asp.net/despos

2
Agenda

• Iterating Data and Patterns
• Showcasing DataGrid Capabilities

3
Iterating Data and Patterns
4
Considerations

• List controls iterate data through a fixed
  graphical pattern
• DropDownList, CheckBoxList, etc
• Iterative controls iterate data through
  user-defined patterns
• Repeater, DataList
• The DataGrid is mighty, but not always the best
  choice for data binding
• You can do virtually everything within a DataGrid
• Dont delegate all tasks to the DataGrid
• Full customization, but limited automation

5
Other considerations

• List controls inherit from ListControl
• Powerful base classes to build custom list
  controls (e.g., ButtonList, HeadlineList, )
• More easily reusable than a solution based on
  templated controls
• Even easier to write than a control that
  incorporates classes based on ITemplate
• A childs game in ASP.NET 2.0
• A good few base classes for data-bound controls

6
Iterative Controls

• Repeater is the simplest control
• Free-form HTML
• Bare repeater, no additional features
• DataList is a richer control
• Constraints on templates markup
• Layout properties, selection, editing, no paging
• Opt for iterative controls when
• Paging is not an issue (or Google-style paging is
  acceptable)
• Long pages are acceptable

7
Data-binding Issues

• Data source
• IEnumerable fits all
• Paging
• Custom paging is the only real-world option
• Design, more than implementation, choice
• Customization
• Capability of modifying the appearance of
  individual cells and data items
• In ASP.NET 1.x, a lot of work is always required
  even for common, simple scenarios

8
Showcasing DataGrid Capabilities
9
What Youll See Here

• Data-bound tooltips
• Columns bound to one field (for data display)
• Add binding to a second field for tooltips
• Summary Rows
• Add a summary row at the end of each logical
  group of rows
• Variable-Length Pages

10
Monday, 8.00 AM...

• HIGH PRIORITY message from your boss
• I want to be able to read notes on orders, when
  available. What about a tooltip?
• Id also like to see negative values at a glance.
  Can we change the values color?

11
Data-bound Tooltips

• Handle the ItemDataBound event
• For Item and AlternatingItem rows get a reference
  to the underlying table row
• Add a title attribute set to the content of the
  field of choice
• Homework
• Wrap it up in a new custom control
• Custom column class with TooltipField?
• Add an name/value collection property to the
  DataGrid filled with column/tooltip-field pairs

12
Runtime Cell Formatting

• Handle the ItemDataBound event
• For Item and AlternatingItem rows get a reference
  to the underlying table row
• Retrieve the value in the cell, verify it, and
  apply any required style
• Homework
• Wrap it up in a new custom control
• Fire a new custom event CellFormatting
• Pass the data item as an argument

13
(next) Monday, 8.00 AM...

• HIGH PRIORITY message from your boss
• Given a year, select all orders issued by all
  customers. (PS Only the total for each order.)
• What about adding a summary row to display the
  total of all orders each customer issued?

SELECT o.customerid, od.orderid,
SUM(od.quantityod.unitprice) AS price FROM
Orders o, Order Details od WHERE
Year(o.orderdate) _at_TheYear AND
od.orderido.orderid GROUP BY o.customerid,
od.orderid ORDER BY o.customerid
14
Summary Rows

• Special data row filled with aggregate values
• How do you insert a summary row?
• Just make it be part of the data source
• How do you give this row an ad-hoc layout?
• You should know it by now...
• Hey, but how can I put summary rows in a SQL
  resultset?

15
WITH ROLLUP

• In T-SQL, GROUP BY provides the WITH ROLLUP
  clause that adds predefined summary rows to the
  result set

DECLARE _at_TheYear int SET _at_TheYear 1998 SELECT
CASE GROUPING(o.customerid) WHEN 0 THEN
o.customerid ELSE '...' END AS CustID, CASE
GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE
-1 END AS OrdID, SUM(od.quantityod.unitprice)
AS price FROM Orders o, Order Details od WHERE
Year(orderdate) _at_TheYear AND od.orderido.orderi
d GROUP BY o.customerid, od.orderid WITH
ROLLUP ORDER BY o.customerid, price
16
Grouping data

• The GROUPING operator causes a new column to be
  added to the result set
• The column contains a value of 1 if the row has
  been added by the ROLLUP operator, and therefore,
  is a summary row.
• Otherwise, the column takes a value of 0
• By using a CASE..WHEN..END statement you can
  merge this new column with the grouping column

17
ItemCreated

• Fires whenever the grid creates a new constituent
  item (header, footer, item, pager)
• Modify here items style and layout
• Need help to identify the summary row
• Remind the SQL code? Summary orders ID is 1
• Apply changes
• Change background color and/or the number of
  cells, or whatever else suits you

18
ItemDataBound

• When ItemCreated fires the text for any
  data-bound item has not been set yet
• And if you set some text, it gets soon
  overwritten
• ItemDataBound is the data binding event that
  fires immediately after ItemCreated
• Its goal is just read text from the bound object
  and set HTML attributes
• Intervene here if you want to modify the text
  being displayed

19
(next) Monday, 8.00 AM...

• HIGH PRIORITY message from your boss
• Given a year, group all the orders by customers
  and month. To see data, users have to click on a
  button representing a month.

SELECT o.orderid, o.orderdate, c.companyname
FROM orders AS o INNER JOIN customers AS c ON
o.customeridc.customerid WHERE
Year(orderdate)_at_TheYear AND Month(orderdate)_at_The
Month
20
While coffees brewing...

• Is this stuff for a DataGrid control?
• It can be seen as a special case of custom paging
• How does the grid know about the contents of each
  page?
• Ensure it through ad-hoc paging
• How can you turn page indexes to month names?
• Thats just why you should love ItemCreated...

21
Custom Paging

• AllowCustomPaging forces the grid to assume that
  all the rows in the current data source belong to
  the current page
• VirtualItemCount defines the virtual size of the
  data source
• Whenever the user moves to a new page, the code
  is expected to retrieve the content that fits
  into that page
• If the pager bar could display month names,
  you're pretty much done...

22
Data Reader Objects

• If you plan to use custom paging, do not use
  DataSet objects and data adapters to fetch data
• DataReader objects are more effective because
• You don't need a pageable data source
• Smaller and simpler, they end up being faster
• Bind the grids DataSource property to the data
  reader object
• Dont forget to close both the reader and the
  underlying connection after DataBind execution

23
Adjusting the pager bar

• Set PageSize and VirtualItemCount to ensure the
  right number of links in the pager bar
• Grid displays at most PageSize rows. (Say 100)
• To have, say, 12 links set item count to 100x12
• links constant actual page size may vary

public void ItemCreated(Object sender,
DataGridItemEventArgs e) ListItemType lit
e.Item.ItemType if (lit ListItemType.Pager)
TableCell pager (TableCell)
e.Item.Controls0 for(int i0
iltpager.Controls.Count i2) // skip blank
controls nbsp ...
24
Summary

• List and iterative controls
• Data-bound tooltips
• Summary rows
• Variable-length pages
• Any questions?