AccessPersonDao.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using be.gaudry.bibliobrol.model;
using be.gaudry.bibliobrol.model.dao;
using be.gaudry.observer;
using be.gaudry.bibliobrol.model.identity;
using be.gaudry.bibliobrol.model.enums;
using be.gaudry.model.exceptions;
using be.gaudry.bibliobrol.config;
 
namespace be.gaudry.bibliobrol.model.dao.msaccess
{
    public sealed class AccessPersonDao : Observable, IPersonDao
    {
        private String conStr;
        private int bibliobrolItemId;//used to get the user role;
        private ActorRole userRole;
 
        #region singleton
        static AccessPersonDao instance = null;
        static readonly object padlock = new object();
        private DbProviderFactory dbpf;
        AccessPersonDao()
        {
            dbpf = ((AccessFactory)AccessFactory.Instance).getDbpf();
            conStr = ((AccessFactory)AccessFactory.Instance).getConnectionString();
            bibliobrolItemId = Config.BibliobrolId;
            userRole = new ActorRole(11, "Utilisateur Bibliobrol"); 
            notify(new Notification(Notification.VERBOSE.debug, "Singleton AccessPersonDao call", this));
        }
        public static AccessPersonDao Instance
        {
            get
            {
                lock(padlock)
                {
                    if(instance==null)
                    {
                        instance = new AccessPersonDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region private methods
        /// <summary>
        /// Test if a person exists into the Access DataBase.
        /// Tests are done only on lastname and firstname fields
        /// </summary>
        /// <param name="person">(Person) person to test</param>
        /// <param name="dbDa">(DbDataAdapter) to avoid create a new connection</param>
        /// <param name="dbCon">(DbConnection) to avoid create a new connection</param>
        /// <returns>(int) id of the person found (-1 if not found)</returns>
        private int personExists(Person person, DbDataAdapter dbDa, DbConnection dbCon)
        {
            dbDa.SelectCommand = dbCon.CreateCommand();
 
 
            /*StringBuilder str = new StringBuilder("SELECT id FROM person WHERE UCASE(lastName) = '");
                        Console.WriteLine("test sql 1 : " + str.ToString());
                        str.Append(AccessUtils.escapeAndTrim(person.LastName).ToUpper());
                        Console.WriteLine("test sql 2 : " + str.ToString());
                        str.Append("' AND UCASE(firstName)='");
                        Console.WriteLine("test sql 3 : " + str.ToString());
                        str.Append(AccessUtils.escapeAndTrim(person.FirstName).ToUpper());
                        Console.WriteLine("test sql 4 : " + str.ToString());
                        str.Append("'");
                        Console.WriteLine("test sql 5 : " + str.ToString());
                        dbDa.SelectCommand.CommandText = str.ToString();*/
 
 
 
            string str = string.Format(
                "SELECT id FROM person WHERE UCASE(lastName) = '{0}' AND UCASE(firstName)='{1}'",
                AccessUtils.escapeAndTrim(person.LastName).ToUpper(),
                AccessUtils.escapeAndTrim(person.FirstName).ToUpper()
                );
            dbDa.SelectCommand.CommandText = str;
 
 
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
 
            bool conOpen = dbCon.State.Equals(ConnectionState.Open);
            if (!conOpen)
                dbCon.Open();
            int r = -1;
            try
            {
                object o = dbDa.SelectCommand.ExecuteScalar();
                if (o != null) r = (int)o;
            }
            catch (OleDbException ode)
            {
                notify(new Notification(Notification.VERBOSE.error, "Vérification d'une personne", ode, this));
            }
            dbCon.Close();
            return r;
        }
        /// <summary>
        /// Test if a user exists into the Access DataBase.
        /// Tests are done on person id field
        /// </summary>
        /// <exception cref="System.Data.OleDb.OleDbException">OleDbException</exception>
        /// <remarks>
        /// Be carefull : a user with no id or default id (-1) will returns false...
        /// </remarks>
        /// <param name="person">(User) user to test</param>
        /// <param name="dbDa">(DbDataAdapter) to avoid create a new connection</param>
        /// <param name="dbCon">(DbConnection) to avoid create a new connection</param>
        /// <returns>true if user exists</returns>
        private bool userExists(User user, DbCommand com)
        {
            com.CommandText = string.Format("SELECT COUNT(personId) FROM [user] WHERE personId = {0}", user.Id);
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
 
            int r = (int)com.ExecuteScalar();
            return r > 0;
        }
        /// <summary>
        /// Verify if some phones had been deleted, and delete it from the Access DataBase. 
        /// Add new phones if exists. 
        /// Update existing phones. 
        /// </summary>
        /// <param name="person">(Person) person containing phones list</param>
        /// <param name="dbCon"></param>
        private void updatePersPhones(User user, DbConnection dbCon, DbDataAdapter dbDa)
        {
            dbDa.SelectCommand = dbCon.CreateCommand(); 
            /*dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();*/
            DbCommand command = dbCon.CreateCommand();
            List<Phone> phones = new List<Phone>();
            dbDa.SelectCommand.CommandText = String.Format(
                "SELECT * FROM personPhone WHERE personId = {0}", 
                user.Id
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
 
            DataTable dt = new DataTable();
            dbDa.Fill(dt);
            try
            {
                foreach (DataRow phoneRow in dt.Rows)
                {
                    phones.Add(getPhoneNbr(phoneRow));
                }
            }
            catch (BuildObjectException boe)
            {
                notify(new Notification(Notification.VERBOSE.error, "Vérification des numéros de téléphone dans la DB", boe, this));
            }
 
            foreach (Phone phone in phones)
            {
                if (!user.Phones.Contains(phone))
                {
                    deletePhone(phone, command);
                }
            }
 
            foreach (Phone phone in user.Phones)
            {
                if (phone.Id>0)
                {
                    updatePhone(phone, command);
                }
                else
                {
                    insertPhone(phone, user.Id, command);
                }
            }
        }
        private Person getPerson(DataRow row)
        {
            if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no lastName found");
            Person p = new Person((int)row["id"], (String)row["lastName"]);
            try
            {
 
                if (!(row["firstName"] is DBNull))
                {
                    p.FirstName = (String)row["firstName"];
                }
                if (!(row["pseudo"] is DBNull))
                {
                    p.Pseudo = (String)row["pseudo"];
                }
                if (!(row["personLocked"] is DBNull))
                {
                    p.Edited = !row["personLocked"].ToString().Equals("False");
                }
                if (!(row["sex"] is DBNull))
                {
                    SEX sex;
                    try
                    {
                        sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
                    }
                    catch (Exception)
                    {
                        sex = SEX._;
                    }
                    p.Sex = sex;
                }
                if (!(row["birthdate"] is DBNull))
                {
                    p.Birthdate = (DateTime)row["birthdate"];
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", e, this));
            }
            return p;
        }
 
        private Phone getPhoneNbr(DataRow row)
        {
            if (row["id"] is DBNull || row["phoneNum"] is DBNull) throw new BuildObjectException("No id or no phoneNum found");
            Phone p = new Phone((int)row["id"], (String)row["phoneNum"]);
            if (!(row["phoneType"] is DBNull))
            {
                Phone.TYPE t;
                try
                {
                    t = (Phone.TYPE)Enum.Parse(typeof(Phone.TYPE), (String)row["phoneType"]);
                }
                catch (Exception)
                {
                    t = Phone.TYPE._;
                }
                p.Type = t;
            }
            return p;
        }
        /// <summary>
        /// Used only to store phones from a new person
        /// </summary>
        /// <param name="p">Person</param>
        /// <param name="dbDa">DbDataAdapter</param>
        /// <returns>-1 if person is not a new person, or number of inserted otherwise</returns>
        private int insertPhones(User user, DbDataAdapter dbDa)
        {
            if (user.Id < 0) return -1;
            int i = 0;
            foreach (Phone phone in user.Phones)
            {
                insertPhone(phone, user.Id, dbDa.InsertCommand);
            }
            return i;
        }
        private bool insertPhone(Phone phone, int persId, DbCommand com)
        {
            com.CommandText = String.Format(
                "INSERT INTO personPhone (personId, phoneNum, phoneType) VALUES({0},'{1}','{2}');",
                persId,
                phone.Number,
                Enum.GetName(typeof(Phone.TYPE), phone.Type)
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            return (com.ExecuteNonQuery() == 1);
        }
        private bool deletePhone(Phone phone, DbCommand com)
        {
            com.CommandText = "DELETE FROM personPhone WHERE id = " + phone.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            return (com.ExecuteNonQuery() == 1);
        }
        /// <summary>
        /// Update a phone. 
        /// Don't update person id in the DB because moving a phone from a person to another is not implemented.
        /// </summary>
        /// <param name="phone">(Phone) phone to update</param>
        /// <param name="com">(DbCommand) command to use</param>
        /// <returns>true if updated</returns>
        private bool updatePhone(Phone phone, DbCommand com)
        {
            com.CommandText = String.Format(
            "UPDATE personPhone SET phoneNum='{0}',phoneType='{1}' WHERE id={2}",
            phone.Number,
            Enum.GetName(typeof(Phone.TYPE), phone.Type),
            phone.Id
            );
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            return (com.ExecuteNonQuery() == 1);
        }
 
        /// <summary>
        /// Used to load persons with their ids.
        /// </summary>
        /// <param name="ids">Array of persons'ids (int values)</param>
        /// <returns>A list of persons</returns>
        private List<Person> loadSelectedPersons(int[] ids)
        {
            List<Person> persons = new List<Person>();
            if (ids.Length < 1)
                return persons;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            DataSet ds = new DataSet();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            //build persons datatable
            StringBuilder str = new StringBuilder("SELECT id ,lastName, firstName, pseudo, birthdate, sex, personLocked FROM person");
            if (ids[0]!=-1)
            {//load all persons if we have -1 value
                str.Append(" WHERE id IN (");
                for (int i = 0; i < ids.Length; i++)
                {
                    if (i > 0) str.Append(",");
                    str.Append(ids[i]);
                }
                str.Append(")");
            }
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            try
            {
                dbDa.Fill(ds, "persons");
 
                //build person objects
                foreach (DataRow persRow in ds.Tables["persons"].Rows)
                {
                    try
                    {
                        persons.Add(getPerson(persRow));
                    }
                    catch (BuildObjectException boe)
                    {
                        notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
                    }
                }
            }
            catch (Exception fillPersEx)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des personnes", fillPersEx, this));
                return persons;
            }
            finally
            {
                dbCon.Close();
            }
            return persons;
        }
        private bool lockUnlockPerson(int id, bool toLocked)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            StringBuilder str =  new StringBuilder("UPDATE person SET personLocked=");
            str.Append((toLocked)?"TRUE":"FALSE");
            str.Append(" WHERE id=");
            str.Append(id);
            dbDa.UpdateCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            int r = dbDa.UpdateCommand.ExecuteNonQuery();
            dbCon.Close();
            return (r == 1);
        }
 
        #endregion
 
        #region IPersonDao Members
        public String getConStr() { return conStr; }
 
        #region person
 
        /// <summary>
        /// Test if a person exists with the same name and firstname
        /// </summary>
        /// <param name="person">(Person) person to test</param>
        /// <returns>(int) id of the person found (-1 if not found)</returns>
        public int personExists(Person person)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            return personExists(person, dbDa, dbCon);
        }
 
        /// <summary>
        /// Lock person to avoid concurent modifications.
        /// </summary>
        /// <param name="id">person's id to lock</param>
        /// <returns>false if a problem occurs (todo : if already locked)</returns>
        public bool lockPerson(int id)
        {
            return lockUnlockPerson(id, true);
        }
        /// <summary>
        /// unlock person to allow modifications.
        /// </summary>
        /// <param name="id">person's id to unlock</param>
        public bool unlockPerson(int id)
        {
            return lockUnlockPerson(id, false);
        }
        /// <summary>
        /// Insert a person into the Access DataBase
        /// </summary>
        /// <param name="person">(Person) person to insert</param>
        /// <returns>(int) id of the new person (-1 if a problem occurs)</returns>
        public int insertPerson(Person person)
        {
            StringBuilder strDisp = new StringBuilder("La personne ");
            strDisp.Append(person.ToString());
            int lastId = -1;
            if (person.Id <= 0)
            {
                DbConnection dbCon = dbpf.CreateConnection();
                DbDataAdapter dbDa = dbpf.CreateDataAdapter();
                dbDa.InsertCommand = dbCon.CreateCommand();
                dbCon.ConnectionString = conStr;
                dbCon.Open();
                lastId = insertPerson(person, dbCon, dbDa, strDisp);
                dbCon.Close();
            }
            strDisp.Append((lastId > 0) ? " est ajoutée." : " ne peut être ajoutée.");
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return lastId;
        }
 
        public int insertBibliobrolUser(Person person)
        {
            throw new Exception("Use of deprecated method : use saveUser(be.gaudry.bibliobrol.User) instead.");
        }
 
        internal int insertPerson(Person person, DbConnection dbCon, DbDataAdapter dbDa, StringBuilder strDisp)
        {
            int lastId = -1;
            /*if (personExists(person, dbDa, dbCon)>0)
            {
                strDisp.Append(" existe déjà dans la base de données, ");
                return lastId;
            }*/
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO person (lastName, firstName, pseudo, sex, personLocked, birthdate) VALUES('");
            str.Append(AccessUtils.escapeAndTrim(person.LastName));
            str.Append("','");
            str.Append(AccessUtils.escapeAndTrim(person.FirstName));
            str.Append("','");
            str.Append(AccessUtils.escapeAndTrim(person.Pseudo));
            str.Append("','");
            str.Append(Enum.GetName(typeof(SEX), person.Sex));
            str.Append("', FALSE,");
            if (person.Birthdate.Equals(new DateTime(0L)))
            {
                str.Append("NULL");
            }
            else
            {
                str.Append(AccessUtils.getAccessDate(person.Birthdate));
            }
            str.Append(");");
            dbDa.InsertCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, str.ToString(), this));
            int r = 0;
            try
            {
                r = dbDa.InsertCommand.ExecuteNonQuery();
                if (r > 1)
                    notify(new Notification(Notification.VERBOSE.error, "Insertions multiples personnes", this));
                //bad smell code : may not work with concurent acces
                dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY";
                lastId = (int)dbDa.InsertCommand.ExecuteScalar();
                person.Id = lastId;
            }
            catch (OleDbException ode)
            {
                notify(new Notification(Notification.VERBOSE.error, "Ajout d'une personne", ode, this));
            }
 
            return lastId;
        }
        /// <summary>
        /// Load a person from the Access DB with an id arg. 
        /// If this person shoud be modified, editing bool arg is true
        /// to avoid concurent modifications (nobody else can save this person).
        /// </summary>
        /// <param name="id">Id of the selected person</param>
        /// <param name="editing">(bool) Shoud be modified or not</param>
        /// <returns>Selected person, or a new person if _ found</returns>
        public Person loadPerson(int id, bool editing)
        {
            Person p;
            List<Person> persons = loadSelectedPersons(new int[] { id });
            if (persons.Count > 0)
            {
                p = persons[0];
                if (editing && !p.Edited) lockPerson(id);
            }
            else p = new Person();
            return p;
        }
 
        /// <summary>
        /// Load a person from the persistant layer with a lastname and a firstname. 
        /// Search case insensitive.
        /// </summary>
        /// <param name="lastName">(string) lastname of the searched person</param>
        /// <param name="firstName">(string) firstname of the searched person</param>
        /// <returns>Persons found, or empty list</returns>
        public List<Person> loadPersons(string lastName, string firstName)
        {
            StringBuilder str = new StringBuilder("SELECT id FROM person WHERE UCASE(lastName) LIKE ('%");
            str.Append(lastName.ToUpper().Trim());
            str.Append("%') AND UCASE(firstName) LIKE ('%");
            str.Append(firstName.ToUpper().Trim());
            str.Append("%')");
 
            List<int> ids = new List<int>();
 
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable persDt = new DataTable();
            try
            {
                dbDa.Fill(persDt);
            }
            catch (Exception e)
            {
                notify(new Notification(
                    Notification.VERBOSE.error,
                    String.Format("Chargement de la liste de personnes correspondant à {0} {1}", lastName, firstName),
                    e,
                    this
                ));
            }
            foreach (DataRow row in persDt.Rows)
            {
                ids.Add((int)row["id"]);
            }
            persDt.Dispose();
            dbCon.Close();
            if (ids.Count < 1) return new List<Person>();
            return loadSelectedPersons(ids.ToArray());
        }
 
        /// <summary>
        /// Load all persons matches (case insensitive) lastName
        /// </summary>
        /// <param name="lastName">(String) lastName to match</param>
        /// <returns>List of persons with the same lastName</returns>
        public List<Person> loadPersons(String lastName)
        {
            List<Person> persons = new List<Person>();
            if (lastName == null || lastName.Equals(String.Empty))
                return persons;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            DataSet ds = new DataSet();
            //DataRelation dr;
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            //build persons datatable
            StringBuilder str = new StringBuilder("SELECT id ,lastName, firstName, pseudo, birthdate, sex, personLocked FROM person");
            str.Append(" WHERE UCASE(lastName) = '");
            str.Append(AccessUtils.escapeAndTrim(lastName.ToUpper()));
            str.Append("'");
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            try
            {
                dbDa.Fill(ds, "persons");
 
                //build person objects
                foreach (DataRow persRow in ds.Tables["persons"].Rows)
                {
                    try
                    {
                        persons.Add(getPerson(persRow));
                    }
                    catch (BuildObjectException boe)
                    {
                        notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
                    }
                }
            }
            catch (Exception fillPersEx)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des personnes", fillPersEx, this));
                return persons;
            }
            finally
            {
                dbCon.Close();
            }
            return persons;
        }
        /// <summary>
        /// Load all persons from the  Access DataBase
        /// </summary>
        /// <returns>List of Persons</returns>
        public List<Person> loadPersons()
        {
            return loadSelectedPersons(new int[] { -1 });
        }
        /// <summary>
        /// Load persons value objects (only display and id)
        /// </summary>
        /// <returns>List of PersonVos</returns>
        public List<PersonLO> loadVos()
        {
            return loadPersonsVos(new ActorRole());
        }
        /// <summary>
        /// Load persons value objects with selected role (only display and id)
        /// If role id is less or equals than 0, all persons are loaded
        /// </summary>
        /// <param name="editing">(ActorRole) person's role</param>
        /// <returns>List of PersonVos</returns>
        public List<PersonLO> loadPersonsVos(ActorRole role)
        {
            StringBuilder str = new StringBuilder("SELECT DISTINCT (person.id) ,person.lastName, person.firstName, person.pseudo");
            if (role.RoleId > 0)
            {
                str.Append(", actor.roleId FROM person");
                str.Append(" LEFT JOIN actor ON actor.personId = person.id ");
                str.Append(" WHERE roleId = ");
                str.Append(role.RoleId);
            }
            else
            {
                str.Append(" FROM person");
            }
            return loadPersonsVosByQuery(str.ToString());
        }
        /// <summary>
        /// Load persons value objects with same lastname
        /// </summary>
        /// <param name="name">(string) person's lastname</param>
        /// <returns>List of PersonVos</returns>
        public List<PersonLO> loadPersonsVos(string name)
        {
            string str = string.Format(
                "SELECT id ,lastName, firstName, pseudo, birthdate, sex, personLocked FROM person WHERE UCASE(lastName) LIKE('{0}')",
                AccessUtils.escapeAndTrim(name.ToUpper())
                );
 
            return loadPersonsVosByQuery(str);
        }
        /// <summary>
        /// Load persons value objects with specific query
        /// </summary>
        /// <param name="query">(string) sql query</param>
        /// <returns>List of PersonVos</returns>
        private List<PersonLO> loadPersonsVosByQuery(string query)
        {
            List<PersonLO> persons = new List<PersonLO>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
 
            dbDa.SelectCommand.CommandText = query;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable persDt = new DataTable();
            try
            {
                dbDa.Fill(persDt);
 
                PERSON_DISPLAY persDisp = Config.PersonDisplay;
                switch (persDisp)
                {
                    case PERSON_DISPLAY.firstname_lastname: addPersonsVos_firstname_lastname(persDt.Rows, persons); break;
                    case PERSON_DISPLAY.lastname_firstname: addPersonsVos_lastname_firstname(persDt.Rows, persons); break;
                    case PERSON_DISPLAY.lastname_firstname_pseudo: addPersonsVos_firstname_lastname_pseudo(persDt.Rows, persons); break;
                    case PERSON_DISPLAY.pseudo: addPersonsVos_pseudo(persDt.Rows, persons); break;
                    case PERSON_DISPLAY.pseudo_lastname_firstname: addPersonsVos_pseudo_lastname_firstname(persDt.Rows, persons); break;
                    default: addPersonsVos_pseudo(persDt.Rows, persons); break;
                }
                //addPersonsVos(persDt.Rows, persons);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement de la liste de personnes selon la requête suivante :\n\r" + query, e, this));
            }
            persDt.Dispose();
            dbCon.Close();
            return persons;
        }
 
        private void addPersonsVos_pseudo_lastname_firstname(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull))
                {
                    if (!(row["pseudo"] is DBNull))
                    {
                        persDisplay.Append((String)row["pseudo"]);
                    }
                    if (!(row["lastName"] is DBNull))
                    {
                        persDisplay.Append(" (");
                        persDisplay.Append((String)row["lastName"]);
                    }
                    if (!(row["firstName"] is DBNull))
                    {
                        persDisplay.Append(" ");
                        persDisplay.Append((String)row["firstName"]);
                        persDisplay.Append(")");
                    }
                    if (persDisplay.Length > 0)
                    {
                        persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                    }
                }
            }
        }
 
