ARTICLE

Displaying single table database hierarchy with DataSet and DataRelations

Posted by Jigar Desai in Articles | Web Dev in VB.NET on August 07, 2006

Tags: ado.net, asp.net, datarelation, dataset, sql, xsl, xslt

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.

Tweet
18607
2

Download Files:

hierarchy.zip

Reader Level: Intermediate

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.

Employee Table Structure

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.

Employee Data

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

Public Function GetDataSet() As DataSet

Dim result As New DataSet

Dim connString As String = "server=.;uid=sa;pwd=;database=northwind"

myConnection = New SqlConnection(connString)

If (True) Then

Dim query As String = "Select EmployeeID,FirstName,Title,ReportsTo from Employees"

Dim myCommand As New SqlCommand(query, myConnection)

Dim myAdapter As New SqlDataAdapter

myCommand.CommandType = CommandType.Text

myAdapter.SelectCommand = myCommand

myAdapter.Fill(result)

myAdapter.Dispose()

End If

result.DataSetName = "Employees"

result.Tables(0).TableName = "Employee" '

Dim relation As New DataRelation("ParentChild", result.Tables("Employee").Columns("EmployeeID"), result.Tables("Employee").Columns("ReportsTo"), True)

relation.Nested = True

result.Relations.Add(relation)

End Function 'GetDataSet

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.

<FirstName>Andrew</FirstName>

<Title>Vice President, Sales</Title>

<FirstName>Nancy</FirstName>

<Title>Sales Representative</Title>

</Employee>

...

</Employee>

</Employees>

Transforming XML using XSLT

XSL Transformation is one of the best option if you want to display nested data, actually before Sitemap and TreeControl which came with ASP.Net 2.0 XSL transformation was the only neat way to display hierarchy.

We will use System.Web.UI.WebControl.Xml control to display xml content using XSL Transformation. Xml control requires XmlDocument and Transformsource to transform the document.

Following is the XSLT that we will apply to XML document. XSLT will perform recursive transformation to generate nested list.

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output omit-xml-declaration="yes" />

<xsl:template match="/Employees">

<xsl:call-template name="EmployeeListing" />

</xsl:template>

<xsl:template name="EmployeeListing">

<ul>

<xsl:apply-templates select="Employee" />

</ul>

</xsl:template>

<xsl:template match="Employee">

<li>

<xsl:value-of select="FirstName"/>

<xsl:if test="count(Employee) > 0">

<xsl:call-template name="EmployeeListing" />

</xsl:if>

</li>

</xsl:template>

</xsl:stylesheet>

Following is the final result.

Result of Transformation

End notes

This sample displays nested list, but you can use same principle to display tree structure, site menu and many other hierarchal data. A part that I have not touched in this sample is retrieving data only under certain node, but you will be able to find methods of doing that by searching on internet. Also take a look Joe Celko's favorite Nested Set model for storing hierarchy in SQL.

Run sample code

NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).

post comment

During the development of a page you may need to populate a number of controls from DB. In that case you have to write a number of functions to populate different controls. In this tip I am going to show you a way to do all these operations in a single function.

Step 1:

- Write the different queries for getting the data from the DB for different controls in a single string variable by separating them with semicolon.

Ex -

//Create the SQL query.

string selectQueryForControlPopulation =

"SELECT COL1,COL2,... FROM TABLE_NAME1 WHERE CONDITION; SELECT COL1,COL2,..FROM TABLE_NAME2 WHERE CONDITION; ......";

Step 2:

- Create the DataAdapter, DataSet object. Execute the query and fill the DataSet object.

Ex -

//Create the Connection object.

OleDbConnection oConnection = newOleDbConnection(ConfigurationSettings.AppSettings["SQLConnectionString"]);

//Create the Command object.

OleDbCommand oCommand = new OleDbCommand(selectQueryForControlPopulation , oConnection );

//Create the DataAdapter object and set its property.

OleDbDataAdapter oAdapter = new OleDbDataAdapter();

oAdapter .SelectCommand = oCommandPopulateOrganizations;

// Create a DataSet object.

DataSet oDataSet = new DataSet();

//Filling the DataSet object.

oAdapter .Fill(oDataSet);

(**) The DataSet object contain the result of different queries as different tables. We can access those table to fill our controls.

http://www.mindfiresolutions.com/Populate-multiple-controls-from-DB-in-a-single-function-using-a-single-DataSet-831.php

Posted by eliza sahoo on Jun 07, 2010

Hi Jigar, Do you know why I receive the following error: System.ArgumentNullException: 'column' argument cannot be null. on the Datarelation line: Dim relation As New DataRelation("ParentChild", result.Tables("Employee").Columns("EmployeeID"), result.Tables("Employee").Columns("ReportsTo"), True) Thanks

Posted by eric fritz on Feb 28, 2008

TOP LEADERS