public static void AjouterEnBDDEmploye(Personne p)
{
string connectString = Properties.Settings.Default.ConnectionString;
string queryString = @"INSERT Employees(FirstName, LastName) VALUES (@Prénom, @Nom)";
var paramNom = new SqlParameter("@Nom", DbType.String);
var paramPrénom = new SqlParameter("@Prénom", DbType.String);
paramNom.Value = p.Nom;
paramPrénom.Value = p.Prénom;
using (var connect = new SqlConnection(connectString))
{
connect.Open();
var command = new SqlCommand(queryString, connect);
command.Parameters.Add(paramNom);
command.Parameters.Add(paramPrénom);
command.ExecuteNonQuery();
}
}
private static void GetPeopleFromReader(List <Personne> listPersonne, SqlDataReader reader)
{
while (reader.Read())
{
int employeeID = (int)reader["EmployeeID"];
if (listPersonne.Count() == 0 || listPersonne.Last().EmployeeID != employeeID)
{
Personne personne = new Personne();
personne.EmployeeID = employeeID;
personne.FirstName = (string)reader["FirstName"];
personne.LastName = (string)reader["LastName"];
if (reader["Photo"] != DBNull.Value)
{
personne.Picture = ConvertBytesToImageSource((Byte[])reader["Photo"]);
}
personne.ListTerritory = new List <string>();
if (reader["ReportsTo"] != DBNull.Value)
{
personne.ManagerID = (int)reader["ReportsTo"];
}
if (reader["MFirstName"] != DBNull.Value)
{
personne.ManagerFirstName = (string)reader["MFirstName"];
}
if (reader["MLastName"] != DBNull.Value)
{
personne.ManagerLastName = (string)reader["MLastName"];
}
listPersonne.Add(personne);
}
else if (reader["TerritoryDescription"] != DBNull.Value)
{
listPersonne.Last().ListTerritory.Add((string)reader["TerritoryDescription"]);
}
}
}
static public void AddEmployee(Personne personne)
{
var connectString = Properties.Settings.Default.ConnectionStringNW;
string sqlQuery = @"INSERT Employees (FirstName, LastName)
VALUES (@FirstName, @LastName)";
var param = new SqlParameter("@FirstName", DbType.String);
param.Value = personne.FirstName;
var param2 = new SqlParameter("@LastName", DbType.String);
param2.Value = personne.LastName;
using (var connect = new SqlConnection(connectString))
{
connect.Open();
//on initialise la transaction
SqlTransaction tran = connect.BeginTransaction();
try
{
var command = new SqlCommand(sqlQuery, connect, tran);
command.Parameters.Add(param);
command.Parameters.Add(param2);
command.ExecuteNonQuery();
// si tout se passe bien on commit la transaction
tran.Commit();
}
catch (Exception)
{
// si un problème survient, on rollback
tran.Rollback();
throw;
}
}
}
/// <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);
}
public ContexteEmploye()
{
Employees = new ObservableCollection <Personne>(DAL.GetPeople());
NewEmployee = new Personne();
}
public ContexteEmploye()
{
_nouvelEmploye = new Personne();
_employes = new ObservableCollection <Personne>();
_employes = DAL.GetEmployesInformations();
}
public WndAjoutEmploye(Personne p)
{
InitializeComponent();
btnOk.Click += BtnOk_Click;
DataContext = p;
}