Using XSL and .NET To Display Database Tables in your Web Browser

Figure 1a- DataBound Customer NorthWind Table in a Windows Form

Figure 1b - Table Shown in Web Browser

Introduction

I'm currently working on a project that uses XSL (EXtensible Stylesheet Language) to create reports from data by transforming XML into HTML. I've decided this route is the way to go if you really want the flexibility you need to generate reports and tailor them easily to your needs. Admittedly, XPath and XSL provide an additional learning curve, but once you get the hang of it, there is no substitute. You would not find the reporting flexibility, for example, in Crystal Reports or other reporting tools.

In this article we'll show you how to transform any table inside of a dataset into an HTML table and display it in the browser.

The Code

The code for getting our customer table in the database into a table in the browser is fairly straightforward and involves 4 easy steps.

1) Fill a DataSet From the Database

2) Write the DataSet as a DiffGram to XML

3) Transform the XML to HTML using XSL.

4) Display the HTML in a browser

Because of .NET's well designed architecture, there are almost as many lines of code as there are steps to performing this operation as seen in Listing 1:

Listing 1 - C# Code for Transforming a DataSet into HTML

// 1) Fill the DataSet with the Customer Data from the NWind Database
this.customersTableAdapter.Fill(this.fPNWINDDataSet.Customers);

// 2) Write the XML data for the customer as a diffgram to an xml file
this.fPNWINDDataSet.Customers.WriteXml(@"customer.xml", XmlWriteMode.DiffGram);

XmlDocument doc = new XmlDocument();

// 3) load the customer table into an xmldocument and perform an XSL transform
doc.Load(@"customer.xml");

XmlWriter writer = XmlWriter.Create(@"customertable.html");
XslTransform transform = new XslTransform();
// load the xslt file used for transformation

transform.Load(@"../../GenerateCustomerReport.xslt");

// transform the customer data in the xml document using the transform
transform.Transform(doc.CreateNavigator(), null, writer);
writer.Close();

// 4) Display the table in the browser
Process.Start(@"customertable.html");

So the hard part of creating a table in the browser from a database table is not actually the C# coding. The difficult part is writing the XSL that will generate the table. When you are designing your reports you will find yourself spending most of your time playing with XSL rather than C#. The nice part about designing the report using XSL is that you can test and debug against your Xml file directly inside of Visual Studio without having to recompile any C#.

Designing the Table

The first step in creating your report is to open up a pure HTML editor such as Front Page and visually design how you want your table to look. I've created a simple table shown below with a cool whitestone background for the header:

Figure 2 - HTML Table Design in Front Page

Now copy the source of the HTML into your blank XSL Transform File. You'll probably need to play with the paths after you add it to your code, but the html tags will be correct.

Listing 2 - HTML Shell of your Table

Now we have the a basic shell for creating our XSL Transform markup around the HTML markup. All we need to do is substitute the xsl and xpath expressions in the places we need to fill in the html table from the DataSet as shown in listing 3:

Listing 3 - XSL for producing an HTML table from the Diffgram

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">


<xsl:variable name="SPC" select="' '" />


<xsl:template match="/">
<html>
<head>
<meta http-equiv="Content-Language" content="en-us" />
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252" />
<title>

<xsl:value-of select="name(current()/*/*/*[1])" />
Report
</title>
</head>
<body>
<table border="1" width="100%" id="table1" cellspacing="1" cellpadding="0">
<tr>


<xsl:for-each select="current()/*/*/*[1]/*">
<td background="stone.jpg" width="199">
<B>
<xsl:value-of select="name()" />
</B>
</td>
</xsl:for-each>
</tr>


<xsl:for-each select="current()/*/*/*">
<tr>


<xsl:for-each select="current()/*">
<td bgcolor="#CCFFFF">
<xsl:value-of select="." />
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

In order to understand why we used the XPath expressions shown in listing 2 of the XSL we need to look at the diffgram we generate from the dataset. A diffgram is an XML representation of the DataSet. In our example we are only exporting the Customer table, so we only need to write XSL for a single table. Each row of the Customers table inside of the diffgram is grouped directly under the NorthWind DataSet. All the nodes under the Customers row contain the actual column name and data for that particular row. Knowing the structure of the XML data we want to transform to HTML gives us an idea of the XSL we need to write.

Listing 4 - Part of the XML Diffgram of the Customer Table

<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<FPNWINDDataSet xmlns="http://tempuri.org/FPNWINDDataSet.xsd">
<Customers diffgr:id="Customers1" msdata:rowOrder="0">
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customers>
<Customers diffgr:id="Customers2" msdata:rowOrder="1">
<CustomerID>ANATR</CustomerID>
<CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
<ContactName>Ana Trujillo</ContactName>
<ContactTitle>Owner</ContactTitle>
<Address>Avda. de la Constitucin 2222</Address>
<City>Mxico D.F.</City>
<PostalCode>05021</PostalCode>
<Country>Mexico</Country>
<Phone>(5) 555-4729</Phone>
<Fax>(5) 555-3745</Fax>
</Customers>

First of all, we want to write a header row with all the column names. Since all Customer nodes contain the column names in their column nodes, we just need to get the first row and strip out all the column tag names. The following XSL loop let's us cycle through the columns in the first row. It selects the XPath expression representing the nodes inside the first Customer Row.

<xsl:for-each select="current()/*/*/*[1]/*">

Then we can use the xsl name function to pick out each tags name to get us the name of the column.

<xsl:value-of select="name()" />

The next step is to populate the actual data for every row. To loop through the rows of the data table, we'll use the following for-each xsl/xpath expression. This XPath expression selects each Customers node in the diffgram

<xsl:for-each select="current()/*/*/*">

Now we need to loop through each column containing the data. So we'll have another loop that uses xpath to pick each child node out of the current Customers Node:

<xsl:for-each select="current()/*">

Finally we need to pull the data out of each Column Node that we are looping through and place them into the HTML <td> tag using XSL value-of the current column data. (A period(.) represents the current node you are on inside the transform)

Conclusion

With the power of XSL, you can bend XML data to your will and render it to a browser. As with any other new language XSL and XPATH, take a bit of time to get the hang of, but once you've used it in one or two reports, there is no turning back. .NET gives you the added power of passing parameters from C# directly to your XSL Transform, so you can easily have your C# Code interact with your reports through button presses and checkboxes. Anyway, now that I have finished reporting to you about the power to transform your life using C# .NET and XSL, hopefully it will benefit you in your work.