Monday, April 20, 2009

Populate an ASP .Net Treeview from a SQL self-join table

I had last week an ASP .net TreeView control to fill from a self-join table. I looked on the Internet to find an Algorithm but I did not find any solution using C# or VB .Net but a solution in Code Project using SQL or other using DataSets. I then decided to post the following Algorithm that might help somebody else.
Here is the table I used in the example and the generated treeview image on the right.


Location.ID Location.Name Location.ParentID
1 Americas 0
2 Europe 0
3 Asia 0
4 Northern America 1
5 Western Europe 2
6 Western Asia 3
7 Eastern Asia 3
8 South-Eastern Asia 3
9 Northern Europe 2
10 Southern Europe 2
11 Canada 4
12 United States 4
13 Netherlands 9
14 France 9
15 Israel 5
16 Japan 6
17 Spain 10
18 United Kingdom 9
19 Vietnam 8
20 Quebec 11
21 California 12
22 District of Columbia 12
23 England 18
24 Hefa 15
25 Midi Pyrenees 14
26 Nara 16
27 New Jersey 12
28 Noord-Brabant 13
29 Ontario 11
30 Santa Cruz De Tenerife 17

 

Here is the code I used to generate the treeview:

<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Page Language="C#" %>

 

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Import Namespace="System.Collections.Generic" %>

<form runat="server">

<asp:treeview runat="server" id="TreeView1" />

</form>

 

<script runat="server">

   

    public class Location

    {

        private string _locationName;

        private int _locationId;

        private int _parentLocationId;

 

        public string LocationName

        {

            get { return _locationName; }

            set { _locationName = value; }

        }

 

        public int LocationId

        {

            get { return _locationId; }

            set { _locationId = value; }

        }

 

        public int ParentLocationId

        {

            get { return _parentLocationId; }

            set { _parentLocationId = value; }

        }

    }

        //list of all nodes

        SortedList<int, Location> myLocations = new SortedList<int, Location>();

        //list of all created nodes

        SortedList<int, TreeNode> myCreatedNodes = new SortedList<int, TreeNode>();

 

    public void Page_Load(object sender, EventArgs e)

    {

        string result="<br>start loading  page : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

        try

        {

            System.Data.SqlClient.SqlConnection myCon = new System.Data.SqlClient.SqlConnection("Server=localhost;Database=myCompany;integrated security=SSPI");

 

            myCon.Open();

            SqlCommand myCommand = new SqlCommand();

 

            myCommand.CommandText = @"select ID,

                                        Name,

                                        ParentID

                                        from Location

                                        ";         

           

            myCommand.Connection = myCon;

            SqlDataReader myReader = myCommand.ExecuteReader();

            Location myLocation = null;

            while (myReader.Read())

            {

                myLocation = new Location();

                myLocation.LocationName = Convert.ToString(myReader[1]);

                myLocation.LocationId = Convert.ToInt32(myReader[0]);

                myLocation.ParentLocationId = Convert.ToInt32(myReader[2]);

                myLocations.Add(myLocation.LocationId, myLocation);

            }

 

            myCon.Close();

 

            TreeNode aNode = null;

            result+="<br>start processing treeview : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

           

            foreach (int aKey in myLocations.Keys)

            {

                string code = myLocations[aKey].LocationId.ToString();

                aNode = new TreeNode(myLocations[aKey].LocationName, code);

                CreateNode(aNode);

            }

            result += "<br>end processing treeview : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

        }

        catch (Exception ex)

        {

            Response.Write("error<BR>" + ex.Message);

            Response.Write("Trace<BR>" + ex.StackTrace);

        }

        Response.Write(result);

    }

 

    public void CreateNode(TreeNode aNode)

    {

        //This list stores all the nodes id from the current node to the ultimate parent

        List<int> myPath = new List<int>();

        if (!myCreatedNodes.ContainsValue(aNode))//if the node was not alreazdy created

        {

            int nodeId=1001;

            nodeId = Convert.ToInt32(aNode.Value);

            //Building the current node path untill the ultimate parent

            myPath.Add(nodeId);

            while (nodeId != 0)

            {

               if(nodeId!=0){

               nodeId= myLocations[nodeId].ParentLocationId;

               myPath.Add(nodeId);

                }

            }

        }

       

        //descending from Ultimate parent until the node

        //if the current node does not exists we create it and add it to created nodes collection.

        //if it has not a parent we add it to the treeview

        //if it has a parent,the parent was already created because we come from it, so we add the current node to the parent.

        TreeNode nodeToAdd = null, ParentNodeTofind = null;

        for (int j = myPath.Count - 1; j > -1; j--)

        {

            if (myPath[j] != 0)

            {

                //checking for each path if the nodes was already created

                if (!myCreatedNodes.Keys.Contains(myLocations[myPath[j]].LocationId))

                {

                    //creating the node and adding it to the created nodes collection.

                    nodeToAdd = new TreeNode(myLocations[myPath[j]].LocationName, myLocations[myPath[j]].LocationId.ToString());

                    myCreatedNodes.Add(myLocations[myPath[j]].LocationId, nodeToAdd);

 

                    int parentId = myLocations[myPath[j]].ParentLocationId;

                    //checking if the node has a parent

                    if (parentId == 0)//this node has no parent we add it to the tree view

                    {

                        TreeView1.Nodes.Add(nodeToAdd);

                    }

                    else//this node has a parent

                    {

                        //rerieving parent node (sure to find it)

                        ParentNodeTofind = myCreatedNodes[myLocations[myPath[j]].ParentLocationId];

                        //we add the node to its parent childNodes

                        ParentNodeTofind.ChildNodes.Add(nodeToAdd);

                    }

                }

            }

        }

    }

</script>

 

 

5 comments:

Anonymous said...

Hi... Thank you for this great post.. I used the codes in this post after converting them to VB, and they work great form me...
... Please I have a questions.. if I want to apply that code not on treeview control but on menu control, what will be the changes needed to be done

Marc Charmois said...

Hi,
thank you for the comment. I am happy if this was helpful...

Regarding the Asp .Net Menu, as it is based on an XML Site Map, we have to generate an XML document either as a file or an object in cache...

Fortunately, we have classes in .Net System.XML, XmlDocument and XmlNode the instance of which is exactly what we want and that has similar methods to the treeview and the treeNode ones.

So I would use this to generate an XmlDocument and would use the XmlDocument as a data source of my menu...

Hope that helps...

Marc

Nasser said...

Hi Marc ..
Please if i want to add a hyperlink to the tree, so when the client clicks on the tree elements it will transfer him to the correct location..... can you point where should i do the changes..

Thanks..

Anonymous said...

Hello,
Hello,

Thank you very much for sharing this code.

I would like to be able to also check whether or not a node has a child/children.

Could you please show us how and where to set such a conditional statement in the code?

Regards.

Jean

Marc Charmois said...

Hi Jean,

you have two places in the code where you can perform this operation :

1 - before creating the treeview :
Loop the myLocations SortedList and check if a Location has its ID that is referenced as a ParentLocationId for another location.
If yes this location has child(ren)...
If no this location has no child

2 - after having populated the treeview :
loop the treeview nodes and use the Nodes property of the TreeNode

http://msdn.microsoft.com/en-us/library/system.windows.forms.treenode.nodes.aspx

Good Luck...

Marc