Displaying Single Table Database Hierarchy with DataSet and DataRelations

Hierarchies can be difficult to store SQL tables and even more difficult to query and display. This example will show you how you can use DataRelation to convert single table hierarchy in to nested XML and then use XSLT to transform it to nested list.

DataRelation is one of the least talked about feature of dataset, it can do wonders if you know how use it in your advantage. It can generate parent child display with nested repeater or even cross tab display if you know how to play with dataset and relations correctly.

Let's dive into details of how you can create nested xml and display on ASP.Net web page. To start with we will select our data table which has hierarchical data.

Table structure and data.

There are several methods to store hierarchy in database but most famous ones are "Adjacency Model" and "Nested Set Model", we will use "Adjacency Model" for our sample, we are going to use Employee table from NorthWind database, following is the structure of table. ReportsTo column has ID of Employee to which Employee reports.

And following is data inside employee table our goal is to convert that data into nested XML data which we can use in our ASP page to display hierarchy.

Getting DataSet and adding relation.

We will get four columns from the employee table (EmployeeID, FirstName, Title and ReportsTo). Once we have dataset filled with data we will add relation to that dataset.

Following code retrives dataset and adds DataRelation

DataSet result = new DataSet();

string connString = "server=localhost;Trusted_Connection=true;database=northwind";

using (SqlConnection myConnection = new SqlConnection(connString))

{

string query

= "Select EmployeeID,FirstName,Title,ReportsTo from Employees";

SqlCommand myCommand = new SqlCommand(query,myConnection);

SqlDataAdapter myAdapter = new SqlDataAdapter();

myCommand.CommandType = CommandType.Text;

myAdapter.SelectCommand = myCommand;

myAdapter.Fill(result);

myAdapter.Dispose();

}

result.DataSetName = "Employees";

result.Tables[0].TableName = "Employee";

DataRelation relation = new DataRelation("ParentChild",

result.Tables["Employee"].Columns["EmployeeID"],

result.Tables["Employee"].Columns["ReportsTo"],

true);

relation.Nested = true;

result.Relations.Add(relation);

You must be familiar with first part of the code which retrieves dataset using DataAdapter, once we have data set we will name dataset and datatable so that xml output has proper element name instead of default "DataSet" and "Table1" as element, next part is most important for us, we will set DataRelation in that part.

We will use DataRelation constructor which takes four parameters. First parameter is name of relation, second parameter is parentColumn which in our case is "EmployeeID" column and third parameter is child Column which in our case is "ReportsTo" and last one is createConstraints we will set it to true because we know all data is valid in our table.

And then we will set Nested property to true so that dataset can create nested XML for us.

Generated XML

We will use DataSet.GetXml() method to generate XML out of dataset, following is the structure of XML generated by dataset. As you can see employeeid 1 is child of employeeid 2.

<Employees>

<Employee>

<EmployeeID>2</EmployeeID>

<FirstName>Andrew</FirstName>

<Title>Vice President, Sales</Title>

<Employee>

<EmployeeID>1</EmployeeID>

<FirstName>Nancy</FirstName>