Represent a hierarchy in a TreeView from MSSQL database

[Jedimark]

Hello there, I was wondering if someone can get me started on how to represent a hierarchy in a TreeView that is derived from a MSSQL Server database with several levels of depth. For example:

- Department one

• Group one

• Group two
  • Team one
  • Team two
    • Employee one
    • Employee two
  • Team three
• Group three
  • Team four
    • Employee three

• Department two

All departments are in a table tblDepartments All groups are in a table tblGroups (with a foreign key linking to a DepartmentID) All teams are in a table tblTeams (with a foreign key linking to a GroupID) All employees are in a table tblEmployees (with a foreign key linking to a TeamID) Ideally I would like to use a solution that uses WPF/LINQ if possible. Many thanks, Mark

[Pete OHanlon]

Well, your TreeView representation should be based on the MVVM implementation described in this[^] excellent article. Basically I can see that you are going to need the following type of hierarchy:

public class DepartmentViewModel
{
public ObservableCollection<GroupViewModel> Groups { get; set; }
// Other properties here...
}

public class GroupViewModel
{
public ObservableCollection<TeamViewModel> Teams { get; set; }
// Other properties here...
}

public class TeamViewModel
{
public ObservableCollection<EmployeeViewModel> Employees { get; set; }
// Other properties here...
}

public class EmployeeViewModel
{
// Other properties here...
}

Pay particular attention, in Josh's example, to the section on geographic breakdown with load on demand. That shows details about applying a complex hierarchy, and several excellent ideas for speeding up the loading of the tree by reducing the amount of data retrieved.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

[ankitjoshi24]

I am not sure how much helpful this is but I found something which could be helpful. Hope the following link is helpful and do let me know if it wrosk :-) http://www.vbdotnetheaven.com/UploadFile/rohatash/6004/[^] Also I found the following code which may help too: - public class MyTreeView : TreeView { public static bool GetIsRootNode(DependencyObject obj) { return (bool)obj.GetValue(IsRootNodeProperty); } public static void SetIsRootNode(DependencyObject obj, bool value) { obj.SetValue(IsRootNodeProperty, value); } public static readonly DependencyProperty IsRootNodeProperty = DependencyProperty.RegisterAttached("IsRootNode", typeof(bool), typeof(MyTreeView), new UIPropertyMetadata(false)); protected override void PrepareContainerForItemOverride( DependencyObject element, object item) { SetIsRootNode(element, true); base.PrepareContainerForItemOverride(element, item); } } -- AJ

[Pete OHanlon]

Unfortunately, those two examples represent poor ways to implement treeviews in that they represent difficult ways to extend and enhance the data.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

[SledgeHammer01]

What does either answer even have to do with the OP's question? Not a dang thing :D.

[Mycroft Holmes]

I use a class called TreeNodeUI

	public class TreeNodeUI
	{
		public TreeNodeUI(string sLabel,string sNodeKey,object oTag)
		{
			NodeLabel = sLabel;
			NodeKey = sNodeKey;
			NodeTag = oTag;
			ChildNodes = new ObservableCollection<TreeNodeUI>();
		}

		public string NodeLabel { get; set; }
		public string NodeKey { get; set; }
		public object NodeTag { get; set; }
		public ObservableCollection<TreeNodeUI> ChildNodes { get; set; }
	}

and I load it with

	public void LoadTree()
	{
		NodeList.Clear();
		gUI.TreeNodeUI oNode;
		FurnishCategoryList.OrderBy(x => x.Category);

		foreach (FurnishCategoryDB oDB in FurnishCategoryList)
		{
			oNode= new gUI.TreeNodeUI(oDB.Category, string.Format("/{0}/", oDB.FurnishCategoryID), oDB);
			LoadNode(oNode, oDB.FurnishCategoryID);
			NodeList.Add(oNode);
		}
	}

		private void LoadNode(gUI.TreeNodeUI oParent, int iCategoryID)
		{
			List<FurnishSubCategoryDB> lSC = VML.FurnishSubCategoryVMStatic.FurnishSubCategoryList.Where(x => x.CategoryID == iCategoryID).ToList();
			oParent.ChildNodes.Clear();
			foreach (FurnishSubCategoryDB oDB in lSC)
			{
				oParent.ChildNodes.Add(new gUI.TreeNodeUI(oDB.SubCategory, string.Format("/{0}/{1}/", oDB.CategoryID, oDB.SubCategoryID), oDB));									
			}				
		}

Note that I put the object into the NodeTag property for convenient retrieval. Caveat I am bloody sure this is probably the most resource intensive method of managing a tree but it works!

Never underestimate the power of human stupidity RAH

[Jedimark]

Many thanks for this.

[Pete OHanlon]

My pleasure. I'm glad to help.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx