DataSet XmlDocument XmlDataDocument

A Simple ADO.NET and XML Sample

We've looked at snippets of code that demonstrate isolated features. Now it's time to put some of these features into a small sample application that shows off the power of XML and of ADO.NET's XML features. To do this, I'll pull together functionality from many of the previous code snippets.

The sample will use parameterized queries to retrieve an order history for a particular customer. Before you roll your eyes, note that we're going to collect more information in this set of queries. The queries will retrieve data from four related tables: Customers, Orders, Order Details, and Products. In this way, our order history will be more robust. We'll see company names as well as the names of the products ordered.

The sample turns the results of the queries into XML, uses an XSLT transformation to turn that XML data into HTML, and then displays this HTML data in Internet Explorer, as shown in Figure 12-8.

Figure 12-8

The Web page generated by the XML sample application

The sample is a console application that launches an instance of Internet Explorer. Using a console application as a starting point makes the sample less flashy but easier to use as a resource. You can apply the same logic in two scenarios in which turning the results of queries into HTML can be extremely helpful: building Web applications and generating reports. I think that these are the most compelling uses of ADO.NET's XML features.

Keep in mind that I'm still a relative novice when it comes to HTML and XSLT. I built a very simple Web page using Microsoft FrontPage, and then I compared the structure of that page's HTML to the structure of the XML document I created using ADO.NET, which contained my date. I then relied on a book on XSLT to figure out how to build a transform to get from point X to point H.

I point out my lack of expertise for two reasons:

• To show that even a database programmer who doesn't have a strong XML background can learn enough XSLT to transform XML into HTML

• To apologize for the lack of style in the Web page

Two Paths, One Destination

Actually, I lied. I didn't create a sample. I created two of them. Both samples rely on the same XSLT transform and produce the same HTML, but they differ in how they generate an XML document that contains data from the standard Northwind database.

The first sample, called DataSetToHTML, connects to the local .NET SDK MSDE Northwind database using the OLE DB .NET Data Provider. The sample uses standard SQL queries and stores the results in a DataSet. In order to access this data as an XML document to perform the XSLT transform and generate HTML, the sample creates an XmlDataDocument object linked to the DataSet.

The second sample, called SqlXmlToHTML, relies on the SQL XML .NET Data Provider and an XML template query that uses the same Northwind tables but uses the FOR XML syntax. The SqlXmlCommand object that this sample uses has its XslPath property set to the location of the XSLT transform.

ADO.NET and XML: A Happy Couple

ADO.NET offers robust support for XML. ADO.NET's XML features allow programmers to easily move back and forth between traditional data access objects and XML objects. You can use the features of the DataSet object to read and write data and/or schema information as XML. The XmlDataSet object allows you to easily access the contents of a DataSet as XML. The SQL XML .NET Data Provider helps you use SQL Server 2000's XML features to retrieve the results of queries in XML format to files, XML documents, and DataSet objects.