public int updateInDB()
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
Console.WriteLine("modification Marque");
string squery = "UPDATE Marques SET Nom = @Nom WHERE RefMarque = @RefMarque";
SQLiteCommand commande = new SQLiteCommand(squery, connection);
commande.Parameters.AddWithValue("@Nom", nom);
commande.Parameters.AddWithValue("@RefMarque", refMarque);
commande.ExecuteNonQuery();
db.closeConnection();
return(this.refMarque);
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/updateInDB");
db.closeConnection();
}
return(-1);
}
public bool updateInDB()
{
db_management db = db_management.Instance;
try{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
String squery;
squery = "UPDATE Articles Set RefMarque = @RefMarque, RefSousFamille = @RefSousFamille, Description = @Description, PrixHT = @PrixHT, Quantite = @Quantite WHERE RefArticle = @RefArticle";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefArticle", refArticle));
commande.Parameters.Add(new SQLiteParameter("@RefMarque", refMarque));
commande.Parameters.Add(new SQLiteParameter("@RefSousFamille", refSousFamille));
commande.Parameters.Add(new SQLiteParameter("@Description", description));
commande.Parameters.Add(new SQLiteParameter("@PrixHT", prixHT));
commande.Parameters.Add(new SQLiteParameter("@Quantite", Quantite));
commande.ExecuteNonQuery();
db.closeConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Articles/updateInDB");
db.closeConnection();
return(false);
}
}
public bool saveInDB()
{
db_management db = db_management.Instance;
try{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
String squery;
squery = "INSERT INTO Articles (RefArticle, RefMarque, RefSousFamille, Description, PrixHT, Quantite) Values (@RefArticle, @RefMarque, @RefSousFamille, @Description, @PrixHT, @Quantite)";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefArticle", refArticle));
commande.Parameters.Add(new SQLiteParameter("@RefMarque", refMarque));
commande.Parameters.Add(new SQLiteParameter("@RefSousFamille", refSousFamille));
commande.Parameters.Add(new SQLiteParameter("@Description", description));
commande.Parameters.Add(new SQLiteParameter("@PrixHT", prixHT));
commande.Parameters.Add(new SQLiteParameter("@Quantite", quantite));
commande.ExecuteNonQuery();
db.closeConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Articles/saveInDB");
db.closeConnection();
return(false);
}
}
public String loadFromDB()
{
db_management db = db_management.Instance;
try
{
if (refArticle == null)
{
Console.WriteLine("Erreur, ref nulle");
return(null);
}
else
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
SQLiteDataReader reader;
string squery;
squery = "SELECT * FROM Articles WHERE RefArticle = @RefArticle";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefArticle", refArticle));
reader = commande.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (reader[0].GetType() != typeof(DBNull))
{
description = (String)reader[1];
refSousFamille = (int)reader[2];
refMarque = (int)reader[3];
prixHT = (float)reader.GetFloat(4);
quantite = (int)reader[5];
}
reader.Close();
db.closeConnection();
return(this.refArticle);
}
else
{
Console.WriteLine("Erreur, l'article avec la référence " + refArticle + " n'existe pas");
reader.Close();
db.closeConnection();
return(null);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Articles/loadFromDB");
db.closeConnection();
return(null);
}
}
public static List <Articles> getListArticles()
{
db_management db = db_management.Instance;
List <Articles> list = new List <Articles>();
try
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
SQLiteDataReader reader;
string squery;
squery = "SELECT * FROM Articles";
commande = new SQLiteCommand(squery, connection);
reader = commande.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Articles article = new Articles();
article.refArticle = (String)reader[0];
article.description = (String)reader[1];
article.refSousFamille = (int)reader[2];
article.refMarque = (int)reader[3];
article.prixHT = (float)reader.GetFloat(4);
article.quantite = (int)reader[5];
list.Add(article);
}
reader.Close();
db.closeConnection();
return(list);
}
else
{
Console.WriteLine("Aucun article dans la base");
reader.Close();
db.closeConnection();
return(list);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Articles/getListArticles");
db.closeConnection();
return(list);
}
}
public static List <SousFamilles> getListSousFamillesFromFamilleRef(int refFamille)
{
db_management db = db_management.Instance;
List <SousFamilles> list = new List <SousFamilles>();
try
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
SQLiteDataReader reader;
string squery;
squery = "SELECT * FROM SousFamilles WHERE RefFamille = @RefFamille";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefFamille", refFamille));
reader = commande.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SousFamilles sfamille = new SousFamilles();
sfamille.refSousFamille = Convert.ToInt32(reader.GetInt64(0));
sfamille.refFamille = Convert.ToInt32(reader.GetInt64(1));
sfamille.nom = (String)reader[2];
list.Add(sfamille);
}
reader.Close();
db.closeConnection();
return(list);
}
else
{
Console.WriteLine("Aucune Sous Famille appartenant à la famille " + refFamille + " dans la base");
reader.Close();
db.closeConnection();
return(list);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In SousFamille/getListSousFamillesFromFamilleRef");
db.closeConnection();
return(list);
}
}
public int loadFromDB()
{
db_management db = db_management.Instance;
try{
if (refSousFamille == -1)
{
Console.WriteLine("Erreur, ref nulle");
db.closeConnection();
return(-1);
}
else
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
SQLiteDataReader reader;
string squery;
squery = "SELECT * FROM SousFamilles WHERE RefSousFamille = @refSousFamille";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@refSousFamille", refSousFamille));
reader = commande.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
refFamille = (int)reader[1];
nom = (String)reader[2];
reader.Close();
db.closeConnection();
return(this.refSousFamille);
}
else
{
Console.WriteLine("Erreur, la sous-famille avec la référence " + refSousFamille + " n'existe pas");
reader.Close();
db.closeConnection();
return(-1);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In SousFamilles/loadFromDB");
db.closeConnection();
}
return(-1);
}
public static List <Marques> getListMarques()
{
db_management db = db_management.Instance;
List <Marques> list = new List <Marques>();
try
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
SQLiteDataReader reader;
string squery;
squery = "SELECT * FROM Marques";
commande = new SQLiteCommand(squery, connection);
reader = commande.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Marques marque = new Marques();
marque.refMarque = Convert.ToInt32(reader.GetInt64(0));
marque.nom = (String)reader[1];
list.Add(marque);
}
reader.Close();
db.closeConnection();
return(list);
}
else
{
Console.WriteLine("Aucune marque dans la base");
reader.Close();
db.closeConnection();
return(list);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/getListMarques");
db.closeConnection();
return(list);
}
}
public int saveInDB()
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
Console.WriteLine("Insertion Sous-famille");
string squery = "SELECT RefSousFamille FROM SousFamilles WHERE Nom = @Nom AND RefFamille = @RefFamille";
SQLiteCommand commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@Nom", nom));
commande.Parameters.Add(new SQLiteParameter("@RefFamille", refFamille));
SQLiteDataReader reader = commande.ExecuteReader();
if (reader.HasRows)
{
Console.WriteLine("L'objet existe déjà ! refSousFamille mis à jour");
reader.Read();
refSousFamille = (int)reader[0];
reader.Close();
db.closeConnection();
return(refSousFamille);
}
else
{
refSousFamille = idSousFamille;
idSousFamille++;
squery = "INSERT INTO SousFamilles (RefSousFamille, RefFamille, Nom) VALUES (@RefSousFamille, @RefFamille, @Nom)";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefSousFamille", refSousFamille));
commande.Parameters.Add(new SQLiteParameter("@RefFamille", refFamille));
commande.Parameters.Add(new SQLiteParameter("@Nom", nom));
commande.ExecuteNonQuery();
db.closeConnection();
return(refSousFamille);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In SousFamilles/saveInDB");
db.closeConnection();
}
return(-1);
}
public static void flushTable()
{
db_management db = db_management.Instance;
try{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
String squery = "DELETE FROM Marques";
commande = new SQLiteCommand(squery, connection);
commande.ExecuteNonQuery();
db.closeConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/flushTable");
db.closeConnection();
}
}
static public int loadLastId()
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
String squery = "SELECT RefMarque, MAX(RefMarque) FROM Marques";
SQLiteCommand commande = new SQLiteCommand(squery, connection);
SQLiteDataReader reader = commande.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
int id;
if (reader[0].GetType() != typeof(DBNull))
{
id = (int)reader[0];
}
else
{
id = 0;
}
reader.Close();
db.closeConnection();
idMarque = id;
return(id);
}
else
{
reader.Close();
db.closeConnection();
return(0);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/loadLastId");
db.closeConnection();
return(0);
}
}
static public int getRefMarqueFromName(String name)
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
String squery = "SELECT RefMarque FROM Marques WHERE Nom = @Nom";
SQLiteCommand commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@Nom", name));
SQLiteDataReader reader = commande.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
int reference;
if (reader[0].GetType() != typeof(DBNull))
{
reference = Convert.ToInt32(reader.GetInt64(0));
}
else
{
reference = -1;
}
reader.Close();
db.closeConnection();
return(reference);
}
else
{
reader.Close();
db.closeConnection();
return(-1);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/getRefMarqueFromName");
db.closeConnection();
return(-1);
}
}
public static int countRows()
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
String squery = "SELECT COUNT(RefMarque) FROM Marques";
SQLiteCommand commande = new SQLiteCommand(squery, connection);
SQLiteDataReader reader = commande.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
int count;
if (reader[0].GetType() != typeof(DBNull))
{
count = Convert.ToInt32(reader.GetInt64(0));
}
else
{
count = -1;
}
reader.Close();
db.closeConnection();
return(count);
}
else
{
reader.Close();
db.closeConnection();
return(-1);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/countRows");
db.closeConnection();
return(-1);
}
}
public bool deleteFromDB()
{
db_management db = db_management.Instance;
try
{
SQLiteConnection connection = db.openConnection();
SQLiteCommand commande;
String squery;
squery = "DELETE FROM Marques WHERE RefMarque = @RefMarque";
commande = new SQLiteCommand(squery, connection);
commande.Parameters.Add(new SQLiteParameter("@RefMarque", refMarque));
commande.ExecuteNonQuery();
db.closeConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message + " | In Marques/deleteFromDB");
db.closeConnection();
return(false);
}
}