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 octets1734949656 23/12/2024 11:27:36
| _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.

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 16/10/2009, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/cs-bibliobrol-source-rf-model/dao/mysql//NoChangePersonDao.cs.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.