コード例 #1
0
ファイル: DAL.cs プロジェクト: idjoufomekong/Exercices
        //Lecture du reader retourné par la requête SQL et construction de l'objet Employé
        private static void GetEmployesFromDataReader(List <Employe> lstEmpl, SqlDataReader reader)
        {
            int Id = (int)reader["EmployeeID"];

            if ((lstEmpl.Count == 0) || lstEmpl.Last().Id != Id) // (lstEmpl[lstEmpl.Count - 1].Id != Id))
            {
                var emp = new Employe();
                emp.Id     = (int)reader["EmployeeID"];
                emp.Nom    = (string)reader["LastName"];
                emp.Prenom = (string)reader["FirstName"];
                if (reader["ReportsTo"] != DBNull.Value)
                {
                    emp.IdManager     = (int)reader["ReportsTo"];
                    emp.NomManager    = (string)reader["MLastName"];
                    emp.PrenomManager = (string)reader["MFirstName"];
                }

                lstEmpl.Add(emp);
                emp.Territoires = new List <Territoire>();
                Id = emp.Id;
            }
            if (reader["TerritoryID"] != DBNull.Value)
            {
                var ter = new Territoire();
                ter.IdTerritoire = (string)reader["TerritoryID"];
                if (reader["TerritoryDescription"] != DBNull.Value)
                {
                    ter.NomTerritoire = (string)reader["TerritoryDescription"];
                }

                lstEmpl[lstEmpl.Count - 1].Territoires.Add(ter);
            }
        }
コード例 #2
0
ファイル: DAL.cs プロジェクト: vichy60/exercices
        // Récupère les employés et leur territoires
        public static List <Employe> GetEmployesTerritoires()
        {
            List <Employe> lstEmployes = new List <Employe>();

            var    connectString = Properties.Settings.Default.NorthwindConnectString;
            string queryString   = @"select e.EmployeeID, LastName, FirstName, t.TerritoryID, t.TerritoryDescription
                        from Employees e
                        inner join EmployeeTerritories et on e.EmployeeID = et.EmployeeID
                        inner join Territories t on et.TerritoryID = t.TerritoryID
                        order by EmployeeID, TerritoryDescription";

            using (var connect = new SqlConnection(connectString))
            {
                var command = new SqlCommand(queryString, connect);
                connect.Open();

                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int idEmpl = (int)reader["EmployeeID"];

                    // Si l'id de l'employe courant est != de celui du dernier de la liste,
                    // on crée un nouvel employé
                    Employe emp = null;
                    if (lstEmployes.Count == 0 || lstEmployes[lstEmployes.Count - 1].Id != idEmpl)
                    {
                        emp             = new Employe();
                        emp.Id          = (int)reader["EmployeeID"];
                        emp.Prenom      = (string)reader["FirstName"];
                        emp.Nom         = (string)reader["LastName"];
                        emp.Territoires = new List <Territoire>();

                        lstEmployes.Add(emp);
                    }
                    else
                    {
                        emp = lstEmployes[lstEmployes.Count - 1];
                    }

                    Territoire t = new Territoire();
                    t.Code        = (string)reader["TerritoryID"];
                    t.Description = (string)reader["TerritoryDescription"];
                    emp.Territoires.Add(t);
                }
            }

            return(lstEmployes);
        }
コード例 #3
0
ファイル: DAL.cs プロジェクト: fwillemain/Exercices
        private static void GetEmployésFromDataReader(SqlDataReader reader, List <Employé> lstEmployé)
        {
            while (reader.Read())
            {
                if (!lstEmployé.Any() || lstEmployé.Last().Id != (int)reader["EmployeeID"])
                {
                    Employé e = new Employé()
                    {
                        Id            = (int)reader["EmployeeID"],
                        Prénom        = reader["FirstName"].ToString(),
                        Nom           = reader["LastName"].ToString(),
                        LstTerritoire = new List <Territoire>()
                    };

                    if (reader["ManagerFirstName"] != DBNull.Value)
                    {
                        e.PrénomManager = reader["ManagerFirstName"].ToString();
                    }

                    if (reader["ManagerLastName"] != DBNull.Value)
                    {
                        e.NomManager = reader["ManagerLastName"].ToString();
                    }

                    lstEmployé.Add(e);
                }

                if (reader["TerritoryID"] != DBNull.Value && reader["TerritoryDescription"] != DBNull.Value)
                {
                    Territoire t = new Territoire()
                    {
                        Id          = reader["TerritoryID"].ToString(),
                        Description = reader["TerritoryDescription"].ToString()
                    };

                    lstEmployé.Last().LstTerritoire.Add(t);
                }
            }
        }
コード例 #4
0
        /// <summary>
        /// Récupère toutes les informations des employés dans une liste de Personne
        /// </summary>
        /// <returns></returns>
        public static ObservableCollection <Personne> GetEmployesInformations()
        {
            int      IdCourant;
            Personne p = null;
            ObservableCollection <Personne> listeEmployes = new ObservableCollection <Personne>();
            string queryString   = @"select E.EmployeeID, E.LastName, E.FirstName, E.Photo, M.FirstName, M.LastName, T.TerritoryID, T.TerritoryDescription from Employees E
                                   left outer join EmployeeTerritories ET on ET.EmployeeID = E.EmployeeID
                                   left outer join Territories T on ET.TerritoryID = T.TerritoryID
                                   left outer join Employees M on M.EmployeeID = E.ReportsTo";
            string connectString = Properties.Settings.Default.ConnectionString;

            using (var connect = new SqlConnection(connectString))
            {
                var command = new SqlCommand(queryString, connect);
                connect.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        IdCourant = (int)reader[0];
                        if (listeEmployes.Count == 0 || listeEmployes.Last().Id != IdCourant)
                        {
                            p             = new Personne();
                            p.Territoires = new List <Territoire>();
                            p.Id          = (int)reader[0];
                            p.Nom         = (string)reader[1];
                            p.Prénom      = (string)reader[2];
                            p.NomComplet  = (string)reader[2] + " " + (string)reader[1];
                            if (reader[3] != DBNull.Value)
                            {
                                p.Photo = ConvertBytesToImageSource((Byte[])reader[3]);
                            }
                            if (reader[4] != DBNull.Value)
                            {
                                p.PrénomManager = (string)reader[4];
                            }
                            if (reader[5] != DBNull.Value)
                            {
                                p.NomManager = (string)reader[5];
                            }
                            listeEmployes.Add(p);
                        }
                        else
                        {
                            p = listeEmployes[listeEmployes.Count - 1];
                        }

                        Territoire ter = new Territoire();
                        if (reader[6] != DBNull.Value)
                        {
                            ter.IdTerritoire = (string)reader[6];
                        }
                        if (reader[7] != DBNull.Value)
                        {
                            ter.DscrpTerritoire = (string)reader[7];
                        }
                        p.Territoires.Add(ter);
                    }
                }
            }
            return(listeEmployes);
        }