I had this seemingly simple task yesterday: populate TreeView control (ASP.NET) with data from SQL Server 2005. The query returning data from SQL Server was spanning three joined tables with one-to-many relations, returning the denormalized set of records that has some redundancy due to information from parent tables replicated in each record (like having Company and Department information in each Employee record). Populating hierarchical TreeView from flat recordset is pretty inconvenient, so I decided to give a shot to the XML output feature of SQL Server 2005.
Converting flat denormalized result set output into hierarchical XML was as easy as adding "FOR XML AUTO" to the end of my query. I ran the query and the output was exactly what I wanted - no redundancy at all, with child records neatly nested inside the parent table XML nodes (for proper record nesting be sure to use correct order of the selected columns: parent table columns should precede child table columns in the T-SQL SELECT statement). Now I needed to bring this result back from SQL to the business tier of my C# application. I extensively use DataSets with the the convenience of built-in data-access methods created using DataSet Editor's query wizards. I right-clicked an appropriate table adapter and went through the "Add Query..." wizard, mapping my new stored procedure returning XML to the new data access method. At the end of the wizard "Tabular Data" option for the query result type was understandably grayed, because XML is hierarchical, not tabular. But generated method turned out to return Object type, and you would never know it unless you looked at the generated source code - properties of the generated methods do now show the return type of the method. I test-ran the method and it turned out to return what you would expect - XML text with all the data.
One could stop digging right here and simply use XmlDocument and XPath to parse out and the walk the XML, populating tree nodes in the process, but I wanted to turn XML data into a strongly-typed hierarchical structure instead of walking XML nodes. In .NET all you need to turn XML into a strongly-typed structure is XML schema. A nice little utility called xsd.exe converts XSD schemas into C# or VB.NET classes, which are XML-serializable. SQL Server 2005 is sweet enough to generate schema, along with bringing the XML data. I temporarily modified my query to have "FOR XML AUTO, XMLSCHEMA" appendix, ran it in SQL Server and got the XML preceded by the schema. I copied schema over to an XSD file and undid the change to the query, reverting the appendix back to "FOR XML AUTO".
Now, having created XSD schema defining XML structure returned by my query, all I needed to do is to feed the XSD file to XSD.exe utility to produce C# file with classes corresponding to m XML nodes, right? Almost. This was the part where things stopped going smoothly. First, xsd.exe complained, as it always does, that it could not find sqltypes.xsd schema, which is imported by the schema produced by SQL server. If you ever used either xsd.exe or wsdl.exe against schemas that import other files, you probably know that all the imported files need to be downloaded and saved locally, and in the case of xsd.exe, all imported schemas should be explicitly listed in the command line. Here's the example of xsd.exe command line (run Visual Studio Command Prompt to load command prompt window with all environment variables set):
xsd.exe yourschema.xsd sqltypes.xsd /classes /namespace:WhateverIsYourNamespace
This command will produce yourschema_sqltypes.cs file with strongl-typed C# classes wrapped into the WhateverIsYourNamspace namespace. If you expect to simply include this file into your project and rejoice at this point, not so fast. Although the code will compile, at run time using XmlSerializer.Deserialize() produced odd results - it complained that my root element with xmlns='' attribute was not expected. That was because schema got the default name when it was generated by SQL Server, while actual XML data did not reference the schema at all. To fix that I had to remove XmlTypeAttribute and XmlRootAttribute attributes from declarations of the generated classes. I had to keep XmlRoot attribute for the class representing root node, by its declaration was simplified to look like [System.Xml.Serialization.XmlRootAttribute(IsNullable = false)].
Once that part was done, deserialization of XML into strongly-typed hierarchical data structure started working just fine.
One more quick note: when .NET classes are generated from XSD schema, nullable data fields get done in a slightly different way compared to the DataSet. In the dataset, you'll get "type<Nullable>" or "type?" declaration for nullable data columns of value types (int, DateTime, etc). .NET classes generated by xsd.exe won't have nullable fields. Instead they will have extra boolean fields telling whether the field is nullable. For example, if your SQL data table has nullable BirthDate, the C# class produced by xsd.exe will have boolean BirthDateSpecified property, which does what BirthDate.HasValue would do in the dataset.
The whole process was not terribly smooth, but also not prohibitively burdensome. Of course, much better design would be if Microsoft allowed to specify XML schema for the TreeView control, and then to let data-bind the TreeView control to a SQL query or an object method that returns XML. This way it would be still strongly-typed, but the whole business of building XML-serializable C# classes out of the schema would not be necessary. This approach would also allow design-time definition of TreeView node formatting, just like GridView does it with strongly-typed datasets. Another useful feature would be automatic generation of C# classes - just like generation of strongly-typed datasets - for data-access methods returning XML.