It is not always possible to load you treeview data from a single self-referencing table. Often you have a certain number of related SQL Tables and you need to populate your treeview instance based on the relationships in the Tables. While implementation details vary depending on your SQL setup, certain rules still apply. For the purpose of this topic, let's assume that we need to populate the treeeview from 3 related SQL Tables
- Sites (SiteID - primary key)
- Groups (GroupID primary key)
- Items (ItemID primary key).
Sites may contain any number of Groups. Groups, in turn, my contain any number of Items, e.g.
Site1
Group 1.1
Item 1.1.1
Item 1.1.2
Group 1.2
Item 1.2.1
Site 2
Group 2.1
Here is our DataBase relationship schema:
The code is pretty much straight-forward - create three distinct DataTable instances for each Table in your database, load the data into the tables, add all tables to a DataSet and create the respective relationships. After that loop through the dependencies to populate the nodes. Here's sample version:
| C# |
 Copy Code |
|
private void GenerateTreeView()
{
OleDbConnection dbCon = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("tables.mdb"));
dbCon.Open(); DataSet ds = new DataSet();
DataTable siteTable = new DataTable();
DataTable groupTable = new DataTable();
DataTable itemTable = new DataTable();
OleDbDataAdapter siteAdapter = new OleDbDataAdapter("SELECT * FROM Sites", dbCon);
OleDbDataAdapter groupAdapter = new OleDbDataAdapter("SELECT * FROM Groups", dbCon);
OleDbDataAdapter itemAdapter = new OleDbDataAdapter("SELECT * FROM Items", dbCon);
siteAdapter.Fill(siteTable);
groupAdapter.Fill(groupTable);
itemAdapter.Fill(itemTable);
ds.Tables.Add(siteTable);
ds.Tables.Add(groupTable);
ds.Tables.Add(itemTable);
ds.Relations.Add("SiteGroup", ds.Tables[0].Columns["SiteID"], ds.Tables[1].Columns["SiteID"]);
ds.Relations.Add("GroupItem", ds.Tables[1].Columns["GroupID"], ds.Tables[2].Columns["GroupID"]);
foreach (DataRow siteRow in ds.Tables[0].Rows)
{
RadTreeNode siteNode = new RadTreeNode(siteRow["SiteText"].ToString());
RadTree1.Nodes.Add(siteNode);
foreach (DataRow groupRow in siteRow.GetChildRows("SiteGroup"))
{
RadTreeNode groupNode = new RadTreeNode(groupRow["GroupText"].ToString());
siteNode.Nodes.Add(groupNode);
foreach (DataRow itemRow in groupRow.GetChildRows("GroupItem"))
{
RadTreeNode itemNode = new RadTreeNode(itemRow["ItemText"].ToString());
groupNode.Nodes.Add(itemNode);
}
}
}
}
|
| VB.NET |
Copy Code |
|
Private Sub GenerateTreeView()
Dim dbCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("tables.mdb"))
dbCon.Open()
Dim ds As New DataSet()
Dim siteTable As New DataTable()
Dim groupTable As New DataTable()
Dim itemTable As New DataTable()
Dim siteAdapter As New OleDbDataAdapter("SELECT * FROM Sites", dbCon)
Dim groupAdapter As New OleDbDataAdapter("SELECT * FROM Groups", dbCon)
Dim itemAdapter As New OleDbDataAdapter("SELECT * FROM Items", dbCon)
siteAdapter.Fill(siteTable)
groupAdapter.Fill(groupTable)
itemAdapter.Fill(itemTable)
ds.Tables.Add(siteTable)
ds.Tables.Add(groupTable)
ds.Tables.Add(itemTable)
ds.Relations.Add("SiteGroup", ds.Tables(0).Columns("SiteID"), ds.Tables(1).Columns("SiteID"))
ds.Relations.Add("GroupItem", ds.Tables(1).Columns("GroupID"), ds.Tables(2).Columns("GroupID"))
Dim siteRow As DataRow
For Each siteRow In ds.Tables(0).Rows
Dim siteNode As New RadTreeNode(siteRow("SiteText").ToString())
RadTree1.Nodes.Add(siteNode)
Dim groupRow As DataRow
For Each groupRow In siteRow.GetChildRows("SiteGroup")
Dim groupNode As New RadTreeNode(groupRow("GroupText").ToString())
siteNode.Nodes.Add(groupNode)
Dim itemRow As DataRow
For Each itemRow In groupRow.GetChildRows("GroupItem")
Dim itemNode As New RadTreeNode(itemRow("ItemText").ToString())
groupNode.Nodes.Add(itemNode)
Next itemRow
Next groupRow
Next siteRow
End Sub
|
See Also:
http://www.telerik.com/support/knowledge-base/kb-article.aspx?b454K=eat&b454T=bgg