// Cette méthode filtre les utilisateur
public static List <Adherent> FiltreUtilisateur(string recherche)
{
int id_adherent;
string nom_adherent;
string prenom_adherent;
DateTime ddn_adherent;
long numtel_adherent;
string email_adherent;
long numparent_adherent;
string autoprelev_adherent;
string sexe_adherent;
DateTime datemaj_adherent;
string archive_adherent;
string classe;
int id_classe;
Adherent unFiltre;
// Connexion à la BD
List <Adherent> lesFiltres = new List <Adherent>();
SqlConnection maConnexion =
ConnexionBD.GetConnexionBD().GetSqlConnexion();
SqlCommand cmd = new SqlCommand();
cmd.Connection = maConnexion;
//SELECT id_adherent, nom_adherent, prenom_adherent, ddn_adherent, numtel_adherent, email_adherent, numparent_adherent, autoprelev_adherent, sexe_adherent, datemaj_adherent, archive_adherent, libelle_classe FROM dbo.adherent as adh, dbo.classe as c WHERE adh.id_classe = c.id_classe
cmd.CommandText = "SELECT id_adherent, nom_adherent, prenom_adherent, ddn_adherent, numtel_adherent, email_adherent, numparent_adherent, autoprelev_adherent, sexe_adherent, datemaj_adherent, archive_adherent, libelle_classe, adh.id_classe FROM gestionAS.dbo.adherent as adh, dbo.classe as c where adh.id_classe = c.id_classe AND nom_adherent LIKE '" + @recherche + "%' OR prenom_adherent LIKE '" + @recherche + "%'";
cmd.Parameters.Add(new SqlParameter("@recherche", SqlDbType.VarChar, 25));
cmd.Parameters["@recherche"].Value = recherche;
SqlDataReader monReader = cmd.ExecuteReader();
// Remplissage de la liste
while (monReader.Read())
{
id_adherent = Int32.Parse(monReader["id_adherent"].ToString());
ddn_adherent = DateTime.Parse(monReader["ddn_adherent"].ToString());
numtel_adherent = long.Parse(monReader["numtel_adherent"].ToString());
numparent_adherent = long.Parse(monReader["numparent_adherent"].ToString());
datemaj_adherent = DateTime.Parse(monReader["datemaj_adherent"].ToString());
id_classe = Int32.Parse(monReader["id_classe"].ToString());
// Nom Adherent
if (monReader["nom_adherent"] == DBNull.Value)
{
nom_adherent = default(string);
}
else
{
nom_adherent = monReader["nom_adherent"].ToString();
}
// Prénom Adherent
if (monReader["prenom_adherent"] == DBNull.Value)
{
prenom_adherent = default(string);
}
else
{
prenom_adherent = monReader["prenom_adherent"].ToString();
}
// Mail Adherent
if (monReader["email_adherent"] == DBNull.Value)
{
email_adherent = default(string);
}
else
{
email_adherent = monReader["email_adherent"].ToString();
}
// Sexe Adherent
if (monReader["sexe_adherent"] == DBNull.Value)
{
sexe_adherent = default(string);
}
else
{
sexe_adherent = monReader["sexe_adherent"].ToString();
}
// Archive Adherent
if (monReader["archive_adherent"] == DBNull.Value)
{
archive_adherent = default(string);
}
else
{
archive_adherent = monReader["archive_adherent"].ToString();
}
// Autoprelev
if (monReader["autoprelev_adherent"] == DBNull.Value)
{
autoprelev_adherent = default(string);
}
else
{
autoprelev_adherent = monReader["autoprelev_adherent"].ToString();
}
// classe
if (monReader["libelle_classe"] == DBNull.Value)
{
classe = default(string);
}
else
{
classe = monReader["libelle_classe"].ToString();
}
unFiltre = new Adherent(id_adherent, nom_adherent, prenom_adherent, ddn_adherent, numtel_adherent, email_adherent, numparent_adherent, autoprelev_adherent, sexe_adherent, datemaj_adherent, archive_adherent, id_classe, classe);
lesFiltres.Add(unFiltre);
}
// Fermeture de la connexion
maConnexion.Close();
return(lesFiltres);
}
// Cette méthode retourne une List contenant les objets Adherentscontenus dans la table Identification
public static List <Adherent> GetAdherents()
{
int id_adherent;
string nom_adherent;
string prenom_adherent;
DateTime ddn_adherent;
long numtel_adherent;
string email_adherent;
long numparent_adherent;
string autoprelev_adherent;
string sexe_adherent;
DateTime datemaj_adherent;
string archive_adherent;
int id_classe;
Adherent unAdherent;
// Connexion à la BD
SqlConnection maConnexion = ConnexionBD.GetConnexionBD().GetSqlConnexion();
// Création d'une liste vide d'objets Adherents
List <Adherent> lesAdherents = new List <Adherent>();
SqlCommand cmd = new SqlCommand();
cmd.Connection = maConnexion;
cmd.CommandText = "SELECT * FROM gestionAS.dbo.adherent";
SqlDataReader monReader = cmd.ExecuteReader();
// Remplissage de la liste
while (monReader.Read())
{
id_adherent = Int32.Parse(monReader["id_adherent"].ToString());
ddn_adherent = DateTime.Parse(monReader["ddn_adherent"].ToString());
numtel_adherent = long.Parse(monReader["numtel_adherent"].ToString());
numparent_adherent = long.Parse(monReader["numparent_adherent"].ToString());
datemaj_adherent = DateTime.Parse(monReader["datemaj_adherent"].ToString());
id_classe = Int32.Parse(monReader["id_classe"].ToString());
// Nom Adherent
if (monReader["nom_adherent"] == DBNull.Value)
{
nom_adherent = default(string);
}
else
{
nom_adherent = monReader["nom_adherent"].ToString();
}
// Prénom Adherent
if (monReader["prenom_adherent"] == DBNull.Value)
{
prenom_adherent = default(string);
}
else
{
prenom_adherent = monReader["prenom_adherent"].ToString();
}
// Mail Adherent
if (monReader["email_adherent"] == DBNull.Value)
{
email_adherent = default(string);
}
else
{
email_adherent = monReader["email_adherent"].ToString();
}
// Sexe Adherent
if (monReader["sexe_adherent"] == DBNull.Value)
{
sexe_adherent = default(string);
}
else
{
sexe_adherent = monReader["sexe_adherent"].ToString();
}
// Archive Adherent
if (monReader["archive_adherent"] == DBNull.Value)
{
archive_adherent = default(string);
}
else
{
archive_adherent = monReader["archive_adherent"].ToString();
}
// Autoprelev
if (monReader["autoprelev_adherent"] == DBNull.Value)
{
autoprelev_adherent = default(string);
}
else
{
autoprelev_adherent = monReader["autoprelev_adherent"].ToString();
}
unAdherent = new Adherent(id_adherent, nom_adherent, prenom_adherent, ddn_adherent, numtel_adherent, email_adherent, numparent_adherent, autoprelev_adherent, sexe_adherent, datemaj_adherent, archive_adherent, id_classe);
lesAdherents.Add(unAdherent);
}
// Fermeture de la connexion
maConnexion.Close();
return(lesAdherents);
}
// Cette méthode retourne une List contenant les objets Adherents contenus dans la table Identification
public static List <Flux> GetAfficheFlux()
{
int id_flux;
string libelle_flux;
DateTime date_flux;
float montant_flux;
string etat_prelevement;
int id_adherent;
int id_typeFlux;
int id_budget;
string adherent;
string budget;
string typeFlux;
Flux unAfficheFlux;
// Connexion à la BD
SqlConnection maConnexion = ConnexionBD.GetConnexionBD().GetSqlConnexion();
// Création d'une liste vide d'objets Adherents
List <Flux> lesFlux = new List <Flux>();
SqlCommand cmd = new SqlCommand();
cmd.Connection = maConnexion;
cmd.CommandText = "SELECT flux.id_flux,flux.libelle_flux,flux.date_flux, flux.montant_flux, flux.etat_prelevement, flux.id_adherent, flux.id_budget, flux.id_typeflux , adh.nom_adherent, typeFlux.libelle_typeflux, budget.libelle_budget FROM dbo.flux as flux, dbo.adherent as adh, dbo.type_flux as typeFlux, dbo.budget as budget WHERE flux.id_typeflux = typeFlux.id_typeflux AND flux.id_adherent = adh.id_adherent AND flux.id_budget = budget.id_budget";
SqlDataReader monReader = cmd.ExecuteReader();
// Remplissage de la liste
while (monReader.Read())
{
id_flux = Int32.Parse(monReader["id_flux"].ToString());
date_flux = DateTime.Parse(monReader["date_flux"].ToString());
montant_flux = float.Parse(monReader["montant_flux"].ToString());
id_adherent = Int32.Parse(monReader["id_adherent"].ToString());
id_budget = Int32.Parse(monReader["id_budget"].ToString());
id_typeFlux = Int32.Parse(monReader["id_typeflux"].ToString());
// Nom Flux
if (monReader["libelle_flux"] == DBNull.Value)
{
libelle_flux = default(string);
}
else
{
libelle_flux = monReader["libelle_flux"].ToString();
}
//Etat prélevement
if (monReader["etat_prelevement"] == DBNull.Value)
{
etat_prelevement = default(string);
}
else
{
etat_prelevement = monReader["etat_prelevement"].ToString();
}
//Nom Adherent
if (monReader["nom_adherent"] == DBNull.Value)
{
adherent = default(string);
}
else
{
adherent = monReader["nom_adherent"].ToString();
}
//Libelle budget
if (monReader["libelle_budget"] == DBNull.Value)
{
budget = default(string);
}
else
{
budget = monReader["libelle_budget"].ToString();
}
//Libelle type flux
if (monReader["libelle_typeflux"] == DBNull.Value)
{
typeFlux = default(string);
}
else
{
typeFlux = monReader["libelle_typeflux"].ToString();
}
unAfficheFlux = new Flux(id_flux, libelle_flux, date_flux, montant_flux, id_adherent, id_typeFlux, id_budget, etat_prelevement, adherent, budget, typeFlux);
lesFlux.Add(unAfficheFlux);
}
// Fermeture de la connexion
maConnexion.Close();
return(lesFlux);
}