//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);
}
}
// 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);
}
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);
}
}
}
/// <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);
}
