XtremeData
LANGUAGES: C#
TECHNOLOGIES: XML | XSLT | ADO.NET
Shape Relational Data
Convert relational data into hierarchical XML structures.
By Dan Wahlin
XML continues to gain popularity throughout the world, but companies still store much of their data in relational databases. This can present a problem when relational data needs to be converted into a hierarchical structure to be used in an application or exchanged with a business partner. You can employ several different techniques to handle such a conversion, including leveraging SQL Server 2000 XML capabilities and generating hierarchical structures by using intrinsic .NET classes.
The sample application I'll introduce in this article demonstrates how you can use the DataSet and DataRelation classes as well as Extensible Stylesheet Language Transformations (XSLT) to convert relational data into a hierarchical structure that Microsoft's TreeView Web control can consume (see http://msdn.microsoft.com/library/default.asp?url=/workshop/webcontrols/reference/treeview_entry.asp). The TreeView control is used to display customers and their associated orders (see Figure 1). Although the data shown in Figure 1 is only three levels deep, you can use the techniques in this article to handle more complex hierarchical relationships.
Figure 1. By using ADO.NET's DataSet and DataRelation classes along with
XSLT, you can generate a hierarchical data structure that can be bound to
Microsoft's TreeView Web control.
Aside from using DataSets and XSLT, the application also relies on Web Services and the Internet Explorer Web Service behavior to allow end users of the application to retrieve customer- and order-related data without refreshing the page each time they click on an entry in the TreeView control. Because the Web Service behavior runs only in Internet Explorer 5 or higher, less sophisticated browsers also are accommodated by using form postback techniques.
Generate Hierarchical XML
The data used for the TreeView application is obtained from the Northwind sample database's Customers and Orders tables. Figure 2 shows how the Customers and Orders tables relate to each other.
Figure 2. The Northwind Customers and Orders database tables are related
to each other through the CustomerID field.
Generating XML from the Customers and Orders tables is a fairly simple process, given the existence of the DataSet class's GetXml and WriteXml methods. For the XML to be bound to the TreeView control successfully, however, each order must be nested under the customer that placed the order. I used ADO.NET classes to handle this type of nesting operation.
After filling a DataSet with data obtained through calling the SqlDataAdapter's Fill method, I created a DataRelation class that relates the Customers and Orders tables together based on the CustomerID primary and foreign keys (see Figure 2). The DataRelation's Nested property is set to True to ensure the orders are nested under the proper customer automatically. In a moment, you'll see how doing this simplifies the XSLT stylesheet. Once the DataRelation object is established, it is added to the DataSet's Relations collection. Figure 3 shows the code to accomplish these tasks.
public static DataSet FillDataSet(string rootNode) {
string connStr =
ConfigurationSettings.AppSettings["ConnStr"];
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT CustomerID,ContactName
FROM Customers
WHERE CustomerID LIKE 'A%';");
sb.Append(@"SELECT OrderID,CustomerID,OrderDate,
ShippedDate FROM Orders WHERE
CustomerID LIKE 'A%'");
SqlConnection conn = new SqlConnection(connStr);
//Grab two tables of data in one call
SqlDataAdapter da =
new SqlDataAdapter(sb.ToString(),conn);
//Change table names from the defaults
da.TableMappings.Add("Table","customers");
da.TableMappings.Add("Table1","orders");
DataSet ds = new DataSet(rootNode);
da.Fill(ds);
//Create Customers/Orders relationship
DataColumn pKey =
ds.Tables["customers"].Columns["CustomerID"];
DataColumn fKey =
ds.Tables["orders"].Columns["CustomerID"];
DataRelation relCustOrders =
new DataRelation("CustomersOrders",pKey,fKey);
//Cause orders to be nested under customers
//when XML is generated
relCustOrders.Nested = true;
ds.Relations.Add(relCustOrders);
return ds;
}
Figure 3. The DataRelation class offers a simple way to relate DataSet tables to one another. Once a relationship between tables is created, you can add it to the DataSet's Relations collection.
Figure 4 shows a portion of the XML data generated from calling the DataSet's WriteXml method. Notice that all order data retrieved from the Northwind database is nested under the appropriate customer.
1997-08-25T00:00:00.0000000-07:00
1997-09-02T00:00:00.0000000-07:00
1997-10-03T00:00:00.0000000-07:00
1997-10-13T00:00:00.0000000-07:00
Figure 4. You can create a nested XML structure by using the DataSet and DataRelation classes. The XML data shown here represents the output of the code in Figure 3.
Transform Relational Data
Once the relationship between the Customers and Orders tables is created within the DataSet, XML can be generated by calling its WriteXml method. Then, the resulting XML can be transformed into an XML structure compatible with the ASP.NET TreeView Web control. I'll show you the code to do this a little later in the article; first, take a look at the XSLT stylesheet code.
Because nesting is handled by the DataSet and DataRelation classes detailed earlier, the XSLT code is simplified quite a bit from what normally would be required (see Figure 5).
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" > indent="yes" /> nodedata="{CustomerID}" imageurl="images/folder.gif" type="customer"> javascript:GetCustomerData(' select="CustomerID" />') nodedata="{OrderID}" imageurl="images/image.gif" type="order"> javascript:GetOrderData(' select="OrderID" />', '
Figure 5. XSLT provides a powerful way to transform XML into virtually any type of structure. The stylesheet code shown here transforms XML generated from a DataSet into XML that the TreeView Web control can consume.
The XSLT stylesheet code starts by creating a root node named TREENODES that is written to the output document. Then, it calls a template that matches the different customers nodes within the source XML document, adds customer data to a node named treenode, and finally iterates through all the customer orders by using an xsl:for-each loop. Figure 6 shows a portion of the output document the transformation generates.
imageurl="images/folder.gif" type="customer" navigateurl="javascript:GetCustomerData('ALFKI')"> imageurl="images/image.gif" type="order" navigateurl= "javascript:GetOrderData('10643','ALFKI')"/>
Figure 6. The XML shown here represents the output generated by the XSLT transformation. The TreeView Web control is capable of consuming this type of XML structure.
The XslTransform class (located in the System.Xml.Xsl namespace) performs XSLT transformations through its Transform method. Transform accepts an object containing the source XML (the object must implement the IXPathNavigable interface), the XSLT stylesheet to use in the transformation, and any parameter data that should be passed to the stylesheet. XSLT parameter data is loaded into a class named XsltArgumentList, which acts as a type of name/value container. You might have noticed that the XSLT stylesheet (see Figure 5) contains a single parameter named upLevelBrowser. This parameter is used to determine whether the TreeView control will use the Web Service behavior of Internet Explorer 5 and beyond. In cases where "older" browsers hit the TreeView ASP.NET page, the Web Service behavior is not used and the TreeView instead performs postbacks.
Figure 7 shows the complete process of loading the XML within the DataSet into an XPathDocument class, which then can be fed into the XslTransform class's Transform method. It also shows how to pass XSLT parameter data using the XsltArgumentList class. It is worthwhile to note that I chose to load the DataSet XML into a MemoryStream, which I then fed into the XPathDocument object's constructor. Although I could have used an XmlDataDocument class in this situation because it goes hand in hand with the DataSet, I didn't because of its tendency (in .NET version 1.0) to be sluggish in XSLT transformations.
private void LoadDataXslt() {
//Fill DataSet
DataSet ds =
CustomersOrdersDB.FillDataSet("treenodes");
//Transform relational data via XSLT.
//Although the DataSet could be converted into an
//XmlDataDocument class, this class's performance
//with XSLT is somewhat sluggish in V1. Instead,
//the DataSet XML is loaded into a MemoryStream,
//which is then fed to an XPathDocument class.
MemoryStream ms = new MemoryStream();
ds.WriteXml(ms);
ms.Position = 0;
XPathDocument doc = new XPathDocument(ms);
XslTransform trans = new XslTransform();
trans.Load(Server.MapPath(
"XSLT/CustomersOrders.xslt"));
//Create XSLT argument for upLevelBrowser boolean
XsltArgumentList args = new XsltArgumentList();
args.AddParam("upLevelBrowser","",
upLevelBrowser.ToString());
StringWriter sw = new StringWriter();
trans.Transform(doc,args,sw);
tvCustomers.TreeNodeSrc = sw.ToString();
tvCustomers.DataBind();
ms.Close();
}
Figure 7. This code demonstrates how you can use the XslTransform class with the XsltArgumentList and XPathDocument classes to load XML within a DataSet and transform it using XSLT.
The output of the transformation is written to a StringWriter class named sw, which then is assigned to the TreeView control's TreeNodeSrc property. After the data is assigned, the control's DataBind method is called and the generation of the different tree nodes happens automatically.
Walk DataTable Hierarchies
XSLT isn't the only solution for generating hierarchical data structures the TreeView control can consume. In addition to being able to nest data, relationships between related DataTables also can be walked using the DataSet's GetChildRows method. Using this method provides an extremely easy way to find related child rows in a table based on primary and foreign key relationships. As the related table rows are walked, new TreeView nodes can be created and the row data can be assigned to them. Figure 8 contains a method named LoadDataDataSet that demonstrates how to use the GetChildRows method to walk through parent-child rows and generate TreeView nodes.
private void LoadDataDataSet() {
DataSet ds =
CustomersOrdersDB.FillDataSet("treenodes");
tvCustomers.Nodes.Clear();
//Create root node
TreeNode rootNode = new TreeNode();
rootNode.Text = "Customers";
//Walk all customers
foreach (DataRow pRow in ds.Tables[0].Rows) {
TreeNode pNode = new TreeNode();
pNode.Text = pRow["ContactName"].ToString() +
" (" + pRow["CustomerID"].ToString() + ")";
pNode.NodeData = pRow["CustomerID"].ToString();
pNode.ImageUrl = "images/folder.gif";
pNode.Type = "customer";
//Determine to use Web Service Behavior code (IE5+)
if (upLevelBrowser) {
pNode.NavigateUrl =
"javascript:GetCustomerData('" +
pRow["CustomerID"].ToString() + "')";
}
//Find child rows. Walk all orders and nest
//them under customer nodes in TreeView control.
foreach (DataRow cRow in
pRow.GetChildRows(ds.Relations[0])) {
TreeNode cNode = new TreeNode();
cNode.Text = "OrderID: " +
cRow["OrderID"].ToString();
cNode.ImageUrl = "images/html.gif";
cNode.Type = "order";
cNode.NodeData = cRow["OrderID"].ToString();
//Determine to use Web Service Behavior (IE5+)
if (upLevelBrowser) {
cNode.NavigateUrl =
"javascript:GetOrderData('" +
cRow["OrderID"].ToString() + "','" +
cRow["CustomerID"].ToString() + "')";
}
pNode.Nodes.Add(cNode);
}
rootNode.Nodes.Add(pNode);
}
tvCustomers.Nodes.Add(rootNode);
}
Figure 8. Walking DataTable relationships is a snap once you establish primary and foreign keys. This code shows how to find child table rows associated with a parent row using the GetChildRows method.
I've heard several people argue for and against the role XSLT should play in the .NET platform; in this article, you've seen that you can use XSLT effectively to convert relational data into a customized XML structure. When used with intrinsic .NET classes such as the DataSet, you can use XSLT to perform many other tasks - from generating reports to targeting different devices. Aside from using XSLT, you've also seen how you can use DataSets alone to generate hierarchical data structures. You can view a live demonstration of the code described in this article on the XML for ASP.NET Developers Web site at http://www.XMLforASP.NET/codeSection.aspx?csID=99.
The sample code in this article is available for download.
Dan Wahlin, a Microsoft Most Valuable Professional in ASP.NET, is president of Wahlin Consulting. He founded the XML for ASP.NET Developers Web site (mailto:www.XMLforASP.NET), which focuses on using XML and Web services in Microsoft's .NET platform. He also is a corporate trainer and speaker who teaches XML and ASP.NET training courses around the United States. Dan co-authored Professional Windows DNA (Wrox) and ASP.NET Tips, Tutorials & Code (Sams), and he authored XML for ASP.NET Developers (Sams). E-mail Dan at [email protected].
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
