Grouping DataRows with Linq from DataSet in VB.Net

Today I thought I’d break out of the mold a bit and give the VB.Netters a little love.
I recently came across some code that was generating server-side HTML from rows of a DataTable. The data was similar to this one, rows would contain redundant header values (Category) and unique content values (Name, Value):

BaseDataset

The algorithm when something like:

create a heading container div
create an unordered list
add unordered list to heading container
set a variable to value of Category column in first row of DataTable
for each row
check Category column
if same as previous Category
add list item to existing unordered list with Name and Value content
else
create new heading
add new heading to content container
create new unordered list
add current item to new unordered list
add unordered list to new heading container
set category variable to current category value
repeat

..And the output looks like:

output

So the code had to track and know when to create a new heading and unordered list by inspecting the Category column on each row. I think I will call that the Value Decider anti-pattern. What a pain.
I decided to re-factor the code to use a Linq query with Group By so that I could use a much easier to understand and maintain nested loop.

Here is basically how it went:

Imports System.Linq
Imports System.Text
Imports System.Web.UI.HtmlControls
Imports System.Web.UI
Imports System.IO

Module Module1

Sub Main()

'A contrived DataSet to illustrate the technique
Dim ds As DataSet = getDataSet()

'Use a Linq extension to make a DataTable queryable
Dim dataRows As EnumerableRowCollection(Of DataRow) = ds.Tables(0).AsEnumerable()

'The Linq query with Group By. This creates a collection of anonymous types
'that have a string property ("category"), and a DataRow array property
'which is all the rows in that same category
Dim qry = From row In dataRows
Group By category = row("Category") Into theGrouping = Group

'The main container for our markup
Dim container As New HtmlGenericControl("div")

'For each catetory
For Each groupedCategory In qry

'Create a heading
Dim heading = New HtmlGenericControl("h3")
heading.InnerHtml = groupedCategory.category
container.Controls.Add(heading)

'Create a <ul>
Dim list = New HtmlGenericControl("ul")
container.Controls.Add(list)

'Add the names and values beneath the heading
For Each rowInCurrentGroup In groupedCategory.theGrouping
Dim li As New HtmlGenericControl("li")
li.InnerHtml = String.Concat(rowInCurrentGroup("Name"), " - ", _
rowInCurrentGroup("Value"))
list.Controls.Add(li)
Next
Next

'Render the markup
Dim sb As StringBuilder = New StringBuilder()
Dim htmltxtWriter As New HtmlTextWriter(New StringWriter(sb))
container.RenderControl(htmltxtWriter)

'This is the raw HTML
Dim output = sb.ToString()

End Sub

Function getDataSet() As DataSet

Dim ds As DataSet = New DataSet()
Dim dt As DataTable = New DataTable()
Dim dcName As DataColumn = New DataColumn("Name")
Dim dcTitle As DataColumn = New DataColumn("Category")
Dim dcValue As DataColumn = New DataColumn("Value")
dt.Columns.Add(dcName)
dt.Columns.Add(dcTitle)
dt.Columns.Add(dcValue)
ds.Tables.Add(dt)

Dim dr As DataRow = dt.NewRow()
dr("Name") = "Humpty Dumpty"
dr("Category") = "Nursery Rhyme"
dr("Value") = "Sat on a wall"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Jack and Jill"
dr("Category") = "Nursery Rhyme"
dr("Value") = "Went up a hill"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Peter Pumpkin Eater"
dr("Category") = "Nursery Rhyme"
dr("Value") = "Had a wife but couln't keep her"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Black Sheep"
dr("Category") = "Nursery Rhyme"
dr("Value") = "Have you any wool"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Star Wars - A New Hope"
dr("Category") = "movie"
dr("Value") = "In a Galaxy far, far away"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Office Space"
dr("Category") = "movie"
dr("Value") = "I wouldn't say I've been missing it, Bob"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Revenge of the Nerds"
dr("Category") = "movie"
dr("Value") = "What are you looking at, nerd?"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Monty"
dr("Category") = "comic"
dr("Value") = "Monty, Robot Man, Fleshy, Moondog"
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("Name") = "Dilbert"
dr("Category") = "comic"
dr("Value") = "Dilbert, Alice, Dogbert, Wally, Asok, Ratbert, Point-Haired Boss"
dt.Rows.Add(dr)

Return ds

End Function

End Module

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s