public static ObservableCollection <PhotoDAO> selectPhotos()
{
ObservableCollection <PhotoDAO> l = new ObservableCollection <PhotoDAO>();
string query = "SELECT * FROM photo ORDER BY id_photo ASC;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
PhotoDAO p = new PhotoDAO(reader.GetInt32(0), reader.GetInt32(1));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table Photo : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
// SELECT
public static PhotoDAO SelectPhotoById(string id)
{
var photoDao = new PhotoDAO();
// Selectionne la photo a partir de l'id
var query =
"SELECT * FROM public.photo a where a.\"idPhoto\"=:idPhotoParam";
var cmd = new NpgsqlCommand(query, DALconnection.OpenConnection());
cmd.Parameters.AddWithValue("idPhotoParam", id);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// récup les paramètres principaux
var idPhoto = (string)reader["idPhoto"];
var produitId = (string)reader["produitId"];
var fichierPhoto = (string)reader["fichierPhoto"];
photoDao = new PhotoDAO(idPhoto, produitId, fichierPhoto);
}
reader.Close();
return(photoDao);
}
public static PhotoViewModel getPhoto(int idPhoto)
{
PhotoDAO pDAO = PhotoDAO.getPhoto(idPhoto);
PhotoViewModel p = new PhotoViewModel(pDAO.id_photo, pDAO.id_produit);
return(p);
}
public static void updatePhoto(PhotoDAO p)
{
string query = "UPDATE photo set id_produit=\"" + p.id_produit + "\" where id_Photo=" + p.id_photo + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertPhoto(PhotoDAO p)
{
int id = getMaxIdPhoto() + 1;
string query = "INSERT INTO photo VALUES (\"" + id + "\",\"" + p.id_produit + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static ObservableCollection <PhotoViewModel> listePhotos()
{
ObservableCollection <PhotoDAO> lDAO = PhotoDAO.listePhotos();
ObservableCollection <PhotoViewModel> l = new ObservableCollection <PhotoViewModel>();
foreach (PhotoDAO element in lDAO)
{
PhotoViewModel p = new PhotoViewModel(element.id_photo, element.id_produit);
l.Add(p);
}
return(l);
}
public static PhotoDAO getPhoto(int idPhoto)
{
string query = "SELECT * FROM photo WHERE id_photo=" + idPhoto + "\";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
PhotoDAO pers = new PhotoDAO(reader.GetInt32(0), reader.GetInt32(1));
reader.Close();
return(pers);
}
// INSERT & Update
public static void InsertOrAddNewPhoto(PhotoDAO photo)
{
// Inserer photo dans la bdd
var query =
@"INSERT INTO public.photo (""idPhoto"",""produitId"",""fichierPhoto"")
values (:idPhoto,:produitId,:fichierPhoto)
ON CONFLICT ON CONSTRAINT pk_photo DO UPDATE SET ""idPhoto""=:idPhoto,
""produitId""=:produitId,
""fichierPhoto""=:fichierPhoto,
where photo.""idPhoto""=:idPhoto";
var cmd = new NpgsqlCommand(query, DALconnection.OpenConnection());
cmd.Parameters.AddWithValue("idPhoto", photo.IdPhoto);
cmd.Parameters.AddWithValue("produitId", photo.ProduitId);
cmd.Parameters.AddWithValue("fichierPhoto", photo.FichierPhoto);
cmd.ExecuteNonQuery();
}
public static void insertPhoto(PhotoViewModel p)
{
PhotoDAO.insertPhoto(new PhotoDAO(p.id, p.id_produit));
}
public static void supprimerPhoto(int id)
{
PhotoDAO.supprimerPhoto(id);
}
public static void updatePhoto(PhotoViewModel p)
{
PhotoDAO.updatePhoto(new PhotoDAO(p.id, p.id_produit));
}
