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>