public List <Adherents> VerifLicence()
{
List <Adherents> lesAdherents = new List <Adherents>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "SELECT Licence from adherents";
//Crée Commande
MySqlCommand command = new MySqlCommand(query, connection);
//On crée un datareader et on execute la commande
using (MySqlDataReader dataReader = command.ExecuteReader())
{
//On li la base de données et on ajoute dans la liste les ahderents
while (dataReader.Read())
{
Adherents lAdherent = new Adherents();
lAdherent.setLicence((string)dataReader["Licence"]);
lesAdherents.Add(lAdherent);
}
//string MySQLFormatDate = dateValue.ToString("yyyy-MM-dd HH:mm:ss")
}
connection.Close();
}
return(lesAdherents);
}
/// <summary>
/// Modifie l'évènement
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonUpdate_Click(object sender, EventArgs e)
{
if (condition())
{
if (verifiLicence())
{
Clubs club = new Clubs();
club.setNom(comboBox2.Text);
Adherents adherent = new Adherents();
adherent.setId(Int32.Parse(textID.Text));
adherent.setClub(club);
adherent.setSexe(comboBox1.Text);
adherent.setNom(textNom.Text);
adherent.setPrenom(textPrenom.Text);
adherent.setLicence(textLicence.Text);
adherent.setNaissance(dateTimePicker1.Value);
adherent.setAdresse(textAdresse.Text);
adherent.setCPT(Int32.Parse(textCodePostal.Text));
adherent.setVille(textVille.Text);
adherent.setCotisation(Int32.Parse(textCotisation.Text));
try
{
ModeleBDD bd = new ModeleBDD();
bd.UPDATEAdherent(adherent);
comboBox1.Text = "";
comboBox2.Text = "";
textID.Text = "";
textNom.Text = "";
textPrenom.Text = "";
textLicence.Text = "";
//dateTimePicker1.Value = DateTime.Now;
textAdresse.Text = "";
textCodePostal.Text = "";
textVille.Text = "";
textCotisation.Text = "";
label10.Text = adherent.getNom() + " a été modifié ";
dataGridView1.ClearSelection();
FillDataGridView();
}catch (Exception e2)
{
MessageBox.Show("Message d'erreur : " + e2.Message + " \nType de l'exception " + e2.GetType() + " \nPile d'appel" + e2.StackTrace);
}
}
else
{
MessageBox.Show("La licence doit être unique");
}
}
else
{
MessageBox.Show("Veuillez remplir tout les champs");
}
}
/// <summary>
/// Ajoute un évènements
/// </summary>
/// <param name="sender"></param>
/// <param name="e">l'évènement à ajouter</param>
private void buttonInsertion_Click(object sender, EventArgs e)
{
if (condition())
{
if (verifiLicence())
{
Clubs club = new Clubs();
club.setNom(comboBox2.Text);
Adherents adherent = new Adherents();
adherent.setClub(club);
adherent.setSexe(comboBox1.Text);
adherent.setNom(textNom.Text);
adherent.setPrenom(textPrenom.Text);
adherent.setLicence(textLicence.Text);
adherent.setNaissance(dateTimePicker1.Value);
adherent.setAdresse(textAdresse.Text);
adherent.setCPT(Int32.Parse(textCodePostal.Text));
adherent.setVille(textVille.Text);
adherent.setCotisation(Int32.Parse(textCotisation.Text));
ModeleBDD bd = new ModeleBDD();
bd.setAdherent(adherent);
comboBox1.Text = "";
comboBox2.Text = "";
textNom.Text = "";
textPrenom.Text = "";
textLicence.Text = "";
//dateTimePicker1.Value = DateTime.Now;
textAdresse.Text = "";
textCodePostal.Text = "";
textVille.Text = "";
textCotisation.Text = "";
label10.Text = adherent.getNom() + " a été ajouter avec succès ";
dataGridView1.ClearSelection();
FillDataGridView();
}
else
{
MessageBox.Show("La Licence doit être unique");
}
}
else
{
MessageBox.Show("Veuillez rmplir tout les champs");
}
}
//les sont modifié //adherent
/// <summary>
/// Methode qui selection tous adhernts sur la base de données
/// </summary>
/// <returns>Return un Listes des adherents</returns>
public List <Adherents> getAdherents()
{
List <Adherents> lesAdherents = new List <Adherents>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "SELECT a.id AS IdAdherent, a.Licence, a.Sexe, a.Nom, a.Prenom, a.Naissance, a.Adresse, a.CodePostal, a.Ville, a.Cotisation, c.id AS IdClub, c.Nom AS NomClub FROM adherents AS a INNER JOIN clubs AS c ON a.id_clubs=c.id";
//Crée Commande
MySqlCommand command = new MySqlCommand(query, connection);
//On crée un datareader et on execute la commande
using (MySqlDataReader dataReader = command.ExecuteReader())
{
//On li la base de données et on ajoute dans la liste les ahderents de la base de données
while (dataReader.Read())
{
Clubs club = new Clubs();
club.setId((int)dataReader["IdClub"]);
club.setNom((string)dataReader["NomClub"]);
Adherents lAdherent = new Adherents();
lAdherent.setId((int)dataReader["IdAdherent"]);
lAdherent.setClub(club);
lAdherent.setLicence((string)dataReader["Licence"]);
lAdherent.setSexe((string)dataReader["Sexe"]);
lAdherent.setNom((string)dataReader["Nom"]);
lAdherent.setPrenom((string)dataReader["Prenom"]);
lAdherent.setNaissance((DateTime)dataReader["Naissance"]);
lAdherent.setAdresse((string)dataReader["Adresse"]);
lAdherent.setCPT((int)dataReader["CodePostal"]);
lAdherent.setVille((string)dataReader["Ville"]);
lAdherent.setCotisation((int)dataReader["Cotisation"]);
lesAdherents.Add(lAdherent);
}
//string MySQLFormatDate = dateValue.ToString("yyyy-MM-dd HH:mm:ss")
}
connection.Close();
return(lesAdherents);
}
}
/// <summary>
/// methode setAdherents ajouter un adherents dans un base de donnée
/// </summary>
/// <param name="me">est un objet de la classe Adherents</param>
public void setAdherent(Adherents me)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO adherents(id_clubs, Licence, Sexe, Nom, Prenom, Naissance, Adresse, CodePostal, Ville, Cotisation) VALUES ((SELECT id FROM clubs WHERE Nom = @Club), @Licence, @Sexe, @Nom, @Prenom, @Naissance, @Adresse, @CodePostal, @Ville, @Cotisation)";
command.Parameters.AddWithValue("@Club", me.getClub().getNom());
command.Parameters.AddWithValue("@Licence", me.getLicence());
command.Parameters.AddWithValue("@Sexe", me.getSexe());
command.Parameters.AddWithValue("@Nom", me.getNom());
command.Parameters.AddWithValue("@Prenom", me.getPrenom());
command.Parameters.AddWithValue("@Naissance", me.getNaissance());
command.Parameters.AddWithValue("@Adresse", me.getAdresse());
command.Parameters.AddWithValue("@CodePostal", me.getCPT());
command.Parameters.AddWithValue("@Ville", me.getVille());
command.Parameters.AddWithValue("@Cotisation", me.getCotisation());
command.ExecuteNonQuery();
connection.Close();
}
}
/// <summary>
/// Methode qui permet de modifié un adhérent dans la base de donnée
/// </summary>
/// <param name="lAdherent">lAdherent a modifié</param>
public void UPDATEAdherent(Adherents lAdherent)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE adherents SET id_clubs=(SELECT id FROM clubs WHERE Nom=@NomClub), Licence=@Licence, Sexe=@Sexe, Nom=@Nom, Prenom=@Prenom, Naissance=@Naissance, Adresse=@Adresse, CodePostal=@CodePostal, Ville=@Ville, Cotisation=@Cotisation WHERE id=@id";
command.Parameters.AddWithValue("@Id", lAdherent.getId());
command.Parameters.AddWithValue("@NomClub", lAdherent.getClub().getNom());
command.Parameters.AddWithValue("@Licence", lAdherent.getLicence());
command.Parameters.AddWithValue("@Nom", lAdherent.getNom());
command.Parameters.AddWithValue("@Prenom", lAdherent.getPrenom());
command.Parameters.AddWithValue("@Sexe", lAdherent.getSexe());
command.Parameters.AddWithValue("@Naissance", lAdherent.getNaissance());
command.Parameters.AddWithValue("@CodePostal", lAdherent.getCPT());
command.Parameters.AddWithValue("@Adresse", lAdherent.getAdresse());
command.Parameters.AddWithValue("@Ville", lAdherent.getVille());
command.Parameters.AddWithValue("@Cotisation", lAdherent.getCotisation());
command.ExecuteNonQuery();
connection.Close();
}
}
