NoChangeBrolDao.cs

Description du code

NoChangeBrolDao.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

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.Common;
  6. using be.gaudry.observer;
  7. using be.gaudry.bibliobrol.model.identity;
  8. using be.gaudry.model.exceptions;
  9. using be.gaudry.bibliobrol.config;
  10.  
  11. namespace be.gaudry.bibliobrol.model.dao.mysql
  12. {
  13. public sealed class NoChangeBrolDao : Observable, IBrolDao
  14. {
  15.  
  16. private String conStr;
  17. #region singleton
  18. static NoChangeBrolDao instance = null;
  19. static readonly object padlock = new object();
  20. private DbProviderFactory dbpf;
  21. NoChangeBrolDao()
  22. {
  23. notify(new Notification(Notification.VERBOSE.debug, "AccessBrolDao singleton call", this));
  24. dbpf = ((MySQLFactory)MySQLFactory.Instance).getDbpf();
  25. conStr = ((MySQLFactory)MySQLFactory.Instance).getConnectionString();
  26. }
  27. public static NoChangeBrolDao Instance
  28. {
  29. get
  30. {
  31. lock (padlock)
  32. {
  33. if (instance == null)
  34. {
  35. instance = new NoChangeBrolDao();
  36. }
  37. return instance;
  38. }
  39. }
  40. }
  41. #endregion
  42.  
  43. #region private methods
  44. internal bool tryToAddRelation(DataSet ds, DataRelation dr)
  45. {
  46. try
  47. {
  48. ds.Relations.Add(dr);
  49. StringBuilder strRel = new StringBuilder("Ajout de la relation ");
  50. strRel.Append(dr.RelationName);
  51. strRel.Append(" pour le DataSet ");
  52. strRel.Append(ds.DataSetName);
  53. notify(new Notification(Notification.VERBOSE.persistentOperation, strRel.ToString(), this));
  54. }
  55. catch (Exception e)
  56. {
  57. notify(new Notification(Notification.VERBOSE.lowError, "Liaison " + dr.RelationName, e, this));
  58. return false;
  59. }
  60. return true;
  61. }
  62.  
  63. /// <summary>
  64. /// Lock or unlock a row into item table
  65. /// </summary>
  66. /// <param name="id">(int) item identifier</param>
  67. /// <param name="toLocked">(bool) true to lock; false to unlock</param>
  68. /// <returns>(bool) true if one and only one row is updated</returns>
  69. private bool lockUnlockItem(int id, bool toLocked)
  70. {
  71. DbConnection dbCon = dbpf.CreateConnection();
  72. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  73. dbDa.UpdateCommand = dbCon.CreateCommand();
  74. dbCon.ConnectionString = conStr;
  75. dbCon.Open();
  76. StringBuilder str = new StringBuilder("UPDATE item SET itemLocked=");
  77. str.Append((toLocked) ? "TRUE" : "FALSE");
  78. str.Append(" WHERE id=");
  79. str.Append(id);
  80. dbDa.UpdateCommand.CommandText = str.ToString();
  81. int r = 0;
  82. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
  83. try
  84. {
  85. r = dbDa.UpdateCommand.ExecuteNonQuery();
  86. }
  87. catch (Exception e)
  88. {
  89. notify(new Notification(Notification.VERBOSE.error, "Verrouillage du media",e, this));
  90. }
  91.  
  92. dbCon.Close();
  93. return (r == 1);
  94. }
  95.  
  96. internal BrolCategory getCategory(DataRow row)
  97. {
  98. if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
  99. BrolCategory cat = new BrolCategory((int)row["id"], (String)row["name"]);
  100. if (!(row["typeId"] is DBNull))
  101. {
  102. //todo : fetch media type ?
  103. }
  104. return cat;
  105. }
  106. internal Actor getActor(DataRow row)
  107. {
  108. if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no name found");
  109. Actor actor = new Actor((int)row["id"], (String)row["lastName"]);
  110. if (!(row["firstName"] is DBNull))
  111. {
  112. actor.FirstName = (String)row["firstName"];
  113. }
  114. if (!(row["roleName"] is DBNull))
  115. {
  116. actor.Role = (row["roleValue"] is DBNull) ? new ActorRole((int)row["roleId"], (String)row["roleName"]) : new ActorRole((int)row["roleId"], (String)row["roleName"], (String)row["roleValue"]);
  117. }
  118. if (!(row["pseudo"] is DBNull))
  119. {
  120. actor.Pseudo = (String)row["pseudo"];
  121. }
  122. if (!(row["sex"] is DBNull))
  123. {
  124. SEX sex;
  125. try
  126. {
  127. sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
  128. }
  129. catch (Exception)
  130. {
  131. sex = SEX._;
  132. }
  133. actor.Sex = sex;
  134. }
  135. if (!(row["birthdate"] is DBNull))
  136. {
  137. actor.Birthdate = (DateTime)row["birthdate"];
  138. }
  139. return actor;
  140. }
  141.  
  142. internal Serie getSerie(DataRow row)
  143. {
  144. if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
  145. Serie serie = new Serie((int)row["id"], (String)row["name"]);
  146. return serie;
  147. }
  148.  
  149. internal SerieItem getSerieItem(DataRow row)
  150. {
  151. if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
  152. Serie serie = new Serie((int)row["id"], (String)row["name"]);
  153. SerieItem serieItem = new SerieItem(serie);
  154. if (!(row["rank"] is DBNull))
  155. {
  156. serieItem.setRank((string)row["rank"]);
  157. }
  158. return serieItem;
  159. }
  160.  
  161. internal Brol getBrol(DataRow row)
  162. {
  163. if (row["id"] is DBNull) throw new BuildObjectException("No id found");
  164. Brol brol = new Brol((int)row["id"]);
  165. if (!(row["itemTitle"] is DBNull))
  166. {
  167. brol.Title = (String)row["itemTitle"];
  168. }
  169. if (!(row["synopsis"] is DBNull))
  170. {
  171. brol.Synopsis = (String)row["synopsis"];
  172. }
  173. if (!(row["cote"] is DBNull))
  174. {
  175. brol.Cotation = (int)row["cote"];
  176. }
  177. if (!(row["comment"] is DBNull))
  178. {
  179. brol.Comment = (String)row["comment"];
  180. }
  181. if (!(row["pubDate"] is DBNull))
  182. {
  183. brol.Date = (DateTime)row["pubDate"];
  184. }
  185. if (!(row["typeId"] is DBNull))
  186. {
  187. String tName = (row["tName"] is DBNull) ? "" : (String)row["tName"];
  188. brol.BrolType = new BrolType((int)row["typeId"],tName);
  189. }
  190. return brol;
  191. }
  192.  
  193. /// <summary>
  194. /// We may only add or delete a relation with an existing category
  195. /// </summary>
  196. /// <param name="brol"></param>
  197. /// <param name="dbCon"></param>
  198. /// <param name="dbDa"></param>
  199. private void updateCategoriesRelations(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
  200. {
  201. dbDa.InsertCommand = dbCon.CreateCommand();
  202. dbDa.DeleteCommand = dbCon.CreateCommand();
  203. foreach (BrolCategory category in brol.Categories)
  204. {
  205. switch (category.Status)
  206. {
  207. case STATUS.toAdd:
  208. addCategoryRelation(category, brol.Id, dbDa.InsertCommand);
  209. break;
  210. case STATUS.toDelete:
  211. deleteCategoryRelation(category, brol.Id, dbDa.DeleteCommand);
  212. break;
  213. }
  214. }
  215. }
  216.  
  217. /// <summary>
  218. /// We may only add or delete a relation with an existing serie
  219. /// </summary>
  220. /// <param name="brol"></param>
  221. /// <param name="dbCon"></param>
  222. /// <param name="dbDa"></param>
  223. private void updateSerieItemsRelations(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
  224. {
  225. dbDa.InsertCommand = dbCon.CreateCommand();
  226. dbDa.DeleteCommand = dbCon.CreateCommand();
  227. foreach (SerieItem serieItem in brol.SerieItems)
  228. {
  229. switch (serieItem.getSerie().Status)
  230. {
  231. case STATUS.toAdd:
  232. addSerieItemsRelation(serieItem, brol.Id, dbDa.InsertCommand);
  233. break;
  234. case STATUS.toDelete:
  235. deleteSerieItemRelation(serieItem, brol.Id, dbDa.DeleteCommand);
  236. break;
  237. }
  238. }
  239. }
  240.  
  241. /// <summary>
  242. /// Insert a new relation between a brol and a category
  243. /// </summary>
  244. /// <param name="category">Category to add</param>
  245. /// <param name="brolId"></param>
  246. /// <param name="com"></param>
  247. /// <returns></returns>
  248. internal bool addCategoryRelation(BrolCategory category, int brolId, DbCommand com)
  249. {
  250. if (!category.Status.Equals(STATUS.toAdd))
  251. {
  252. notify(new Notification(Notification.VERBOSE.error, "ERREUR insertCategory() : status de la catégorie != toAdd", this));
  253. return false;
  254. }
  255. StringBuilder str = new StringBuilder();
  256. str.Append("INSERT INTO categoryItemRelation (itemId, categoryId) VALUES(");
  257. str.Append(brolId);
  258. str.Append(",");
  259. str.Append(category.Id);
  260. str.Append(");");
  261. com.CommandText = str.ToString();
  262. int r = 0;
  263. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  264. try
  265. {
  266. r = com.ExecuteNonQuery();
  267. }
  268. catch (Exception e)
  269. {
  270. notify(new Notification(Notification.VERBOSE.error, "Ajout d'une catégorie pour un ouvrage", e, this));
  271. }
  272.  
  273. return r==1;
  274. }
  275.  
  276. /// <summary>
  277. /// Insert a new relation between a brol and a serie
  278. /// </summary>
  279. /// <param name="serie">Serie to add</param>
  280. /// <param name="brolId"></param>
  281. /// <param name="com"></param>
  282. /// <returns></returns>
  283. internal bool addSerieItemsRelation(SerieItem serieItem, int brolId, DbCommand com)
  284. {
  285. if (!serieItem.getSerie().Status.Equals(STATUS.toAdd))
  286. {
  287. notify(new Notification(Notification.VERBOSE.error, "ERREUR insertSerie() : status de la série != toAdd", this));
  288. return false;
  289. }
  290. StringBuilder str = new StringBuilder();
  291. str.Append("INSERT INTO serieItemRelation (itemId, serieId, rank) VALUES(");
  292. str.Append(brolId);
  293. str.Append(",");
  294. str.Append(serieItem.getSerie().Id);
  295. str.Append(",'");
  296. str.Append(MySQLUtils.escapeAndTrim(serieItem.getRank()));
  297. str.Append("');");
  298. com.CommandText = str.ToString();
  299. int r = 0;
  300. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  301. try
  302. {
  303. r = com.ExecuteNonQuery();
  304. }
  305. catch (Exception e)
  306. {
  307. notify(new Notification(Notification.VERBOSE.error, "Ajout d'une série pour un ouvrage", e, this));
  308. }
  309.  
  310. return r == 1;
  311. }
  312.  
  313. /// <summary>
  314. /// Delete a relation between a brol and a category
  315. /// </summary>
  316. /// <param name="catId"></param>
  317. /// <param name="brolId"></param>
  318. /// <param name="com"></param>
  319. /// <returns></returns>
  320. private bool deleteCategoryRelation(BrolCategory category, int brolId, DbCommand com)
  321. {
  322. if (!category.Status.Equals(STATUS.toDelete))
  323. {
  324. notify(new Notification(Notification.VERBOSE.error, "ERREUR deleteCategory() : status de la catégorie != toDelete", this));
  325. return false;
  326. }
  327. com.CommandText = "DELETE FROM categoryItemRelation WHERE itemId = " +
  328. brolId +
  329. " AND categoryId = " + category.Id;
  330. int r = 0;
  331. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  332. try
  333. {
  334. r = com.ExecuteNonQuery();
  335. }
  336. catch (Exception e)
  337. {
  338. notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression d'une catégorie pour un ouvrage", e, this));
  339. }
  340.  
  341. return r == 1;
  342. }
  343.  
  344. /// <summary>
  345. /// Delete a relation between a brol and a serie
  346. /// </summary>
  347. /// <param name="brolId"></param>
  348. /// <param name="com"></param>
  349. /// <returns></returns>
  350. private bool deleteSerieItemRelation(SerieItem serieItem, int brolId, DbCommand com)
  351. {
  352. if (!serieItem.getSerie().Status.Equals(STATUS.toDelete))
  353. {
  354. notify(new Notification(Notification.VERBOSE.error, "ERREUR deleteSerie() : status de la série != toDelete", this));
  355. return false;
  356. }
  357. com.CommandText = "DELETE FROM serieItemRelation WHERE itemId = " +
  358. brolId +
  359. " AND serieId = " + serieItem.getSerie().Id;
  360. int r = 0;
  361. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  362. try
  363. {
  364. r = com.ExecuteNonQuery();
  365. }
  366. catch (Exception e)
  367. {
  368. notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression d'une série pour un ouvrage", e, this));
  369. }
  370.  
  371. return r == 1;
  372. }
  373.  
  374. /// <summary>
  375. /// We may only add or delete a relation with an existing person, or update infos
  376. /// </summary>
  377. /// <param name="brol"></param>
  378. /// <param name="dbCon"></param>
  379. /// <param name="dbDa"></param>
  380. private void updateActors(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
  381. {
  382. dbDa.InsertCommand = dbCon.CreateCommand();
  383. dbDa.DeleteCommand = dbCon.CreateCommand();
  384. foreach (Actor actor in brol.Actors)
  385. {
  386. switch (actor.Status)
  387. {
  388. case STATUS.toAdd:
  389. insertActorRole(actor, brol, dbDa.InsertCommand);
  390. break;
  391. case STATUS.toDelete:
  392. deleteActorRole(actor, brol.Id, dbDa.DeleteCommand);
  393. break;
  394. case STATUS.toUpdate:
  395. updateActorRole(actor, brol.Id, dbDa.DeleteCommand);
  396. break;
  397. }
  398. }
  399. }
  400.  
  401. /// <summary>
  402. /// Insert a new relation between a brol and an actor
  403. /// </summary>
  404. /// <param name="actor">(Actor) actor to add into relation</param>
  405. /// <param name="brolId">(int) Brol identifier</param>
  406. /// <param name="com">(DbCommand) command unsed into connection</param>
  407. /// <returns></returns>
  408. internal bool insertActorRole(Actor actor, Brol brol, DbCommand com)
  409. {
  410. if (!actor.Status.Equals(STATUS.toAdd))
  411. {
  412. notify(new Notification(Notification.VERBOSE.lowError, "Impossible d'ajouter le rôle pour la personne", this));
  413. notify(new Notification(Notification.VERBOSE.error, "Le status de l'acteur est différent de \"toAdd\"", this));
  414. return false;
  415. }
  416. /*
  417.   * No need to insert here into role table, because only existing role value
  418.   * may be here. Roles management is for another method.
  419.   */
  420. StringBuilder str = new StringBuilder("INSERT INTO actor (itemId, personId, roleId, roleValue) VALUES(");
  421. str.Append(brol.Id);
  422. str.Append(", ");
  423. str.Append(actor.Id);
  424. str.Append(", ");
  425. str.Append((actor.Role != null) ? actor.Role.RoleId.ToString() : "0");
  426. str.Append(", '");
  427. if (actor.Role != null && actor.Role.Value != null)
  428. {
  429. if (actor.Role.Value.Length >= 100)
  430. {
  431. actor.Role.Value = actor.Role.Value.Remove(96) + "...";
  432. }
  433. str.Append(MySQLUtils.escapeAndTrim(actor.Role.Value));
  434. }
  435. str.Append("');");
  436. com.CommandText = str.ToString();
  437. int r = 0;
  438. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  439. try
  440. {
  441. r = com.ExecuteNonQuery();
  442. }
  443. catch (Exception e)
  444. {
  445. notify(new Notification(Notification.VERBOSE.advancedOperation,"Ajout d'un rôle pour une personne", e, this));
  446. }
  447.  
  448. return r == 1;
  449. }
  450.  
  451. /// <summary>
  452. /// Delete a relation between a brol and an actor
  453. /// </summary>
  454. /// <param name="actorId">(int) Person identifier</param>
  455. /// <param name="brolId">(int) Brol identifier</param>
  456. /// <param name="com"></param>
  457. /// <returns></returns>
  458. private bool deleteActorRole(Actor actor, int brolId, DbCommand com)
  459. {
  460. if (!actor.Status.Equals(STATUS.toDelete))
  461. {
  462. notify(new Notification(Notification.VERBOSE.lowError, "Rôle non supprimé : " + actor.ToString(), this));
  463. notify(new Notification(Notification.VERBOSE.error, "Le status de l'acteur est différent de \"toDelete\"", this));
  464. return false;
  465. }
  466. if (brolId==Config.BibliobrolId)
  467. {
  468. notify(new Notification(Notification.VERBOSE.lowError, "Impossible de supprimer le rôle par défaut", this));
  469. return false;
  470. }
  471. StringBuilder str = new StringBuilder();
  472. str.Append("DELETE FROM actor WHERE itemId = ");
  473. str.Append(brolId);
  474. str.Append(" AND personId = ");
  475. str.Append(actor.Id);
  476. com.CommandText = str.ToString();
  477. int r = 0;
  478. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  479. try
  480. {
  481. r = com.ExecuteNonQuery();
  482. }
  483. catch (Exception e)
  484. {
  485. notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression du rôle " + actor.ToString(), e, this));
  486. }
  487.  
  488. return r > 0;
  489. }
  490.  
  491. /// <summary>
  492. /// Update the infos of an existing relation between a brol and an actor
  493. /// </summary>
  494. /// <param name="actor">(Actor) actor to retreive relation's infos</param>
  495. /// <param name="brolId">(int) Brol identifier</param>
  496. /// <param name="com">(DbCommand) command unsed into connection</param>
  497. /// <returns></returns>
  498. internal bool updateActorRole(Actor actor, int brolId, DbCommand com)
  499. {
  500. /*
  501.   * No need to insert here into role table, because only existing role value
  502.   * may be here. Roles management is for another method.
  503.   */
  504. StringBuilder str = new StringBuilder("UPDATE actor SET roleValue='");
  505. if (actor.Role != null && actor.Role.Value != null)
  506. {
  507. if (actor.Role.Value.Length >= 100)
  508. {
  509. actor.Role.Value = actor.Role.Value.Remove(96) + "...";
  510. }
  511. str.Append(MySQLUtils.escapeAndTrim(actor.Role.Value));
  512. }
  513. str.Append("' WHERE itemId=");
  514. str.Append(brolId);
  515. str.Append(" AND personId=");
  516. str.Append(actor.Id);
  517. str.Append(" AND roleId=");
  518. str.Append((actor.Role != null) ? actor.Role.RoleId.ToString() : "0");
  519. com.CommandText = str.ToString();
  520. int r = 0;
  521. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  522. try
  523. {
  524. r = com.ExecuteNonQuery();
  525. }
  526. catch (Exception e)
  527. {
  528. notify(new Notification(Notification.VERBOSE.advancedOperation, "Ajout d'un rôle pour une personne", e, this));
  529. }
  530.  
  531. return r == 1;
  532. }
  533. #endregion
  534.  
  535. #region IBrolDao Members
  536.  
  537. /// <summary>
  538. /// Load roles for a person
  539. /// </summary>
  540. /// <param name="person"></param>
  541. /// <returns></returns>
  542. public List<ActorRole> loadRoles(Person person)
  543. {
  544. List<ActorRole> roles = new List<ActorRole>();
  545. DbConnection dbCon = dbpf.CreateConnection();
  546. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  547. dbDa.SelectCommand = dbCon.CreateCommand();
  548. dbCon.ConnectionString = conStr;
  549. StringBuilder str = new StringBuilder("SELECT role.roleName, item.id as brolId, a.roleValue, p.lastName, p.id, item.itemTitle");
  550. str.Append(" FROM role RIGHT JOIN (");
  551. str.Append("person AS p INNER JOIN (");
  552. str.Append("item INNER JOIN actor AS a ON item.id = a.itemId");
  553. str.Append(") ON p.id = a.personId) ON role.id = a.roleId");
  554. str.Append(" WHERE (p.id=");
  555. str.Append(person.Id);
  556. str.Append(" AND a.itemId>0);");
  557.  
  558. dbDa.SelectCommand.CommandText = str.ToString();
  559. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  560. DataTable rolesDt = new DataTable();
  561. try
  562. {
  563. dbDa.Fill(rolesDt);
  564. foreach (DataRow row in rolesDt.Rows)
  565. {
  566. if (!(row["roleName"] is DBNull))
  567. {
  568. string roleValue = (row["roleValue"] is DBNull) ? "":(String)row["roleValue"];
  569. roles.Add(
  570. (row["itemTitle"] is DBNull) ?
  571. new ActorRole((int)row["brolId"], (String)row["roleName"], roleValue) :
  572. new ActorRole((int)row["brolId"], (String)row["roleName"], roleValue, (String)row["itemTitle"])
  573. );
  574. }
  575. }
  576. }
  577. catch (Exception e)
  578. {
  579. notify(new Notification(Notification.VERBOSE.error,"Chargement des rôles de personnes", e, this));
  580. }
  581. rolesDt.Dispose();
  582. dbCon.Close();
  583. return roles;
  584. }
  585.  
  586. /// <summary>
  587. /// Load the default role for a person
  588. /// </summary>
  589. /// <param name="person"></param>
  590. /// <returns></returns>
  591. public ActorRole loadDefaultRole(Person person)
  592. {
  593. ActorRole role = null;
  594. DbConnection dbCon = dbpf.CreateConnection();
  595. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  596. dbDa.SelectCommand = dbCon.CreateCommand();
  597. dbCon.ConnectionString = conStr;
  598. StringBuilder str = new StringBuilder("SELECT role.roleName, a.roleId");
  599. str.Append(" FROM role RIGHT JOIN (actor AS a) ON role.id = a.roleId");
  600. str.Append(" WHERE a.personId=");
  601. str.Append(person.Id);
  602. str.Append(" AND a.itemId = ");
  603. str.Append(Config.BibliobrolId);
  604. str.Append(";");
  605.  
  606. dbDa.SelectCommand.CommandText = str.ToString();
  607. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  608. DataTable rolesDt = new DataTable();
  609. try
  610. {
  611. dbDa.Fill(rolesDt);
  612. foreach (DataRow row in rolesDt.Rows)
  613. {
  614. if (!(row["roleName"] is DBNull))
  615. {
  616. role = new ActorRole((int)row["roleId"], (String)row["roleName"]);
  617. }
  618. }
  619. }
  620. catch (Exception e)
  621. {
  622. notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle par défaut d'une personne", e, this));
  623. }
  624. rolesDt.Dispose();
  625. dbCon.Close();
  626. return role;
  627. }
  628.  
  629. /// <summary>
  630. /// set the default role for a person
  631. /// </summary>
  632. /// <param name="person"></param>
  633. /// <param name="role"></param>
  634. public void setDefaultRole(Person person, ActorRole role)
  635. {
  636. DbConnection dbCon = dbpf.CreateConnection();
  637. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  638. DbCommand com = dbCon.CreateCommand();
  639. dbCon.ConnectionString = conStr;
  640. dbCon.Open();
  641. StringBuilder str = new StringBuilder("UPDATE actor set roleId = ");
  642. str.Append(role.RoleId);
  643. str.Append(", roleValue = '");
  644. if (role.Value != null) str.Append(role.Value);
  645. str.Append("' WHERE personId = ");
  646. str.Append(person.Id);
  647. str.Append(" AND itemId = ");
  648. str.Append(Config.BibliobrolId);
  649. str.Append(");");
  650. com.CommandText = str.ToString();
  651. int r = 0;
  652. try
  653. {
  654. r = com.ExecuteNonQuery();
  655.  
  656. }
  657. catch (Exception)
  658. {
  659. try
  660. {
  661. if (r < 1)
  662. {
  663. str = new StringBuilder("INSERT INTO actor (itemId, personId, roleId, roleValue) VALUES(");
  664. str.Append(Config.BibliobrolId);
  665. str.Append(", ");
  666. str.Append(person.Id);
  667. str.Append(", ");
  668. str.Append(role.RoleId);
  669. str.Append(", '");
  670. if (role.Value != null) str.Append(role.Value);
  671. str.Append("');");
  672. com.CommandText = str.ToString();
  673. r = com.ExecuteNonQuery();
  674. }
  675. }
  676. catch (Exception e)
  677. {
  678. notify(new Notification(Notification.VERBOSE.advancedOperation, "Définition du rôle par défaut pour une personne", e, this));
  679. }
  680. }
  681. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  682. dbCon.Close();
  683. }
  684.  
  685. /// <summary>
  686. /// get a roles with the id
  687. /// </summary>
  688. /// <param name="roleId">(int) id of the requested role</param>
  689. /// <returns>(ActorRole) requested role</returns>
  690. public ActorRole loadRole(int roleId)
  691. {
  692. ActorRole role = null;
  693. DbConnection dbCon = dbpf.CreateConnection();
  694. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  695. dbDa.SelectCommand = dbCon.CreateCommand();
  696. dbCon.ConnectionString = conStr;
  697. dbDa.SelectCommand.CommandText = String.Format(
  698. "SELECT id, roleName, info FROM role WHERE id = {0}",
  699. roleId
  700. );
  701. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  702. DataTable rolesDt = new DataTable();
  703. try
  704. {
  705. dbDa.Fill(rolesDt);
  706. if (rolesDt.Rows.Count == 1)
  707. {
  708. role = new ActorRole(
  709. (int)rolesDt.Rows[0]["id"],
  710. rolesDt.Rows[0]["roleName"] as String,
  711. "",
  712. (rolesDt.Rows[0]["info"] is DBNull)? "":rolesDt.Rows[0]["info"] as String
  713. );
  714. }
  715. }
  716. catch (Exception e)
  717. {
  718. notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle", e, this));
  719. }
  720. rolesDt.Dispose();
  721. dbCon.Close();
  722. return role;
  723. }
  724.  
  725. /// <summary>
  726. /// get a roles with the name
  727. /// </summary>
  728. /// <param name="roleName">(string) name of the requested role</param>
  729. /// <returns>(ActorRole) requested role</returns>
  730. public ActorRole loadRole(string roleName)
  731. {
  732. ActorRole role = null;
  733. DbConnection dbCon = dbpf.CreateConnection();
  734. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  735. dbDa.SelectCommand = dbCon.CreateCommand();
  736. dbCon.ConnectionString = conStr;
  737. dbDa.SelectCommand.CommandText = String.Format(
  738. "SELECT id, roleName, info FROM role WHERE roleName = '{0}'",
  739. roleName
  740. );
  741. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  742. DataTable rolesDt = new DataTable();
  743. try
  744. {
  745. dbDa.Fill(rolesDt);
  746. if (rolesDt.Rows.Count == 1)
  747. {
  748. role = new ActorRole(
  749. (int)rolesDt.Rows[0]["id"],
  750. rolesDt.Rows[0]["roleName"] as String,
  751. "",
  752. (rolesDt.Rows[0]["info"] is DBNull) ? "" : rolesDt.Rows[0]["info"] as String
  753. );
  754. }
  755. }
  756. catch (Exception e)
  757. {
  758. notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle", e, this));
  759. }
  760. rolesDt.Dispose();
  761. dbCon.Close();
  762. return role;
  763. }
  764.  
  765. /// <summary>
  766. /// get a list of roles with the same name
  767. /// </summary>
  768. /// <param name="roleName">(string) name of the requested roles</param>
  769. /// <returns>(List of ActorRole) requested roles</returns>
  770. public List<ActorRole> loadRoles(string roleName)
  771. {
  772. List<ActorRole> roles = new List<ActorRole>();
  773. DbConnection dbCon = dbpf.CreateConnection();
  774. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  775. dbDa.SelectCommand = dbCon.CreateCommand();
  776. dbCon.ConnectionString = conStr;
  777. dbDa.SelectCommand.CommandText = String.Format(
  778. "SELECT id, roleName, info FROM role WHERE UCASE(roleName) LIKE UCASE('{0}')",
  779. roleName
  780. );
  781. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  782. DataTable rolesDt = new DataTable();
  783. try
  784. {
  785. dbDa.Fill(rolesDt);
  786. foreach(DataRow row in rolesDt.Rows)
  787. {
  788. roles.Add(
  789. new ActorRole(
  790. (int)row["id"],
  791. row["roleName"] as String,
  792. "",
  793. (row["info"] is DBNull) ? "" : row["info"] as String
  794. )
  795. );
  796. }
  797. }
  798. catch (Exception e)
  799. {
  800. notify(new Notification(Notification.VERBOSE.error, "Chargement des rôles", e, this));
  801. }
  802. rolesDt.Dispose();
  803. dbCon.Close();
  804. return roles;
  805. }
  806.  
  807. /// <summary>
  808. /// Insert a new role. Don't check if the role name exists in the db. You must do it before.
  809. /// </summary>
  810. /// <param name="actor">(ActorRole) role to add</param>
  811. /// <returns></returns>
  812. public bool insertRole(ActorRole role)
  813. {
  814. DbConnection dbCon = dbpf.CreateConnection();
  815. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  816. DbCommand com = dbCon.CreateCommand();
  817. dbCon.ConnectionString = conStr;
  818. dbCon.Open();
  819.  
  820. com.CommandText = string.Format(
  821. "INSERT INTO role (roleName, info) VALUES('{0}','{1}');",
  822. role.Name,
  823. role.Info
  824. );
  825. int r = com.ExecuteNonQuery();
  826. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  827.  
  828. dbCon.Close();
  829. return r == 1;
  830. }
  831.  
  832. /// <summary>
  833. /// Load all roles strings
  834. /// </summary>
  835. /// <returns></returns>
  836. public List<ActorRole> loadRoles()
  837. {
  838. List<ActorRole> roles = new List<ActorRole>();
  839. DbConnection dbCon = dbpf.CreateConnection();
  840. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  841. dbDa.SelectCommand = dbCon.CreateCommand();
  842. dbCon.ConnectionString = conStr;
  843. String str = "SELECT id, roleName FROM role";
  844. dbDa.SelectCommand.CommandText = str;
  845. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  846. DataTable rolesDt = new DataTable();
  847. try
  848. {
  849. dbDa.Fill(rolesDt);
  850. foreach (DataRow row in rolesDt.Rows)
  851. {
  852. if (!(row["roleName"] is DBNull))
  853. {
  854. roles.Add(new ActorRole((int)row["id"], (String)row["roleName"]));
  855. }
  856. }
  857. }
  858. catch (Exception e)
  859. {
  860. notify(new Notification(Notification.VERBOSE.error, "Chargement des rôles de personnes", e, this));
  861. }
  862. rolesDt.Dispose();
  863. dbCon.Close();
  864. return roles;
  865. }
  866.  
  867. /// <summary>
  868. /// Load roles strings for a brolType
  869. /// </summary>
  870. /// <param name="type"></param>
  871. /// <returns></returns>
  872. public List<ActorRole> loadRoles(BrolType type)
  873. {
  874. List<ActorRole> roles = new List<ActorRole>();
  875. DbConnection dbCon = dbpf.CreateConnection();
  876. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  877. dbDa.SelectCommand = dbCon.CreateCommand();
  878. dbCon.ConnectionString = conStr;
  879. String str = "SELECT id, roleName FROM role WHERE typeId = " + type.Id;
  880. dbDa.SelectCommand.CommandText = str;
  881. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  882. DataTable rolesDt = new DataTable();
  883. try
  884. {
  885. dbDa.Fill(rolesDt);
  886. foreach (DataRow row in rolesDt.Rows)
  887. {
  888. if (!(row["roleName"] is DBNull))
  889. {
  890. roles.Add(new ActorRole((int)row["id"], (String)row["roleName"]));
  891. }
  892. }
  893. }
  894. catch (Exception e)
  895. {
  896. notify(new Notification(Notification.VERBOSE.advancedOperation, "Chargement des rôles de personnes", e, this));
  897. }
  898. rolesDt.Dispose();
  899. dbCon.Close();
  900. return roles;
  901. }
  902.  
  903. /// <summary>
  904. /// Load all available categories for a type from the Access database.
  905. /// i.e. for a film : Science-fiction, Horror, etc.
  906. /// </summary>
  907. /// <param name="typeId">(int) selected type (film, book, etc.) negative value to load all</param>
  908. /// <returns>List of BrolCategories</returns>
  909. public List<BrolCategory> loadCategories(int typeId)
  910. {
  911. List<BrolCategory> cats = new List<BrolCategory>();
  912. DbConnection dbCon = dbpf.CreateConnection();
  913. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  914. dbDa.SelectCommand = dbCon.CreateCommand();
  915. dbCon.ConnectionString = conStr;
  916. String str = "SELECT id, name FROM category";
  917. if(typeId>=0)str+=" WHERE typeId = " + typeId;
  918. dbDa.SelectCommand.CommandText = str;
  919. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  920. DataTable catsDt = new DataTable();
  921. try
  922. {
  923. dbDa.Fill(catsDt);
  924. foreach (DataRow row in catsDt.Rows)
  925. {
  926. if (!(row["id"] is DBNull) && !(row["name"] is DBNull))
  927. {
  928. cats.Add(new BrolCategory((int)row["id"], (String)row["name"]));
  929. }
  930. }
  931. }
  932. catch (Exception e)
  933. {
  934. notify(new Notification(Notification.VERBOSE.advancedOperation, "Chargement des catégories", e, this));
  935. }
  936. catsDt.Dispose();
  937. dbCon.Close();
  938. return cats;
  939. }
  940.  
  941. /// <summary>
  942. /// Insert a brol (i.e. a film) into the Access database
  943. /// </summary>
  944. /// <param name="brol">(Brol) brol to insert</param>
  945. /// <returns>(int) new brol id</returns>
  946. public int insertBrol(Brol brol)
  947. {
  948. DbConnection dbCon = dbpf.CreateConnection();
  949. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  950. dbDa.InsertCommand = dbCon.CreateCommand();
  951. dbCon.ConnectionString = conStr;
  952. dbCon.Open();
  953. insertBrol(brol, dbCon, dbDa, true);
  954. dbCon.Close();
  955. return brol.Id;
  956. }
  957.  
  958. public int insertBrols(List<Brol> brols, bool insertMediabrol)
  959. {
  960. DbConnection dbCon = dbpf.CreateConnection();
  961. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  962. dbDa.InsertCommand = dbCon.CreateCommand();
  963. dbCon.ConnectionString = conStr;
  964. dbCon.Open();
  965. int insertedNb = 0;
  966. foreach (Brol brol in brols)
  967. {
  968. insertBrol(brol, dbCon, dbDa, true);
  969. insertedNb++;
  970. }
  971. dbCon.Close();
  972. return insertedNb;
  973. }
  974.  
  975. /// <summary>
  976. /// Load a brol with the selected id from the Access database.
  977. /// If this brol shoud be modified, editing bool arg is true
  978. /// to avoid concurent modifications (nobody else can save this brol).
  979. /// </summary>
  980. /// <param name="id">(int) Id of the selected bool</param>
  981. /// <param name="editing">(bool) Shoud be modified or not</param>
  982. /// <returns>Selected bool, or a new bool if _ found</returns>
  983. public Brol loadBrol(int id, bool editing)
  984. {
  985. if (id < 0) return new Brol();
  986. int[] ids = { id };
  987. Brol brol;
  988. List<Brol> brols = NoChangeExporterDao.Instance.loadSelectedBrols(ids);
  989. if (brols.Count > 0)
  990. {
  991. brol = brols[0];
  992. if (editing) lockBrol(id);
  993. }
  994. else brol = new Brol();
  995. return brol;
  996. }
  997.  
  998. /// <summary>
  999. /// Load all brols for a type from the Access database.
  1000. /// i.e. films, books, etc.
  1001. /// typeId 0 allow to load all types
  1002. /// </summary>
  1003. /// <returns>List of Brols</returns>
  1004. public List<Brol> loadBrols(int typeId)
  1005. {
  1006. notify(new Notification(Notification.VERBOSE.error, "List<Brol> loadBrols() : NOT IMPLEMENTED", this));
  1007. return new List<Brol>();
  1008. }
  1009.  
  1010. /// <summary>
  1011. /// Load all brols for a serie from the Access database.
  1012. /// </summary>
  1013. /// <param name="serie"></param>
  1014. /// <returns>List of Brols</returns>
  1015. public List<Brol> loadBrols(Serie serie)
  1016. {
  1017. throw new Exception("Not yet implemented!!!");
  1018.  
  1019. }
  1020.  
  1021. /// <summary>
  1022. /// Load brols value objects (only selected fields) for a type from the Access database.
  1023. /// </summary>
  1024. /// <param name="fields">List of fields to load for the brols</param>
  1025. /// <param name="typeId">
  1026. /// (int) selected type (film, book, etc.)
  1027. /// 0 allow to load all types
  1028. /// </param>
  1029. /// <returns>DataTable with selected fields for Brols</returns>
  1030. public DataTable loadDataTableVos(List<DAOUtils.BROL_FIELD> fields, int typeId)
  1031. {
  1032. DbConnection dbCon = dbpf.CreateConnection();
  1033. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1034. dbDa.SelectCommand = dbCon.CreateCommand();
  1035. dbCon.ConnectionString = conStr;
  1036. StringBuilder str = new StringBuilder("SELECT ");
  1037. if (fields.Contains(DAOUtils.BROL_FIELD.serie))
  1038. {
  1039. str.Append("DISTINCTROW ");
  1040. }
  1041. str.Append(" item.id");
  1042. if (fields.Contains(DAOUtils.BROL_FIELD.title))
  1043. {
  1044. str.Append(", item.itemTitle");
  1045. }
  1046. if (fields.Contains(DAOUtils.BROL_FIELD.date))
  1047. {
  1048. str.Append(", item.pubDate");
  1049. }
  1050. if (fields.Contains(DAOUtils.BROL_FIELD.serie))
  1051. {
  1052. str.Append(", serie.name&' '&serieItemRelation.rank");
  1053. }
  1054. if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
  1055. {
  1056. str.Append(", item.cote ");
  1057. }
  1058.  
  1059. if (fields.Contains(DAOUtils.BROL_FIELD.serie))
  1060. {
  1061. str.Append(" FROM serie RIGHT JOIN (item LEFT JOIN serieItemRelation ON item.id = serieItemRelation.itemId) ON serie.id = serieItemRelation.serieId");
  1062. }
  1063. else
  1064. {
  1065. str.Append(" FROM item ");
  1066. }
  1067. if (typeId != 0)
  1068. {
  1069. str.Append(" WHERE item.typeId = ");
  1070. str.Append(typeId);
  1071. }
  1072. if (fields.Contains(DAOUtils.BROL_FIELD.serie))
  1073. {
  1074. str.Append(" GROUP BY serie.name,");
  1075. if (fields.Contains(DAOUtils.BROL_FIELD.title))
  1076. {
  1077. str.Append(" item.itemTitle,");
  1078. }
  1079. if (fields.Contains(DAOUtils.BROL_FIELD.date))
  1080. {
  1081. str.Append(" item.pubDate,");
  1082. }
  1083. if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
  1084. {
  1085. str.Append(" item.cote ,");
  1086. }
  1087. str.Append(" serieItemRelation.rank, item.id");
  1088. }
  1089. else
  1090. {
  1091. str.Append(" ORDER BY");
  1092. if (fields.Contains(DAOUtils.BROL_FIELD.title))
  1093. {
  1094. str.Append(" item.itemTitle,");
  1095. }
  1096. if (fields.Contains(DAOUtils.BROL_FIELD.date))
  1097. {
  1098. str.Append(" item.pubDate,");
  1099. }
  1100. if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
  1101. {
  1102. str.Append(" item.cote ,");
  1103. }
  1104. str.Append(" item.id");
  1105. }
  1106. /*
  1107.   SELECT DISTINCTROW item.id, serie.name&' '&serieItemRelation.rank AS [rank], item.itemTitle
  1108. FROM serie RIGHT JOIN (item LEFT JOIN serieItemRelation ON item.id = serieItemRelation.itemId) ON serie.id = serieItemRelation.serieId
  1109. WHERE typeId = 2
  1110.  
  1111. GROUP BY serie.name, serieItemRelation.rank, item.id, item.itemTitle
  1112.   */
  1113. dbDa.SelectCommand.CommandText = str.ToString();
  1114. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  1115. DataTable brolDt = new DataTable();
  1116. try
  1117. {
  1118. dbDa.Fill(brolDt);
  1119. }
  1120. catch (Exception e)
  1121. {
  1122. notify(new Notification(Notification.VERBOSE.error, "Chargement des ouvrages",e, this));
  1123. }
  1124. brolDt.Dispose();
  1125. dbCon.Close();
  1126. return brolDt;
  1127. }
  1128.  
  1129. /// <summary>
  1130. /// Store new values for a brol.
  1131. /// Verify if some categories or actors had been deleted, and delete it from the persistant layer.
  1132. /// Add new categories or actors if exists.
  1133. /// Update existing categories or actors.
  1134. /// </summary>
  1135. /// <param name="person">(Brol) brol with new values to store</param>
  1136. /// <returns>(bool) true if update is done</returns>
  1137. public bool updateBrol(Brol brol)
  1138. {
  1139. StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
  1140. strDisp.Append(brol.Title);
  1141. DbConnection dbCon = dbpf.CreateConnection();
  1142. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1143. dbDa.UpdateCommand = dbCon.CreateCommand();
  1144. dbCon.ConnectionString = conStr;
  1145. dbCon.Open();
  1146. if (brol.BrolLocked)
  1147. {
  1148. strDisp.Append("\" ne peut être mis à jour car il est déjà ouvert en mode modification.");
  1149. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1150. return false;
  1151. }
  1152. bool updated = updateBrol(brol, dbCon, dbDa, true, strDisp);
  1153. dbCon.Close();
  1154. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1155. return updated;
  1156. }
  1157.  
  1158. /// <summary>
  1159. /// Lock brol to avoid concurent modifications.
  1160. /// </summary>
  1161. /// <param name="id">(int) brol's id to lock</param>
  1162. /// <returns>(bool) false if a problem occurs (todo : if already locked)</returns>
  1163. public bool lockBrol(int id)
  1164. {
  1165. return lockUnlockItem(id, true);
  1166. }
  1167.  
  1168. /// <summary>
  1169. /// unlock brol to allow modifications.
  1170. /// </summary>
  1171. /// <param name="id">(int) brol's id to unlock</param>
  1172. public void unlockBrol(int id)
  1173. {
  1174. lockUnlockItem(id, false);
  1175. }
  1176.  
  1177. /// <summary>
  1178. /// Delete a brol from the Access database,
  1179. /// and delete all associated actors and categories
  1180. /// </summary>
  1181. /// <param name="p">(Brol) brol to delete</param>
  1182. /// <returns>(bool) true if deleted</returns>
  1183. public bool deleteBrol(Brol brol)
  1184. {
  1185. if (brol.Id == Config.BibliobrolId)
  1186. {
  1187. notify(new Notification(Notification.VERBOSE.lowError, "Impossible de supprimer l'application par défaut", this));
  1188. return false;
  1189. }
  1190. bool deleted = true;
  1191. DbConnection dbCon = dbpf.CreateConnection();
  1192. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1193. dbDa.DeleteCommand = dbCon.CreateCommand();
  1194. dbCon.ConnectionString = conStr;
  1195. dbCon.Open();
  1196. //delete brol
  1197. String str = "DELETE FROM item WHERE id=" + brol.Id;
  1198. dbDa.DeleteCommand.CommandText = str;
  1199. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
  1200. try
  1201. {
  1202. deleted = (dbDa.DeleteCommand.ExecuteNonQuery() == 1);
  1203. }
  1204. catch (Exception e)
  1205. {
  1206. notify(new Notification(Notification.VERBOSE.error, "Impossible de supprimer l'ouvrage " + brol.Title,e, this));
  1207. }
  1208. //delete actors relations
  1209. str = "DELETE FROM actor WHERE itemId = " + brol.Id;
  1210. dbDa.DeleteCommand.CommandText = str;
  1211. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
  1212. try
  1213. {
  1214. dbDa.DeleteCommand.ExecuteNonQuery();
  1215. }
  1216. catch (Exception e)
  1217. {
  1218. notify(new Notification(Notification.VERBOSE.error, "Suppression des personnes pour l'ouvrage " + brol.Title,e, this));
  1219. }
  1220. //delete categories relations
  1221. str = "DELETE FROM categoryItemRelation WHERE itemId = " + brol.Id;
  1222. dbDa.DeleteCommand.CommandText = str;
  1223. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
  1224. try
  1225. {
  1226. dbDa.DeleteCommand.ExecuteNonQuery();
  1227. }
  1228. catch (Exception e)
  1229. {
  1230. notify(new Notification(Notification.VERBOSE.error, "Suppression des catégories pour l'ouvrage " + brol.Title,e, this));
  1231. }
  1232. dbCon.Close();
  1233. StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
  1234. strDisp.Append(brol.Title);
  1235. strDisp.Append((deleted) ? "\" est supprimé." : "\" ne peut être supprimé.");
  1236. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1237. return deleted;
  1238. }
  1239.  
  1240. private List<String> getBatch()
  1241. {
  1242. List<String> strList = new List<string>();
  1243. //strList.Add("Buddy Longway T02 : L'ennemi");
  1244. return strList;
  1245. }
  1246.  
  1247. public int executeBatch(bool insertMediabrol)
  1248. {
  1249. int insertedNb = 0;
  1250. int modelId = 1063;
  1251. List<String> strList = getBatch();
  1252. if (modelId < 1 || strList.Count < 1)
  1253. {
  1254. notify(new Notification(Notification.VERBOSE.error, "Impossible d'exécuter le batch (soit la liste est vide, soit le modèle n'est pas valide)", this));
  1255. return 0;
  1256. }
  1257. DbConnection dbCon = dbpf.CreateConnection();
  1258. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1259. dbDa.InsertCommand = dbCon.CreateCommand();
  1260. dbCon.ConnectionString = conStr;
  1261. dbCon.Open();
  1262. insertedNb = insertBrolFromBatch(strList, loadBrol(modelId,false), insertMediabrol, dbCon, dbDa);
  1263. dbCon.Close();
  1264. return insertedNb;
  1265. }
  1266. #endregion
  1267.  
  1268.  
  1269. /// <summary>
  1270. /// To do :
  1271. /// - get infos from a dataset or a file
  1272. /// </summary>
  1273. /// <param name="batchItems"></param>
  1274. /// <param name="model"></param>
  1275. /// <param name="dbCon"></param>
  1276. /// <param name="dbDa"></param>
  1277. private int insertBrolFromBatch(List<String> batchItems, Brol model,bool insertMediabrol, DbConnection dbCon, DbDataAdapter dbDa)
  1278. {
  1279. int i = 0;
  1280. MediaBrol mediabrol;
  1281. foreach (Actor a in model.Actors)
  1282. {
  1283. a.Status = STATUS.toAdd;
  1284. }
  1285. foreach (BrolCategory c in model.Categories)
  1286. {
  1287. c.Status = STATUS.toAdd;
  1288. }
  1289. foreach (String batchItem in batchItems)
  1290. {
  1291. model.Title = batchItem;
  1292. model.Id = -1;
  1293. model.Id = insertBrol(model, dbCon, dbDa, true);
  1294. if (insertMediabrol)
  1295. {
  1296. mediabrol = new MediaBrol(-1, model, "Ajout automatique " + model.Id);
  1297. mediabrol.InsertionDate = DateTime.Now;
  1298. mediabrol.Owner = Config.Owner;
  1299. DAOFactory.Instance.getMediaBrolDao().insertMediaBrol(mediabrol);
  1300. }
  1301. i++;
  1302. }
  1303. notify(new Notification(Notification.VERBOSE.opsResult, "Fin des insertions batch : " + i + "/" + batchItems.Count, this));
  1304. return i;
  1305. }
  1306.  
  1307. internal int insertBrol(Brol brol, DbConnection dbCon, DbDataAdapter dbDa, bool displayNotifications)
  1308. {
  1309. if (dbDa.InsertCommand == null) dbDa.InsertCommand = dbCon.CreateCommand();
  1310. StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
  1311. strDisp.Append(brol.Title);
  1312. if (brol.Id != -1)
  1313. {
  1314. strDisp.Append("\" ne peut être ajouté avec le n°");
  1315. strDisp.Append(brol.Id);
  1316. notify(new Notification(Notification.VERBOSE.error, strDisp.ToString(), this));
  1317. return -1;
  1318. }
  1319. if (brol.BrolLocked)
  1320. {
  1321. strDisp.Append("\" ne peut être ajouté car il est verrouillé.");
  1322. notify(new Notification(Notification.VERBOSE.error, strDisp.ToString(), this));
  1323. return -1;
  1324. }
  1325. bool pubDateValid = (!brol.Date.Equals(new DateTime(0L)));
  1326. StringBuilder str = new StringBuilder();
  1327. str.Append("INSERT INTO item (itemTitle,synopsis,cote,itemLocked,typeId,comment");
  1328. if (pubDateValid) str.Append(",pubDate");
  1329. str.Append(") VALUES ('");
  1330. str.Append(MySQLUtils.escapeAndTrim(brol.Title));
  1331. str.Append("', '");
  1332. str.Append(MySQLUtils.escapeAndTrim(brol.Synopsis));
  1333. str.Append("', ");
  1334. str.Append(brol.Cotation);
  1335. str.Append(", FALSE, ");
  1336. str.Append(brol.BrolType.Id);
  1337. str.Append(", '");
  1338. str.Append(MySQLUtils.escapeAndTrim(brol.Comment));
  1339. str.Append("'");
  1340. if (pubDateValid)
  1341. {
  1342. str.Append(", ");
  1343. str.Append(MySQLUtils.getAccessDate(brol.Date));
  1344. }
  1345. str.Append(")");
  1346. dbDa.InsertCommand.CommandText = str.ToString();
  1347. try
  1348. {
  1349. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
  1350. dbDa.InsertCommand.ExecuteNonQuery();
  1351. //bad smell code : may not work with concurent acces
  1352. dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY";
  1353. brol.Id = (int)dbDa.InsertCommand.ExecuteScalar();
  1354. }
  1355. catch (Exception ex)
  1356. {
  1357. notify(new Notification(Notification.VERBOSE.error, "insertion d'un nouvel ouvrage",ex, this));
  1358. return -1;
  1359. }
  1360. foreach (Actor actor in brol.Actors)
  1361. {
  1362. if (!insertActorRole(actor, brol, dbDa.InsertCommand))
  1363. notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de l'acteur " + actor.ToString() + " pour l'ouvrage " + brol.ToString(), this));
  1364.  
  1365. }
  1366. foreach (BrolCategory category in brol.Categories)
  1367. {
  1368. if (!addCategoryRelation(category, brol.Id, dbDa.InsertCommand))
  1369. notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de la catégorie " + category.ToString() + " pour l'ouvrage " + brol.ToString(), this));
  1370.  
  1371. }
  1372. foreach (SerieItem serieItem in brol.SerieItems)
  1373. {
  1374. if (!addSerieItemsRelation(serieItem, brol.Id, dbDa.InsertCommand))
  1375. notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de la série " + serieItem.ToString() + " pour l'ouvrage " + brol.ToString(), this));
  1376.  
  1377. }
  1378.  
  1379. strDisp.Append("\" est ajouté.");
  1380. if(displayNotifications)notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1381. return brol.Id;
  1382. }
  1383.  
  1384. internal bool updateBrol(Brol brol, DbConnection dbCon, DbDataAdapter dbDa, bool displayNotifications, StringBuilder strDisp)
  1385. {
  1386. StringBuilder str = new StringBuilder();
  1387. if (dbDa.UpdateCommand == null)
  1388. {
  1389. dbDa.UpdateCommand = dbCon.CreateCommand();
  1390. }
  1391. str.Append("UPDATE item SET itemTitle='");
  1392. str.Append(MySQLUtils.escapeAndTrim(brol.Title));
  1393. str.Append("', synopsis='");
  1394. str.Append(MySQLUtils.escapeAndTrim(brol.Synopsis));
  1395. str.Append("', comment='");
  1396. str.Append(MySQLUtils.escapeAndTrim(brol.Comment));
  1397. str.Append("', cote=");
  1398. str.Append(brol.Cotation);
  1399. if (!brol.Date.Equals(new DateTime(0L)))
  1400. {
  1401. str.Append(", pubDate=");
  1402. str.Append(MySQLUtils.getAccessDate(brol.Date));
  1403. }
  1404. str.Append(", itemLocked=FALSE WHERE id=");
  1405. str.Append(brol.Id);
  1406. //no need to update type : a film may not became a book
  1407. dbDa.UpdateCommand.CommandText = str.ToString();
  1408. int r = 0;
  1409. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
  1410. try
  1411. {
  1412. r = dbDa.UpdateCommand.ExecuteNonQuery();
  1413. }
  1414. catch (Exception e)
  1415. {
  1416. notify(new Notification(Notification.VERBOSE.error, "Mise à jour de l'ouvrage", e, this));
  1417. }
  1418. if (r < 1)
  1419. {
  1420. strDisp.Append("\" ne peut être mis à jour.");
  1421. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1422. dbCon.Close();
  1423. return false;
  1424. }
  1425. updateActors(brol, dbCon, dbDa);
  1426. updateCategoriesRelations(brol, dbCon, dbDa);
  1427. updateSerieItemsRelations(brol, dbCon, dbDa);
  1428. if (displayNotifications)
  1429. {
  1430. strDisp.Append("\" est mis à jour (");
  1431. strDisp.Append(r);
  1432. strDisp.Append(" ouvrages mis à jour).");
  1433. }
  1434. return true;
  1435. }
  1436. }
  1437. }

Structure et Fichiers du projet

Afficher/masquer...


Répertoires contenus dans /var/www/bin/sniplets/bibliobrol/src/model/dao/mysql/ 
IcôneNomTailleModification
IcôneNomTailleModification
| _ Répertoire parent0 octets1734931456 23/12/2024 06:24:16
| _utils0 octets1541007203 31/10/2018 18:33:23
Fichiers contenus dans /var/www/bin/sniplets/bibliobrol/src/model/dao/mysql/ 
IcôneNomTailleModificationAction
IcôneNomTailleModificationAction
Afficher le fichier .cs|.csNoChangeImporterDao.cs27.14 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csNoChangeExporterDao.cs33.69 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csNoChangeSerieDao.cs6.22 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csMySQLFactory.cs3.75 Ko31/10/2018 18:33:17-refusé-
Afficher le fichier .cs|.csNoChangeConfigDao.cs12.25 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csNoChangePersonDao.cs59.9 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csNoChangeTaskDao.cs8.92 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csNoChangeBrolDao.cs58.25 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csMySQLStatsDao.cs10.63 Ko31/10/2018 18:33:18-refusé-
Afficher le fichier .cs|.csMySQLMediaBrolDao.cs50.51 Ko31/10/2018 18:33:18-refusé-

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.

Version en cache

23/12/2024 06:24:16 Cette version de la page est en cache (à la date du 23/12/2024 06:24:16) afin d'accélérer le traitement. Vous pouvez activer le mode utilisateur dans le menu en haut pour afficher la dernère version de la page.

Document créé le 16/10/2009, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/cs-bibliobrol-source-rf-model/dao/mysql/NoChangeBrolDao.cs.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.