NoChangePersonDao.cs

Description du code

NoChangePersonDao.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;
  3. using System.Collections.Generic;
  4. using System.Text;
  5. using System.Data;
  6. using System.Data.OleDb;
  7. using System.Data.Common;
  8. using be.gaudry.bibliobrol.model;
  9. using be.gaudry.bibliobrol.model.dao;
  10. using be.gaudry.observer;
  11. using be.gaudry.bibliobrol.model.identity;
  12. using be.gaudry.bibliobrol.model.enums;
  13. using be.gaudry.model.exceptions;
  14. using be.gaudry.bibliobrol.config;
  15.  
  16. namespace be.gaudry.bibliobrol.model.dao.mysql
  17. {
  18. public sealed class NoChangePersonDao : Observable, IPersonDao
  19. {
  20. private String conStr;
  21. private int bibliobrolItemId;//used to get the user role;
  22. private ActorRole userRole;
  23.  
  24. #region singleton
  25. static NoChangePersonDao instance = null;
  26. static readonly object padlock = new object();
  27. private DbProviderFactory dbpf;
  28. NoChangePersonDao()
  29. {
  30. dbpf = ((MySQLFactory)MySQLFactory.Instance).getDbpf();
  31. conStr = ((MySQLFactory)MySQLFactory.Instance).getConnectionString();
  32. bibliobrolItemId = Config.BibliobrolId;
  33. userRole = new ActorRole(11, "Utilisateur Bibliobrol");
  34. notify(new Notification(Notification.VERBOSE.debug, "Singleton AccessPersonDao call", this));
  35. }
  36. public static NoChangePersonDao Instance
  37. {
  38. get
  39. {
  40. lock(padlock)
  41. {
  42. if(instance==null)
  43. {
  44. instance = new NoChangePersonDao();
  45. }
  46. return instance;
  47. }
  48. }
  49. }
  50. #endregion
  51.  
  52. #region private methods
  53. /// <summary>
  54. /// Test if a person exists into the Access DataBase.
  55. /// Tests are done only on lastname and firstname fields
  56. /// </summary>
  57. /// <param name="person">(Person) person to test</param>
  58. /// <param name="dbDa">(DbDataAdapter) to avoid create a new connection</param>
  59. /// <param name="dbCon">(DbConnection) to avoid create a new connection</param>
  60. /// <returns>(int) id of the person found (-1 if not found)</returns>
  61. private int personExists(Person person, DbDataAdapter dbDa, DbConnection dbCon)
  62. {
  63. dbDa.SelectCommand = dbCon.CreateCommand();
  64.  
  65.  
  66. /*StringBuilder str = new StringBuilder("SELECT id FROM person WHERE UCASE(lastName) = '");
  67.   Console.WriteLine("test sql 1 : " + str.ToString());
  68.   str.Append(AccessUtils.escapeAndTrim(person.LastName).ToUpper());
  69.   Console.WriteLine("test sql 2 : " + str.ToString());
  70.   str.Append("' AND UCASE(firstName)='");
  71.   Console.WriteLine("test sql 3 : " + str.ToString());
  72.   str.Append(AccessUtils.escapeAndTrim(person.FirstName).ToUpper());
  73.   Console.WriteLine("test sql 4 : " + str.ToString());
  74.   str.Append("'");
  75.   Console.WriteLine("test sql 5 : " + str.ToString());
  76.   dbDa.SelectCommand.CommandText = str.ToString();*/
  77.  
  78.  
  79.  
  80. string str = string.Format(
  81. "SELECT id FROM person WHERE UCASE(lastName) = '{0}' AND UCASE(firstName)='{1}'",
  82. MySQLUtils.escapeAndTrim(person.LastName).ToUpper(),
  83. MySQLUtils.escapeAndTrim(person.FirstName).ToUpper()
  84. );
  85. dbDa.SelectCommand.CommandText = str;
  86.  
  87.  
  88. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  89.  
  90. bool conOpen = dbCon.State.Equals(ConnectionState.Open);
  91. if (!conOpen)
  92. dbCon.Open();
  93. int r = -1;
  94. try
  95. {
  96. object o = dbDa.SelectCommand.ExecuteScalar();
  97. if (o != null) r = (int)o;
  98. }
  99. catch (OleDbException ode)
  100. {
  101. notify(new Notification(Notification.VERBOSE.error, "Vérification d'une personne", ode, this));
  102. }
  103. dbCon.Close();
  104. return r;
  105. }
  106. /// <summary>
  107. /// Test if a user exists into the Access DataBase.
  108. /// Tests are done on person id field
  109. /// </summary>
  110. /// <exception cref="System.Data.OleDb.OleDbException">OleDbException</exception>
  111. /// <remarks>
  112. /// Be carefull : a user with no id or default id (-1) will returns false...
  113. /// </remarks>
  114. /// <param name="person">(User) user to test</param>
  115. /// <param name="dbDa">(DbDataAdapter) to avoid create a new connection</param>
  116. /// <param name="dbCon">(DbConnection) to avoid create a new connection</param>
  117. /// <returns>true if user exists</returns>
  118. private bool userExists(User user, DbCommand com)
  119. {
  120. com.CommandText = string.Format("SELECT COUNT(personId) FROM [user] WHERE personId = {0}", user.Id);
  121. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  122.  
  123. int r = (int)com.ExecuteScalar();
  124. return r > 0;
  125. }
  126. /// <summary>
  127. /// Verify if some phones had been deleted, and delete it from the Access DataBase.
  128. /// Add new phones if exists.
  129. /// Update existing phones.
  130. /// </summary>
  131. /// <param name="person">(Person) person containing phones list</param>
  132. /// <param name="dbCon"></param>
  133. private void updatePersPhones(User user, DbConnection dbCon, DbDataAdapter dbDa)
  134. {
  135. dbDa.SelectCommand = dbCon.CreateCommand();
  136. /*dbDa.InsertCommand = dbCon.CreateCommand();
  137.   dbDa.UpdateCommand = dbCon.CreateCommand();
  138.   dbDa.DeleteCommand = dbCon.CreateCommand();*/
  139. DbCommand command = dbCon.CreateCommand();
  140. List<Phone> phones = new List<Phone>();
  141. dbDa.SelectCommand.CommandText = String.Format(
  142. "SELECT * FROM personPhone WHERE personId = {0}",
  143. user.Id
  144. );
  145. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  146.  
  147. DataTable dt = new DataTable();
  148. dbDa.Fill(dt);
  149. try
  150. {
  151. foreach (DataRow phoneRow in dt.Rows)
  152. {
  153. phones.Add(getPhoneNbr(phoneRow));
  154. }
  155. }
  156. catch (BuildObjectException boe)
  157. {
  158. notify(new Notification(Notification.VERBOSE.error, "Vérification des numéros de téléphone dans la DB", boe, this));
  159. }
  160.  
  161. foreach (Phone phone in phones)
  162. {
  163. if (!user.Phones.Contains(phone))
  164. {
  165. deletePhone(phone, command);
  166. }
  167. }
  168.  
  169. foreach (Phone phone in user.Phones)
  170. {
  171. if (phone.Id>0)
  172. {
  173. updatePhone(phone, command);
  174. }
  175. else
  176. {
  177. insertPhone(phone, user.Id, command);
  178. }
  179. }
  180. }
  181. private Person getPerson(DataRow row)
  182. {
  183. if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no lastName found");
  184. Person p = new Person((int)row["id"], (String)row["lastName"]);
  185. try
  186. {
  187.  
  188. if (!(row["firstName"] is DBNull))
  189. {
  190. p.FirstName = (String)row["firstName"];
  191. }
  192. if (!(row["pseudo"] is DBNull))
  193. {
  194. p.Pseudo = (String)row["pseudo"];
  195. }
  196. //if (!(row["personLocked"] is DBNull))
  197. //{
  198. // p.Edited = !row["personLocked"].ToString().Equals("False");
  199. //}
  200. if (!(row["sex"] is DBNull))
  201. {
  202. SEX sex;
  203. try
  204. {
  205. sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
  206. }
  207. catch (Exception)
  208. {
  209. sex = SEX._;
  210. }
  211. p.Sex = sex;
  212. }
  213. if (!(row["birthdate"] is DBNull))
  214. {
  215. p.Birthdate = (DateTime)row["birthdate"];
  216. }
  217. }
  218. catch (Exception e)
  219. {
  220. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", e, this));
  221. }
  222. return p;
  223. }
  224.  
  225. private Phone getPhoneNbr(DataRow row)
  226. {
  227. if (row["id"] is DBNull || row["phoneNum"] is DBNull) throw new BuildObjectException("No id or no phoneNum found");
  228. Phone p = new Phone((int)row["id"], (String)row["phoneNum"]);
  229. if (!(row["phoneType"] is DBNull))
  230. {
  231. Phone.TYPE t;
  232. try
  233. {
  234. t = (Phone.TYPE)Enum.Parse(typeof(Phone.TYPE), (String)row["phoneType"]);
  235. }
  236. catch (Exception)
  237. {
  238. t = Phone.TYPE._;
  239. }
  240. p.Type = t;
  241. }
  242. return p;
  243. }
  244. /// <summary>
  245. /// Used only to store phones from a new person
  246. /// </summary>
  247. /// <param name="p">Person</param>
  248. /// <param name="dbDa">DbDataAdapter</param>
  249. /// <returns>-1 if person is not a new person, or number of inserted otherwise</returns>
  250. private int insertPhones(User user, DbDataAdapter dbDa)
  251. {
  252. if (user.Id < 0) return -1;
  253. int i = 0;
  254. foreach (Phone phone in user.Phones)
  255. {
  256. insertPhone(phone, user.Id, dbDa.InsertCommand);
  257. }
  258. return i;
  259. }
  260. private bool insertPhone(Phone phone, int persId, DbCommand com)
  261. {
  262. com.CommandText = String.Format(
  263. "INSERT INTO personPhone (personId, phoneNum, phoneType) VALUES({0},'{1}','{2}');",
  264. persId,
  265. phone.Number,
  266. Enum.GetName(typeof(Phone.TYPE), phone.Type)
  267. );
  268. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  269. return (com.ExecuteNonQuery() == 1);
  270. }
  271. private bool deletePhone(Phone phone, DbCommand com)
  272. {
  273. com.CommandText = "DELETE FROM personPhone WHERE id = " + phone.Id;
  274. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  275. return (com.ExecuteNonQuery() == 1);
  276. }
  277. /// <summary>
  278. /// Update a phone.
  279. /// Don't update person id in the DB because moving a phone from a person to another is not implemented.
  280. /// </summary>
  281. /// <param name="phone">(Phone) phone to update</param>
  282. /// <param name="com">(DbCommand) command to use</param>
  283. /// <returns>true if updated</returns>
  284. private bool updatePhone(Phone phone, DbCommand com)
  285. {
  286. com.CommandText = String.Format(
  287. "UPDATE personPhone SET phoneNum='{0}',phoneType='{1}' WHERE id={2}",
  288. phone.Number,
  289. Enum.GetName(typeof(Phone.TYPE), phone.Type),
  290. phone.Id
  291. );
  292. notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
  293. return (com.ExecuteNonQuery() == 1);
  294. }
  295.  
  296. /// <summary>
  297. /// Used to load persons with their ids.
  298. /// </summary>
  299. /// <param name="ids">Array of persons'ids (int values)</param>
  300. /// <returns>A list of persons</returns>
  301. private List<Person> loadSelectedPersons(int[] ids)
  302. {
  303. List<Person> persons = new List<Person>();
  304. if (ids.Length < 1)
  305. return persons;
  306. DbConnection dbCon = dbpf.CreateConnection();
  307. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  308. DataSet ds = new DataSet();
  309. dbDa.SelectCommand = dbCon.CreateCommand();
  310. dbCon.ConnectionString = conStr;
  311. //build persons datatable
  312. StringBuilder str = new StringBuilder("SELECT id ,lastName, firstName, pseudo, birthdate, sex FROM person");
  313. if (ids[0]!=-1)
  314. {//load all persons if we have -1 value
  315. str.Append(" WHERE id IN (");
  316. for (int i = 0; i < ids.Length; i++)
  317. {
  318. if (i > 0) str.Append(",");
  319. str.Append(ids[i]);
  320. }
  321. str.Append(")");
  322. }
  323. dbDa.SelectCommand.CommandText = str.ToString();
  324. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  325. try
  326. {
  327. dbDa.Fill(ds, "persons");
  328.  
  329. //build person objects
  330. foreach (DataRow persRow in ds.Tables["persons"].Rows)
  331. {
  332. try
  333. {
  334. persons.Add(getPerson(persRow));
  335. }
  336. catch (BuildObjectException boe)
  337. {
  338. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
  339. }
  340. }
  341. }
  342. catch (Exception fillPersEx)
  343. {
  344. notify(new Notification(Notification.VERBOSE.error, "Chargement des personnes", fillPersEx, this));
  345. return persons;
  346. }
  347. finally
  348. {
  349. dbCon.Close();
  350. }
  351. return persons;
  352. }
  353. private bool lockUnlockPerson(int id, bool toLocked)
  354. {
  355. DbConnection dbCon = dbpf.CreateConnection();
  356. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  357. dbDa.UpdateCommand = dbCon.CreateCommand();
  358. dbCon.ConnectionString = conStr;
  359. dbCon.Open();
  360. StringBuilder str = new StringBuilder("UPDATE person SET personLocked=");
  361. str.Append((toLocked)?"TRUE":"FALSE");
  362. str.Append(" WHERE id=");
  363. str.Append(id);
  364. dbDa.UpdateCommand.CommandText = str.ToString();
  365. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
  366. int r = dbDa.UpdateCommand.ExecuteNonQuery();
  367. dbCon.Close();
  368. return (r == 1);
  369. }
  370.  
  371. #endregion
  372.  
  373. #region IPersonDao Members
  374. public String getConStr() { return conStr; }
  375.  
  376. #region person
  377.  
  378. /// <summary>
  379. /// Test if a person exists with the same name and firstname
  380. /// </summary>
  381. /// <param name="person">(Person) person to test</param>
  382. /// <returns>(int) id of the person found (-1 if not found)</returns>
  383. public int personExists(Person person)
  384. {
  385. DbConnection dbCon = dbpf.CreateConnection();
  386. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  387. dbDa.InsertCommand = dbCon.CreateCommand();
  388. dbCon.ConnectionString = conStr;
  389. return personExists(person, dbDa, dbCon);
  390. }
  391.  
  392. /// <summary>
  393. /// Lock person to avoid concurent modifications.
  394. /// </summary>
  395. /// <param name="id">person's id to lock</param>
  396. /// <returns>false if a problem occurs (todo : if already locked)</returns>
  397. public bool lockPerson(int id)
  398. {
  399. return lockUnlockPerson(id, true);
  400. }
  401. /// <summary>
  402. /// unlock person to allow modifications.
  403. /// </summary>
  404. /// <param name="id">person's id to unlock</param>
  405. public bool unlockPerson(int id)
  406. {
  407. return lockUnlockPerson(id, false);
  408. }
  409. /// <summary>
  410. /// Insert a person into the Access DataBase
  411. /// </summary>
  412. /// <param name="person">(Person) person to insert</param>
  413. /// <returns>(int) id of the new person (-1 if a problem occurs)</returns>
  414. public int insertPerson(Person person)
  415. {
  416. StringBuilder strDisp = new StringBuilder("La personne ");
  417. strDisp.Append(person.ToString());
  418. int lastId = -1;
  419. if (person.Id <= 0)
  420. {
  421. DbConnection dbCon = dbpf.CreateConnection();
  422. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  423. dbDa.InsertCommand = dbCon.CreateCommand();
  424. dbCon.ConnectionString = conStr;
  425. dbCon.Open();
  426. lastId = insertPerson(person, dbCon, dbDa, strDisp);
  427. dbCon.Close();
  428. }
  429. strDisp.Append((lastId > 0) ? " est ajoutée." : " ne peut être ajoutée.");
  430. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  431. return lastId;
  432. }
  433.  
  434. public int insertBibliobrolUser(Person person)
  435. {
  436. throw new Exception("Use of deprecated method : use saveUser(be.gaudry.bibliobrol.User) instead.");
  437. }
  438.  
  439. internal int insertPerson(Person person, DbConnection dbCon, DbDataAdapter dbDa, StringBuilder strDisp)
  440. {
  441. int lastId = -1;
  442. /*if (personExists(person, dbDa, dbCon)>0)
  443.   {
  444.   strDisp.Append(" existe déjà dans la base de données, ");
  445.   return lastId;
  446.   }*/
  447. StringBuilder str = new StringBuilder();
  448. str.Append("INSERT INTO person (lastName, firstName, pseudo, sex, personLocked, birthdate) VALUES('");
  449. str.Append(MySQLUtils.escapeAndTrim(person.LastName));
  450. str.Append("','");
  451. str.Append(MySQLUtils.escapeAndTrim(person.FirstName));
  452. str.Append("','");
  453. str.Append(MySQLUtils.escapeAndTrim(person.Pseudo));
  454. str.Append("','");
  455. str.Append(Enum.GetName(typeof(SEX), person.Sex));
  456. str.Append("', FALSE,");
  457. if (person.Birthdate.Equals(new DateTime(0L)))
  458. {
  459. str.Append("NULL");
  460. }
  461. else
  462. {
  463. str.Append(MySQLUtils.getAccessDate(person.Birthdate));
  464. }
  465. str.Append(");");
  466. dbDa.InsertCommand.CommandText = str.ToString();
  467. notify(new Notification(Notification.VERBOSE.persistentOperation, str.ToString(), this));
  468. int r = 0;
  469. try
  470. {
  471. r = dbDa.InsertCommand.ExecuteNonQuery();
  472. if (r > 1)
  473. notify(new Notification(Notification.VERBOSE.error, "Insertions multiples personnes", this));
  474. //bad smell code : may not work with concurent acces
  475. dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY";
  476. lastId = (int)dbDa.InsertCommand.ExecuteScalar();
  477. person.Id = lastId;
  478. }
  479. catch (OleDbException ode)
  480. {
  481. notify(new Notification(Notification.VERBOSE.error, "Ajout d'une personne", ode, this));
  482. }
  483.  
  484. return lastId;
  485. }
  486. /// <summary>
  487. /// Load a person from the Access DB with an id arg.
  488. /// If this person shoud be modified, editing bool arg is true
  489. /// to avoid concurent modifications (nobody else can save this person).
  490. /// </summary>
  491. /// <param name="id">Id of the selected person</param>
  492. /// <param name="editing">(bool) Shoud be modified or not</param>
  493. /// <returns>Selected person, or a new person if _ found</returns>
  494. public Person loadPerson(int id, bool editing)
  495. {
  496. Person p;
  497. List<Person> persons = loadSelectedPersons(new int[] { id });
  498. if (persons.Count > 0)
  499. {
  500. p = persons[0];
  501. if (editing && !p.Edited) lockPerson(id);
  502. }
  503. else p = new Person();
  504. return p;
  505. }
  506.  
  507. /// <summary>
  508. /// Load a person from the persistant layer with a lastname and a firstname.
  509. /// Search case insensitive.
  510. /// </summary>
  511. /// <param name="lastName">(string) lastname of the searched person</param>
  512. /// <param name="firstName">(string) firstname of the searched person</param>
  513. /// <returns>Persons found, or empty list</returns>
  514. public List<Person> loadPersons(string lastName, string firstName)
  515. {
  516. StringBuilder str = new StringBuilder("SELECT id FROM person WHERE UCASE(lastName) LIKE ('%");
  517. str.Append(lastName.ToUpper().Trim());
  518. str.Append("%') AND UCASE(firstName) LIKE ('%");
  519. str.Append(firstName.ToUpper().Trim());
  520. str.Append("%')");
  521.  
  522. List<int> ids = new List<int>();
  523.  
  524. DbConnection dbCon = dbpf.CreateConnection();
  525. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  526. dbDa.SelectCommand = dbCon.CreateCommand();
  527. dbCon.ConnectionString = conStr;
  528. dbDa.SelectCommand.CommandText = str.ToString();
  529. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  530. DataTable persDt = new DataTable();
  531. try
  532. {
  533. dbDa.Fill(persDt);
  534. }
  535. catch (Exception e)
  536. {
  537. notify(new Notification(
  538. Notification.VERBOSE.error,
  539. String.Format("Chargement de la liste de personnes correspondant à {0} {1}", lastName, firstName),
  540. e,
  541. this
  542. ));
  543. }
  544. foreach (DataRow row in persDt.Rows)
  545. {
  546. ids.Add((int)row["id"]);
  547. }
  548. persDt.Dispose();
  549. dbCon.Close();
  550. if (ids.Count < 1) return new List<Person>();
  551. return loadSelectedPersons(ids.ToArray());
  552. }
  553.  
  554. /// <summary>
  555. /// Load all persons matches (case insensitive) lastName
  556. /// </summary>
  557. /// <param name="lastName">(String) lastName to match</param>
  558. /// <returns>List of persons with the same lastName</returns>
  559. public List<Person> loadPersons(String lastName)
  560. {
  561. List<Person> persons = new List<Person>();
  562. if (lastName == null || lastName.Equals(String.Empty))
  563. return persons;
  564. DbConnection dbCon = dbpf.CreateConnection();
  565. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  566. DataSet ds = new DataSet();
  567. //DataRelation dr;
  568. dbDa.SelectCommand = dbCon.CreateCommand();
  569. dbCon.ConnectionString = conStr;
  570. //build persons datatable
  571. StringBuilder str = new StringBuilder("SELECT id ,lastName, firstName, pseudo, birthdate, sex, personLocked FROM person");
  572. str.Append(" WHERE UCASE(lastName) = '");
  573. str.Append(MySQLUtils.escapeAndTrim(lastName.ToUpper()));
  574. str.Append("'");
  575. dbDa.SelectCommand.CommandText = str.ToString();
  576. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  577. try
  578. {
  579. dbDa.Fill(ds, "persons");
  580.  
  581. //build person objects
  582. foreach (DataRow persRow in ds.Tables["persons"].Rows)
  583. {
  584. try
  585. {
  586. persons.Add(getPerson(persRow));
  587. }
  588. catch (BuildObjectException boe)
  589. {
  590. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
  591. }
  592. }
  593. }
  594. catch (Exception fillPersEx)
  595. {
  596. notify(new Notification(Notification.VERBOSE.error, "Chargement des personnes", fillPersEx, this));
  597. return persons;
  598. }
  599. finally
  600. {
  601. dbCon.Close();
  602. }
  603. return persons;
  604. }
  605. /// <summary>
  606. /// Load all persons from the Access DataBase
  607. /// </summary>
  608. /// <returns>List of Persons</returns>
  609. public List<Person> loadPersons()
  610. {
  611. return loadSelectedPersons(new int[] { -1 });
  612. }
  613. /// <summary>
  614. /// Load persons value objects (only display and id)
  615. /// </summary>
  616. /// <returns>List of PersonVos</returns>
  617. public List<PersonLO> loadVos()
  618. {
  619. return loadPersonsVos(new ActorRole());
  620. }
  621. /// <summary>
  622. /// Load persons value objects with selected role (only display and id)
  623. /// If role id is less or equals than 0, all persons are loaded
  624. /// </summary>
  625. /// <param name="editing">(ActorRole) person's role</param>
  626. /// <returns>List of PersonVos</returns>
  627. public List<PersonLO> loadPersonsVos(ActorRole role)
  628. {
  629. StringBuilder str = new StringBuilder("SELECT DISTINCT (person.id) ,person.lastName, person.firstName, person.pseudo");
  630. if (role.RoleId > 0)
  631. {
  632. str.Append(", actor.roleId FROM person");
  633. str.Append(" LEFT JOIN actor ON actor.personId = person.id ");
  634. str.Append(" WHERE roleId = ");
  635. str.Append(role.RoleId);
  636. }
  637. else
  638. {
  639. str.Append(" FROM person");
  640. }
  641. return loadPersonsVosByQuery(str.ToString());
  642. }
  643. /// <summary>
  644. /// Load persons value objects with same lastname
  645. /// </summary>
  646. /// <param name="name">(string) person's lastname</param>
  647. /// <returns>List of PersonVos</returns>
  648. public List<PersonLO> loadPersonsVos(string name)
  649. {
  650. string str = string.Format(
  651. "SELECT id ,lastName, firstName, pseudo, birthdate, sex, personLocked FROM person WHERE UCASE(lastName) LIKE('{0}')",
  652. MySQLUtils.escapeAndTrim(name.ToUpper())
  653. );
  654.  
  655. return loadPersonsVosByQuery(str);
  656. }
  657. /// <summary>
  658. /// Load persons value objects with specific query
  659. /// </summary>
  660. /// <param name="query">(string) sql query</param>
  661. /// <returns>List of PersonVos</returns>
  662. private List<PersonLO> loadPersonsVosByQuery(string query)
  663. {
  664. List<PersonLO> persons = new List<PersonLO>();
  665. DbConnection dbCon = dbpf.CreateConnection();
  666. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  667. dbDa.SelectCommand = dbCon.CreateCommand();
  668. dbCon.ConnectionString = conStr;
  669.  
  670. dbDa.SelectCommand.CommandText = query;
  671. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  672. DataTable persDt = new DataTable();
  673. try
  674. {
  675. dbDa.Fill(persDt);
  676.  
  677. PERSON_DISPLAY persDisp = Config.PersonDisplay;
  678. switch (persDisp)
  679. {
  680. case PERSON_DISPLAY.firstname_lastname: addPersonsVos_firstname_lastname(persDt.Rows, persons); break;
  681. case PERSON_DISPLAY.lastname_firstname: addPersonsVos_lastname_firstname(persDt.Rows, persons); break;
  682. case PERSON_DISPLAY.lastname_firstname_pseudo: addPersonsVos_firstname_lastname_pseudo(persDt.Rows, persons); break;
  683. case PERSON_DISPLAY.pseudo: addPersonsVos_pseudo(persDt.Rows, persons); break;
  684. case PERSON_DISPLAY.pseudo_lastname_firstname: addPersonsVos_pseudo_lastname_firstname(persDt.Rows, persons); break;
  685. default: addPersonsVos_pseudo(persDt.Rows, persons); break;
  686. }
  687. //addPersonsVos(persDt.Rows, persons);
  688. }
  689. catch (Exception e)
  690. {
  691. notify(new Notification(Notification.VERBOSE.error, "Chargement de la liste de personnes selon la requête suivante :\n\r" + query, e, this));
  692. }
  693. persDt.Dispose();
  694. dbCon.Close();
  695. return persons;
  696. }
  697.  
  698. private void addPersonsVos_pseudo_lastname_firstname(DataRowCollection dataRowCollection, List<PersonLO> persons)
  699. {
  700. StringBuilder persDisplay;
  701. foreach (DataRow row in dataRowCollection)
  702. {
  703. persDisplay = new StringBuilder();
  704. if (!(row["id"] is DBNull))
  705. {
  706. if (!(row["pseudo"] is DBNull))
  707. {
  708. persDisplay.Append((String)row["pseudo"]);
  709. }
  710. if (!(row["lastName"] is DBNull))
  711. {
  712. persDisplay.Append(" (");
  713. persDisplay.Append((String)row["lastName"]);
  714. }
  715. if (!(row["firstName"] is DBNull))
  716. {
  717. persDisplay.Append(" ");
  718. persDisplay.Append((String)row["firstName"]);
  719. persDisplay.Append(")");
  720. }
  721. if (persDisplay.Length > 0)
  722. {
  723. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  724. }
  725. }
  726. }
  727. }
  728.  
  729. private void addPersonsVos_pseudo(DataRowCollection dataRowCollection, List<PersonLO> persons)
  730. {
  731. StringBuilder persDisplay;
  732. foreach (DataRow row in dataRowCollection)
  733. {
  734. persDisplay = new StringBuilder();
  735. if (!(row["id"] is DBNull))
  736. {
  737. if (!(row["pseudo"] is DBNull) && !String.Empty.Equals(row["pseudo"]))
  738. {
  739. persDisplay.Append((String)row["pseudo"]);
  740. }
  741. else
  742. {
  743. persDisplay.Append("Sans pseudo");
  744. persDisplay.Append(" (");
  745. if (!(row["lastName"] is DBNull))
  746. {
  747. persDisplay.Append((String)row["lastName"]);
  748. }
  749. if (!(row["firstName"] is DBNull))
  750. {
  751. persDisplay.Append(" ");
  752. persDisplay.Append((String)row["firstName"]);
  753. }
  754. persDisplay.Append(")");
  755. }
  756. if (persDisplay.Length > 0)
  757. {
  758. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  759. }
  760. }
  761. }
  762. }
  763.  
  764. private void addPersonsVos_firstname_lastname_pseudo(DataRowCollection dataRowCollection, List<PersonLO> persons)
  765. {
  766. StringBuilder persDisplay;
  767. foreach (DataRow row in dataRowCollection)
  768. {
  769. persDisplay = new StringBuilder();
  770. if (!(row["id"] is DBNull))
  771. {
  772. if (!(row["firstName"] is DBNull))
  773. {
  774. persDisplay.Append((String)row["firstName"]);
  775. }
  776. if (!(row["lastName"] is DBNull))
  777. {
  778. persDisplay.Append(" ");
  779. persDisplay.Append((String)row["lastName"]);
  780. }
  781. if (!(row["pseudo"] is DBNull))
  782. {
  783. persDisplay.Append(" (");
  784. persDisplay.Append((String)row["pseudo"]);
  785. persDisplay.Append(")");
  786. }
  787. if (persDisplay.Length > 0)
  788. {
  789. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  790. }
  791. }
  792. }
  793. }
  794.  
  795. private void addPersonsVos_lastname_firstname(DataRowCollection dataRowCollection, List<PersonLO> persons)
  796. {
  797. StringBuilder persDisplay;
  798. foreach (DataRow row in dataRowCollection)
  799. {
  800. persDisplay = new StringBuilder();
  801. if (!(row["id"] is DBNull))
  802. {
  803. if (!(row["lastName"] is DBNull))
  804. {
  805. persDisplay.Append((String)row["lastName"]);
  806. }
  807. if (!(row["firstName"] is DBNull))
  808. {
  809. persDisplay.Append(" ");
  810. persDisplay.Append((String)row["firstName"]);
  811. }
  812. if (persDisplay.Length > 0)
  813. {
  814. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  815. }
  816. }
  817. }
  818. }
  819.  
  820. private void addPersonsVos_firstname_lastname(DataRowCollection dataRowCollection, List<PersonLO> persons)
  821. {
  822. StringBuilder persDisplay;
  823. foreach (DataRow row in dataRowCollection)
  824. {
  825. persDisplay = new StringBuilder();
  826. if (!(row["id"] is DBNull))
  827. {
  828. if (!(row["firstName"] is DBNull))
  829. {
  830. persDisplay.Append((String)row["firstName"]);
  831. }
  832. if (!(row["lastName"] is DBNull))
  833. {
  834. persDisplay.Append(" ");
  835. persDisplay.Append((String)row["lastName"]);
  836. }
  837. if (persDisplay.Length > 0)
  838. {
  839. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  840. }
  841. }
  842. }
  843. }
  844.  
  845. private void addPersonsVos(DataRowCollection dataRowCollection, List<PersonLO> persons)
  846. {
  847. StringBuilder persDisplay;
  848. foreach (DataRow row in dataRowCollection)
  849. {
  850. persDisplay = new StringBuilder();
  851. if (!(row["id"] is DBNull) && !(row["lastName"] is DBNull))
  852. {
  853. if (row["firstName"] is DBNull)
  854. {
  855. if (!(row["pseudo"] is DBNull))
  856. {
  857. persDisplay.Append((String)row["pseudo"]);
  858. persDisplay.Append("\t");
  859. }
  860. persDisplay.Append((String)row["lastName"]);
  861. }
  862. else
  863. {
  864. if (!(row["pseudo"] is DBNull) && !((String)row["pseudo"]).Equals(String.Empty))
  865. {
  866. persDisplay.Append((String)row["lastName"]);
  867. persDisplay.Append("\t");
  868. persDisplay.Append((String)row["firstName"]);
  869. persDisplay.Append(" ");
  870. persDisplay.Append((String)row["pseudo"]);
  871. }
  872. else
  873. persDisplay.Append((String)row["lastName"] + "\t" + (String)row["firstName"]);
  874. }
  875. persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
  876. }
  877. }
  878. }
  879.  
  880.  
  881. /// <summary>
  882. /// Store new values for a person.
  883. /// Verify if some phones had been deleted, and delete it from the Access DataBase.
  884. /// Add new phones if exists.
  885. /// Update existing phones.
  886. /// </summary>
  887. /// <param name="person">Person with new values to store</param>
  888. /// <returns>true if update is done</returns>
  889. public bool updatePerson(Person person)
  890. {
  891. StringBuilder strDisp = new StringBuilder("La personne \"");
  892. strDisp.Append(person.ToString());
  893. if (person.Edited)
  894. {
  895. strDisp.Append("\" ne peut être mise à jour car elle est verrouillée.");
  896. notify(new Notification(Notification.VERBOSE.lowError, strDisp.ToString(), this));
  897. return false;
  898. }
  899. DbConnection dbCon = dbpf.CreateConnection();
  900. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  901. dbDa.UpdateCommand = dbCon.CreateCommand();
  902. dbCon.ConnectionString = conStr;
  903. dbCon.Open();
  904. int result = updatePerson(person, dbCon, dbDa);
  905. dbCon.Close();
  906. strDisp.Append((result == 1) ? "\" est mise à jour." : "\" ne peut être mise à jour.");
  907. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  908. return result == 1;
  909. }
  910.  
  911. private int updatePerson(Person person, DbConnection dbCon, DbDataAdapter dbDa)
  912. {
  913. StringBuilder str = new StringBuilder();
  914. str.Append("UPDATE person SET lastName='");
  915. str.Append(MySQLUtils.escapeAndTrim(person.LastName));
  916. str.Append("', firstName='");
  917. str.Append(MySQLUtils.escapeAndTrim(person.FirstName));
  918. str.Append("', pseudo='");
  919. str.Append(MySQLUtils.escapeAndTrim(person.Pseudo));
  920. str.Append("', sex='");
  921. str.Append(Enum.GetName(typeof(SEX), person.Sex));
  922. str.Append("', birthdate=");
  923. if (person.Birthdate.Equals(new DateTime(0L)))
  924. {
  925. str.Append("NULL");
  926. }
  927. else
  928. {
  929. str.Append(MySQLUtils.getAccessDate(person.Birthdate));
  930. }
  931. str.Append(", personLocked=FALSE WHERE id=");
  932. str.Append(person.Id);
  933. dbDa.UpdateCommand.CommandText = str.ToString();
  934. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
  935. int result = 0;
  936. try
  937. {
  938. result = dbDa.UpdateCommand.ExecuteNonQuery();
  939. }
  940. catch (Exception dbe)
  941. {
  942. unlockPerson(person.Id);
  943. notify(new Notification(Notification.VERBOSE.error, "Mise à jour de la personne", dbe, this));
  944. }
  945. return result;
  946. }
  947.  
  948. /// <summary>
  949. /// Delete a person from the Access DataBase,
  950. /// and delete all associated tables !!!
  951. /// </summary>
  952. /// <param name="p">(Person) person to delete</param>
  953. /// <returns>true if deleted</returns>
  954. public bool deletePerson(Person p)
  955. {
  956. if (p.Id.Equals(Config.Owner.Id))
  957. {
  958. notify(new Notification(Notification.VERBOSE.opsResult, "On ne peut supprimer le propriétaire de l'application...", this));
  959. return false;
  960. }
  961. StringBuilder strDisp = new StringBuilder("La personne \"");
  962. strDisp.Append(p.ToString());
  963. DbConnection dbCon = dbpf.CreateConnection();
  964. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  965. dbDa.DeleteCommand = dbCon.CreateCommand();
  966. dbCon.ConnectionString = conStr;
  967. dbCon.Open();
  968. //delete mail
  969. String str = "DELETE FROM personMail WHERE personId=" + p.Id;
  970. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  971. dbDa.DeleteCommand.CommandText = str;
  972. dbDa.DeleteCommand.ExecuteNonQuery();
  973. //delete phones
  974. str = "DELETE FROM personPhone WHERE personId=" + p.Id;
  975. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  976. dbDa.DeleteCommand.CommandText = str;
  977. dbDa.DeleteCommand.ExecuteNonQuery();
  978. //delete actor
  979. str = "DELETE FROM actor WHERE personId=" + p.Id;
  980. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  981. dbDa.DeleteCommand.CommandText = str;
  982. dbDa.DeleteCommand.ExecuteNonQuery();
  983. //delete user
  984. str = "DELETE FROM [user] WHERE personId=" + p.Id;
  985. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  986. dbDa.DeleteCommand.CommandText = str;
  987. dbDa.DeleteCommand.ExecuteNonQuery();
  988. //delete person
  989. str = "DELETE FROM person WHERE id=" + p.Id;
  990. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  991. dbDa.DeleteCommand.CommandText = str;
  992. int result = dbDa.DeleteCommand.ExecuteNonQuery();
  993. dbCon.Close();
  994. strDisp.Append((result == 1) ? "\" est supprimée." : "\" ne peut être supprimée.");
  995. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  996. return result == 1;
  997. }
  998. #endregion
  999.  
  1000. #region user
  1001. public User loadUser(int persId)
  1002. {
  1003. User user = null;
  1004.  
  1005. DbConnection dbCon = dbpf.CreateConnection();
  1006. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1007. DataSet ds = new DataSet();
  1008. DataRelation dr;
  1009. dbDa.SelectCommand = dbCon.CreateCommand();
  1010. dbCon.ConnectionString = conStr;
  1011. //build user datatable
  1012. StringBuilder str = new StringBuilder("SELECT p.*, u.* FROM person AS p");
  1013. str.Append(" LEFT JOIN [user] AS u ON p.id = u.personId ");
  1014. str.Append(" WHERE p.id = ");
  1015. str.Append(persId);
  1016. dbDa.SelectCommand.CommandText = str.ToString();
  1017. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  1018.  
  1019. try
  1020. {
  1021. dbDa.Fill(ds, "persons");
  1022. //build phones datatable
  1023. str = new StringBuilder("SELECT id , personId, phoneNum, phoneType FROM personPhone WHERE personId = ");
  1024. str.Append(persId);
  1025. dbDa.SelectCommand.CommandText = str.ToString();
  1026. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
  1027.  
  1028. dbDa.Fill(ds, "phones");
  1029. //build relation
  1030. dr = new DataRelation(
  1031. "pers-phones",
  1032. ds.Tables["persons"].Columns["id"],
  1033. ds.Tables["phones"].Columns["personId"]
  1034. );
  1035. try
  1036. {
  1037. ds.Relations.Add(dr);
  1038. }
  1039. catch (Exception dre)
  1040. {
  1041. notify(new Notification(Notification.VERBOSE.error, "Liaison entre les personnes et les téléphones", dre, this));
  1042. }
  1043. //build person objects
  1044. foreach (DataRow row in ds.Tables["persons"].Rows)
  1045. {
  1046. try
  1047. {
  1048. if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no lastName found");
  1049. user = new User((int)row["id"], (String)row["lastName"]);
  1050. //get person data
  1051. try
  1052. {
  1053. if (!(row["firstName"] is DBNull))
  1054. {
  1055. user.FirstName = (String)row["firstName"];
  1056. }
  1057. if (!(row["pseudo"] is DBNull))
  1058. {
  1059. user.Pseudo = (String)row["pseudo"];
  1060. }
  1061. if (!(row["personLocked"] is DBNull))
  1062. {
  1063. user.Edited = !row["personLocked"].ToString().Equals("False");
  1064. }
  1065. if (!(row["sex"] is DBNull))
  1066. {
  1067. SEX sex;
  1068. try
  1069. {
  1070. sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
  1071. }
  1072. catch (Exception)
  1073. {
  1074. sex = SEX._;
  1075. }
  1076. user.Sex = sex;
  1077. }
  1078. if (!(row["birthdate"] is DBNull))
  1079. {
  1080. user.Birthdate = (DateTime)row["birthdate"];
  1081. }
  1082. }
  1083. catch (Exception e)
  1084. {
  1085. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", e, this));
  1086. }
  1087.  
  1088. Address adress = user.Address;
  1089. //get user data
  1090. try
  1091. {
  1092. if (!(row["firstName2"] is DBNull))
  1093. {
  1094. user.FirstName2 = (String)row["firstName2"];
  1095. }
  1096. if (!(row["firstName3"] is DBNull))
  1097. {
  1098. user.FirstName3 = (String)row["firstName3"];
  1099. }
  1100. if (!(row["birthplace"] is DBNull))
  1101. {
  1102. user.BirthPlace = (String)row["birthplace"];
  1103. }
  1104. if (!(row["nationality"] is DBNull))
  1105. {
  1106. user.Nationality = (String)row["nationality"];
  1107. }
  1108. if (!(row["street"] is DBNull))
  1109. {
  1110. adress.Street = (String)row["street"];
  1111. }
  1112. if (!(row["housenumber"] is DBNull))
  1113. {
  1114. adress.HouseNumber = (String)row["housenumber"];
  1115. }
  1116. if (!(row["boxnumber"] is DBNull))
  1117. {
  1118. adress.BoxNumber = (String)row["boxnumber"];
  1119. }
  1120. if (!(row["zipcode"] is DBNull))
  1121. {
  1122. adress.ZipCode = (String)row["zipcode"];
  1123. }
  1124. if (!(row["municipality"] is DBNull))
  1125. {
  1126. adress.Municipality = (String)row["municipality"];
  1127. }
  1128. if (!(row["country"] is DBNull))
  1129. {
  1130. adress.Country = (String)row["country"];
  1131. }
  1132. if (!(row["nationalnumber"] is DBNull))
  1133. {
  1134. user.NationalNumber = (String)row["nationalnumber"];
  1135. }
  1136. }
  1137. catch (Exception e2)
  1138. {
  1139. notify(new Notification(Notification.VERBOSE.error, "Chargement de l'utilisateur", e2, this));
  1140. }
  1141.  
  1142. foreach (DataRow phoneRow in row.GetChildRows(dr))
  1143. {
  1144. user.addPhone(getPhoneNbr(phoneRow));
  1145. }
  1146. }
  1147. catch (BuildObjectException boe)
  1148. {
  1149. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
  1150. }
  1151. }
  1152. }
  1153. catch (Exception fillPersEx)
  1154. {
  1155. notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", fillPersEx, this));
  1156. }
  1157. finally
  1158. {
  1159. dbCon.Close();
  1160. }
  1161. return user;
  1162. }
  1163. /// <summary>
  1164. /// Updates a <code>be.gaudry.bibliobrol.model.User</code> if exists or creates it (and sets role).
  1165. /// </summary>
  1166. /// <param name="user"></param>
  1167. /// <returns>Person Id</returns>
  1168. public int saveUser(User user)
  1169. {
  1170. DbConnection dbCon = dbpf.CreateConnection();
  1171. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1172. dbDa.InsertCommand = dbCon.CreateCommand();
  1173. dbDa.UpdateCommand = dbCon.CreateCommand();
  1174. dbCon.ConnectionString = conStr;
  1175.  
  1176. dbCon.Open();
  1177. StringBuilder str = new StringBuilder();
  1178. StringBuilder strDisp = new StringBuilder();
  1179.  
  1180. if (user.Id < 0)
  1181. {
  1182. user.Id = insertPerson(user, dbCon, dbDa, strDisp);
  1183. }
  1184. else
  1185. {
  1186. updatePerson(user, dbCon, dbDa);
  1187. }
  1188.  
  1189. bool testUserExists;
  1190. try
  1191. {
  1192. testUserExists = userExists(user, dbDa.InsertCommand);
  1193. }
  1194. catch (OleDbException e)
  1195. {
  1196. dbCon.Close();
  1197.  
  1198. notify(new Notification(Notification.VERBOSE.error, "Impossible de tester l'utilisateur", e, this));
  1199. return -1;
  1200. }
  1201.  
  1202.  
  1203. if (testUserExists)
  1204. {
  1205. updateUser(user, dbCon, dbDa, strDisp);
  1206. dbCon.Close();
  1207.  
  1208. notify(new Notification(Notification.VERBOSE.opsResult, "Mise à jour d'un utilisateur", strDisp.ToString(), this));
  1209. return user.Id;
  1210. }
  1211. else
  1212. {
  1213. if (insertPhones(user, dbDa) == -1)
  1214. notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion des N° de Tf pour " + user.ToString(), this));
  1215.  
  1216. str = new StringBuilder("INSERT INTO [user] (personId,firstName2,firstName3,birthplace,nationality");
  1217. str.Append(",street,housenumber,boxnumber,zipcode,municipality,country,nationalnumber)");
  1218. str.Append(" VALUES(");
  1219. Address adress = user.Address;
  1220. str.Append(user.Id);
  1221. str.Append(", '");
  1222. str.Append(MySQLUtils.escapeAndTrim(user.FirstName2));
  1223. str.Append("', '");
  1224. str.Append(MySQLUtils.escapeAndTrim(user.FirstName3));
  1225. str.Append("', '");
  1226. str.Append(MySQLUtils.escapeAndTrim(user.BirthPlace));
  1227. str.Append("', '");
  1228. str.Append(MySQLUtils.escapeAndTrim(user.Nationality));
  1229. str.Append("', '");
  1230. str.Append(MySQLUtils.escapeAndTrim(adress.Street));
  1231. str.Append("', '");
  1232. str.Append(MySQLUtils.escapeAndTrim(adress.HouseNumber));
  1233. str.Append("', '");
  1234. str.Append(MySQLUtils.escapeAndTrim(adress.BoxNumber));
  1235. str.Append("', '");
  1236. str.Append(MySQLUtils.escapeAndTrim(adress.ZipCode));
  1237. str.Append("', '");
  1238. str.Append(MySQLUtils.escapeAndTrim(adress.Municipality));
  1239. str.Append("', '");
  1240. str.Append(MySQLUtils.escapeAndTrim(adress.Country));
  1241. str.Append("', '");
  1242. str.Append(MySQLUtils.escapeAndTrim(user.NationalNumber));
  1243.  
  1244. str.Append("')");
  1245. dbDa.InsertCommand.CommandText = str.ToString();
  1246. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
  1247. try
  1248. {
  1249. dbDa.InsertCommand.ExecuteNonQuery();
  1250. }
  1251. catch (OleDbException odbe)
  1252. {
  1253. notify(new Notification(Notification.VERBOSE.error, "Erreur dans la requête suivante :\n\r" + str.ToString(), odbe, this));
  1254. }
  1255.  
  1256.  
  1257. //insert role
  1258. String req = String.Format("INSERT INTO actor (itemId, personId, roleId) VALUES({0},{1},{2});", bibliobrolItemId, user.Id, userRole.RoleId);
  1259. dbDa.InsertCommand.CommandText = req;
  1260. notify(new Notification(Notification.VERBOSE.persistentOperation, req, this));
  1261. try
  1262. {
  1263. dbDa.InsertCommand.ExecuteNonQuery();
  1264. }
  1265. catch (OleDbException odbe2)
  1266. {
  1267. notify(new Notification(Notification.VERBOSE.error, "Erreur dans la requête suivante :\n\r" + str.ToString(), odbe2, this));
  1268. }
  1269. }
  1270. dbCon.Close();
  1271.  
  1272. notify(new Notification(Notification.VERBOSE.opsResult, string.Format("{0}\n\rL'utilisateur {1} {2} est ajouté dans bibliobrol.", strDisp.ToString(), user.FirstName, user.LastName), this));
  1273. return user.Id;
  1274. }
  1275. /// <summary>
  1276. /// Update user data. Attention : don't care for update Person data
  1277. /// </summary>
  1278. /// <param name="user"></param>
  1279. /// <param name="dbCon"></param>
  1280. /// <param name="dbDa"></param>
  1281. /// <param name="strDisp"></param>
  1282. /// <returns>(bool) true if update is complete</returns>
  1283. private bool updateUser(User user, DbConnection dbCon, DbDataAdapter dbDa, StringBuilder strDisp)
  1284. {
  1285. strDisp.Append("L'utilisateur \"");
  1286. strDisp.Append(user.ToString());
  1287. if (user.Edited)
  1288. {
  1289. strDisp.Append("\" ne peut être mis à jour car il est verrouillé.");
  1290. notify(new Notification(Notification.VERBOSE.lowError, strDisp.ToString(), this));
  1291. return false;
  1292. }
  1293.  
  1294. int result = 0;
  1295. StringBuilder str = new StringBuilder();
  1296. try
  1297. {
  1298. result = dbDa.UpdateCommand.ExecuteNonQuery();
  1299. if (result == 1)
  1300. {
  1301. //update user
  1302. Address address = user.Address;
  1303. str.Append("UPDATE [user] SET firstName2='");
  1304. str.Append(MySQLUtils.escapeAndTrim(user.FirstName2));
  1305. str.Append("', firstName3='");
  1306. str.Append(MySQLUtils.escapeAndTrim(user.FirstName3));
  1307. str.Append("', birthplace='");
  1308. str.Append(MySQLUtils.escapeAndTrim(user.BirthPlace));
  1309. str.Append("', nationality='");
  1310. str.Append(MySQLUtils.escapeAndTrim(user.Nationality));
  1311. str.Append("', street='");
  1312. str.Append(MySQLUtils.escapeAndTrim(address.Street));
  1313. str.Append("', housenumber='");
  1314. str.Append(MySQLUtils.escapeAndTrim(address.HouseNumber));
  1315. str.Append("', boxnumber='");
  1316. str.Append(MySQLUtils.escapeAndTrim(address.BoxNumber));
  1317. str.Append("', zipcode='");
  1318. str.Append(MySQLUtils.escapeAndTrim(address.ZipCode));
  1319. str.Append("', municipality='");
  1320. str.Append(MySQLUtils.escapeAndTrim(address.Municipality));
  1321. str.Append("', country='");
  1322. str.Append(MySQLUtils.escapeAndTrim(address.Country));
  1323. str.Append("', nationalnumber='");
  1324. str.Append(MySQLUtils.escapeAndTrim(user.NationalNumber));
  1325. str.Append("' WHERE personId=");
  1326. str.Append(user.Id);
  1327. dbDa.UpdateCommand.CommandText = str.ToString();
  1328. notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
  1329. try
  1330. {
  1331. result = dbDa.UpdateCommand.ExecuteNonQuery();
  1332. updatePersPhones(user, dbCon, dbDa);
  1333. }
  1334. catch (Exception dbe)
  1335. {
  1336. notify(new Notification(Notification.VERBOSE.error, "Mise à jour de l'utilisateur", dbe, this));
  1337. }
  1338. }
  1339. }
  1340. catch (Exception dbe)
  1341. {
  1342. notify(new Notification(Notification.VERBOSE.error, "Mise à jour de la personne", dbe, this));
  1343. }
  1344. strDisp.Append((result == 1) ? "\" est mis à jour." : "\" ne peut être mis à jour.");
  1345. return (result == 1);
  1346. }
  1347. public void deleteUser(User user, bool deletePerson)
  1348. {
  1349. if (user.Id.Equals(Config.Owner.Id))
  1350. {
  1351. notify(new Notification(Notification.VERBOSE.opsResult, "On ne peut supprimer le propriétaire de l'application...", this));
  1352. return;
  1353. }
  1354. StringBuilder strDisp = new StringBuilder("L'utilisateur \"");
  1355. strDisp.Append(user.ToString());
  1356.  
  1357. DbConnection dbCon = dbpf.CreateConnection();
  1358. DbDataAdapter dbDa = dbpf.CreateDataAdapter();
  1359. dbDa.DeleteCommand = dbCon.CreateCommand();
  1360. dbCon.ConnectionString = conStr;
  1361. dbCon.Open();
  1362.  
  1363. //delete user
  1364. String str = "DELETE FROM [user] WHERE personId=" + user.Id;
  1365. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  1366. dbDa.DeleteCommand.CommandText = str;
  1367. int result = dbDa.DeleteCommand.ExecuteNonQuery();
  1368. //delete phones
  1369. str = "DELETE FROM personPhone WHERE personId=" + user.Id;
  1370. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  1371. dbDa.DeleteCommand.CommandText = str;
  1372. dbDa.DeleteCommand.ExecuteNonQuery();
  1373. //delete mails
  1374. str = "DELETE FROM personMail WHERE personId=" + user.Id;
  1375. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  1376. dbDa.DeleteCommand.CommandText = str;
  1377. dbDa.DeleteCommand.ExecuteNonQuery();
  1378.  
  1379. if (deletePerson)
  1380. {
  1381. //delete person
  1382. str = "DELETE FROM person WHERE id=" + user.Id;
  1383. notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
  1384. dbDa.DeleteCommand.CommandText = str;
  1385. dbDa.DeleteCommand.ExecuteNonQuery();
  1386. }
  1387. dbCon.Close();
  1388.  
  1389. strDisp.Append((result == 1) ? "\" est supprimé." : "\" ne peut être supprimé.");
  1390. notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
  1391. }
  1392. #endregion
  1393.  
  1394. public ActorRole getUserRole()
  1395. {
  1396. return userRole;
  1397. }
  1398.  
  1399. #endregion
  1400. }
  1401. }

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 octets1734948748 23/12/2024 11:12:28
| _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 11:12:28 Cette version de la page est en cache (à la date du 23/12/2024 11:12:28) 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//NoChangePersonDao.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.