The WriteXml method of the DataSet enables you to write the contents of a DataSet as XML data. A common task is to then transform that XML to another format using XSL Transformations (XSLT). However In .NET, the DataSet is synchronized with the XmlDataDocument which enables you to apply an XSLT stylesheet to the contents of a DataSet without having to first write the contents of the DataSet as XML data using WriteXml.
Let’s get started with small C# and VB.NET console application.
The following example uses a single XSLT stylesheet to transform the XML for the HumanResources.Contact table in the AdventureWorks database into HTML displaying the contact data in an HTML table.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:template match="/">
<html>
<STYLE>
BODY {font-family:verdana;font-size:12px}
TD {font-size:10pt}
</STYLE>
<BODY>
<TABLE BORDER="1">
<tr bgcolor="#AAAAAA">
<td>Contact ID</td>
<td>Title</td>
<td>First Name</td>
<td>Last Name</td>
<td>Email Address</td>
</tr>
<xsl:for-each select="/ContactDS/Contact">
<tr>
<td style="text-align:center">
<xsl:value-of select="ContactID" />
</td>
<td style="text-align:center">
<xsl:value-of select="Title" />
</td>
<td>
<xsl:value-of select="FirstName" />
</td>
<td>
<xsl:value-of select="LastName" />
</td>
<td>
<xsl:value-of select="EmailAddress" />
</td>
</tr>
</xsl:for-each>
</TABLE>
</BODY>
</html>
</xsl:template>
</xsl:stylesheet>
By creating a new style sheet file, named it to Contact.xslt and save it in the same directory as the project file.
[C#]
string xsltContactFile = "../../Contact.xslt";
string strConn = @"Data Source=.\sqlexpress;Initial Catalog=AdventureWorks;Persist Security Info=True;Integrated Security=True;";
string strComm = "select top 10 ContactID,Title,FirstName,LastName, EmailAddress from Person.Contact";
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
SqlDataAdapter dap = new SqlDataAdapter(strComm, sqlConn);
DataSet ds = new DataSet("ContactDS");
dap.Fill(ds, "Contact");
XmlDataDocument dataDoc = new XmlDataDocument(ds);
XslCompiledTransform transform = new XslCompiledTransform();
transform.Load(xsltContactFile);
XmlTextWriter xmlWriter = new XmlTextWriter("../../Contact.html", Encoding.UTF8);
transform.Transform(dataDoc,null,xmlWriter);
xmlWriter.Close();
Console.Write("DataSet transformed to HTML in file " + xsltContactFile + " Successful!");
Console.Read();
}
[VB.NET]
Dim xsltContactFile As String = "../../Contact.xslt"
Dim strConn As String = "Data Source=.\sqlexpress;Initial Catalog=AdventureWorks;Persist Security Info=True;Integrated Security=True;"
Dim strComm As String = "select top 10 ContactID,Title,FirstName,LastName, EmailAddress from Person.Contact"
Using sqlConn As New SqlConnection(strConn)
Dim dap As New SqlDataAdapter(strComm, sqlConn)
Dim ds As New DataSet("ContactDS")
dap.Fill(ds, "Contact")
Dim XmlDataDocument As New XmlDataDocument(ds)
Dim transform As New XslCompiledTransform()
transform.Load(xsltContactFile)
Dim xmlWriter As New XmlTextWriter("../../Contact.html", System.Text.Encoding.UTF8)
transform.Transform(XmlDataDocument, Nothing, xmlWriter)
xmlWriter.Close()
Console.Write("DataSet transformed to HTML in file " & xsltContactFile & " Successful!")
Console.Read()
End Using
To use XSLT to transform the contents of a DataSet, create an XslCompiledTransform object and call the Transform() method on that object.
This code above fills a DataSet with a several fields from the TOP 10 rows of the Person.Contact table in the AdventureWorks database. The style sheet Contact.xslt used to transform the DataSet into a HTML file named Contact.html written to the same directory of project file.
Press F5 then you can open Contact.html file you can see the result like:
Applying-an-XSLT-Transform-to-a-DataSet.rar (67.29 kb)
Subscribe