Dynamic Comparison Tables in ASP.NET

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 = "&nbsp;"  
   
       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.

A sample .aspx and .aspx.vb page that includes all of the code and a working example can be downloaded here (.zip file).