private void buttonCreerAdherent_Click(object sender, EventArgs e)
{
int CP;
int cot;
DateTime thisDay = DateTime.Today;
if (dateTimePickerNewNaissanceAdherent.Value != thisDay && textBoxNewNomAdherent.Text != "Nom de l'adhérent" && textBoxNewPrenomAdherent.Text != "Prénom de l'adhérent" && textBoxNewSexAdherent.Text != "Sexe" && textBoxNewLicenceAdherent.Text != "N° de licence" && textBoxNewAdressAdherent.Text != "Adresse de l'adhérent" && textBoxNewCPAdherent.Text != "Code Postal" && textBoxNewVilleAdherent.Text != "Ville" && textBoxNewCotisationAdherent.Text != "Montant de la cotisation" && int.TryParse(textBoxNewCPAdherent.Text, out CP) && int.TryParse(textBoxNewCotisationAdherent.Text, out cot))
{
BDD newAdherent = new BDD();
Adherent nouvelAdherent = new Adherent(0, textBoxNewNomAdherent.Text, textBoxNewPrenomAdherent.Text, dateTimePickerNewNaissanceAdherent.Value, textBoxNewSexAdherent.Text, textBoxNewLicenceAdherent.Text, textBoxNewAdressAdherent.Text, CP, textBoxNewVilleAdherent.Text, cot, idClub);
newAdherent.InsertAdherent(nouvelAdherent);
Close();
}
else
{
erreur = new ErrAjout();
erreur.ShowDialog();
}
}
public List <Adherent> SelectAllEventAdherent(int idEvent, int idClub)
{
Adherent LeAdherent = null;
List <Adherent> ListAdherent = new List <Adherent>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM adherent Where id_club = @club AND id IN (SELECT id_adherent FROM participants WHERE id_evenement = @event) ORDER BY id ASC";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@club", idClub);
cmd.Parameters.AddWithValue("@event", idEvent);
//Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
//Read the data and store them in the list
while (dataReader.Read())
{
LeAdherent = new Adherent(
(int)dataReader["id"],
(string)dataReader["nomAdh"],
(string)dataReader["prenomAdh"],
(DateTime)dataReader["naissance"],
(string)dataReader["sexe"],
(string)dataReader["numLicence"],
(string)dataReader["adresseAdh"],
(int)dataReader["CPAdh"],
(string)dataReader["villeAdh"],
(int)dataReader["cotisation"],
(int)dataReader["id_club"]);
ListAdherent.Add(LeAdherent);
}
}
}
return(ListAdherent);
}
public Adherent ReadAdherent(int id)
{
Adherent ThisAdherent = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM Adherent where id=@id";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@id", id);
//Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
//Read the data and store them in the list
while (dataReader.Read())
{
ThisAdherent = new Adherent(
(int)dataReader["id"],
(string)dataReader["nomAdh"],
(string)dataReader["prenomAdh"],
(DateTime)dataReader["naissance"],
(string)dataReader["sexe"],
(string)dataReader["numLicence"],
(string)dataReader["adresseAdh"],
(int)dataReader["CPAdh"],
(string)dataReader["villeAdh"],
(int)dataReader["cotisation"],
(int)dataReader["id_club"]);
}
}
}
return(ThisAdherent);
}
public List <Adherent> SearchAdherent(string recherche, int idClub)
{
Adherent lesAdherents = null;
List <Adherent> ListAdherent = new List <Adherent>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM Adherent WHERE nomAdh LIKE @recherche AND id_club = @idClub ORDER BY id ASC";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@recherche", "%" + recherche + "%");
cmd.Parameters.AddWithValue("@idClub", idClub);
//Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
//Read the data and store them in the list
while (dataReader.Read())
{
lesAdherents = new Adherent(
(int)dataReader["id"],
(string)dataReader["nomAdh"],
(string)dataReader["prenomAdh"],
(DateTime)dataReader["naissance"],
(string)dataReader["sexe"],
(string)dataReader["numLicence"],
(string)dataReader["adresseAdh"],
(int)dataReader["CPAdh"],
(string)dataReader["villeAdh"],
(int)dataReader["cotisation"],
(int)dataReader["id_club"]);
ListAdherent.Add(lesAdherents);
}
}
}
return(ListAdherent);
}
public void UpdateAdherent(Adherent ModifAdherent)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "UPDATE adherent SET nomAdh = @nom, prenomAdh = @prenom, naissance = @naissance, sexe = @sexe, numLicence = @licence, adresseAdh = @adresse, CPAdh = @cp, villeAdh = @ville, cotisation = @cotisation WHERE id = @id";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@id", ModifAdherent.id);
cmd.Parameters.AddWithValue("@nom", ModifAdherent.nomAdh);
cmd.Parameters.AddWithValue("@prenom", ModifAdherent.prenomAdh);
cmd.Parameters.AddWithValue("@naissance", ModifAdherent.naissance);
cmd.Parameters.AddWithValue("@sexe", ModifAdherent.sexe);
cmd.Parameters.AddWithValue("@licence", ModifAdherent.numLicence);
cmd.Parameters.AddWithValue("@adresse", ModifAdherent.adresseAdh);
cmd.Parameters.AddWithValue("@cp", ModifAdherent.CPAdh);
cmd.Parameters.AddWithValue("@ville", ModifAdherent.villeAdh);
cmd.Parameters.AddWithValue("@cotisation", ModifAdherent.cotisation);
cmd.ExecuteReader();
//cmd.ExecuteNonQuery();
}
}
public void InsertAdherent(Adherent nouvelAdherent)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string query = "INSERT INTO adherent (nomAdh, prenomAdh, naissance, sexe, numLicence, adresseAdh, CPAdh, villeAdh, cotisation, id_club) VALUES (@nom, @prenom, @naissance, @sexe, @licence, @adresse, @CP, @ville, @cotisation, @idClub)";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@nom", nouvelAdherent.nomAdh);
cmd.Parameters.AddWithValue("@prenom", nouvelAdherent.prenomAdh);
cmd.Parameters.AddWithValue("@naissance", nouvelAdherent.naissance);
cmd.Parameters.AddWithValue("@sexe", nouvelAdherent.sexe);
cmd.Parameters.AddWithValue("@licence", nouvelAdherent.numLicence);
cmd.Parameters.AddWithValue("@adresse", nouvelAdherent.adresseAdh);
cmd.Parameters.AddWithValue("@CP", nouvelAdherent.CPAdh);
cmd.Parameters.AddWithValue("@ville", nouvelAdherent.villeAdh);
cmd.Parameters.AddWithValue("@cotisation", nouvelAdherent.cotisation);
cmd.Parameters.AddWithValue("@idClub", nouvelAdherent.id_club);
cmd.ExecuteReader();
}
}