Monthly Archives: November 2008

Going off the grid

So much of the data we work with fits nicely in a typical grid. Of course, most data itself is stored in tables and it’s natural to want to display it as such. Many of the underlying data structures in our toolbox up til now (ADO.Net DataReader, DataTable, the old classic Recordset…) have also consisted of simple columns and rows. But now that I’m using LINQ, I’m finding that the shape of the data I’m working with breaks with the usual cell-ular symmetry. For example, let’s say I’ve got a table that stores Orders and another table that stores products, and a third that relates Orders to Product. In the grid world you’d run a join like…

SELECT o.Name OrderName, p.Name ProductName
FROM Order o
JOIN OrderProduct op on o.OrderId = op.OrderId
JOIN Product p on op.ProductId = p.ProductId

…and get back a result that looks something like this:

Order 1 Product 1
Order 1 Product 2
Order 1 Product 3
Order 2 Product 1
Order 3 Product 1
Order 3 Product 2

You may decide to get fancy with your presentation code and suppress the repetition of the Order items. You may decide to leave the repeated cells blank like this:

Order 1 Product 1
Product 2
Product 3
Order 2 Product 1
Order 3 Product 1
Product 2

Or you could merge the multiple product items rows into a single cell, perhaps make them a bulleted list (or even another sub-table, perhaps) like this:

Order 1
  • Product 1
  • Product 2
  • Product 3
Order 2
  • Product 1
Order 3
  • Product 1
  • Product 2

But in either case if you use the standard asp.net tools such as an ADO.Net DataTable and the GridView control, you’ll probably be coding against the grain.

I believe the more fitting approach is to use LINQ with the ListView control.

To begin with, getting the data via LINQ to SQL will likely give you data that’s not ‘grid’ shaped any more, but something more object oriented. Here’s an example LINQ query:

using (var db = new ProductOrdersDataContext())
{
var orders = db.Orders
}

So where are the products in each Order? Well, assuming you setup your LINQ to SQL model based on your DB schema with relationships established, you’ll automatically get entity classes with built in relationships to other entity classes. So to get the products in a each Order you’d code something like:

using (var db = new ProductOrdersDataContext())
{
var orders = db.Orders
foreach(var order in orders){
var products = from po in order.OrderProducts
select po.product;
//... do something with products ....
}
}

So what about binding to a data control? GridView and ListView and BulletedList and any of the others can all be bound to the typical collection types that LINQ statements might return (IEnumerable, List, Array, etc.), so we’re in business there. But what about the problem of the odd, object-oriented shape of the data? LINQ projections can help with that.

So here’s an example of a LINQ statement that produces results shaped something like this:

Order 1
  • Product 1
  • Product 2
  • Product 3
Order 2
  • Product 1
Order 3
  • Product 1
  • Product 2
using (var db = new ProductOrdersDataContext())
{
var results = from o in db.Orders
orderby o.Name
select new
{
OrderName = o.Name,
Products = from op in o.OrderProducts
select new
{
ProductName = op.Product.Name
}
};
}

You can bind this result to a ListView. First let’s look at the ListView declaration along with a Label and BulletedList control that will help out with presentation:

<asp:ListView ID="ListView1" runat="server">
<LayoutTemplate>
<table id="CardAccessLevelList" class="iTunes">
<thead>
<th>Order Name</th>
<th>Products</th>
</thead>
<tbody runat="server" id="itemPlaceHolder" />
</table>
</LayoutTemplate>
<ItemTemplate>
<tr class='<%# (Container.DataItemIndex % 2 == 0) ? "even" : "odd" %>'>
<td><asp:Label ID="Label1" runat="Server" Text='<%# Eval( "OrderName" ) %>' /></td>
<td>
<asp:BulletedList ID="BulletedList1" runat="server"
DataSource='<%# Eval("Products") %>'
DataTextField="ProductName" />
</td>
</tr>
</ItemTemplate>
</asp:ListView>

Here’s how you could bind the ListView in the code behind:

using (var db = new ProductOrdersDataContext())
{
ListView1.DataSource = from o in db.Orders
orderby o.Name
select new
{
OrderName = o.Name,
Products = from op in o.OrderProducts
select new
{
ProductName = op.Product.Name
}
};
ListView1.DataBind();

}

On a side note, I want to give credit to Andrew L. Van Slaars for the bit of code in the <tr> tag. The purpose of that DataItemIndex inspection in setting the css class of the row is to allow the style of the row to alternate back and forth, saving us the trouble of coding the ListView’s “AlternatingItemTemplate” which would be a much larger chunk of code in a real world case.