using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public class Employees : List<Employee>
    {
        public Employees()
        {
            string connectionString = @"Data Source=.\mssqlserver2014;Initial Catalog=AdventureWorks;Integrated Security=True";
            string sql = "select e.EmployeeID, e.ManagerID, e.Title, c.FirstName, "
                + "c.LastName, e.LoginID from HumanResources.Employee "
                + "e inner join Person.Contact c on e.ContactID = c.ContactID "
                + "order by e.ManagerID";
            List<Employee> employees = new List<Employee>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Employee e = this.Read(reader);
                    employees.Add(e);
                }
            }

            this.InsertAll(employees);
        }

        private Employee Read(SqlDataReader reader)
        {
            int employeeID = reader.GetInt32(0);
            int managerID = reader[1] == System.DBNull.Value ? 0 : (int)reader.GetInt32(1);
            string title = reader.GetString(2);
            string firstName = reader.GetString(3);
            string lastName = reader.GetString(4);
            string loginID = reader.GetString(5);
            return new Employee
            {
                EmployeeID = employeeID
                ,
                ManagerID = managerID
                ,
                Title = title
                ,
                FirstName = firstName
                ,
                LastName = lastName
                ,
                LoginID = loginID
                ,
                ManagedEmployees = new List<Employee>()
            };
        }

        private void InsertAll(List<Employee> employees)
        {
            foreach (Employee e in employees)
            {
                if (this.Count == 0 || e.ManagerID == null)
                {
                    this.Add(e);
                }
                else
                {
                    foreach (Employee manager in employees)
                        if (Insert(manager, e, 2))
                            break;
                }
            }
        }

        private bool Insert(Employee manager, Employee e, int tab)
        {
            if (manager.EmployeeID == e.ManagerID)
            {
                manager.ManagedEmployees.Add(e);
                e.Manager = manager;
                return true;
            }
            else
            {
                foreach (Employee m in manager.ManagedEmployees)
                    if (Insert(m, e, tab))
                        return true;
            }
            return false;
        }
    }

    public class Employee
    {
        public int EmployeeID { get; set; }
        public int? ManagerID { get; set; }
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string LoginID { get; set; }
        public List<Employee> ManagedEmployees { get; set; }
        public Employee Manager { get; set; }
        public override string ToString()
        {
            string mask = "{0} {1} {2} {3} {4}";
            return string.Format(mask, ManagerID, EmployeeID, Title, FirstName, LastName);
        }
    }
}
