public static void setClient(Client c)
{
string sql = "INSERT INTO client(NomC,PrenomC , EmailC, AddresseC, TeleC, Password) " +
"VALUES ('"+c.Nom+"','"+c.Prenom+"','"+c.Email+"','"+c.Addresse+"','"+c.Tele+"','"+c.password+"');";
ConnexionBD.setResultat(sql);
}
public static List<getCommande> getCommandes()
{
List<getCommande> commandes = new List<getCommande>();
String query = "SELECT cl.NomC, cl.PrenomC, cl.EmailC, cl.AddresseC, cl.TeleC, b.NomBk, b.IdBk, cm.Date FROM client cl, commande cm, books b "
+ "WHERE cm.IdC=cl.IdC and cm.IdBk=b.IdBk";
MySqlDataReader dc = ConnexionBD.getResultat(query);
while (dc.Read())
{
getCommande c = new getCommande();
c.Nom = dc.GetString("NomC");
c.Prenom = dc.GetString("PrenomC");
c.Email = dc.GetString("EmailC");
c.Addresse = dc.GetString("AddresseC");
c.Tele = dc.GetString("TeleC");
c.NomBk = dc.GetString("NomBk");
c.IdBk = dc.GetInt16("IdBk");
c.date = dc.GetString("Date");
commandes.Add(c);
}
return commandes;
}
public static List<Categorie> getCategorie()
{
List<Categorie> listCat = new List<Categorie>();
string sql = "SELECT * FROM biblio;";
MySqlDataReader dataCat = ConnexionBD.getResultat(sql);
while (dataCat.Read())
{
Categorie cat = new Categorie();
cat.IdB = dataCat.GetInt16(0);
cat.NomB = dataCat.GetString(1);
listCat.Add(cat);
}
return listCat;
}
public static void setResultat(string sql)
{
try
{
MySqlConnection cnx = ConnexionBD.getConnexion();
MySqlCommand cmd = new MySqlCommand(sql, cnx);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
}
catch (Exception e)
{
Console.WriteLine("Erreur " + e.Message);
}
}
public static Livre getLivreById(int id)
{
Livre livre = new Livre();
string sql = "SELECT * FROM books WHERE IdBk="+id+";";
MySqlDataReader l= ConnexionBD.getResultat(sql);
if (l.Read())
{
livre.IdBk = l.GetInt16(0);
livre.Nom = l.GetString(1);
livre.Auteur = l.GetString(2);
livre.date_edition = l.GetString(3);
livre.Prix = l.GetDouble(4);
livre.pthimg = l.GetString(6);
livre.IdB = l.GetInt16(7);
}
return livre;
}
public static List<Livre> getLivre()
{
List<Livre> listLivres = new List<Livre>();
string sql = "SELECT * FROM books;";
MySqlDataReader dataLivres = ConnexionBD.getResultat(sql);
while (dataLivres.Read())
{
Livre livre = new Livre();
livre.IdBk = dataLivres.GetInt16(0);
livre.Nom = dataLivres.GetString(1);
livre.pthimg = dataLivres.GetString(6);
listLivres.Add(livre);
}
return listLivres;
}
public static List<Livre> getLivreByIdB(string cat)
{
List<Livre> listLivres = new List<Livre>();
string sql = "SELECT * FROM books b, biblio o WHERE b.IdB=o.Idb and o.NomB='"+cat+"'";
MySqlDataReader dataLivres = ConnexionBD.getResultat(sql);
Livre livre = new Livre();
while (dataLivres.Read())
{
livre.IdBk = dataLivres.GetInt16(0);
livre.Nom = dataLivres.GetString(1);
livre.pthimg = dataLivres.GetString(6);
listLivres.Add(livre);
}
return listLivres;
}
public static MySqlDataReader getResultat(string sql)
{
try
{
MySqlConnection cnx = ConnexionBD.getConnexion();
MySqlCommand cmd = new MySqlCommand(sql, cnx);
cnx.Open();
MySqlDataReader resultat = cmd.ExecuteReader();
return resultat;
}
catch (Exception e)
{
Console.WriteLine("Erreur " + e.Message);
return null;
}
}
public static List<Client> getClients()
{
List<Client> clients = new List<Client>();
string sql = "SELECT * FROM client";
MySqlDataReader dataClients = ConnexionBD.getResultat(sql);
while (dataClients.Read())
{
Client cl = new Client();
cl.IdC = dataClients.GetInt16(0);
cl.Nom = dataClients.GetString(1);
cl.Prenom = dataClients.GetString(2);
cl.Email = dataClients.GetString(3);
cl.Addresse = dataClients.GetString(4);
cl.Tele = dataClients.GetString(5);
clients.Add(cl);
}
return clients;
}
public static Boolean estAdmin(string email, string pass)
{
string sql = "SELECT * FROM client where IdC=1";
MySqlDataReader admin = ConnexionBD.getResultat(sql);
if(admin.Read())
{
string ad_email = admin.GetString(3);
string ad_pass = admin.GetString(6);
if (email.Equals(ad_email) && pass.Equals(ad_pass) ) {
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
public static Client login(string email, string pass)
{
string sql = "SELECT * FROM client WHERE EmailC ='" + email + "' AND Password='******'";
Client user = new Client();
MySqlDataReader dataUser = ConnexionBD.getResultat(sql);
if (dataUser.Read())
{
user.IdC = dataUser.GetInt32(0);
user.Nom = dataUser.GetString(1);
user.Prenom = dataUser.GetString(2);
user.Email = dataUser.GetString(3);
user.Addresse = dataUser.GetString(4);
user.Tele = dataUser.GetString(5);
user.password = dataUser.GetString(6);
return user;
}
else
{
return null;
}
}
public static void setCommande(Commande c)
{
string sql = "INSERT INTO commande(IdC, IdBk, quantite, Date) " +
"VALUES ('"+c.IdC+"','"+c.IdBk+"','"+c.quantite+"','"+c.Date+"')";
ConnexionBD.setResultat(sql);
}
public static void setCategorie(Categorie c)
{
string sql= "INSERT INTO biblio(IdB, NomB) VALUES ("+c.IdB+",'"+c.NomB+"')";
ConnexionBD.setResultat(sql);
}
public static void setLivre(Livre l)
{
string sql = "INSERT INTO books(NomBk, AuteurBk, Date_edition, PrixBk, pthimage, IdB)" +
"VALUES ('"+l.Nom+"','"+l.Auteur+"','"+l.date_edition+"','"+ l.Prix+"','"+l.pthimg+"','"+l.IdB+"')";
ConnexionBD.setResultat(sql);
}