        private void addPersonsVos_pseudo(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull))
                {
                    if (!(row["pseudo"] is DBNull) && !String.Empty.Equals(row["pseudo"]))
                    {
                        persDisplay.Append((String)row["pseudo"]);
                    }
                    else
                    {
                        persDisplay.Append("Sans pseudo");
                        persDisplay.Append(" (");
                        if (!(row["lastName"] is DBNull))
                        {
                            persDisplay.Append((String)row["lastName"]);
                        }
                        if (!(row["firstName"] is DBNull))
                        {
                            persDisplay.Append(" ");
                            persDisplay.Append((String)row["firstName"]);
                        } 
                        persDisplay.Append(")");
                    }
                    if (persDisplay.Length > 0)
                    {
                        persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                    }
                }
            }
        }
 
        private void addPersonsVos_firstname_lastname_pseudo(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull))
                {
                    if (!(row["firstName"] is DBNull))
                    {
                        persDisplay.Append((String)row["firstName"]);
                    }
                    if (!(row["lastName"] is DBNull))
                    {
                        persDisplay.Append(" ");
                        persDisplay.Append((String)row["lastName"]);
                    }
                    if (!(row["pseudo"] is DBNull))
                    {
                        persDisplay.Append(" (");
                        persDisplay.Append((String)row["pseudo"]);
                        persDisplay.Append(")");
                    }
                    if (persDisplay.Length > 0)
                    {
                        persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                    }
                }
            }
        }
 
        private void addPersonsVos_lastname_firstname(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull))
                {
                    if (!(row["lastName"] is DBNull))
                    {
                        persDisplay.Append((String)row["lastName"]);
                    }
                    if (!(row["firstName"] is DBNull))
                    {
                        persDisplay.Append(" ");
                        persDisplay.Append((String)row["firstName"]);
                    }
                    if (persDisplay.Length > 0)
                    {
                        persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                    }
                }
            }
        }
 
        private void addPersonsVos_firstname_lastname(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull))
                {
                    if (!(row["firstName"] is DBNull))
                    {
                        persDisplay.Append((String)row["firstName"]);
                    }
                    if (!(row["lastName"] is DBNull))
                    {
                        persDisplay.Append(" ");
                        persDisplay.Append((String)row["lastName"]);
                    }
                    if (persDisplay.Length > 0)
                    {
                        persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                    }
                }
            }
        }
 
        private void addPersonsVos(DataRowCollection dataRowCollection, List<PersonLO> persons)
        {
            StringBuilder persDisplay;
            foreach (DataRow row in dataRowCollection)
            {
                persDisplay = new StringBuilder();
                if (!(row["id"] is DBNull) && !(row["lastName"] is DBNull))
                {
                    if (row["firstName"] is DBNull)
                    {
                        if (!(row["pseudo"] is DBNull))
                        {
                            persDisplay.Append((String)row["pseudo"]);
                            persDisplay.Append("\t");
                        }
                        persDisplay.Append((String)row["lastName"]);
                    }
                    else
                    {
                        if (!(row["pseudo"] is DBNull) && !((String)row["pseudo"]).Equals(String.Empty))
                        {
                            persDisplay.Append((String)row["lastName"]);
                            persDisplay.Append("\t");
                            persDisplay.Append((String)row["firstName"]);
                            persDisplay.Append(" ");
                            persDisplay.Append((String)row["pseudo"]);
                        }
                        else
                            persDisplay.Append((String)row["lastName"] + "\t" + (String)row["firstName"]);
                    }
                    persons.Add(new PersonLO((int)row["id"], persDisplay.ToString()));
                }
            }
        }
 
 
        /// <summary>
        /// Store new values for a person. 
        /// Verify if some phones had been deleted, and delete it from the Access DataBase. 
        /// Add new phones if exists. 
        /// Update existing phones.
        /// </summary>
        /// <param name="person">Person with new values to store</param>
        /// <returns>true if update is done</returns>
        public bool updatePerson(Person person)
        {
            StringBuilder strDisp = new StringBuilder("La personne \"");
            strDisp.Append(person.ToString());
            if (person.Edited)
            {
                strDisp.Append("\" ne peut être mise à jour car elle est verrouillée.");
                notify(new Notification(Notification.VERBOSE.lowError, strDisp.ToString(), this));
                return false;
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            int result = updatePerson(person, dbCon, dbDa);
            dbCon.Close();
            strDisp.Append((result == 1) ? "\" est mise à jour." : "\" ne peut être mise à jour.");
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return result == 1;
        }
 
        private int updatePerson(Person person, DbConnection dbCon, DbDataAdapter dbDa)
        {
            StringBuilder str = new StringBuilder();
            str.Append("UPDATE person SET lastName='");
            str.Append(AccessUtils.escapeAndTrim(person.LastName));
            str.Append("', firstName='");
            str.Append(AccessUtils.escapeAndTrim(person.FirstName));
            str.Append("', pseudo='");
            str.Append(AccessUtils.escapeAndTrim(person.Pseudo));
            str.Append("', sex='");
            str.Append(Enum.GetName(typeof(SEX), person.Sex));
            str.Append("', birthdate=");
            if (person.Birthdate.Equals(new DateTime(0L)))
            {
                str.Append("NULL");
            }
            else
            {
                str.Append(AccessUtils.getAccessDate(person.Birthdate));
            }
            str.Append(", personLocked=FALSE WHERE id=");
            str.Append(person.Id);
            dbDa.UpdateCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            int result = 0;
            try
            {
                result = dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception dbe)
            {
                unlockPerson(person.Id);
                notify(new Notification(Notification.VERBOSE.error, "Mise à jour de la personne", dbe, this));
            }
            return result;
        }
 
        /// <summary>
        /// Delete a person from the Access DataBase, 
        /// and delete all associated tables !!!
        /// </summary>
        /// <param name="p">(Person) person to delete</param>
        /// <returns>true if deleted</returns>
        public bool deletePerson(Person p)
        {
            if (p.Id.Equals(Config.Owner.Id))
            {
                notify(new Notification(Notification.VERBOSE.opsResult, "On ne peut supprimer le propriétaire de l'application...", this));
                return false;
            }
            StringBuilder strDisp = new StringBuilder("La personne \"");
            strDisp.Append(p.ToString());
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            //delete mail
            String str = "DELETE FROM personMail WHERE personId=" + p.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
            //delete phones
            str = "DELETE FROM personPhone WHERE personId=" + p.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
            //delete actor
            str = "DELETE FROM actor WHERE personId=" + p.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
            //delete user
            str = "DELETE FROM [user] WHERE personId=" + p.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
            //delete person
            str = "DELETE FROM person WHERE id=" + p.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            int result = dbDa.DeleteCommand.ExecuteNonQuery();
            dbCon.Close();
            strDisp.Append((result == 1) ? "\" est supprimée." : "\" ne peut être supprimée.");
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return result == 1;
        } 
        #endregion
 
        #region user
        public User loadUser(int persId)
        {
            User user = null;
 
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            DataSet ds = new DataSet();
            DataRelation dr;
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            //build user datatable
            StringBuilder str = new StringBuilder("SELECT p.*, u.* FROM person AS p");
            str.Append(" LEFT JOIN [user] AS u ON p.id = u.personId ");
            str.Append(" WHERE p.id = ");
            str.Append(persId);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
 
            try
            {
                dbDa.Fill(ds, "persons");
                //build phones datatable
                str = new StringBuilder("SELECT id , personId, phoneNum, phoneType FROM personPhone WHERE personId = ");
                str.Append(persId);
                dbDa.SelectCommand.CommandText = str.ToString();
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
 
                dbDa.Fill(ds, "phones");
                //build relation
                dr = new DataRelation(
                    "pers-phones",
                    ds.Tables["persons"].Columns["id"],
                    ds.Tables["phones"].Columns["personId"]
                    );
                try
                {
                    ds.Relations.Add(dr);
                }
                catch (Exception dre)
                {
                    notify(new Notification(Notification.VERBOSE.error, "Liaison entre les personnes et les téléphones", dre, this));
                }
                //build person objects
                foreach (DataRow row in ds.Tables["persons"].Rows)
                {
                    try
                    {
                        if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no lastName found");
                        user = new User((int)row["id"], (String)row["lastName"]);
                        //get person data
                        try
                        {
                            if (!(row["firstName"] is DBNull))
                            {
                                user.FirstName = (String)row["firstName"];
                            }
                            if (!(row["pseudo"] is DBNull))
                            {
                                user.Pseudo = (String)row["pseudo"];
                            }
                            if (!(row["personLocked"] is DBNull))
                            {
                                user.Edited = !row["personLocked"].ToString().Equals("False");
                            }
                            if (!(row["sex"] is DBNull))
                            {
                                SEX sex;
                                try
                                {
                                    sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
                                }
                                catch (Exception)
                                {
                                    sex = SEX._;
                                }
                                user.Sex = sex;
                            }
                            if (!(row["birthdate"] is DBNull))
                            {
                                user.Birthdate = (DateTime)row["birthdate"];
                            }
                        }
                        catch (Exception e)
                        {
                            notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", e, this));
                        }
 
                        Address adress = user.Address;
                        //get user data
                        try
                        {
                            if (!(row["firstName2"] is DBNull))
                            {
                                user.FirstName2 = (String)row["firstName2"];
                            }
                            if (!(row["firstName3"] is DBNull))
                            {
                                user.FirstName3 = (String)row["firstName3"];
                            }
                            if (!(row["birthplace"] is DBNull))
                            {
                                user.BirthPlace = (String)row["birthplace"];
                            }
                            if (!(row["nationality"] is DBNull))
                            {
                                user.Nationality = (String)row["nationality"];
                            }
                            if (!(row["street"] is DBNull))
                            {
                                adress.Street = (String)row["street"];
                            }
                            if (!(row["housenumber"] is DBNull))
                            {
                                adress.HouseNumber = (String)row["housenumber"];
                            }
                            if (!(row["boxnumber"] is DBNull))
                            {
                                adress.BoxNumber = (String)row["boxnumber"];
                            }
                            if (!(row["zipcode"] is DBNull))
                            {
                                adress.ZipCode = (String)row["zipcode"];
                            }
                            if (!(row["municipality"] is DBNull))
                            {
                                adress.Municipality = (String)row["municipality"];
                            }
                            if (!(row["country"] is DBNull))
                            {
                                adress.Country = (String)row["country"];
                            }
                            if (!(row["nationalnumber"] is DBNull))
                            {
                                user.NationalNumber = (String)row["nationalnumber"];
                            }
                        }
                        catch (Exception e2)
                        {
                            notify(new Notification(Notification.VERBOSE.error, "Chargement de l'utilisateur", e2, this));
                        }
 
                        foreach (DataRow phoneRow in row.GetChildRows(dr))
                        {
                            user.addPhone(getPhoneNbr(phoneRow));
                        }
                    }
                    catch (BuildObjectException boe)
                    {
                        notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", boe, this));
                    }
                }
            }
            catch (Exception fillPersEx)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement de la personne", fillPersEx, this));
            }
            finally
            {
                dbCon.Close();
            }
            return user;
        }
        /// <summary>
        /// Updates a <code>be.gaudry.bibliobrol.model.User</code> if exists or creates it (and sets role).
        /// </summary>
        /// <param name="user"></param>
        /// <returns>Person Id</returns>
        public int saveUser(User user)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
 
            dbCon.Open();
            StringBuilder str = new StringBuilder();
            StringBuilder strDisp = new StringBuilder();
 
            if (user.Id < 0)
            {
                user.Id = insertPerson(user, dbCon, dbDa, strDisp);
            }
            else
            {
                updatePerson(user, dbCon, dbDa);
            }
 
            bool testUserExists;
            try
            {
                testUserExists = userExists(user, dbDa.InsertCommand);
            }
            catch (OleDbException e)
            {
                dbCon.Close();
 
                notify(new Notification(Notification.VERBOSE.error, "Impossible de tester l'utilisateur", e, this));
                return -1;
            }
 
 
            if (testUserExists)
            {
                updateUser(user, dbCon, dbDa, strDisp);
                dbCon.Close();
 
                notify(new Notification(Notification.VERBOSE.opsResult, "Mise à jour d'un utilisateur", strDisp.ToString(), this));
                return user.Id;
            }
            else
            {
                if (insertPhones(user, dbDa) == -1)
                    notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion des N° de Tf pour " + user.ToString(), this));
 
                str = new StringBuilder("INSERT INTO [user] (personId,firstName2,firstName3,birthplace,nationality");
                str.Append(",street,housenumber,boxnumber,zipcode,municipality,country,nationalnumber)");
                str.Append(" VALUES(");
                Address adress = user.Address;
                str.Append(user.Id);
                str.Append(", '");
                str.Append(AccessUtils.escapeAndTrim(user.FirstName2));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(user.FirstName3));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(user.BirthPlace));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(user.Nationality));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.Street));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.HouseNumber));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.BoxNumber));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.ZipCode));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.Municipality));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(adress.Country));
                str.Append("', '");
                str.Append(AccessUtils.escapeAndTrim(user.NationalNumber));
 
                str.Append("')");
                dbDa.InsertCommand.CommandText = str.ToString();
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
                try
                {
                    dbDa.InsertCommand.ExecuteNonQuery();
                }
                catch (OleDbException odbe)
                {
                    notify(new Notification(Notification.VERBOSE.error, "Erreur dans la requête suivante :\n\r" + str.ToString(), odbe, this));
                }
 
 
                //insert role
                String req = String.Format("INSERT INTO actor (itemId, personId, roleId) VALUES({0},{1},{2});", bibliobrolItemId, user.Id, userRole.RoleId);
                dbDa.InsertCommand.CommandText = req;
                notify(new Notification(Notification.VERBOSE.persistentOperation, req, this));
                try
                {
                    dbDa.InsertCommand.ExecuteNonQuery();
                }
                catch (OleDbException odbe2)
                {
                    notify(new Notification(Notification.VERBOSE.error, "Erreur dans la requête suivante :\n\r" + str.ToString(), odbe2, this));
                }
            }
            dbCon.Close();
 
            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));
            return user.Id;
        }
        /// <summary>
        /// Update user data. Attention : don't care for update Person data
        /// </summary>
        /// <param name="user"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        /// <param name="strDisp"></param>
        /// <returns>(bool) true if update is complete</returns>
        private bool updateUser(User user, DbConnection dbCon, DbDataAdapter dbDa, StringBuilder strDisp)
        {
            strDisp.Append("L'utilisateur \"");
            strDisp.Append(user.ToString());
            if (user.Edited)
            {
                strDisp.Append("\" ne peut être mis à jour car il est verrouillé.");
                notify(new Notification(Notification.VERBOSE.lowError, strDisp.ToString(), this));
                return false;
            }
 
            int result = 0;
            StringBuilder str = new StringBuilder();
            try
            {
                result = dbDa.UpdateCommand.ExecuteNonQuery();
                if (result == 1)
                {
                    //update user
                    Address address = user.Address;
                    str.Append("UPDATE [user] SET firstName2='");
                    str.Append(AccessUtils.escapeAndTrim(user.FirstName2));
                    str.Append("', firstName3='");
                    str.Append(AccessUtils.escapeAndTrim(user.FirstName3));
                    str.Append("', birthplace='");
                    str.Append(AccessUtils.escapeAndTrim(user.BirthPlace));
                    str.Append("', nationality='");
                    str.Append(AccessUtils.escapeAndTrim(user.Nationality));
                    str.Append("', street='");
                    str.Append(AccessUtils.escapeAndTrim(address.Street));
                    str.Append("', housenumber='");
                    str.Append(AccessUtils.escapeAndTrim(address.HouseNumber));
                    str.Append("', boxnumber='");
                    str.Append(AccessUtils.escapeAndTrim(address.BoxNumber));
                    str.Append("', zipcode='");
                    str.Append(AccessUtils.escapeAndTrim(address.ZipCode));
                    str.Append("', municipality='");
                    str.Append(AccessUtils.escapeAndTrim(address.Municipality));
                    str.Append("', country='");
                    str.Append(AccessUtils.escapeAndTrim(address.Country));
                    str.Append("', nationalnumber='");
                    str.Append(AccessUtils.escapeAndTrim(user.NationalNumber));
                    str.Append("' WHERE personId=");
                    str.Append(user.Id);
                    dbDa.UpdateCommand.CommandText = str.ToString();
                    notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
                    try
                    {
                        result = dbDa.UpdateCommand.ExecuteNonQuery();
                        updatePersPhones(user, dbCon, dbDa);
                    }
                    catch (Exception dbe)
                    {
                        notify(new Notification(Notification.VERBOSE.error, "Mise à jour de l'utilisateur", dbe, this));
                    }
                }
            }
            catch (Exception dbe)
            {
                notify(new Notification(Notification.VERBOSE.error, "Mise à jour de la personne", dbe, this));
            }
            strDisp.Append((result == 1) ? "\" est mis à jour." : "\" ne peut être mis à jour.");
            return (result == 1);
        }
        public void deleteUser(User user, bool deletePerson)
        {
            if (user.Id.Equals(Config.Owner.Id))
            {
                notify(new Notification(Notification.VERBOSE.opsResult, "On ne peut supprimer le propriétaire de l'application...", this));
                return;
            }
            StringBuilder strDisp = new StringBuilder("L'utilisateur \"");
            strDisp.Append(user.ToString());
 
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
 
            //delete user
            String str = "DELETE FROM [user] WHERE personId=" + user.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            int result = dbDa.DeleteCommand.ExecuteNonQuery();
            //delete phones
            str = "DELETE FROM personPhone WHERE personId=" + user.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
            //delete mails
            str = "DELETE FROM personMail WHERE personId=" + user.Id;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            dbDa.DeleteCommand.ExecuteNonQuery();
 
            if (deletePerson)
            {
                //delete person
                str = "DELETE FROM person WHERE id=" + user.Id;
                notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
                dbDa.DeleteCommand.CommandText = str;
                dbDa.DeleteCommand.ExecuteNonQuery();
            }
            dbCon.Close();
 
            strDisp.Append((result == 1) ? "\" est supprimé." : "\" ne peut être supprimé.");
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
        }
        #endregion
 
        public ActorRole getUserRole()
        {
            return userRole; 
        }
 
        #endregion
    } 
}