| Introduction |
| A Hierarchical Data is a data that is organized in a tree-like structure and structure allows information to be stored in a parent-child relationship with one-to-many relation records. This data can be stored either in a single table or different database tables with a parent-child relationship using foreign key. |
| |
 |
The table structure shows the raw data stored in a database table. The column EmpNo is the primary unique key field and the column ReportsTo is the field we are going to refer as foreign key, by which the records are related to each other. There is lot of ways to do this concept, but we always prefer a simple and easiest way to achieve this |
|
| |
For demonstration purpose, we are going to create Organizational Structure of a Company, which involves a Director, Manager, Sales Manager, Purchase Manager and other subordinate staffs. So we create table named as “Designation”, with 4 columns such as EmpNo, Designation, ReportsTo and Level. First three fields such as EmpNo, Designation and ReportsTo are self-explanatory. The last field Level is the rank of the designation in the Organization with 0 as its starting index. The Director of the company enjoys the top most position that he/she doesn’t need to report any one, so we input the level as 0, all the Managers of the company will come under Director, thus the level is 1, the department Managers such as Sales and Purchase will come under Manager, thus the level is 2 and the staff under them will be on level 3 and so on. Likewise, you can keep on increment the level based on rank of the Designation. Basically this column is used for creating a tree-like structure. |
|
Setup the Repeater Control:
In the aspx page, drag and drop a Repeater
control, then go to the html source of this page, add the ItemTemplate
in between the Repeater tag, then add an Asp.Net
Table control from the toolbox.
|
<asp:Repeater ID="Repeater1"
runat="server" OnItemDataBound="Repeater1_ItemDataBound">
<ItemTemplate>
<asp:Table ID="Table1" runat="server"></asp:Table>
</ItemTemplate>
</asp:Repeater>
|
|
Setup the Code-Behind:
The code-behind for this concept needs some attention, as it explains you the way
to display the data in the database in a Hierarchical or tree-like structure. Let
us go step by step.
Step 1: Add the SqlClient namespace in the code-behind
as follows,
|
using System.Data.SqlClient; |
Step 2: In the Page_Load event, you have to retrieve the
data from the database and store it in a DataTable,
DataTable dt = new DataTable();
string sql = "Select * from Designation";
SqlDataAdapter da = new
SqlDataAdapter(sql,
ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
da.Fill(dt); |
Step 3: Add the DataTable to a DataSet to add relationship
between the columns in later stages.
DataSet Ds = new DataSet();
Ds.Tables.Add(dt); |
Step 4: Now create two DataColumn objects, one for Primary
Key with the “EmpNo” column and one for Foreign Key with “ReportsTo” column from
the DataTable.
DataColumn PriKey = new DataColumn();
PriKey = dt.Columns["EmpNo"];
DataColumn ForKey = new DataColumn();
ForKey = dt.Columns["ReportsTo"]; |
Step 5:
Create a DataRelation object,
binding
both the Primary Key “PriKey” and Foreign
Key “ForKey” columns and name the relation as “ParentChild”.
|
DataRelation dataRel = new DataRelation("ParentChild", PriKey, ForKey, false);
|
Step 6: Very Important, we need this DataRelation object
“dataRel” to be nested, since we don’t know the number of levels it is going to
contain.
Step 7: Finally, we need to add this relationship to the
DataSet as follows,
|
Ds.Relations.Add(dataRel); |
|
| |
So the DataSet is ready with relationship and next we need to bind the DataSet with
the Repeater control. As per the concept of this article, we are going to display
the data in an hirerchical structure. For this we need to do some code to achive
this.
As we know only the initial
level, but we have no idea of the final level, since
there can be any number of level
or even it can be infinity. So the best way is to loop through the rows
is by a recursive methods. This recursive method will retrieve
the record by record till its
last level and store every record in a DataTable that
can be referred by its memory
location rather than its value.
Let us start, by writing a recursive method to retrieve the record and store it
in a DataTable, which is accessed by its reference rather than its value.
|
|
|
private void GetChildObjects(DataRow[] drChild, ref DataTable dtFinal)
{
foreach (DataRow childRow in drChild)
{
dtFinal.ImportRow(childRow);
GetChildObjects(childRow.GetChildRows("ParentChild"), ref
dtFinal);
}
}
|
|
The above method GetChildObjects, has two
parameters, the first one is the array
of DataRow and the second is the “ref” DataTable which is going to have the records.
Please note the “ref” keyword before the parameter declaration. This DataTable is
responsible to hold the records to binding. Inside this method, we loop through
the records of the DataRow arrays and we import record by record to the “ref” DataTable.
Then again we call the same method GetChildObjects, to loop through its child rows.
If there is no child rows, then execution will be stopped and no further call to
this method will be made.
Now, we are going to write another method to kick off the GetChildObjects methods,
as follows,
|
private DataTable GetParentObjects(DataTable dtMain)
{
DataTable dtFinal = dtMain.Clone();
foreach (DataRow parentRow in dtMain.Select("ReportsTo is null"))
{
dtFinal.ImportRow(parentRow);
GetChildObjects(parentRow.GetChildRows("ParentChild"), ref
dtFinal);
}
return dtFinal;
}
|
|
| |
| |
The above GetParentObjects method, takes a DataTable parameter, which can be the
DataTable that holds the data from the database. Inside this method, we create a
local DataTable object as “dtFinal” by cloning from the input parameter’s DataTable.
The “Clone” method, simply copies the structure of the DataTable without the data.
Next we are going to loop through the records in the incoming DataTable, and as
we know the top level record in the database will have the ReportsTo field as null,
we are going to start the loop from this record. If there are more than one record
with ReportsTo as null, then this loop will run that many times. So we find the
first record with ReportsTo as null, then import it into the “dtFinal” DataTable
using the ImportRow method. Then we call the GetChildObjects method by passing child
records of the current parent record and “dtFinal” DataTable which is passed by
reference.
This is the starting point to the GetChildObjects method. Since we have nested the
DataSet, the recusive method will run continuously till the last record to find
its related child records. |
|
|
Step 8: The last step in the Page_Load event, is to bind
the Repeater control by calling the GetParentObjects as follows. Note that the argument
passed to the GetParentObjects method is the DataSet’s first Table, not the DataTable
object.
|
Repeater1.DataSource = GetParentObjects(Ds.Tables[0]);
Repeater1.DataBind();
|
The complete code of the Page_Load event is as follows,
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = new DataTable();
string sql = "Select * from Designation";
SqlDataAdapter da = new
SqlDataAdapter(sql,
ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
da.Fill(dt);
DataSet Ds = new DataSet();
Ds.Tables.Add(dt);
DataColumn PriKey = new DataColumn();
PriKey = dt.Columns["EmpNo"];
DataColumn ForKey = new DataColumn();
ForKey = dt.Columns["ReportsTo"];
DataRelation dataRel = new DataRelation("ParentChild", PriKey, ForKey,
false);
dataRel.Nested = true;
Ds.Relations.Add(dataRel);
Repeater1.DataSource = GetParentObjects(Ds.Tables[0]);
Repeater1.DataBind();
}
}
|
Setup Asp.Net Table Control to display the Hierarchy structure
To display the data in a Hierarchical structure, we need to code in the ItemDataBound
event of the repeater control as follows.
|
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType ==
ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
Table Table1 = (Table)e.Item.FindControl("Table1");
Table1.Width = Unit.Percentage(30);
Table1.BorderStyle = BorderStyle.Solid;
Table1.BorderWidth = Unit.Pixel(1);
Table1.CellPadding = 0;
Table1.CellSpacing = 0;
TableRow tblRow = new TableRow();
TableCell tblEmptyCell = new TableCell();
tblEmptyCell.Controls.Add(new LiteralControl(String.Empty));
tblEmptyCell.Width =
Unit.Percentage(20 *
Convert.ToInt32(((DataRowView)(e.Item.DataItem))["Level"].ToString()));
tblRow.Cells.Add(tblEmptyCell);
TableCell tblCell = new TableCell();
tblCell.Controls.Add(new
LiteralControl(((DataRowView)(e.Item.DataItem))["Designation"].ToString()));
tblCell.HorizontalAlign = HorizontalAlign.Left;
tblRow.Cells.Add(tblCell);
Table1.Rows.Add(tblRow);
}
} |
The above code is simple as its need no detail explanation. First we find the Table
control inside the Repeater control using its FindControl method, then we customize
the Table control by setting its properties such as Width, BorderStyle, BorderWidth,
CellPadding and CellSpacing. Next we create a TableRow to add to the Table control
and two TableCell objects, that is to be added to the TableRow. The first TableCell
object “tblEmptyCell” is responsible to create a tree-like structure by indenting
with an empty string and the width is calculated based on the Level field from the
database. The second TableCell object “tblCell”, is used to display the Designation
value for every record.
Now save all and press the “F5” button, the browser will popup, you can see the
data displayed in a hierarchical structure. You can customise the ItemDataBound
event as your wish to alter the look and feel of the display. |
