public static void insertEspece(EspeceDAO p)
{
string query = "INSERT INTO espece (Nom) VALUES (@NomEspece);";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
cmd2.Parameters.AddWithValue("@NomEspece", p.nomEspeceDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void supprimerEspeceNombre(int idEspeceNombre)
{
string query = "DELETE FROM etude_has_plage_has_Espece WHERE IdNombreE = @IdNombreE;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdNombreE", idEspeceNombre);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerZoneInvestigation(int IdZone)
{
string query = "DELETE FROM etude_has_plage WHERE IdZone = @ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", IdZone);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerEtude(int id)
{
string query = "DELETE FROM Etude WHERE idEtude = @ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void SelectCommune(int id)
{
string query = "SELECT * FROM Commune WHERE idCommune= @ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateEspece(EspeceDAO p)
{
string query = "UPDATE espece set nomEspece=@NomEspece where idEspece=@IdEspece;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@NomEspece", p.nomEspeceDAO);
cmd.Parameters.AddWithValue("@IdEspece", p.idEspeceDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updatePlage(PlageDAO p)
{
string query = "UPDATE Plage set Nom=@NomPlage,SuperficEtude=@Superfi where idPlage=@IdPlage;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@NomPlage", p.nomPlageDAO);
cmd.Parameters.AddWithValue("@Superfi", p.superficEtudePlageDAO);
cmd.Parameters.AddWithValue("@IdPlage", p.idPlageDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateCommune(CommuneDAO p)
{
string query = "UPDATE Commune set Nom=@NomCommune,CodePostale=@CodePostale where idCommune=@IdCommune;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@NomCommune", p.nomCommuneDAO);
cmd.Parameters.AddWithValue("@CodePostale", p.CodePostaleDAO);
cmd.Parameters.AddWithValue("@IdCommune", p.idCommuneDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateEspeceNombre(EspeceNombreDAO p)
{
string query = "UPDATE etude_has_plage_has_Espece set Nombre=@Nombre where Etude_has_Plage_Etude_idEtude=@IdNombre;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@Nombre", p.NombreDAO);
cmd.Parameters.AddWithValue("@IdNombre", p.IdNombreEDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateDepartement(DepartementDAO p)
{
string query = "UPDATE departement set Nom=@NomDepartement,CodePostale=@CodePOSTALE where Departement=@IdDepartement;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@NomDepartement", p.nomDepartementDAO);
cmd.Parameters.AddWithValue("@CodePOSTALE", p.CodePostaleDAO);
cmd.Parameters.AddWithValue("@IdDepartement", p.idDepartementDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertDepartement(DepartementDAO p)
{
int id = getMaxIdDepartement() + 1;
string query = "INSERT INTO departement VALUES (@ID,@NomDepartement,@CodePostale);";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
cmd2.Parameters.AddWithValue("@ID", id);
cmd2.Parameters.AddWithValue("@NomDepartement", p.nomDepartementDAO);
cmd2.Parameters.AddWithValue("@CodePostale", p.CodePostaleDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertCommune(CommuneDAO p)
{
int id = getMaxIdCommune() + 1;
string query = "INSERT INTO Commune VALUES (@ID,@NomCommune,@CodePostale,@IdCommune);";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
cmd2.Parameters.AddWithValue("@ID", id);
cmd2.Parameters.AddWithValue("@NomCommune", p.nomCommuneDAO);
cmd2.Parameters.AddWithValue("@CodePostale", p.CodePostaleDAO);
cmd2.Parameters.AddWithValue("@IdCommune", p.idCommuneDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertPlage(PlageDAO p)
{
int id = getMaxIdPlage() + 1;
string query = "INSERT INTO Plage VALUES (@ID,@NomPlage,@Superfi,@Commune);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@NomPlage", p.nomPlageDAO);
cmd.Parameters.AddWithValue("@Superfi", p.superficEtudePlageDAO);
cmd.Parameters.AddWithValue("@Commune", p.CommuneDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static int getMaxIdEtude()
{
string query = "SELECT IFNULL(MAX(idEtude),0) FROM etude;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int maxIdEtude = reader.GetInt32(0);
reader.Close();
return(maxIdEtude);
}
public static UsersDAO getUsers(int idUsers)
{
string query = "SELECT * FROM users WHERE idUsers=@ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", idUsers);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
UsersDAO pers = new UsersDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetByte(6));
reader.Close();
return(pers);
}
public static EspeceDAO getEspece(int idEspece)
{
string query = "SELECT * FROM espece WHERE idEspece=@IdEspece;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdEspece", idEspece);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
EspeceDAO pers = new EspeceDAO(reader.GetInt32(0), reader.GetString(1));
reader.Close();
return(pers);
}
public static void updateEtude(EtudeDAO p)
{
string query = "UPDATE Etude set NbPersonne=@Nombre,Titre=@Titre,NbPlage=@Plage,dateCreation=@DateCrea, dateFin=@DateFin where idEtude=@IdEude;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@Nombre", p.NbPersonneEtudeDAO);
cmd.Parameters.AddWithValue("@Titre", p.TitreEtudeEtudeDAO);
cmd.Parameters.AddWithValue("@Plage", p.PlageEtudeDAO);
cmd.Parameters.AddWithValue("@DateCrea", p.dateCreationDAO);
cmd.Parameters.AddWithValue("@DateFin", p.dateFinDAO);
cmd.Parameters.AddWithValue("@IdEude", p.idEtudeDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateZoneInvestigation(ZoneInvestigationDAO p)
{
string query = "UPDATE etude_has_plage set NomZone=@NomZone,Angle1=@Angle1,Angle2=@Angle2,Angle3=@Angle3,Angle4=@Angle4 where IdZone=@IdZone;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdZone", p.IdZoneDAO);
cmd.Parameters.AddWithValue("@NomZone", p.NomZoneDAO);
cmd.Parameters.AddWithValue("@Angle1", p.Angle1DAO);
cmd.Parameters.AddWithValue("@Angle2", p.Angle2DAO);
cmd.Parameters.AddWithValue("@Angle3", p.Angle3DAO);
cmd.Parameters.AddWithValue("@Angle4", p.Angle4DAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertEspeceNombre(EspeceNombreDAO p)
{
int id = getMaxIdEspeceNombre() + 1;
string query = "INSERT INTO etude_has_plage_has_Espece VALUES (@ID,@IdEspece,@IdZone,@IdEtude,@IdPlage,@Nombre);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@IdEspece", p.IdEspeceDAO);
cmd.Parameters.AddWithValue("@IdZone", p.IdZoneDAO);
cmd.Parameters.AddWithValue("@IdEtude", p.IdEtudeDAO);
cmd.Parameters.AddWithValue("@IdPlage", p.IdPlageDAO);
cmd.Parameters.AddWithValue("@Nombre", p.NombreDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertEtude(EtudeDAO p)
{
int id = getMaxIdEtude() + 1;
string query = "INSERT INTO Etude VALUES (@ID,@Nombre,@Titre,@Plage,@DateCrea,@DateFin);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@Nombre", p.NbPersonneEtudeDAO);
cmd.Parameters.AddWithValue("@Titre", p.TitreEtudeEtudeDAO);
cmd.Parameters.AddWithValue("@Plage", p.PlageEtudeDAO);
cmd.Parameters.AddWithValue("@DateCrea", p.dateCreationDAO);
cmd.Parameters.AddWithValue("@DateFin", p.dateFinDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateUsers(UsersDAO p)
{
string query = "UPDATE Users set ( Nom=@NomUsers, Prenom=@PrenomUsers, AdresseMail=@Mail,Identifiant=@Identifiant, MotDePasse=@MDP, Administrateur=@ADMIN where idUsers=@IdUsers);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@NomUsers", p.nomUsersDAO);
cmd.Parameters.AddWithValue("@PrenomUsers", p.prenomUsersDAO);
cmd.Parameters.AddWithValue("@Mail", p.adresseMailUsersDAO);
cmd.Parameters.AddWithValue("@Identifiant", p.identifiantUsersDAO);
cmd.Parameters.AddWithValue("@MDP", p.motDePasseUsersDAO);
cmd.Parameters.AddWithValue("@ADMIN", p.administrateurUsersDAO);
cmd.Parameters.AddWithValue("@IdUsers", p.idUsersDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertUsers(UsersDAO p)
{
int id = getMaxIdUsers() + 1;
string query = "INSERT INTO Users VALUES (@ID,@NomUsers,@PrenomUsers,@Mail,@Identifiant,@MDP,@ADMIN);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@NomUsers", p.nomUsersDAO);
cmd.Parameters.AddWithValue("@PrenomUsers", p.prenomUsersDAO);
cmd.Parameters.AddWithValue("@Mail", p.adresseMailUsersDAO);
cmd.Parameters.AddWithValue("@Identifiant", p.identifiantUsersDAO);
cmd.Parameters.AddWithValue("@MDP", p.motDePasseUsersDAO);
cmd.Parameters.AddWithValue("@ADMIN", p.administrateurUsersDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertZoneInvestigation(ZoneInvestigationDAO p)
{
int id = getMaxIdZoneInvestigation() + 1;
string query = "INSERT INTO etude_has_plage (IdZone,Etude_idEtude,Plage_idPlage,Date,NomZone,Angle1,Angle2,Angle3,Angle4,Users_idUsers) VALUES (@IdZone,@IdEtude,@IdPlage,@DateCrea,@NomZone,@Angle1,@Angle2,@Angle3,@Angle4,@IdUsers);";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdZone", id);
cmd.Parameters.AddWithValue("@NomZone", p.NomZoneDAO);
cmd.Parameters.AddWithValue("@IdEtude", p.IdEtudeDAO);
cmd.Parameters.AddWithValue("@IdPlage", p.IdPlageDAO);
cmd.Parameters.AddWithValue("@DateCrea", p.EtudeDateDAO);
cmd.Parameters.AddWithValue("@Angle1", p.Angle1DAO);
cmd.Parameters.AddWithValue("@Angle2", p.Angle2DAO);
cmd.Parameters.AddWithValue("@Angle3", p.Angle3DAO);
cmd.Parameters.AddWithValue("@Angle4", p.Angle4DAO);
cmd.Parameters.AddWithValue("@IdUsers", p.IdUsersDAO);
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static CommuneDAO getCommune(int idCommune)
{
string query = "SELECT * FROM commune WHERE idCommune=@IDCommune;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IDCommune", idCommune);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
CommuneDAO com;
if (reader.HasRows)
{
com = new CommuneDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
}
else
{
com = new CommuneDAO(1, "Mauvais Num Commune", "Mauvais Code Postale", 1);
}
reader.Close();
return(com);
}
public static ZoneInvestigationDAO getZoneInvestigation(int IdZone)
{
string query = " SELECT * FROM etude_has_plage WHERE IdZone =@ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", IdZone);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
ZoneInvestigationDAO com;
if (reader.HasRows)
{
com = new ZoneInvestigationDAO(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetDateTime(3), reader.GetString(4), reader.GetDecimal(5), reader.GetDecimal(6), reader.GetDecimal(7), reader.GetDecimal(8), reader.GetInt32(9));
}
else
{
com = new ZoneInvestigationDAO(1, 1, 1, DateTime.Today, "Mauvais nom", 0, 0, 0, 0, 1);
}
reader.Close();
return(com);
}
public static EtudeDAO getEtude(int idEtude)
{
string query = " SELECT * FROM etude WHERE idEtude =@ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", idEtude);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
EtudeDAO com;
if (reader.HasRows)
{
com = new EtudeDAO(reader.GetInt32(0), reader.GetDecimal(1), reader.GetString(2), reader.GetInt32(3), reader.GetDateTime(4), reader.GetDateTime(5));
}
else
{
com = new EtudeDAO(1, 0, "Mauvaise superficie d'etude", 1, DateTime.Today, DateTime.Today);
}
reader.Close();
return(com);
}
public static UsersDAO getUsersMotdePasse(string motDePasseUsers)
{
string query = "SELECT * FROM users WHERE MotDePasse=@MDP;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@MDP", motDePasseUsers);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
UsersDAO user;
if (reader.HasRows)
{
user = new UsersDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetByte(6));
}
else
{
user = new UsersDAO(1, "Name", "First Name", "Identifiant", "@Mail", "MDP", 0);
}
reader.Close();
return(user);
}
public static DepartementDAO getDepartement(int idDepartement)
{
string query = " SELECT * FROM departement WHERE Departement =@IdDepartement;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdDepartement", idDepartement);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
DepartementDAO com;
if (reader.HasRows)
{
com = new DepartementDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
}
else
{
com = new DepartementDAO(1, "Mauvais Nom Departement", "Mauvais Code Postale");
}
reader.Close();
return(com);
}
public static EspeceNombreDAO getEspeceNombre(int idEspeceNombre)
{
string query = " SELECT * FROM etude_has_plage_has_Espece WHERE IdNombreE = @IdNombreE;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@IdNombreE", idEspeceNombre);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
EspeceNombreDAO com;
if (reader.HasRows)
{
com = new EspeceNombreDAO(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetDecimal(5));
}
else
{
com = new EspeceNombreDAO(1, 1, 1, 1, 1, 0);
}
reader.Close();
return(com);
}
public static PlageDAO getPlage(int idPlage)
{
string query = " SELECT * FROM Plage WHERE idPlage =@ID;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.Parameters.AddWithValue("@ID", idPlage);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
PlageDAO com;
if (reader.HasRows)
{
com = new PlageDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
}
else
{
com = new PlageDAO(1, "Mauvais Nom de Plage", "Mauvaise superficie d'etude", 1);
}
reader.Close();
return(com);
}
