public void ChargeDonnées(string[] titreCommencePar) { using (new WaitingCursor()) using (IDbCommand cmd = BDDatabase.Database.GetCommand()) { StringBuilder SQL = new StringBuilder(); cmd.Parameters.Clear(); SQL.Append("SELECT * FROM ALBUMS A INNER JOIN SERIES S ON A.ID_SERIE = S.ID_SERIE"); if (titreCommencePar != null && titreCommencePar.Length > 0) { SQL.Append("\nWHERE"); for (int i = 0; i < titreCommencePar.Length; i++) { SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERTITREALBUM LIKE ?"); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%")); } } SQL.Append("\nORDER BY A.UPPERTITREALBUM"); cmd.CommandText = SQL.ToString(); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result)) if (result != null) { dataReader.FillList(this); } } }
public void ChargeDonnées(string[] titreCommencePar) { using (new WaitingCursor()) using (IDbCommand cmd = BDPPCDatabase.GetCommand()) { StringBuilder SQL = new StringBuilder(); cmd.Parameters.Clear(); SQL.Append("SELECT REFEMPRUNTEUR, NOMEMPRUNTEUR FROM EMPRUNTEURS"); if (titreCommencePar != null && titreCommencePar.Length > 0) { SQL.Append("\nWHERE"); for (int i = 0; i < titreCommencePar.Length; i++) { SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERNOMEMPRUNTEUR LIKE ?"); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%")); } } SQL.Append("\nORDER BY UPPERNOMEMPRUNTEUR"); cmd.CommandText = SQL.ToString(); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Emprunteur> dataReader = new BaseDataReader <Emprunteur>(result)) if (result != null) { dataReader.FillList(this); } } }
public void ChargeDonnées(string[] titreCommencePar) { using (new WaitingCursor()) using (IDbCommand cmd = BDDatabase.Database.GetCommand()) { StringBuilder SQL = new StringBuilder(); cmd.Parameters.Clear(); SQL.Append("SELECT * FROM SERIES S LEFT JOIN EDITEURS E ON S.ID_EDITEUR = E.ID_EDITEUR LEFT JOIN COLLECTIONS C ON S.ID_COLLECTION=C.ID_COLLECTION"); if (titreCommencePar != null && titreCommencePar.Length > 0) { SQL.Append("\nWHERE"); for (int i = 0; i < titreCommencePar.Length; i++) { SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERTITRESERIE LIKE ?"); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%")); } } SQL.Append("\nORDER BY S.UPPERTITRESERIE"); cmd.CommandText = SQL.ToString(); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Série> dataReader = new BaseDataReader <Série>(result)) if (result != null) { dataReader.FillList(this); } } }
public override void Fill(params System.Object[] references) { if (references.Length < 1) { return; } Guid reference = (Guid)references[0]; if (reference.Equals(Guid.Empty)) { return; } using (new WaitingCursor()) using (IDbCommand cmd = BDDatabase.Database.GetCommand()) { cmd.CommandText = "SELECT ID_EDITION, ID_Album, e.ID_Editeur, e.ID_Collection, NOMCOLLECTION, ANNEEEDITION, PRIX, VO, COULEUR, ISBN, DEDICACE, PRETE," + "STOCK, Offert, Gratuit, NombreDePages, etat, le.libelle as setat, reliure, lr.libelle as sreliure, orientation, lo.libelle as sorientation," + "FormatEdition, lf.libelle as sFormatEdition, typeedition, lte.libelle as stypeedition, DateAchat, Notes, AnneeCote, PrixCote " + "FROM EDITIONS e LEFT JOIN COLLECTIONS c ON e.ID_Collection = c.ID_Collection " + "LEFT JOIN LISTES le on (le.ref = e.etat and le.categorie = 1) " + "LEFT JOIN LISTES lr on (lr.ref = e.reliure and lr.categorie = 2) " + "LEFT JOIN LISTES lte on (lte.ref = e.typeedition and lte.categorie = 3) " + "LEFT JOIN LISTES lo on (lo.ref = e.orientation and lo.categorie = 4) " + "LEFT JOIN LISTES lf on (lf.ref = e.formatedition and lf.categorie = 5) " + "WHERE ID_Edition = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Edition", StringUtils.GuidToString(reference))); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <EditionComplet> dataReader = new BaseDataReader <EditionComplet>(result)) if (result != null && result.Read()) { dataReader.LoadData(this); Editeur.Fill(dataReader.GetGuid("ID_EDITEUR")); // Self.Collection.Fill(q); } Emprunts.Fill(reference, srcEmprunt.Album); cmd.CommandText = "SELECT ID_Couverture, FichierCouverture, STOCKAGECOUVERTURE, CategorieImage, l.Libelle as sCategorieImage"; cmd.CommandText += " FROM Couvertures c LEFT JOIN Listes l ON (c.categorieimage = l.ref and l.categorie = 6)"; cmd.CommandText += " WHERE ID_Edition = ? ORDER BY c.categorieimage NULLS FIRST, c.Ordre"; cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Edition", StringUtils.GuidToString(reference))); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Couverture> dataReader = new BaseDataReader <Couverture>(result)) if (result != null) { dataReader.FillList(fCouvertures); } } }
public override void ProcAuteurs(ListeAuteurs list, Guid ID_Album, Guid ID_Série, Guid ID_ParaBD) { using (new WaitingCursor()) using (IDbCommand cmd = BDDatabase.Database.GetCommand()) { cmd.CommandText = "select id_personne, nompersonne, id_album, id_serie, id_parabd, metier from proc_auteurs(?, ?, ?)"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Album", StringUtils.GuidToString(ID_Album))); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(ID_Série))); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_ParaBD", StringUtils.GuidToString(ID_ParaBD))); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result)) if (result != null) { dataReader.FillList(list); } } }
public static void ProcAuteurs(ArrayList list, int refAlbum, int refSérie) { using (new WaitingCursor()) using (IDbCommand cmd = BDPPCDatabase.GetCommand()) { if (refAlbum != -1) { cmd.CommandText = "select p.refpersonne, p.nompersonne, a.refalbum, NULL as refserie, a.metier " + "from personnes p inner join auteurs a on a.refpersonne = p.refpersonne " + "where a.refalbum = ? " + "order by a.metier, p.uppernompersonne"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefAlbum", refAlbum)); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result)) if (result != null) { dataReader.FillList(list); } } if (refSérie != -1) { cmd.CommandText = "select p.refpersonne, p.nompersonne, NULL as refalbum, a.refserie, a.metier " + "from personnes p inner join auteurs_series a on a.refpersonne = p.refpersonne " + "where a.refserie = ? " + "order by a.metier, p.uppernompersonne"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", refSérie)); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result)) if (result != null) { dataReader.FillList(list); } } } }
public override void Fill(params object[] references) { if (references.Length < 1) { return; } Guid reference = (Guid)references[0]; if (reference.Equals(Guid.Empty)) { return; } if (references.Length > 1) { this.fID_Auteur = (Guid)references[1]; } else { this.fID_Auteur = Guid.Empty; } using (new WaitingCursor()) using (IDbCommand cmd = BDDatabase.Database.GetCommand()) { cmd.CommandText = "SELECT ID_SERIE, TITRESERIE, TERMINEE, SUJETSERIE, REMARQUESSERIE, SITEWEB, S.ID_EDITEUR, S.ID_COLLECTION, NOMCOLLECTION " + "FROM SERIES S LEFT JOIN COLLECTIONS C ON S.ID_COLLECTION = C.ID_COLLECTION " + "WHERE ID_SERIE = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_serie", StringUtils.GuidToString(reference))); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <SérieComplet> dataReader = new BaseDataReader <SérieComplet>(result)) if (result != null && result.Read()) { dataReader.LoadData(this); this.fTerminée = dataReader.GetInt(2, -1); this.Editeur.Fill(this.Editeur.Reference); } cmd.CommandText = "SELECT ID_ALBUM, TITREALBUM, INTEGRALE, HORSSERIE, TOME, TOMEDEBUT, TOMEFIN, ID_SERIE " + "FROM ALBUMS " + "WHERE ID_SERIE = ? "; if (!this.fID_Auteur.Equals(Guid.Empty)) { cmd.CommandText += "AND ID_ALBUM IN (SELECT ID_ALBUM FROM AUTEURS WHERE ID_PERSONNE = ?) "; } cmd.CommandText += "ORDER BY COALESCE(HORSSERIE, -1), COALESCE(INTEGRALE, -1), COALESCE(TOME, -1)"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(reference))); if (!this.fID_Auteur.Equals(Guid.Empty)) { cmd.Parameters.Add(BDDatabase.Database.GetParameter("@FIdAuteur", StringUtils.GuidToString(this.fID_Auteur))); } using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result)) if (result != null) { dataReader.FillList(this.Albums); } cmd.CommandText = "SELECT Genre " + "FROM GenreSeries s INNER JOIN Genres g ON g.ID_Genre = s.ID_Genre " + "WHERE ID_Serie = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(reference))); this.fGenres.Clear(); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <object> dataReader = new BaseDataReader <object>(result)) while (result != null && result.Read()) { this.fGenres.Add(dataReader.GetString(0, string.Empty)); } ListeAuteurs Auteurs = new ListeAuteurs(); StoredProcedures.PS.ProcAuteurs(Auteurs, Guid.Empty, reference, Guid.Empty); this.Scénaristes.Clear(); this.Dessinateurs.Clear(); this.Coloristes.Clear(); foreach (Auteur auteur in Auteurs) { switch (auteur.Metier) { case 0: { this.Scénaristes.Add(auteur); break; } case 1: { this.Dessinateurs.Add(auteur); break; } case 2: { this.Coloristes.Add(auteur); break; } } } } }
public override void Fill(params object[] references) { if (references.Length < 1) { return; } int reference = (int)references[0]; if (reference == 0) { return; } if (references.Length > 1) { this.FIdAuteur = (int)references[1]; } else { this.FIdAuteur = -1; } using (new WaitingCursor()) using (IDbCommand cmd = BDPPCDatabase.GetCommand()) { cmd.CommandText = "SELECT REFSERIE, TITRESERIE, TERMINEE, SUJETSERIE, REMARQUESSERIE, SITEWEB, S.REFEDITEUR, S.REFCOLLECTION, NOMCOLLECTION " + "FROM SERIES S LEFT JOIN COLLECTIONS C ON S.REFCOLLECTION = C.REFCOLLECTION " + "WHERE REFSERIE = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refserie", reference)); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <SérieComplet> dataReader = new BaseDataReader <SérieComplet>(result)) if (result != null && result.Read()) { dataReader.LoadData(this); this.Terminée = dataReader.GetInt(2, -1); this.Editeur.Fill(this.Editeur.Reference); } cmd.CommandText = "SELECT REFALBUM, TITREALBUM, INTEGRALE, HORSSERIE, TOME, TOMEDEBUT, TOMEFIN, REFSERIE " + "FROM ALBUMS " + "WHERE REFSERIE = ? "; if (this.FIdAuteur != -1) { cmd.CommandText += "AND REFALBUM IN (SELECT REFALBUM FROM AUTEURS WHERE REFPERSONNE = ?) "; } cmd.CommandText += "ORDER BY COALESCE(HORSSERIE, -1), COALESCE(INTEGRALE, -1), COALESCE(TOME, -1)"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", reference)); if (this.FIdAuteur != -1) { cmd.Parameters.Add(BDPPCDatabase.GetParameter("@FIdAuteur", this.FIdAuteur)); } using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result)) if (result != null) { dataReader.FillList(this.Albums); } cmd.CommandText = "SELECT Genre " + "FROM GenreSeries s INNER JOIN Genres g ON g.RefGenre = s.RefGenre " + "WHERE RefSerie = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", reference)); this.Genres.Clear(); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <object> dataReader = new BaseDataReader <object>(result)) while (result.Read()) { this.Genres.Add(dataReader.GetString(0, string.Empty)); } ArrayList Auteurs = new ArrayList(); StoredProceduresPPC.ProcAuteurs(Auteurs, -1, reference); this.Scénaristes.Clear(); this.Dessinateurs.Clear(); this.Coloristes.Clear(); foreach (Auteur auteur in Auteurs) { switch (auteur.Metier) { case 0: { this.Scénaristes.Add(auteur); break; } case 1: { this.Dessinateurs.Add(auteur); break; } case 2: { this.Coloristes.Add(auteur); break; } } } } }
public override void Fill(params object[] references) { if (references.Length < 1) { return; } int reference = (int)references[0]; if (reference == 0) { return; } using (new WaitingCursor()) using (IDbCommand cmd = BDPPCDatabase.GetCommand()) { cmd.CommandText = "SELECT REFALBUM, TITREALBUM, ANNEEPARUTION, REFSERIE, TOME, TOMEDEBUT, TOMEFIN, SUJETALBUM, REMARQUESALBUM, HORSSERIE, INTEGRALE " + "FROM ALBUMS " + "WHERE RefAlbum = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refalbum", reference)); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <AlbumCompletPPC> dataReader = new BaseDataReader <AlbumCompletPPC>(result)) if (result != null && result.Read()) { dataReader.LoadData(this); Série.Fill(dataReader.GetInt(3, -1)); } ArrayList Auteurs = new ArrayList(); StoredProceduresPPC.ProcAuteurs(Auteurs, reference, -1); this.Scénaristes.Clear(); this.Dessinateurs.Clear(); this.Coloristes.Clear(); foreach (Auteur auteur in Auteurs) { switch (auteur.Metier) { case 0: { this.Scénaristes.Add(auteur); break; } case 1: { this.Dessinateurs.Add(auteur); break; } case 2: { this.Coloristes.Add(auteur); break; } } } this.Editions.Clear(); cmd.CommandText = "SELECT REFEDITION, RefAlbum, e.REFEDITEUR, e.REFCOLLECTION, NOMCOLLECTION, ANNEEEDITION, PRIX, VO, COULEUR, ISBN, DEDICACE, PRETE, STOCK, Offert, Gratuit, " + "NombreDePages, etat, le.libelle as setat, reliure, lr.libelle as sreliure, orientation, lo.libelle as sorientation, FormatEdition, lf.libelle as sFormatEdition, typeedition, lte.libelle as stypeedition, DateAchat, Notes " + "FROM EDITIONS e LEFT JOIN COLLECTIONS c ON e.REFCOLLECTION = c.REFCOLLECTION " + "LEFT JOIN LISTES le on (le.ref = e.etat and le.categorie = 1) " + "LEFT JOIN LISTES lr on (lr.ref = e.reliure and lr.categorie = 2) " + "LEFT JOIN LISTES lte on (lte.ref = e.typeedition and lte.categorie = 3) " + "LEFT JOIN LISTES lo on (lo.ref = e.orientation and lo.categorie = 4) " + "LEFT JOIN LISTES lf on (lf.ref = e.formatedition and lf.categorie = 5) " + "WHERE REFALBUM = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refalbum", RefAlbum)); using (IDataReader result = cmd.ExecuteReader()) using (BaseDataReader <Edition> dataReader = new BaseDataReader <Edition>(result)) if (result != null) { dataReader.FillList(this.Editions); } //Self.Editeur.Fill(Fields.ByNameAsInteger['REFEDITEUR']); //Self.Collection.Fill(q); } }