Display hierarchical
data with TreeView
Very often
applications have need to manage hierarchical data and indeed such that
hierarchy is unlimited. This has been a bit problem in databases, although
solved using self-referencing tables or alternative solutions. OK, but how to
display such data that uses self-referencing table?
With
ASP.NET one optional answer is to use TreeView control. TreeView gives
developers chance to display hierarchical data as I’ll demonstrate in this
article. What makes TreeView so cool is that it can also display elements (tree
nodes) based on XML.
Overview of
things that developer needs to do to get this feature are:
Get the data from
self-referencing table into DataSetCreate DataRelation that
corresponds to relation in self-referencing table and add it to DataSet’s
Relations collection. DataRelation’s Nested property needs to be true.Get DataSet’s XML
representation and apply XSLT transformation for it so that result
corresponds to XML syntax used by TreeView control.Bind TreeView.
Self-referencing table
Assume that
in database we have table as follows:
CATEGORIES
CategoryID
ParentCategoryID
CategoryName
2
1
3
2
4
3
5
2
1.1
6
2
1.2
7
2
1.3
8
3
2.1
9
3
2.2
10
4
3.1
11
5
1.1.1
12
5
1.1.2
13
10
3.1.1
14
13
3.1.1.1
15
14
3.1.1.1.1
16
14
3.1.1.1.2
17
14
3.1.1.1.3
Shortly,
CategoryID is the primary key and ParentCategoryID is foreign key referencing
to CategoryID with default value NULL. CategoryName represents text I want to
display in TreeView.
Get data into DataSet
//Connection to database
OleDbConnection objConn=new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("db.mdb") +
";Persist Security Info=False");
//SQL query to get data from CATEGORIES table
OleDbCommand objCommand=new
OleDbCommand("SELECT * FROM CATEGORIES",objConn);
//OleDbDataAdapter
OleDbDataAdapter objDa =new
OleDbDataAdapter(objCommand);
//DataSet
DataSet ds=new
DataSet("CATEGORIESDS");
//Fill DataSet
objDa.Fill(ds ,"CATEGORIES");
Nested DataRelation
//Create DataRelation
DataRelation drel=new
DataRelation("CATEGORIES_RECURSIVE",
ds.Tables["CATEGORIES"].Columns["CategoryID"],
ds.Tables["CATEGORIES"].Columns["ParentCategoryID"]);
//Make sure relation is nested
drel.Nested =true;
//Add relation to DataSet's Relations collection
ds.Relations.Add(drel);
DataSet’s XML and XSLT
transformation [Source XML][XSLT stylesheet]
//XmlDocument to hold XML generated from DataSet
XmlDocument objDoc=new XmlDocument();
//Load XML
objDoc.LoadXml(ds.GetXml());
//Create XslTransform object
XslTransform objXSL=new XslTransform();
//Load XSLT stylesheet
objXSL.Load(Server.MapPath("transformationtemplate.xslt"));
//StringWriter to temporarily hold result of the
transformation
StringWriter writer=new StringWriter();
//Apply transformation with no arguments and dump
results to StringWriter.
objXSL.Transform(objDoc.CreateNavigator(),null,writer);
Bind TreeView [Result of transformation]
//Set TreeView's TreeNodeSrc property to get XML from
StringWriter.
TreeView1.TreeNodeSrc =writer.ToString();
//Bind TreeView
TreeView1.DataBind();
//Close StringWriter
writer.Close();
Other useful resources at AspAlliance:
Programming with TreeView by Steve Sharrock
Developing with the Treeview Web Control Part 1 by James Avery
Developing with the Treeview Web Control Part 2 by James Avery