Éric Moreau's blog : Using LINQ and XML Literals to transform a DataTable into a HTML table

Using LINQ and XML Literals to transform a DataTable into a HTML table

Saturday, April 11, 2009

Print this page

Download source code

I really love this one. We often have to transform a structure of data (a DataTable in my example) into another format. In those scenarios, we often start looping through our data source and concatenate values using a string builder. You will surely admit that you have done this thing way too often.

This month’s trick ends the long cryptic concatenation of strings (using string builder).

Using LINQ (specifically LINQ-to-Dataset in my case), and also XML Literals in some cases, I will show you how to generate CSV, HTML and XML outputs from your data source.

You can reuse the core of this article to build different output just as easily.

No C# code this month!

It is not because I don’t like you C# developers, it is just that an important feature is still missing from C#. I just named XML Literals. Many C# developers had requested it and it is supposed to be included into the next version (C# 4.0).

The CSV output is not using XML Literals so you could re-use that one easily.

But .Net 3.5 is required here!

To be able to use this code, you will need to use Visual Basic 2008 because LINQ and XML Literals that will be used here are part of the .Net Framework 3.5.

Figure 1:The demo application in action

We need a data source

For the sake of demoing this feature, it is a lot easier for me to simply create an in-memory DataTable. The 3 transformation we will then do will all start from this very simple DataTable. If you read some of my other articles, you might recognize this table (from which I made a huge cleanup because I don’t as much data here).

This is the CreateDataset method:

Private Function CreateDataSet() As DataSet
    Dim dsData As New DataSet("TestDataSet")

    Using dt As New DataTable("States")
        dt.Columns.Add("ID", GetType(Integer))
        dt.Columns.Add("Code", GetType(String))
        dt.Columns.Add("Description", GetType(String))
        dt.Columns.Add("Population", GetType(Integer))
        dt.PrimaryKey = New DataColumn() {dt.Columns("ID"), dt.Columns("IDParent")}

        dt.LoadDataRow(New Object() {1, "AB", "Alberta", 0}, True)  
        dt.LoadDataRow(New Object() {2, "BC", "British Columbia", 0}, True)  
        dt.LoadDataRow(New Object() {3, "MB", "Manitoba", 1162800}, True)
        dt.LoadDataRow(New Object() {4, "NB", "New Brunswick", 750600}, True)
        dt.LoadDataRow(New Object() {5, "QC", "Québec", 7487200}, True)

        dsData.Tables.Add(dt)
    End Using

    Return dsData
End Function

I have voluntarily set the population of 2 rows to 0 because I will need this case in the demo.

Generate a CSV output

Like it or not, CSV files are still strongly used in many applications. Many companies haven’t switch to the XML “paradigm” yet.

So this first transformation I will show you is how to transform your DataTable into a CSV string.

The first thing we need to build is our DataTable. To build it, we just have to call the marvellous CreateDataset method as shown here:

Dim ds As DataSet = CreateDataSet()

Normally at this point, we would start looping (probably using a Do ... Loop structure) through the DataTable and append the values into a string. Instead of the traditional looping structure, I will use LINQ.

So here is the LINQ query I built:

Dim StatesTable = _
    From state In ds.Tables("States").AsEnumerable _
       Order By state("Population") _
       Select state("Code").ToString + ", " + _
              state("Description").ToString + ", " + _
              state("Population").ToString

Notice that I am also sorting the rows by the population. Also notice that I do the string concatenation into the SELECT clause of the query. Finally, because I am using LINQ-to-DataSet to a DataSet that is not a Typed DataSet, I have to specify the fields I want to access as indexer.

Now that we have the LINQ query in the StatesTable variable, we can run it to get a collection of string. All we have to do, in my simple scenario, is to concatenate them all together. The simplest method is to use the String.Join method as shown here:

MessageBox.Show(String.Join(Environment.NewLine, StatesTable.ToArray), "Output to CSV")

With those very few lines, you will have a string containing your next CSV file content. Could it be easier? I don’t think so.

Figure 2:The CSV output in a MessageBox

A very useful trick I found lately is that we can embed conditions into the LINQ query. Say for example that instead of showing a population value of 0, I want to show the value “unknown”. To do it, all you need to do is to replace the last line of the LINQ query with this one:

If(Convert.ToInt32(state("Population")) <= 0, "unknown", state("Population").ToString)

That syntax reminds the old IIF statement. There are 3 sections to that IF statement:

IF(Condition, Value if True, Value if False)

A short note on the syntax

