MySQLMediaBrolDao.cs
Description du code
MySQLMediaBrolDao.cs est un fichier du projet BiblioBrol.Ce fichier est situé dans /var/www/bin/sniplets/bibliobrol/src/.
Projet BiblioBrol :
Gestion de media en CSharp.
Pour plus d'infos, vous pouvez consulter la brève analyse.
Code source ou contenu du fichier
Code c# (MySQLMediaBrolDao.cs) (1145 lignes)
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.Common; using System.Runtime.Serialization.Formatters.Binary; using System.IO; using be.gaudry.observer; using be.gaudry.bibliobrol.model.identity; using be.gaudry.model.exceptions; namespace be.gaudry.bibliobrol.model.dao.mysql { public sealed class MySQLMediaBrolDao : Observable, IMediaBrolDao { private String conStr; #region singleton static MySQLMediaBrolDao instance = null; private DbProviderFactory dbpf; MySQLMediaBrolDao() { dbpf = ((MySQLFactory)MySQLFactory.Instance).getDbpf(); conStr = ((MySQLFactory)MySQLFactory.Instance).getConnectionString(); } public static MySQLMediaBrolDao Instance { get { lock (padlock) { if (instance == null) { } return instance; } } } #endregion #region IMediaBrolDao Members #region mediabrol public int insertMediaBrol(MediaBrol mediabrol) { int lastId = -1; if (mediabrol.Id <= 0) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.InsertCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); str.Append("INSERT INTO itemBiblio (biblioItemName, itemId, ownerId, mediaId, comment, localisation"); if (insertionDateValid) str.Append(", insertionDate"); str.Append(") VALUES('"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Name)); str.Append("',"); str.Append(mediabrol.Brol.Id); str.Append(","); str.Append(mediabrol.Owner.Id); str.Append(","); str.Append(mediabrol.MediaType.Id); str.Append(",'"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Comment)); str.Append("','"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Localisation)); str.Append("',"); str.Append(MySQLUtils.getAccessDate((insertionDateValid)?mediabrol.InsertionDate:DateTime.Now)); str.Append(");"); try { dbDa.InsertCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this)); if (dbDa.InsertCommand.ExecuteNonQuery() != 1) notify(new Notification(Notification.VERBOSE.error, "ERREUR Insertions multiples exemplaire", this)); //bad smell code : may not work with concurent acces dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY"; lastId = (int)dbDa.InsertCommand.ExecuteScalar(); mediabrol.Id = lastId; if (insertQualities(mediabrol, dbDa) == -1) notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion des caractéristiques de media pour " + mediabrol.Name, this)); } catch (Exception ex) { } dbCon.Close(); } str.Append(mediabrol.Name); str.Append("\" de l'ouvrage \""); str.Append(mediabrol.Brol.Title); str.Append((lastId > 0) ? "\" est ajouté." : "\" ne peut être ajouté."); return lastId; } internal MediaBrol getMediaBrol(DataRow row, bool anonymous) { //Load owner infos if (!anonymous) { { owner.Id = (int)row["pid"]; } { owner.LastName = (String)row["lastName"]; } { owner.FirstName = (String)row["firstName"]; } { owner.Birthdate = (DateTime)row["birthdate"]; } } //load mediabrol infos mediabrol.Owner = owner; mediabrol.Id = (int)row["iid"]; { mediabrol.InsertionDate = (DateTime)row["insertionDate"]; } { mediabrol.Name = (String)row["biblioItemName"]; } { mediabrol.Comment = (String)row["comment"]; } { mediabrol.Localisation = (String)row["localisation"]; } //load media infos { (int)row["mid"], ); } return mediabrol; } private List<MediaBrol> loadSelectedMediaBrols(int id) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; str.Append("i.id AS iid, p.id AS pid, m.id AS mid, ");// needed by MySQL to avoid having multiple id columns (it doesn't allows using p.id) str.Append("i.*, p.*, m.* FROM "); str.Append("(itemBiblio AS i"); str.Append(" LEFT JOIN person AS p ON i.ownerId = p.id)"); str.Append(" LEFT JOIN media AS m ON i.mediaId = m.id"); str.Append(" WHERE i.id = "); str.Append(id); //todo : use another request to load qualities instead of join //todo : load borrows? NO : there are loades only if we click on borrows tabpage... dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); MediaBrol mediabrol; try { dbDa.Fill(brolDt); foreach (DataRow brolRow in brolDt.Rows) { mediabrol = getMediaBrol(brolRow,false); try { mediabrol.Brol = NoChangeBrolDao.Instance.loadBrol((int)brolRow["itemId"], false); } catch (Exception eBrol) { } mediabrol.Qualities = loadQualitiesCommand(mediabrol.Id, dbDa); mediabrol.Borrowed = isBorrowed(mediabrol.Id, dbDa, dbCon); mediabrols.Add(mediabrol); } } catch (Exception eMedia) { notify(new Notification(Notification.VERBOSE.advancedOperation,"Chargement des media", eMedia, this)); } brolDt.Dispose(); dbCon.Close(); return mediabrols; } public MediaBrol loadMediaBrol(int id, bool editing) { List<MediaBrol> mediabrols = loadSelectedMediaBrols(id); /*int[] ids = { id }; List<MediaBrol> mediabrols = AccessImportExporter.Instance.loadSelectedMediaBrols(ids);*/ if (mediabrols.Count > 0) { //if (editing) lockBrol(id); return mediabrols[0]; } } /// <summary> /// Load all mediabrols for a brol from the persistant layer. /// </summary> /// <param name="brol"></param> /// <param name="fields">list of fields to load</param> /// <returns>(DataTable) table with selected columns</returns> public DataTable loadMediaBrols(Brol brol, List<DAOUtils.MEDIABROL_FIELD> fields) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; if (fields.Contains(DAOUtils.MEDIABROL_FIELD.name)) str.Append(", itemBiblio.biblioItemName AS name"); if (fields.Contains(DAOUtils.MEDIABROL_FIELD.date)) str.Append(", itemBiblio.insertionDate"); bool owner = false; if (fields.Contains(DAOUtils.MEDIABROL_FIELD.owner)) { str.Append(", CONCAT_WS(' ',person.firstName,person.lastName) AS owner"); owner = true; } str.Append(" FROM"); if (owner) str.Append(" person RIGHT JOIN itemBiblio"); if (owner) str.Append(" ON person.id = itemBiblio.ownerId"); str.Append(" WHERE itemBiblio.itemId = "); str.Append(brol.Id); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { } brolDt.Dispose(); dbCon.Close(); return brolDt; } /// <summary> /// Load all available medias for a type from the Access database. /// i.e. for a film : DVD, VHS, CD, etc. /// </summary> /// <param name="typeId">(int) selected type (film, book, etc.)</param> /// <returns>List of Medias</returns> public List<Media> loadMedias(int typeId) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; StringBuilder str = new StringBuilder("SELECT media.id AS mi, media.name AS mn, type.id AS ti, type.name AS tn"); str.Append(" FROM type RIGHT JOIN media ON type.id = media.type"); str.Append(" WHERE type.id = "); str.Append(typeId); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { } String mName; BrolType brolType; foreach (DataRow brolRow in brolDt.Rows) { } brolDt.Dispose(); dbCon.Close(); return medias; } /// <summary> /// Load brols value objects (only selected fields) for a type from the Access database. /// </summary> /// <param name="fields">List of fields to load for the brols</param> /// <param name="typeId"> /// (int) selected type (film, book, etc.) /// 0 allow to load all types /// </param> /// <returns>DataTable with selected fields for Brols</returns> public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId) { return loadDataTableVos(fields, typeId, null); } /// <summary> /// Load brols value objects (only selected fields) for a type from the Access database. /// </summary> /// <param name="fields">List of fields to load for the brols</param> /// <param name="typeId"> /// (int) selected type (film, book, etc.) /// 0 allow to load all types /// </param> /// <param name="categories">List of categories to match</param> /// <returns>DataTable with selected fields for Brols</returns> public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories) { return loadDataTableVos(fields, typeId, categories, 0); } /// <summary> /// Load brols value objects (only selected fields) for a type from the Access database. /// </summary> /// <param name="fields">List of fields to load for the brols</param> /// <param name="typeId"> /// (int) selected type (film, book, etc.) /// 0 allow to load all types /// </param> /// <param name="categories">List of categories to match</param> /// <param name="mediaId">media to match</param> /// <returns>DataTable with selected fields for Brols</returns> public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories, int mediaId) { return loadDataTableVos(fields, typeId, categories, mediaId, ""); } /// <summary> /// Load brols value objects (only selected fields) for a type from the Access database. /// </summary> /// <param name="fields">List of fields to load for the brols</param> /// <param name="typeId"> /// (int) selected type (film, book, etc.) /// 0 allow to load all types /// </param> /// <param name="categories">List of categories to match</param> /// <param name="mediaId">media to match</param> /// <param name="title">(String) string to find into brol's title</param> /// <returns>DataTable with selected fields for Brols</returns> public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories, int mediaId, string title) { bool searchCategories = (categories != null && categories.Count > 0); DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; if (fields.Contains(DAOUtils.MEDIABROL_FIELD.name)) str.Append(", itemBiblio.biblioItemName"); if (fields.Contains(DAOUtils.MEDIABROL_FIELD.i_title)) str.Append(", item.itemTitle"); if (fields.Contains(DAOUtils.MEDIABROL_FIELD.i_date)) str.Append(", item.pubDate"); if (fields.Contains(DAOUtils.MEDIABROL_FIELD.date)) str.Append(", itemBiblio.insertionDate"); bool owner = false; if (fields.Contains(DAOUtils.MEDIABROL_FIELD.owner)) { str.Append(", CONCAT_WS(' ',person.firstName,person.lastName) AS owner"); owner = true; } if (fields.Contains(DAOUtils.MEDIABROL_FIELD.brolId)) str.Append(", item.id"); str.Append(" FROM"); if (owner) str.Append(" person RIGHT JOIN ("); if (searchCategories) str.Append(" (categoryItemRelation INNER JOIN item ON categoryItemRelation.itemId = item.id)"); else str.Append(" item"); str.Append(" INNER JOIN itemBiblio ON item.id = itemBiblio.itemId"); if (owner) str.Append(") ON person.id = itemBiblio.ownerId"); str.Append(" WHERE item.id>0"); if (typeId > 0) { str.Append(" AND item.typeId="); str.Append(typeId); if (searchCategories) { str.Append(" AND categoryItemRelation.categoryId IN(0"); foreach(BrolCategory cat in categories) { str.Append(","+cat.Id); } str.Append(")"); } if (mediaId>0) { str.Append(" AND itemBiblio.mediaId ="); str.Append(mediaId); } } if (!String.Empty.Equals(title)) { str.Append(" AND item.itemTitle LIKE ('"); str.Append(MySQLUtils.escapeAndTrim(title)); str.Append("')"); } str.Append(" ORDER BY item.itemTitle ASC"); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { } brolDt.Dispose(); dbCon.Close(); return brolDt; } public bool updateMediaBrol(MediaBrol mediabrol) { bool updated = true; DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.UpdateCommand = dbCon.CreateCommand(); dbDa.InsertCommand = dbCon.CreateCommand(); dbDa.DeleteCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); try { str.Append("UPDATE itemBiblio SET biblioItemName='"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Name)); str.Append("', itemId="); str.Append(mediabrol.Brol.Id); str.Append(", ownerId="); str.Append(mediabrol.Owner.Id); str.Append(", mediaId="); str.Append(mediabrol.MediaType.Id); str.Append(", comment='"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Comment)); str.Append("', localisation='"); str.Append(MySQLUtils.escapeAndTrim(mediabrol.Localisation)); str.Append("'"); { str.Append(", insertionDate="); str.Append(MySQLUtils.getAccessDate(mediabrol.InsertionDate)); } str.Append(" WHERE id = "); str.Append(mediabrol.Id); dbDa.UpdateCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this)); int result = dbDa.UpdateCommand.ExecuteNonQuery(); if (result < 1) updated = false; if (result > 1) notify(new Notification(Notification.VERBOSE.error, "ERREUR modifications multiples exemplaire", this)); //todo : check multiple insertion sql request if (mediabrol.Qualities != null) { foreach (Quality q in mediabrol.Qualities) { switch (q.Status) { case STATUS.toAdd: str.Append(mediabrol.Id); str.Append(","); str.Append(q.Id); str.Append(")"); dbDa.InsertCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this)); dbDa.InsertCommand.ExecuteNonQuery(); break; case STATUS.toDelete: StringBuilder strDel = new StringBuilder("DELETE FROM itemBiblioQualityRelation WHERE itemBiblioId = "); strDel.Append(mediabrol.Id); strDel.Append(" AND qualityId = "); strDel.Append(q.Id); dbDa.DeleteCommand.CommandText = strDel.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this)); dbDa.DeleteCommand.ExecuteNonQuery(); break; } } } } catch (Exception ex) { updated = false; } dbCon.Close(); str.Append(mediabrol.Name); str.Append("\" de l'ouvrage \""); str.Append(mediabrol.Brol.Title); str.Append((updated)?"\" est mis à jour.":"\" ne peut être mis à jour."); return updated; } public bool lockMediaBrol(int id) { } public void unlockMediaBrol(int id) { } /// <summary> /// If no currents borrows, /// delete a mediabrol, all associated borrows, and all qualities relations /// </summary> /// <param name="mediabrolId"></param> /// <param name="deleteBrol">Set true to delete associated brol</param> /// <returns></returns> public bool deleteMediaBrol(int mediabrolId, String title, bool deleteBrol) { bool deleted = false; str.Append(mediabrolId); str.Append(" \""); str.Append(title); if (cleanBorrows(mediabrolId, false) > -1) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.DeleteCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); String strDel = "DELETE FROM itemBiblioQualityRelation WHERE itemBiblioId =" + mediabrolId; dbDa.DeleteCommand.CommandText = strDel; dbDa.DeleteCommand.ExecuteNonQuery(); int brolId = 0; if (deleteBrol) { //get brol id strDel = "SELECT itemId FROM itemBiblio WHERE id =" + mediabrolId; dbDa.SelectCommand = dbCon.CreateCommand(); dbDa.SelectCommand.CommandText = strDel; brolId = (int)dbDa.SelectCommand.ExecuteScalar(); } strDel = "DELETE FROM itemBiblio WHERE id =" + mediabrolId; dbDa.DeleteCommand.CommandText = strDel; dbDa.DeleteCommand.ExecuteNonQuery(); if (deleteBrol) { strDel = "SELECT COUNT(id) FROM itemBiblio WHERE itemId =" + brolId; dbDa.SelectCommand = dbCon.CreateCommand(); dbDa.SelectCommand.CommandText = strDel; //test if we have more than this mediabrol for this brol if ((int)dbDa.SelectCommand.ExecuteScalar() == 0) { //call accessbroldao to //delete this brol } } dbCon.Close(); deleted = true; str.Append("\" est supprimé."); } else { str.Append("\" ne peut être supprimé."); str.AppendLine("\nIl faut d'abord clôturer l'emprunt en cours pour cet exemplaire."); } return deleted; } /// <summary> /// If no currents borrows, /// delete a mediabrol, all associated borrows, and all qualities relations /// </summary> /// <param name="mediabrol"></param> /// <returns></returns> public bool deleteMediaBrol(MediaBrol mediabrol) { return deleteMediaBrol(mediabrol.Id, mediabrol.Brol.Title, false); } #endregion #region borrows public List<Borrow> loadBorrows(Person person) { } /// <summary> /// Load borrows and fill a DataTable. /// If mediabrolId equals 0, all borrows are loaded. /// </summary> /// <param name="mediabrolId">(int) Selected mediabrol id</param> /// <param name="borrowFields">List of fields to load for the borrows</param> /// <param name="typeId"> /// (int) selected type (film, book, etc.) /// 0 allow to load all types /// </param> /// <param name="title">(String) string to find into brol's title</param> /// <returns>(DataTable) DataTable with borrows</returns> public DataTable loadBorrows(int mediabrolId, List<DAOUtils.BORROW_FIELD> fields, int typeId, bool closed, string title) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; if (fields.Contains(DAOUtils.BORROW_FIELD.id)) str.Append("itemBiblio.id"); else str.Append("borrow.id"); if (fields.Contains(DAOUtils.BORROW_FIELD.type)) str.Append(", type.name as typeName"); if (fields.Contains(DAOUtils.BORROW_FIELD.borrower)) str.Append(" ,CONCAT_WS(' ',person.firstName,person.lastName) AS borrower"); if (fields.Contains(DAOUtils.BORROW_FIELD.name)) str.Append(", biblioItemName AS name"); if (fields.Contains(DAOUtils.BORROW_FIELD.startDate)) str.Append(" ,borrow.startDate"); if (fields.Contains(DAOUtils.BORROW_FIELD.endDate)) str.Append(" ,borrow.endDate"); if (fields.Contains(DAOUtils.BORROW_FIELD.planDate)) str.Append(" ,borrow.planDate"); if (fields.Contains(DAOUtils.BORROW_FIELD.comment)) str.Append(" ,borrow.comment"); if (fields.Contains(DAOUtils.BORROW_FIELD.title)) str.Append(" ,item.itemTitle AS title"); if (fields.Contains(DAOUtils.BORROW_FIELD.brolId)) str.Append(" ,item.id AS brolId"); str.Append(" FROM "); /*if (fields.Contains(DAOUtils.BORROW_FIELD.title)) {*/ str.Append("(person INNER JOIN ((borrow"); str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id)"); str.Append(" LEFT JOIN item ON itemBiblio.itemId = item.id)"); str.Append(" ON person.id = borrow.personId)"); if (fields.Contains(DAOUtils.BORROW_FIELD.type)) str.Append(" LEFT JOIN type ON item.typeId = type.id"); /*} else { str.Append("(person RIGHT JOIN borrow ON person.id = borrow.personId)"); str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id"); }*/ str.Append(" WHERE itemBiblio.id>0"); if (typeId > 0) { str.Append(" AND item.typeId = "); str.Append(typeId); } if (mediabrolId > 0) { str.Append(" AND itemBiblio.id = "); str.Append(mediabrolId); } if (!closed) { str.Append(" AND borrow.endDate IS NULL "); } if (!String.Empty.Equals(title)) { str.Append(" AND item.itemTitle LIKE ('"); str.Append(MySQLUtils.escapeAndTrim(title)); str.Append("')"); } str.Append(" ORDER BY borrow.startDate DESC"); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (System.Data.OleDb.OleDbException e) { } dbCon.Close(); return brolDt; } /// <summary> /// Load selected borrow /// </summary> /// <param name="mediabrol"></param> /// <returns></returns> public Borrow loadBorrow(int borrowId) { Borrow borrow; DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; str.Append(" FROM (person RIGHT JOIN borrow ON person.id = borrow.personId)"); str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id"); str.Append(" WHERE borrow.id = "); str.Append(borrowId); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { notify(new Notification(Notification.VERBOSE.lowError, "Aucun emprunt à charger (ou alors la table est ouverte par un autre utilisateur)", e, this)); //we may have exception if we have _ to fill } DataRow row = brolDt.Rows[0]; borrowId, getBorrower(row), ); brolDt.Dispose(); dbCon.Close(); return borrow; } private Person getBorrower(DataRow row) { try { { p.FirstName = (String)row["firstName"]; } { p.Edited = !row["personLocked"].ToString().Equals("False"); } { SEX sex; try { } catch (Exception) { sex = SEX._; } p.Sex = sex; } { p.Birthdate = (DateTime)row["birthdate"]; } } catch (Exception e) { } return p; } private bool isBorrowed(int mediabrolId, DbDataAdapter dbDa, DbConnection dbCon) { String str = String.Format("SELECT COUNT(0) FROM borrow WHERE itemBiblioId = {0} AND endDate IS NULL ", mediabrolId); if(dbDa.SelectCommand==null) dbDa.SelectCommand = dbCon.CreateCommand(); dbDa.SelectCommand.CommandText = str; bool conOpen = dbCon.State.Equals(ConnectionState.Open); if (!conOpen) dbCon.Open(); int r = (int)(Int64)dbDa.SelectCommand.ExecuteScalar(); notify(new Notification(Notification.VERBOSE.debug, String.Format("{0}\n\nResult : {1}",dbDa.SelectCommand.CommandText,r), this)); if (!conOpen) dbCon.Close(); return r > 0; } /// <summary> /// Start a borrow if not borrowed /// </summary> /// <param name="mediabrolId">(int) id of the mediabrol to borrow</param> /// <param name="borrowerId">(int) id of the borrower</param> /// <returns></returns> public bool startBorrow(int mediabrolId, int borrowerId) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.InsertCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); if (isBorrowed(mediabrolId, dbDa, dbCon)) return false; str.Append("INSERT INTO borrow (itemBiblioId,personId,startDate) VALUES ("); str.Append(mediabrolId); str.Append(", "); str.Append(borrowerId); str.Append(", "); str.Append(MySQLUtils.getAccessDate(DateTime.Now)); str.Append(")"); dbDa.InsertCommand.CommandText = str.ToString(); try { notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this)); dbDa.InsertCommand.ExecuteNonQuery(); } catch (Exception ex) { return false; } finally { dbCon.Close(); } return true; } public bool stopBorrow(int borrowId) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.UpdateCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); str.Append(MySQLUtils.getAccessDate(DateTime.Now)); str.Append(" WHERE id="); str.Append(borrowId); dbDa.UpdateCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this)); try { dbDa.UpdateCommand.ExecuteNonQuery(); } catch (Exception e) { return true; } finally { dbCon.Close(); } return true; } public bool stopBorrow(MediaBrol mediabrol) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.UpdateCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); dbDa.UpdateCommand.CommandText = String.Format( "UPDATE borrow SET endDate={0} WHERE id=(SELECT id FROM borrow WHERE itemBiblioId = {1} AND endDate IS NULL)", MySQLUtils.getAccessDate(DateTime.Now), mediabrol.Id ); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this)); try { dbDa.UpdateCommand.ExecuteNonQuery(); } catch (Exception e) { return true; } finally { dbCon.Close(); } return true; } /// <summary> /// Delete all borrows for a mediabrol /// </summary> /// <param name="mediabrolId">(int) mediabrol identifier</param> public int cleanBorrows(int mediabrolId, bool forceDelete) { int result = -1; DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbDa.DeleteCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); str.Append(mediabrolId); str.Append(" AND endDate IS Null"); dbDa.SelectCommand.CommandText = str.ToString(); result = (int)dbDa.SelectCommand.ExecuteScalar(); str.AppendLine("\nResult of this operation : "); str.Append(result); if ((result > 0) && (!forceDelete)) { notify(new Notification(Notification.VERBOSE.lowError, "Il n'est pas possible de supprimer l'exemplaire car il est en prêt", this)); result = -1; } else { str.Append(mediabrolId); dbDa.DeleteCommand.CommandText = str.ToString(); result = dbDa.DeleteCommand.ExecuteNonQuery(); } dbCon.Close(); return result; } /// <summary> /// Delete selected borrow /// </summary> /// <param name="selectedBorrowId">(int) borrow identifier</param> public void cleanBorrow(int selectedBorrowId) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.DeleteCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; dbCon.Open(); String str = "DELETE FROM borrow WHERE id =" + selectedBorrowId; dbDa.DeleteCommand.CommandText = str; int result = dbDa.DeleteCommand.ExecuteNonQuery(); dbCon.Close(); } /// <summary> /// Update a borrow /// </summary> /// <param name="borrow"></param> public bool updateBorrow(Borrow borrow) { if (borrow.Borrower == null || borrow.Borrower.Id <= 0 || borrow.MediabrolId <= 0) { notify(new Notification(Notification.VERBOSE.error, "Impossible de sauver les modifications apportées à l'emprunt.", this)); return false; } DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.UpdateCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; str.Append("comment='"); str.Append(MySQLUtils.escapeAndTrim(borrow.Comment)); str.Append("', personId="); str.Append(borrow.Borrower.Id); str.Append(", itemBiblioId="); str.Append(borrow.MediabrolId); str.Append(", startDate="); { str.Append(MySQLUtils.getAccessDate(borrow.StartDate)); } else str.Append("NULL"); str.Append(", planDate="); { str.Append(MySQLUtils.getAccessDate(borrow.PlanDate)); } else str.Append("NULL"); str.Append(", endDate="); { str.Append(MySQLUtils.getAccessDate(borrow.EndDate)); } else str.Append("NULL"); str.Append(" WHERE id="); str.Append(borrow.Id); dbCon.Open(); dbDa.UpdateCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this)); try { dbDa.UpdateCommand.ExecuteNonQuery(); } catch (Exception e) { return true; } finally { dbCon.Close(); } return true; } #endregion #region qualities private int insertQualities(MediaBrol mediabrol, DbDataAdapter dbDa) { if (mediabrol.Id < 0) return -1; int i = 0; foreach (Quality q in mediabrol.Qualities) { if (q.Status.Equals(STATUS.toAdd)) { ++i; insertQuality(q, mediabrol.Id, dbDa.InsertCommand); } else notify(new Notification(Notification.VERBOSE.error, "Insertion des qualités : ERREUR (status != toAdd)", this)); } return i; } private bool insertQuality(Quality q, int mediabrolId, DbCommand com) { if (q.Status.Equals(STATUS.toAdd)) { str.Append("INSERT INTO itemBiblioQualityRelation (itemBiblioId, qualityId) VALUES("); str.Append(mediabrolId); str.Append(","); str.Append(q.Id); str.Append(");"); com.CommandText = str.ToString(); return (com.ExecuteNonQuery() == 1); } notify(new Notification(Notification.VERBOSE.error, "Insertion d'une qualité : ERREUR (status != toAdd)", this)); return false; } public List<Quality> loadQualities(int itemBiblioBrolId) { List<Quality> qualities; DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; qualities = loadQualitiesCommand(itemBiblioBrolId, dbDa); dbCon.Close(); return qualities; } internal List<Quality> loadQualitiesCommand(int itemBiblioBrolId, DbDataAdapter dbDa) { str.Append("FROM itemBiblioQualityRelation AS r "); str.Append("LEFT JOIN quality ON r.qualityId = quality.id "); str.Append("WHERE r.itemBiblioId = "); str.Append(itemBiblioBrolId); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { notify(new Notification(Notification.VERBOSE.error, "Chargement des caractéristiques du support", e, this)); } foreach (DataRow brolRow in brolDt.Rows) { (int)brolRow["id"], )); } brolDt.Dispose(); return qualities; } public List<Quality> loadQualities(BrolType brolType) { DbConnection dbCon = dbpf.CreateConnection(); DbDataAdapter dbDa = dbpf.CreateDataAdapter(); dbDa.SelectCommand = dbCon.CreateCommand(); dbCon.ConnectionString = conStr; str.Append(brolType.Id); dbDa.SelectCommand.CommandText = str.ToString(); notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this)); try { dbDa.Fill(brolDt); } catch (Exception e) { notify(new Notification(Notification.VERBOSE.error, "Chargement des caractéristiques du support", e, this)); } foreach (DataRow brolRow in brolDt.Rows) { (int)brolRow["id"], )); } brolDt.Dispose(); dbCon.Close(); return qualities; } #endregion #endregion } }
Structure et Fichiers du projet
Afficher/masquer...Icône | Nom | Taille | Modification |
Icône | Nom | Taille | Modification |
| _ | Répertoire parent | 0 octets | 1734938014 23/12/2024 08:13:34 |
| _ | utils | 0 octets | 1541007203 31/10/2018 18:33:23 |
Utilisation de l'explorateur de code
- Navigation :
- Un clic sur une icône de répertoire ouvre ce répertoire pour en afficher les fichiers.
- Lorsque le répertoire en cours ne contient pas de sous-répertoires il est possible de remonter vers le répertoire parent.
- La structure de répertoires en treetable (tableau en forme d'arborescence) n'est plus possibledans cette version.
- Un clic sur une icône de fichier ouvre ce fichier pour en afficher le code avec la coloration syntaxique adaptée en fonction du langage principal utilisé dans le fichier.
- Affichage :
- Il est possible de trier les répertoires ou les fichiers selon certains critères (nom, taille, date).
- Actions :
- Les actions possible sur les fichiers dépendent de vos droits d'utilisateur sur le site. Veuillez activer le mode utilisateur pour activer les actions.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 16/10/2009 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/cs-bibliobrol-source-rf-model/dao/mysql//MySQLMediaBrolDao.cs.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.