Comparison tables / charts are quite common on the web. Using a custom method, you can dynamically populate a comparison table in ASP.NET, using normalized data, with the table rendered with exactly as many columns as are needed.
First I’m going to talk about a suboptimal way of attempting this. If you’re worked with data grids in ASP.NET before, you know that you can dynamically populate a table by binding a data table to a data grid with the AutoGenerateColumns property is set to true.
So if your data looks like this:
You can bind it to a data table and get this:
(Yes, I could have made this example about NFL quarterbacks, cars, or whatever, but why pretend to be someone that I’m not?)
The problem is, how do you get a SQL table or view like that to begin with? If your data is normalized, you may have do so something like:
select
[Person] = rc.Name,
[Race] = isnull(cv1.value, ''),
[Affiliation] = isnull(cv2.value, ''),
[Primary Weapon] = isnull(cv3.value, ''),
[Secondary Weapon] = isnull(cv4.value, ''),
[Residence] = isnull(cv5.value, '')
from
@RowCat rc
left join @CellValues cv1 on rc.Id = cv1.RowCatId and cv1.ColCatId = 1
left join @CellValues cv2 on rc.Id = cv2.RowCatId and cv2.ColCatId = 2
left join @CellValues cv3 on rc.Id = cv3.RowCatId and cv3.ColCatId = 3
left join @CellValues cv4 on rc.Id = cv4.RowCatId and cv4.ColCatId = 4
left join @CellValues cv5 on rc.Id = cv5.RowCatId and cv5.ColCatId = 5
Why is that a problem?
- It’s tedious to write.
- There’s a performance-hurting left join for each category.
- Changing the categories requires a database object update.
- Since the categories are hard-coded, the query is only good for fixed categories names and a fixed number of categories.
I created a solution that addresses all of those issues. Basically, you can keep your data normalized, using a query like this rather than the one above:
select
RowCatId = rc.id,
RowCatName = rc.Name,
ColCatName = cc.name,
CellValue = isnull(cv.Value, '')
from
@RowCat rc
cross join @ColCat cc
left join @CellValues cv on rc.Id = cv.RowCatId and cc.Id = cv.ColCatId
order by
rc.Id,
cc.Id;
Then you can take that dataset and pass it to the custom method below. Instead of populating a data grid, the method populates a table control.
''' <summary>
''' Data table should contain the columns in this enumeration. They don't have to have the same names.
''' Sort by row category id, then column category id. Every row category should have exactly one of each column category.
''' If it doesn't this method is not going to work right.
''' </summary>
''' <remarks></remarks>
Public Enum DynamicColomnDataTableConstants As Integer
''' <summary>
'''
''' </summary>
''' <remarks>must be a positive integer</remarks>
RowCategoryId
RowCategoryName
ColomnCategoryName
CellValue
End Enum
Public Shared Sub PopulateDynamicColumnTable(ByVal TheWebTable As Table, ByVal TheDataTable As DataTable)
If TheDataTable.Rows.Count = 0 Then Return
Dim HeaderRowId As Integer = TheWebTable.Rows.Add(New TableRow)
TheWebTable.Rows(0).Cells.Add(New TableCell) 'this is the empty cell in the upper-left-hand corner
Dim LastRowCategoryId As Integer = TheDataTable.Rows(0)(DynamicColomnDataTableConstants.RowCategoryId)
Dim ThisRowCategoryId As Integer = LastRowCategoryId
Dim HeaderCellId As Integer = -1
'this isn't going to loop through all rows of the table table, only enough to create all of the
'header columns
For Each dr As DataRow In TheDataTable.Rows
ThisRowCategoryId = dr(DynamicColomnDataTableConstants.RowCategoryId)
'if row category has changed, header row has been created, and this loop can be exited
If ThisRowCategoryId <> LastRowCategoryId Then Exit For
HeaderCellId = TheWebTable.Rows(HeaderRowId).Cells.Add(New TableCell)
TheWebTable.Rows(HeaderRowId).Cells(HeaderCellId).Text = dr(DynamicColomnDataTableConstants.ColomnCategoryName)
Next
LastRowCategoryId = -1
ThisRowCategoryId = TheDataTable.Rows(0)(DynamicColomnDataTableConstants.RowCategoryId)
Dim ThisRowId As Integer = -1
Dim ThisCellId As Integer = -1
Dim TheCellValue As String = String.Empty
For Each dr As DataRow In TheDataTable.Rows
ThisRowCategoryId = dr(DynamicColomnDataTableConstants.RowCategoryId)
If ThisRowCategoryId <> LastRowCategoryId Then 'row category is different, need to create new row and header cells
ThisRowId = TheWebTable.Rows.Add(New TableRow)
ThisCellId = TheWebTable.Rows(ThisRowId).Cells.Add(New TableCell)
TheWebTable.Rows(ThisRowId).Cells(ThisCellId).Text = dr(DynamicColomnDataTableConstants.RowCategoryName)
End If
ThisCellId = TheWebTable.Rows(ThisRowId).Cells.Add(New TableCell)
TheCellValue = dr(DynamicColomnDataTableConstants.CellValue)
If TheCellValue = String.Empty Then TheCellValue = " "
TheWebTable.Rows(ThisRowId).Cells(ThisCellId).Text = TheCellValue
LastRowCategoryId = ThisRowCategoryId
Next
End Sub
With that method, you’ll get a table that’s exactly the same as the data grid shown earlier in this post.
I’m sure the the same thing could be accomplished with fewer lines of code, and probably has been. But this is my solution, and it seems to work well.