Instead of specifying your field name between (“”) like I did in the previous example, you can also use the Bang operator (!) like this:

Dim StatesTable = _
   From state In ds.Tables("States").AsEnumerable _
      Order By state!Population _
      Select state!Code.ToString + ", " + _
             state!Description.ToString + ", " + _
             If(Convert.ToInt32(state!Population) <= 0, "unknown", state!Population.ToString)

Does this remember you the good old DAO days? IntelliSense still won’t popup because Visual Studio doesn’t know what schema is associated with this un-typed dataset.

About the casting of the fields, there is yet another method which reads like this (see the Population variable on the last line):

Dim StatesTable = _
   From state In ds.Tables("States").AsEnumerable _
      Order By state!Population _
      Select state!Code.ToString + ", " + _
             state!Description.ToString + ", " + _
             If(state.Field(Of Int32)("Population") <= 0, "unknown", state!Population.ToString)

Generate a XML output

If you think of it, creating a XML output instead of a CSV output is simply a matter of modifying the string concatenation of the SELECT clause of the LINQ query. That would be ugly but that would work. But wait, there is another coolest method. I just named: XML Literals.

Most of what we have done in the previous section is still valid. The only thing that will change is the LINQ query that will now read like this:

Dim StatesDoc = _
  <States>
      <%= From state In ds.Tables("States").AsEnumerable _
          Order By state("Population") _
          Select <State><Code><%= state("Code") %></Code>
                     <Description><%= state("Description") %></Description>
                     <Population><%= If(Convert.ToInt32(state("Population")) <= 0, _
                                     "unknown", _
                                     Convert.ToInt32(state("Population")).ToString("#,##0")) _
                                 %></Population>
                 </State> %>
  </States>

Wow! Simply amazing. Have you found the LINQ query? VB9 now understands XML Literals and it reserves placeholders (exactly like in ASP.Net) to replace values when the query runs. This means that you can mix XML header with the content.

The other thing worth mentioning is that now when the StatesTable query runs, it returns a real XML Document (not even a string!). Because it is a document and not a string collection, you can just call the ToString method to display it in the message box like this:

MessageBox.Show(StatesTable.ToString, "Output to XML")

Figure 3:The XML output in a MessageBox

Excellent. Fantastic.

I know that the DataSet provides methods to transform it into XML but here we have more than just transformation. We have sorted and converted the population value. We could have also filtered and selected only some fields, joined with another data source, ... Can the methods of the DataSet do all that? Plus the fact that your data source is not always a DataSet! I hope you are convinced.

Generate an HTML Output

Seriously, are you expecting big differences here? Does HTML really differ from XML? Not at all. Simply add a longer header to define columns directly inside the LINQ query and you are done.

My LINQ query now reads like this:

Dim StatesTable = _
  <table>
      <tr><th>Code</th>
          <th>State</th>
          <th>Population</th>
      </tr><%= From state In ds.Tables("States").AsEnumerable _
               Order By state("Population") _
               Select <tr><td><%= state("Code") %></td>
                          <td><%= state("Description") %></td>
                          <td><%= If(Convert.ToInt32(state("Population")) <= 0, _
                                  "unknown", _
                                  Convert.ToInt32(state("Population")).ToString("#,##0")) _
                              %></td>
                      </tr> %>
  </table>

Wow! Simply amazing. Excellent. Fantastic. Haven't I told you that already?

Figure 4:The HTML output in a MessageBox

Be careful

I have replaced some code I have in a real application that generates HTML content the application sends by e-mail.

While I was converting, I found that the XML syntax is very strict. Consider this short example:

Dim StatesTable2 = _
  <table>
      <tr>
          <th>long<br>title</th>
      </tr>
  </table>

This should create a table having a single row. This row has a single column header and I wanted to break the caption on 2 lines. This code runs in any browser. But here it is invalid. Every tag must be closed otherwise the compiler will complain but it might display funny errors. The problem with the previous example is the <br> tag that is not closed. To fix the problem, you need to use something like this:

Dim StatesTable2 = _
  <table>
      <tr>
          <th>long<br/>title</th>
      </tr>
  </table>

Similarly, I had <p> tags to create new paragraph. I don’t close them very often. Using the XML Literals, I absolutely need to.

Conclusion

XML Literals was a great addition to VB9. So great that even C# developers now want it.

Mixing that features to your favourite LINQ flavour can generate all kind of output. This is what I just demonstrate.

I seriously hope that you will give it a try. It is really worth it.

Download source code

Share this post: Email it!
Posted by Eric