public static List <Livre> getListLivres()
{
List <Livre> lLivre = new List <Livre>();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Produits";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Livre liv = new Livre();
liv.isBN = reader["ISBN"].ToString();
liv.Titre = reader["Titre"].ToString();
liv.QuantiteDispo = Convert.ToInt32(reader["LastName"]);
liv.PUnitaire = Convert.ToDecimal(reader["PrixU"]);
liv.AnneePublication = Convert.ToDateTime(reader["AnneePub"]);
liv.CategorieL = reader["Categorie"].ToString();
lLivre.Add(liv);
}
}
return(lLivre);
}
public static List <Employes> rechercheParNomPrenom(string nom, string prenom)
{
List <Employes> lEmployes = new List <Employes>();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Employes WHERE EmployeNom = '" + nom + "' AND EmployePrenom = '" + prenom + "';";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Employes emp = new Employes();
emp.NoIdentifiant = Convert.ToInt32(reader["EmployeId"]);
emp.MPasse = reader["EmployeMDP"].ToString();
emp.NomUtil = reader["EmployeNom"].ToString();
emp.PrenomAdmin = reader["EmployePrenom"].ToString();
emp.Categorie = reader["EmployeFonction"].ToString();
lEmployes.Add(emp);
}
}
return(lEmployes);
}
public static Employes rechercheParId(int id)
{
Employes emp = new Employes();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Employes WHERE EmployeId=" + id + ";";
SqlCommand cmd = new SqlCommand(query, conn);
try
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
emp.NoIdentifiant = Convert.ToInt32(reader["EmployeId"]);
emp.MPasse = reader["EmployeMDP"].ToString();
emp.NomUtil = reader["EmployeNom"].ToString();
emp.PrenomAdmin = reader["EmployePrenom"].ToString();
emp.Categorie = reader["EmployeFonction"].ToString();
}
return(emp);
}
catch (Exception ex)
{
return(null);
}
}
}
public static Employes UtilTrouve(int empId, string password)
{
SqlConnection con = UtilityBD.connectBD();
SqlCommand sqlCmd = new SqlCommand();
Employes emp = new Employes();
emp.NoIdentifiant = empId;
emp.MPasse = password;
sqlCmd.Parameters.Add("@EmployeId", emp.NoIdentifiant);
sqlCmd.Parameters.Add("@EmployeMDP", emp.MPasse);
sqlCmd.Connection = con;
sqlCmd.CommandText = "SELECT * FROM Employes WHERE EmployeId = @EmployeId AND EmployeMDP = @EmployeMDP ";
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
while (sqlReader.Read())
{
emp.NoIdentifiant = Convert.ToInt32(sqlReader["EmployeId"]);
emp.MPasse = Convert.ToString(sqlReader["EmployeMDP"]);
emp.PrenomAdmin = Convert.ToString(sqlReader["EmployePrenom"]);
emp.NomUtil = Convert.ToString(sqlReader["EmployeNom"]);
emp.Categorie = Convert.ToString(sqlReader["EmployeFonction"]);
return(emp);
}
return(null);
}
public static List <Auteur> getListAuteur()
{
List <Auteur> lAuteur = new List <Auteur>();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Auteurs";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Auteur aut = new Auteur();
aut.AuteurID = Convert.ToInt32(reader["AuteurId"]);
aut.AuteurIPrenom = reader["AuteurPrenom"].ToString();
aut.AuteurNom = reader["AuteurNom"].ToString();
aut.AuteurCourriel = reader["AuteurCourriel"].ToString();
lAuteur.Add(aut);
}
}
return(lAuteur);
}
public static List <Client> getListClient()
{
List <Client> lClient = new List <Client>();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Clients";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Client cl = new Client();
cl.ClientNo = reader["ClientId"].ToString();
cl.ClientNom = reader["ClientNom"].ToString();
cl.ClientRue = reader["ClientRue"].ToString();
cl.ClientVille = reader["ClientVille"].ToString();
cl.ClientCP = reader["ClientCP"].ToString();
cl.ClientNTel = reader["ClientTelephone"].ToString();
cl.ClientTelecopieur = reader["ClientTelecopieur"].ToString();
cl.ClientCeditLim = Convert.ToDecimal(reader["ClientLimiteCredit"]);
lClient.Add(cl);
}
}
return(lClient);
}
public static bool DeleteData(Employes emp)
{
using (SqlConnection con = UtilityBD.connectBD())
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.Parameters.Add("@EmployeId", emp.NoIdentifiant);
sqlCmd.CommandText = string.Format("DELETE FROM Employes WHERE EmployeId = @EmployeId ;");
return(Convert.ToBoolean(sqlCmd.ExecuteNonQuery()));
}
}
public static bool modifyData(Employes emp)
{
using (SqlConnection con = UtilityBD.connectBD())
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.Parameters.Add("@EmployeId", emp.NoIdentifiant);
sqlCmd.Parameters.Add("@EmployePrenom", emp.PrenomAdmin);
sqlCmd.Parameters.Add("@EmployeNom", emp.NomUtil);
sqlCmd.Parameters.Add("@EmployeFonction", emp.Categorie);
sqlCmd.Parameters.Add("@EmployeMDP", emp.MPasse);
sqlCmd.CommandText = string.Format("UPDATE Employes SET EmployePrenom = @EmployePrenom, EmployeNom = @EmployeNom, EmployeFonction = @EmployeFonction, EmployeMDP = @EmployeMDP WHERE EmployeId = @EmployeId ;");
return(Convert.ToBoolean(sqlCmd.ExecuteNonQuery()));
}
}
public static bool InsertData(Employes emp)
{
using (SqlConnection con = UtilityBD.connectBD())
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.Parameters.Add("@EmployeeId", emp.NoIdentifiant);
sqlCmd.Parameters.Add("@EmployeMDP", emp.MPasse);
sqlCmd.Parameters.Add("@EmployePrenom", emp.PrenomAdmin);
sqlCmd.Parameters.Add("@EmployeNom", emp.NomUtil);
sqlCmd.Parameters.Add("@EmployeFonction", emp.Categorie);
sqlCmd.CommandText = "INSERT INTO Employes VALUES (@EmployeeId,@EmployeMDP,@EmployePrenom,@EmployeNom,@EmployeFonction)";
return(Convert.ToBoolean(sqlCmd.ExecuteNonQuery()));
}
}
public static List <Commande> getListCommande()
{
List <Commande> lCommande = new List <Commande>();
using (SqlConnection conn = UtilityBD.connectBD())
{
string query = "SELECT * FROM Commande";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
/*
*/
}
}
return(lCommande);
}
public static bool ModifyData(Client cli)
{
using (SqlConnection con = UtilityBD.connectBD())
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.Parameters.Add("@ClientId", cli.ClientNo);
sqlCmd.Parameters.Add("@ClientNom", cli.ClientNom);
sqlCmd.Parameters.Add("@ClientRue", cli.ClientRue);
sqlCmd.Parameters.Add("@ClientVille", cli.ClientVille);
sqlCmd.Parameters.Add("@ClientCP", cli.ClientCP);
sqlCmd.Parameters.Add("@ClientTelephone", cli.ClientNTel);
sqlCmd.Parameters.Add("@ClientTelecopieur", cli.ClientTelecopieur);
sqlCmd.Parameters.Add("@ClientLimiteCredit", cli.ClientCeditLim);
sqlCmd.CommandText = string.Format("UPDATE Clients SET ClientNom = @ClientNom, ClientRue = @ClientRue, ClientVille = @ClientVille, ClientCP = @ClientCP, ClientTelephone = @ClientTelephone, ClientTelecopieur = @ClientTelecopieur, ClientLimiteCredit = @ClientLimiteCredit WHERE ClientId = @ClientId ;");
return(Convert.ToBoolean(sqlCmd.ExecuteNonQuery()));
}
}
public static bool SaveData(Client cli)
{
using (SqlConnection con = UtilityBD.connectBD())
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.Parameters.Add("@ClientId", cli.ClientNo);
sqlCmd.Parameters.Add("@ClientNom", cli.ClientNom);
sqlCmd.Parameters.Add("@ClientRue", cli.ClientRue);
sqlCmd.Parameters.Add("@ClientVille", cli.ClientVille);
sqlCmd.Parameters.Add("@ClientCP", cli.ClientCP);
sqlCmd.Parameters.Add("@ClientTelephone", cli.ClientNTel);
sqlCmd.Parameters.Add("@ClientTelecopieur", cli.ClientTelecopieur);
sqlCmd.Parameters.Add("@ClientLimiteCredit", cli.ClientCeditLim);
sqlCmd.CommandText = string.Format("INSERT INTO Clients VALUE (@ClientId, @ClientNom, @ClientRue, @ClientVille, @ClientCP, @ClientTelephone, @ClientTelecopieur, @ClientLimiteCredit ) ;");
return(Convert.ToBoolean(sqlCmd.ExecuteNonQuery()));
}
}