Applying XSLT to XML Using ASP.NET - Query a Database and Transform through XSLT using ASP.NET
(Page 4 of 5 )
In this section, we will generate XML dynamically (using the ADO.NET related XML architecture) for any query issued to a database, and finally transform it based on a particular XSLT file. We will implement the same strategy as above, but with little bit modifications to deal with database queries effectively.
Create a new ASP.NET project in VS.NET. Drag an XML control from toolbox on to the webform. Press F7 (code window) and copy the following two statements at the top:
Imports System.Xml
Imports System.Xml.Xsl
Add a new method to the class as following:
Private Function getXMLContent(ByVal SQL As String) As XmlDocument
Dim ds As New DataSet("SQLData")
Dim da As New SqlDataAdapter(SQL, "data source=.;initial catalog=northwind;user id=sa")
da.Fill(ds, "Rows")
da.Dispose()
Dim sw As New System.IO.StringWriter
ds.WriteXml(sw)
ds.Dispose()
Dim docXML As New XmlDocument
docXML.LoadXml(sw.ToString())
sw.Close()
Return docXML
End Function
Don’t forget to make necessary modifications for the database connection string to meet your requirements. Next, Copy the following code in the page load event of the webform.
Dim docXML As XmlDocument = getXMLContent("select orderid,freight from orders where orderid=11000")
Dim docXSL As New XslTransform
docXSL.Load(Server.MapPath("Format1.xslt"))
Xml1.Document = docXML
Xml1.Transform = docXSL
Add a new file ‘Format1.xslt’ to the current project and paste in the following code:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table width="50%" cellspacing="0" cellpadding="0" style="font-family:verdana;font-size:X-Small" border="1">
<tr bgcolor="#336699"><th align="left"><font color="White">OrderID</font></th><th align="right"><font color="White">Freight</font></th></tr>
<tr><td align="left"><xsl:value-of select="SQLData/Rows/orderid" /></td><td align="right"><xsl:value-of select="SQLData/Rows/freight" /></td></tr>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
Press F5 to execute the web application and you should be able to see a table with single row of information showing orderID and freight. I leave it to the programmers to understand the above code, as it deals with very basics of database programming and I/O in .NET.
Next: How to Transform More than One Row? >>
More XML Articles
More By Jagadish Chaterjee
/ 62 
