Dot Net Thoughts

If you want to display hierarchical data in a Treeview normally we are using recursion. I was looking for code which helps to avoid recursion using a Single query. But that code was using VB.Net and it was using a class called “Collection”, which is not available in C#. So I was looking for a compatable code in C# for long time and today I got the chance to re-write it using C#, but I am using Lamda expressions for this.

Here is the Table structure I want to display in Treeview

CREATE TABLE [dbo].[tblEmployees](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](50) NOT NULL,
[Parent] [int] NOT NULL)

And I inserted following Data in it.

And the Stored Procedure for getting employees using Common Table Expressions.

-- usp_GetEmployees
CREATE PROCEDURE [dbo].[usp_GetEmployees]
AS
BEGIN
	WITH SimpleRecursive AS
	(SELECT EmployeeName, EmployeeId, Parent, 0 AS Depth FROM dbo.tblEmployees
	WHERE (EmployeeId IN(SELECT EmployeeId FROM dbo.tblEmployees AS Recursion1
	WHERE (Parent = 0)))
	UNION ALL
	SELECT P.EmployeeName, P.EmployeeId,P.Parent, A.Depth + 1 AS Depth
	FROM dbo.tblEmployees AS P INNER JOIN SimpleRecursive AS A ON A.EmployeeId = P.Parent)
	SELECT EmployeeName, EmployeeId, CONVERT(INT, Parent) AS Parent, Depth
	FROM SimpleRecursive AS SimpleRecursive_1
	ORDER BY Depth
END

And the code in C# which adding nodes to Treeview.

private void PopulateTreeview()
{
    this.tvEmployees.Nodes.Clear();
    Employees employees = new Employees();
    using (SqlConnection connection = new SqlConnection(@"Server=.\SQLEXPRESS; User Id=SQLUser;Password=SQLPassword;Database=Database"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("usp_GetEmployees", connection))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                employees.Add(new Employees.Employee()
                {
                    Depth = int.Parse(reader["depth"].ToString()),
                    EmployeeId = int.Parse(reader["EmployeeId"].ToString()),
                    Parent = int.Parse(reader["Parent"].ToString()),
                    EmployeeName = reader["EmployeeName"].ToString(),
                });
            }
        }
    }

    foreach (Employees.Employee employee in employees)
    {
        Employees.Employee parentEmp = employees.Find(o => o.EmployeeId == employee.Parent);
        if (parentEmp != null)
        {
            this.tvEmployees.Nodes.Find(parentEmp.EmployeeId.ToString(), true)[0].Nodes.Add(employee.EmployeeId.ToString(), employee.EmployeeName);
        }
        else
        {
            this.tvEmployees.Nodes.Add(employee.EmployeeId.ToString(), employee.EmployeeName);
        }
    }
    this.tvEmployees.ExpandAll();
}

And the employees class

public class Employees : List<Employees.Employee>
{
    public class Employee
    {
        public int EmployeeId
        {
            get;
            set;
        }
        public string EmployeeName
        {
            get;
            set;
        }
        public int Parent
        {
            get;
            set;
        }
        public int Depth
        {
            get;
            set;
        }
    }
}

And here is the screenshot

Thanks to Aneesh and Anas for their valuable comments.
Happy Programming