AccessBrolDao.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using be.gaudry.observer;
using be.gaudry.bibliobrol.model.identity;
using be.gaudry.model.exceptions;
using be.gaudry.bibliobrol.config;
 
namespace be.gaudry.bibliobrol.model.dao.msaccess
{
    public sealed class AccessBrolDao : Observable, IBrolDao
    {
 
        private String conStr;
        #region singleton
        static AccessBrolDao instance = null;
        static readonly object padlock = new object();
        private DbProviderFactory dbpf;
        AccessBrolDao()
        {
            notify(new Notification(Notification.VERBOSE.debug, "AccessBrolDao singleton call", this));
            dbpf = ((AccessFactory)AccessFactory.Instance).getDbpf();
            conStr = ((AccessFactory)AccessFactory.Instance).getConnectionString();
        }
        public static AccessBrolDao Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new AccessBrolDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region private methods
        internal bool tryToAddRelation(DataSet ds, DataRelation dr)
        {
            try
            {
                ds.Relations.Add(dr);
                StringBuilder strRel = new StringBuilder("Ajout de la relation ");
                strRel.Append(dr.RelationName);
                strRel.Append(" pour le DataSet ");
                strRel.Append(ds.DataSetName);
                notify(new Notification(Notification.VERBOSE.persistentOperation, strRel.ToString(), this));
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Liaison " + dr.RelationName, e, this));
                return false;
            }
            return true;
        }
 
        /// <summary>
        /// Lock or unlock a row into item table
        /// </summary>
        /// <param name="id">(int) item identifier</param>
        /// <param name="toLocked">(bool) true to lock; false to unlock</param>
        /// <returns>(bool) true if one and only one row is updated</returns>
        private bool lockUnlockItem(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 item SET itemLocked=");
            str.Append((toLocked) ? "TRUE" : "FALSE");
            str.Append(" WHERE id=");
            str.Append(id);
            dbDa.UpdateCommand.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                r = dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Verrouillage du media",e, this));
            }
 
            dbCon.Close();
            return (r == 1);
        }
 
        internal BrolCategory getCategory(DataRow row)
        {
            if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
            BrolCategory cat = new BrolCategory((int)row["id"], (String)row["name"]);
            if (!(row["typeId"] is DBNull))
            {
                //todo : fetch media type ?
            }
            return cat;
        }
        internal Actor getActor(DataRow row)
        {
            if (row["id"] is DBNull || row["lastName"] is DBNull) throw new BuildObjectException("No id or no name found");
            Actor actor = new Actor((int)row["id"], (String)row["lastName"]);
            if (!(row["firstName"] is DBNull))
            {
                actor.FirstName = (String)row["firstName"];
            }
            if (!(row["roleName"] is DBNull))
            {
                actor.Role = (row["roleValue"] is DBNull) ? new ActorRole((int)row["roleId"], (String)row["roleName"]) : new ActorRole((int)row["roleId"], (String)row["roleName"], (String)row["roleValue"]);
            }
            if (!(row["pseudo"] is DBNull))
            {
                actor.Pseudo = (String)row["pseudo"];
            }
            if (!(row["sex"] is DBNull))
            {
                SEX sex;
                try
                {
                    sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
                }
                catch (Exception)
                {
                    sex = SEX._;
                }
                actor.Sex = sex;
            }
            if (!(row["birthdate"] is DBNull))
            {
                actor.Birthdate = (DateTime)row["birthdate"];
            }
            return actor;
        }
 
        internal Serie getSerie(DataRow row)
        {
            if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
            Serie serie = new Serie((int)row["id"], (String)row["name"]);
            return serie;
        }
 
        internal SerieItem getSerieItem(DataRow row)
        {
            if (row["id"] is DBNull || row["name"] is DBNull) throw new BuildObjectException("No id or no name found");
            Serie serie = new Serie((int)row["id"], (String)row["name"]);
            SerieItem serieItem = new SerieItem(serie);
            if (!(row["rank"] is DBNull))
            {
                serieItem.setRank((string)row["rank"]);
            }
            return serieItem;
        }
 
        internal Brol getBrol(DataRow row)
        {
            if (row["id"] is DBNull) throw new BuildObjectException("No id found");
            Brol brol = new Brol((int)row["id"]);
            if (!(row["itemTitle"] is DBNull))
            {
                brol.Title = (String)row["itemTitle"];
            }
            if (!(row["synopsis"] is DBNull))
            {
                brol.Synopsis = (String)row["synopsis"];
            }
            if (!(row["cote"] is DBNull))
            {
                brol.Cotation = (int)row["cote"];
            }
            if (!(row["comment"] is DBNull))
            {
                brol.Comment = (String)row["comment"];
            }
            if (!(row["pubDate"] is DBNull))
            {
                brol.Date = (DateTime)row["pubDate"];
            }
            if (!(row["typeId"] is DBNull))
            {
                String tName = (row["tName"] is DBNull) ? "" : (String)row["tName"];
                brol.BrolType = new BrolType((int)row["typeId"],tName);
            }
            return brol;
        }
 
        /// <summary>
        /// We may only add or delete a relation with an existing category
        /// </summary>
        /// <param name="brol"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        private void updateCategoriesRelations(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
        {
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            foreach (BrolCategory category in brol.Categories)
            {
                switch (category.Status)
                {
                    case STATUS.toAdd:
                        addCategoryRelation(category, brol.Id, dbDa.InsertCommand);
                        break;
                    case STATUS.toDelete:
                        deleteCategoryRelation(category, brol.Id, dbDa.DeleteCommand);
                        break;
                }
            }
        }
 
        /// <summary>
        /// We may only add or delete a relation with an existing serie
        /// </summary>
        /// <param name="brol"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        private void updateSerieItemsRelations(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
        {
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            foreach (SerieItem serieItem in brol.SerieItems)
            {
                switch (serieItem.getSerie().Status)
                {
                    case STATUS.toAdd:
                        addSerieItemsRelation(serieItem, brol.Id, dbDa.InsertCommand);
                        break;
                    case STATUS.toDelete:
                        deleteSerieItemRelation(serieItem, brol.Id, dbDa.DeleteCommand);
                        break;
                }
            }
        }
 
        /// <summary>
        /// Insert a new relation between a brol and a category
        /// </summary>
        /// <param name="category">Category to add</param>
        /// <param name="brolId"></param>
        /// <param name="com"></param>
        /// <returns></returns>
        internal bool addCategoryRelation(BrolCategory category, int brolId, DbCommand com)
        {
            if (!category.Status.Equals(STATUS.toAdd))
            {
                notify(new Notification(Notification.VERBOSE.error, "ERREUR insertCategory()  : status de la catégorie != toAdd", this));
                return false;
            }
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO categoryItemRelation (itemId, categoryId) VALUES(");
            str.Append(brolId);
            str.Append(",");
            str.Append(category.Id);
            str.Append(");");
            com.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Ajout d'une catégorie pour un ouvrage", e, this));
            }
 
            return r==1;
        }
 
        /// <summary>
        /// Insert a new relation between a brol and a serie
        /// </summary>
        /// <param name="serie">Serie to add</param>
        /// <param name="brolId"></param>
        /// <param name="com"></param>
        /// <returns></returns>
        internal bool addSerieItemsRelation(SerieItem serieItem, int brolId, DbCommand com)
        {
            if (!serieItem.getSerie().Status.Equals(STATUS.toAdd))
            {
                notify(new Notification(Notification.VERBOSE.error, "ERREUR insertSerie()  : status de la série != toAdd", this));
                return false;
            }
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO serieItemRelation (itemId, serieId, rank) VALUES(");
            str.Append(brolId);
            str.Append(",");
            str.Append(serieItem.getSerie().Id);
            str.Append(",'");
            str.Append(AccessUtils.escapeAndTrim(serieItem.getRank()));
            str.Append("');");
            com.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Ajout d'une série pour un ouvrage", e, this));
            }
 
            return r == 1;
        }
 
        /// <summary>
        ///  Delete a relation between a brol and a category
        /// </summary>
        /// <param name="catId"></param>
        /// <param name="brolId"></param>
        /// <param name="com"></param>
        /// <returns></returns>
        private bool deleteCategoryRelation(BrolCategory category, int brolId, DbCommand com)
        {
            if (!category.Status.Equals(STATUS.toDelete))
            {
                notify(new Notification(Notification.VERBOSE.error, "ERREUR deleteCategory()  : status de la catégorie != toDelete", this));
                return false;
            }
            com.CommandText = "DELETE FROM categoryItemRelation WHERE itemId = " +
                    brolId +
                    " AND categoryId = " + category.Id;
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression d'une catégorie pour un ouvrage", e, this));
            }
 
            return r == 1;
        }
 
        /// <summary>
        ///  Delete a relation between a brol and a serie
        /// </summary>
        /// <param name="brolId"></param>
        /// <param name="com"></param>
        /// <returns></returns>
        private bool deleteSerieItemRelation(SerieItem serieItem, int brolId, DbCommand com)
        {
            if (!serieItem.getSerie().Status.Equals(STATUS.toDelete))
            {
                notify(new Notification(Notification.VERBOSE.error, "ERREUR deleteSerie()  : status de la série != toDelete", this));
                return false;
            }
            com.CommandText = "DELETE FROM serieItemRelation WHERE itemId = " +
                    brolId +
                    " AND serieId = " + serieItem.getSerie().Id;
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression d'une série pour un ouvrage", e, this));
            }
 
            return r == 1;
        }
 
        /// <summary>
        /// We may only add or delete a relation with an existing person, or update infos
        /// </summary>
        /// <param name="brol"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        private void updateActors(Brol brol, DbConnection dbCon, DbDataAdapter dbDa)
        {
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            foreach (Actor actor in brol.Actors)
            {
                switch (actor.Status)
                {
                    case STATUS.toAdd:
                        insertActorRole(actor, brol, dbDa.InsertCommand);
                        break;
                    case STATUS.toDelete:
                        deleteActorRole(actor, brol.Id, dbDa.DeleteCommand);
                        break;
                    case STATUS.toUpdate:
                        updateActorRole(actor, brol.Id, dbDa.DeleteCommand);
                        break;
                }
            }
        }
 
        /// <summary>
        /// Insert a new relation between a brol and an actor
        /// </summary>
        /// <param name="actor">(Actor) actor to add into relation</param>
        /// <param name="brolId">(int) Brol identifier</param>
        /// <param name="com">(DbCommand) command unsed into connection</param>
        /// <returns></returns>
        internal bool insertActorRole(Actor actor, Brol brol, DbCommand com)
        {
            if (!actor.Status.Equals(STATUS.toAdd))
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Impossible d'ajouter le rôle pour la personne", this));
                notify(new Notification(Notification.VERBOSE.error, "Le status de l'acteur est différent de \"toAdd\"", this));
                return false;
            }
            /* 
             * No need to insert here into role table, because only existing role value
             * may be here. Roles management is for another method.
             */
            StringBuilder str = new StringBuilder("INSERT INTO actor (itemId, personId, roleId, roleValue) VALUES(");
            str.Append(brol.Id);
            str.Append(", ");
            str.Append(actor.Id);
            str.Append(", ");
            str.Append((actor.Role != null) ? actor.Role.RoleId.ToString() : "0");
            str.Append(", '");
            if (actor.Role != null && actor.Role.Value != null)
            {
                if (actor.Role.Value.Length >= 100)
                {
                    actor.Role.Value = actor.Role.Value.Remove(96) + "...";
                }
                str.Append(AccessUtils.escapeAndTrim(actor.Role.Value));
            }
            str.Append("');");
            com.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation,"Ajout d'un rôle pour une personne", e, this));
            }
 
            return r == 1;
        }
 
        /// <summary>
        ///  Delete a relation between a brol and an actor
        /// </summary>
        /// <param name="actorId">(int) Person identifier</param>
        /// <param name="brolId">(int) Brol identifier</param>
        /// <param name="com"></param>
        /// <returns></returns>
        private bool deleteActorRole(Actor actor, int brolId, DbCommand com)
        {
            if (!actor.Status.Equals(STATUS.toDelete))
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Rôle non supprimé : " + actor.ToString(), this));
                notify(new Notification(Notification.VERBOSE.error, "Le status de l'acteur est différent de \"toDelete\"", this));
                return false;
            }
            if (brolId==Config.BibliobrolId)
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Impossible de supprimer le rôle par défaut", this));
                return false;
            }
            StringBuilder str = new StringBuilder();
            str.Append("DELETE FROM actor WHERE itemId = ");
            str.Append(brolId);
            str.Append(" AND personId = ");
            str.Append(actor.Id);
            com.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Suppression du rôle " + actor.ToString(), e, this));
            }
 
            return r > 0;
        }
 
        /// <summary>
        /// Update the infos of an existing relation between a brol and an actor
        /// </summary>
        /// <param name="actor">(Actor) actor to retreive relation's infos</param>
        /// <param name="brolId">(int) Brol identifier</param>
        /// <param name="com">(DbCommand) command unsed into connection</param>
        /// <returns></returns>
        internal bool updateActorRole(Actor actor, int brolId, DbCommand com)
        {
            /* 
             * No need to insert here into role table, because only existing role value
             * may be here. Roles management is for another method.
             */
            StringBuilder str = new StringBuilder("UPDATE actor SET roleValue='");
            if (actor.Role != null && actor.Role.Value != null)
            {
                if (actor.Role.Value.Length >= 100)
                {
                    actor.Role.Value = actor.Role.Value.Remove(96) + "...";
                }
                str.Append(AccessUtils.escapeAndTrim(actor.Role.Value));
            }
            str.Append("' WHERE itemId=");
            str.Append(brolId);
            str.Append(" AND personId=");
            str.Append(actor.Id);
            str.Append(" AND roleId=");
            str.Append((actor.Role != null) ? actor.Role.RoleId.ToString() : "0");
            com.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Ajout d'un rôle pour une personne", e, this));
            }
 
            return r == 1;
        }
        #endregion
 
        #region IBrolDao Members
 
        /// <summary>
        /// Load roles for a person
        /// </summary>
        /// <param name="person"></param>
        /// <returns></returns>
        public List<ActorRole> loadRoles(Person person)
        {
            List<ActorRole> roles = new List<ActorRole>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT role.roleName, item.id as brolId, a.roleValue, p.lastName, p.id, item.itemTitle");
            str.Append(" FROM role RIGHT JOIN (");
            str.Append("person AS p INNER JOIN (");
            str.Append("item INNER JOIN actor AS a ON item.id = a.itemId");
            str.Append(") ON p.id = a.personId) ON role.id = a.roleId");
            str.Append(" WHERE (p.id=");
            str.Append(person.Id);
            str.Append(" AND a.itemId>0);");
 
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                foreach (DataRow row in rolesDt.Rows)
                {
                    if (!(row["roleName"] is DBNull))
                    {
                        string roleValue = (row["roleValue"] is DBNull) ? "":(String)row["roleValue"];
                        roles.Add(
                            (row["itemTitle"] is DBNull) ?
                            new ActorRole((int)row["brolId"], (String)row["roleName"], roleValue) :
                            new ActorRole((int)row["brolId"], (String)row["roleName"], roleValue, (String)row["itemTitle"])
                            );
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error,"Chargement des rôles de personnes", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return roles;
        }
 
        /// <summary>
        /// Load the default role for a person
        /// </summary>
        /// <param name="person"></param>
        /// <returns></returns>
        public ActorRole loadDefaultRole(Person person)
        {
            ActorRole role = null;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT role.roleName, a.roleId");
            str.Append(" FROM role RIGHT JOIN (actor AS a) ON role.id = a.roleId");
            str.Append(" WHERE a.personId=");
            str.Append(person.Id);
            str.Append(" AND a.itemId = ");
            str.Append(Config.BibliobrolId);
            str.Append(";");
 
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                foreach (DataRow row in rolesDt.Rows)
                {
                    if (!(row["roleName"] is DBNull))
                    {
                        role = new ActorRole((int)row["roleId"], (String)row["roleName"]);
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle par défaut d'une personne", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return role;
        }
 
        /// <summary>
        /// set the default role for a person
        /// </summary>
        /// <param name="person"></param>
        /// <param name="role"></param>
        public void setDefaultRole(Person person, ActorRole role)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            DbCommand com = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            StringBuilder str = new StringBuilder("UPDATE actor set roleId = ");
            str.Append(role.RoleId);
            str.Append(", roleValue = '");
            if (role.Value != null) str.Append(role.Value);
            str.Append("' WHERE personId = ");
            str.Append(person.Id);
            str.Append(" AND itemId = ");
            str.Append(Config.BibliobrolId);
            str.Append(");");
            com.CommandText = str.ToString();
            int r = 0;
            try
            {
                r = com.ExecuteNonQuery();
 
            }
            catch (Exception)
            {
                try
                {
                    if (r < 1)
                    {
                        str = new StringBuilder("INSERT INTO actor (itemId, personId, roleId, roleValue) VALUES(");
                        str.Append(Config.BibliobrolId);
                        str.Append(", ");
                        str.Append(person.Id);
                        str.Append(", ");
                        str.Append(role.RoleId);
                        str.Append(", '");
                        if (role.Value != null) str.Append(role.Value);
                        str.Append("');");
                        com.CommandText = str.ToString();
                        r = com.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    notify(new Notification(Notification.VERBOSE.advancedOperation, "Définition du rôle par défaut pour une personne", e, this));
                }
            }
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
            dbCon.Close();
        }
 
        /// <summary>
        /// get a roles with the id
        /// </summary>
        /// <param name="roleId">(int) id of the requested role</param>
        /// <returns>(ActorRole) requested role</returns>
        public ActorRole loadRole(int roleId)
        {
            ActorRole role = null;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand.CommandText = String.Format(
                "SELECT id, roleName, info FROM role WHERE id = {0}",
                roleId
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                if (rolesDt.Rows.Count == 1)
                {
                    role = new ActorRole(
                        (int)rolesDt.Rows[0]["id"], 
                        rolesDt.Rows[0]["roleName"] as String,
                        "",
                        (rolesDt.Rows[0]["info"] is DBNull)? "":rolesDt.Rows[0]["info"] as String
                        );
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return role;
        }
 
        /// <summary>
        /// get a roles with the name
        /// </summary>
        /// <param name="roleName">(string) name of the requested role</param>
        /// <returns>(ActorRole) requested role</returns>
        public ActorRole loadRole(string roleName)
        {
            ActorRole role = null;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand.CommandText = String.Format(
                "SELECT id, roleName, info FROM role WHERE roleName = '{0}'",
                roleName
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                if (rolesDt.Rows.Count == 1)
                {
                    role = new ActorRole(
                        (int)rolesDt.Rows[0]["id"],
                        rolesDt.Rows[0]["roleName"] as String,
                        "",
                        (rolesDt.Rows[0]["info"] is DBNull) ? "" : rolesDt.Rows[0]["info"] as String
                        );
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement du rôle", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return role;
        }
 
        /// <summary>
        /// get a list of roles with the same name
        /// </summary>
        /// <param name="roleName">(string) name of the requested roles</param>
        /// <returns>(List of ActorRole) requested roles</returns>
        public List<ActorRole> loadRoles(string roleName)
        {
            List<ActorRole> roles = new List<ActorRole>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand.CommandText = String.Format(
                "SELECT id, roleName, info FROM role WHERE UCASE(roleName) LIKE UCASE('{0}')",
                roleName
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                foreach(DataRow row in rolesDt.Rows)
                {
                    roles.Add(
                        new ActorRole(
                            (int)row["id"],
                            row["roleName"] as String,
                            "",
                            (row["info"] is DBNull) ? "" : row["info"] as String
                            )
                        );
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des rôles", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return roles;
        }
 
        /// <summary>
        /// Insert a new role. Don't check if the role name exists in the db. You must do it before.
        /// </summary>
        /// <param name="actor">(ActorRole) role to add</param>
        /// <returns></returns>
        public bool insertRole(ActorRole role)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            DbCommand com = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
 
            com.CommandText = string.Format(
                "INSERT INTO role (roleName, info) VALUES('{0}','{1}');",
                role.Name,
                role.Info
                );
            int r = com.ExecuteNonQuery();
            notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
 
            dbCon.Close();
            return r == 1;
        }
 
        /// <summary>
        /// Load all roles strings
        /// </summary>
        /// <returns></returns>
        public List<ActorRole> loadRoles()
        {
            List<ActorRole> roles = new List<ActorRole>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            String str = "SELECT id, roleName FROM role";
            dbDa.SelectCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                foreach (DataRow row in rolesDt.Rows)
                {
                    if (!(row["roleName"] is DBNull))
                    {
                        roles.Add(new ActorRole((int)row["id"], (String)row["roleName"]));
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des rôles de personnes", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return roles;
        }
 
        /// <summary>
        /// Load roles strings for a brolType
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<ActorRole> loadRoles(BrolType type)
        {
            List<ActorRole> roles = new List<ActorRole>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            String str = "SELECT id, roleName FROM role WHERE typeId = " + type.Id;
            dbDa.SelectCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable rolesDt = new DataTable();
            try
            {
                dbDa.Fill(rolesDt);
                foreach (DataRow row in rolesDt.Rows)
                {
                    if (!(row["roleName"] is DBNull))
                    {
                        roles.Add(new ActorRole((int)row["id"], (String)row["roleName"]));
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Chargement des rôles de personnes", e, this));
            }
            rolesDt.Dispose();
            dbCon.Close();
            return roles;
        }
 
        /// <summary>
        /// Load all available categories for a type from the Access database. 
        /// i.e. for a film : Science-fiction, Horror, etc.
        /// </summary>
        /// <param name="typeId">(int) selected type (film, book, etc.) negative value to load all</param>
        /// <returns>List of BrolCategories</returns>
        public List<BrolCategory> loadCategories(int typeId)
        {
            List<BrolCategory> cats = new List<BrolCategory>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            String str = "SELECT id, name FROM category";
            if(typeId>=0)str+=" WHERE typeId = " + typeId;
            dbDa.SelectCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable catsDt = new DataTable();
            try
            {
                dbDa.Fill(catsDt);
                foreach (DataRow row in catsDt.Rows)
                {
                    if (!(row["id"] is DBNull) && !(row["name"] is DBNull))
                    {
                        cats.Add(new BrolCategory((int)row["id"], (String)row["name"]));
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Chargement des catégories", e, this));
            }
            catsDt.Dispose();
            dbCon.Close();
            return cats;
        }
 
        /// <summary>
        /// Insert a brol (i.e. a film) into the Access database
        /// </summary>
        /// <param name="brol">(Brol) brol to insert</param>
        /// <returns>(int) new brol id</returns>
        public int insertBrol(Brol brol)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            insertBrol(brol, dbCon, dbDa, true);
            dbCon.Close();
            return brol.Id;
        }
 
        public int insertBrols(List<Brol> brols, bool insertMediabrol)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            int insertedNb = 0;
            foreach (Brol brol in brols)
            {
                insertBrol(brol, dbCon, dbDa, true);
                insertedNb++;
            }
            dbCon.Close();
            return insertedNb;
        }
 
        /// <summary>
        /// Load a brol with the selected id from the Access database. 
        /// If this brol shoud be modified, editing bool arg is true
        /// to avoid concurent modifications (nobody else can save this brol).
        /// </summary>
        /// <param name="id">(int) Id of the selected bool</param>
        /// <param name="editing">(bool) Shoud be modified or not</param>
        /// <returns>Selected bool, or a new bool if _ found</returns>
        public Brol loadBrol(int id, bool editing)
        {
            if (id < 0) return new Brol();
            int[] ids = { id };
            Brol brol;
            List<Brol> brols = AccessExporterDao.Instance.loadSelectedBrols(ids);
            if (brols.Count > 0)
            {
                brol = brols[0];
                if (editing) lockBrol(id);
            }
            else brol = new Brol();
            return brol;
        }
 
        /// <summary>
        /// Load all brols for a type from the Access database. 
        /// i.e. films, books, etc.
        /// typeId 0 allow to load all types
        /// </summary>
        /// <returns>List of Brols</returns>
        public List<Brol> loadBrols(int typeId)
        {
            notify(new Notification(Notification.VERBOSE.error, "List<Brol> loadBrols() : NOT IMPLEMENTED", this));
            return new List<Brol>();
        }
 
        /// <summary>
        /// Load all brols for a serie from the Access database.
        /// </summary>
        /// <param name="serie"></param>
        /// <returns>List of Brols</returns>
        public List<Brol> loadBrols(Serie serie)
        {
            throw new Exception("Not yet implemented!!!");
 
        }
 
        /// <summary>
        /// Load brols value objects (only selected fields) for a type from the Access database. 
        /// </summary>
        /// <param name="fields">List of fields to load for the brols</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <returns>DataTable with selected fields for Brols</returns>
        public DataTable loadDataTableVos(List<DAOUtils.BROL_FIELD> fields, int typeId)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT ");
            if (fields.Contains(DAOUtils.BROL_FIELD.serie))
            {
                str.Append("DISTINCTROW ");
            }
            str.Append(" item.id");
            if (fields.Contains(DAOUtils.BROL_FIELD.title))
            {
                str.Append(", item.itemTitle");
            }
            if (fields.Contains(DAOUtils.BROL_FIELD.date))
            {
                str.Append(", item.pubDate");
            }
            if (fields.Contains(DAOUtils.BROL_FIELD.serie))
            {
                str.Append(", serie.name&' '&serieItemRelation.rank");
            }
            if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
            {
                str.Append(", item.cote ");
            }
 
            if (fields.Contains(DAOUtils.BROL_FIELD.serie))
            {
                str.Append(" FROM serie RIGHT JOIN (item LEFT JOIN serieItemRelation ON item.id = serieItemRelation.itemId) ON serie.id = serieItemRelation.serieId");
            }
            else
            {
                str.Append(" FROM item ");
            }
            if (typeId != 0)
            {
                str.Append(" WHERE item.typeId = ");
                str.Append(typeId);
            }
            if (fields.Contains(DAOUtils.BROL_FIELD.serie))
            {
                str.Append(" GROUP BY serie.name,");
                if (fields.Contains(DAOUtils.BROL_FIELD.title))
                {
                    str.Append(" item.itemTitle,");
                }
                if (fields.Contains(DAOUtils.BROL_FIELD.date))
                {
                    str.Append(" item.pubDate,");
                }
                if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
                {
                    str.Append(" item.cote ,");
                }
                str.Append(" serieItemRelation.rank, item.id");
            }
            else
            {
                str.Append(" ORDER BY");
                if (fields.Contains(DAOUtils.BROL_FIELD.title))
                {
                    str.Append(" item.itemTitle,");
                }
                if (fields.Contains(DAOUtils.BROL_FIELD.date))
                {
                    str.Append(" item.pubDate,");
                }
                if (fields.Contains(DAOUtils.BROL_FIELD.cotation))
                {
                    str.Append(" item.cote ,");
                }
                str.Append(" item.id");
            }
            /*
           SELECT DISTINCTROW item.id, serie.name&' '&serieItemRelation.rank AS [rank], item.itemTitle
FROM serie RIGHT JOIN (item LEFT JOIN serieItemRelation ON item.id = serieItemRelation.itemId) ON serie.id = serieItemRelation.serieId
WHERE typeId = 2 
 
GROUP BY  serie.name, serieItemRelation.rank, item.id, item.itemTitle
             */
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des ouvrages",e, this));
            }
            brolDt.Dispose();
            dbCon.Close();
            return brolDt;
        }
 
        /// <summary>
        /// Store new values for a brol. 
        /// Verify if some categories or actors had been deleted, and delete it from the persistant layer. 
        /// Add new categories or actors if exists. 
        /// Update existing categories or actors.
        /// </summary>
        /// <param name="person">(Brol) brol with new values to store</param>
        /// <returns>(bool) true if update is done</returns>
        public bool updateBrol(Brol brol)
        {
            StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
            strDisp.Append(brol.Title);
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            if (brol.BrolLocked)
            {
                strDisp.Append("\" ne peut être mis à jour car il est déjà ouvert en mode modification.");
                notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
                return false;
            }
            bool updated = updateBrol(brol, dbCon, dbDa, true, strDisp);
            dbCon.Close();
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return updated;
        }
 
        /// <summary>
        /// Lock brol to avoid concurent modifications.
        /// </summary>
        /// <param name="id">(int) brol's id to lock</param>
        /// <returns>(bool) false if a problem occurs (todo : if already locked)</returns>
        public bool lockBrol(int id)
        {
            return lockUnlockItem(id, true);
        }
 
        /// <summary>
        /// unlock brol to allow modifications.
        /// </summary>
        /// <param name="id">(int) brol's id to unlock</param>
        public void unlockBrol(int id)
        {
            lockUnlockItem(id, false);
        }
 
        /// <summary>
        /// Delete a brol from the Access database, 
        /// and delete all associated actors and categories
        /// </summary>
        /// <param name="p">(Brol) brol to delete</param>
        /// <returns>(bool) true if deleted</returns>
        public bool deleteBrol(Brol brol)
        {
            if (brol.Id == Config.BibliobrolId)
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Impossible de supprimer l'application par défaut", this));
                return false;
            }
            bool deleted = true;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            //delete brol
            String str = "DELETE FROM item WHERE id=" + brol.Id;
            dbDa.DeleteCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
            try
            {
                deleted = (dbDa.DeleteCommand.ExecuteNonQuery() == 1);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Impossible de supprimer l'ouvrage " + brol.Title,e, this));
            }
            //delete actors relations
            str = "DELETE FROM actor WHERE itemId = " + brol.Id;
            dbDa.DeleteCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
            try
            {
                dbDa.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Suppression des personnes pour l'ouvrage " + brol.Title,e, this));
            }
            //delete categories relations
            str = "DELETE FROM categoryItemRelation WHERE itemId = " + brol.Id;
            dbDa.DeleteCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
            try
            {
                dbDa.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Suppression des catégories pour l'ouvrage " + brol.Title,e, this));
            }
            dbCon.Close();
            StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
            strDisp.Append(brol.Title);
            strDisp.Append((deleted) ? "\" est supprimé." : "\" ne peut être supprimé.");
            notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return deleted;
        }
 
        private List<String> getBatch()
        {
            List<String> strList = new List<string>();
            //strList.Add("Buddy Longway T02 : L'ennemi");
            return strList;
        }
 
        public int executeBatch(bool insertMediabrol)
        {
            int insertedNb = 0;
            int modelId = 1063;
            List<String> strList = getBatch();
            if (modelId < 1 || strList.Count < 1)
            {
                notify(new Notification(Notification.VERBOSE.error, "Impossible d'exécuter le batch (soit la liste est vide, soit le modèle n'est pas valide)", this));
                return 0;
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            insertedNb = insertBrolFromBatch(strList, loadBrol(modelId,false), insertMediabrol, dbCon, dbDa);
            dbCon.Close();
            return insertedNb;
        }
        #endregion
 
 
        /// <summary>
        /// To do : 
        ///  - get infos from a dataset or a file
        /// </summary>
        /// <param name="batchItems"></param>
        /// <param name="model"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        private int insertBrolFromBatch(List<String> batchItems, Brol model,bool insertMediabrol, DbConnection dbCon, DbDataAdapter dbDa)
        {
            int i = 0;
            MediaBrol mediabrol;
            foreach (Actor a in model.Actors)
            {
                a.Status = STATUS.toAdd;
            }
            foreach (BrolCategory c in model.Categories)
            {
                c.Status = STATUS.toAdd;
            }
            foreach (String batchItem in batchItems)
            {
                model.Title = batchItem;
                model.Id = -1;
                model.Id = insertBrol(model, dbCon, dbDa, true);
                if (insertMediabrol)
                {
                    mediabrol = new MediaBrol(-1, model, "Ajout automatique " + model.Id);
                    mediabrol.InsertionDate = DateTime.Now;
                    mediabrol.Owner = Config.Owner;
                    DAOFactory.Instance.getMediaBrolDao().insertMediaBrol(mediabrol);
                }
                i++;
            }
            notify(new Notification(Notification.VERBOSE.opsResult, "Fin des insertions batch : " + i + "/" + batchItems.Count, this));
            return i;
        }
 
        internal int insertBrol(Brol brol, DbConnection dbCon, DbDataAdapter dbDa, bool displayNotifications)
        {
            if (dbDa.InsertCommand == null) dbDa.InsertCommand = dbCon.CreateCommand();
            StringBuilder strDisp = new StringBuilder("L'ouvrage \"");
            strDisp.Append(brol.Title);
            if (brol.Id != -1)
            {
                strDisp.Append("\" ne peut être ajouté avec le n°");
                strDisp.Append(brol.Id);
                notify(new Notification(Notification.VERBOSE.error, strDisp.ToString(), this));
                return -1;
            }
            if (brol.BrolLocked)
            {
                strDisp.Append("\" ne peut être ajouté car il est verrouillé.");
                notify(new Notification(Notification.VERBOSE.error, strDisp.ToString(), this));
                return -1;
            }
            bool pubDateValid = (!brol.Date.Equals(new DateTime(0L)));
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO item (itemTitle,synopsis,cote,itemLocked,typeId,comment");
            if (pubDateValid) str.Append(",pubDate");
            str.Append(") VALUES ('");
            str.Append(AccessUtils.escapeAndTrim(brol.Title));
            str.Append("', '");
            str.Append(AccessUtils.escapeAndTrim(brol.Synopsis));
            str.Append("', ");
            str.Append(brol.Cotation);
            str.Append(", FALSE, ");
            str.Append(brol.BrolType.Id);
            str.Append(", '");
            str.Append(AccessUtils.escapeAndTrim(brol.Comment));
            str.Append("'");
            if (pubDateValid)
            {
                str.Append(", ");
                str.Append(AccessUtils.getAccessDate(brol.Date));
            }
            str.Append(")");
            dbDa.InsertCommand.CommandText = str.ToString();
            try
            {
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
                dbDa.InsertCommand.ExecuteNonQuery();
                //bad smell code : may not work with concurent acces
                dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY";
                brol.Id = (int)dbDa.InsertCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                notify(new Notification(Notification.VERBOSE.error, "insertion d'un nouvel ouvrage",ex, this));
                return -1;
            }
            foreach (Actor actor in brol.Actors)
            {
                if (!insertActorRole(actor, brol, dbDa.InsertCommand))
                    notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de l'acteur " + actor.ToString() + " pour l'ouvrage " + brol.ToString(), this));
 
            }
            foreach (BrolCategory category in brol.Categories)
            {
                if (!addCategoryRelation(category, brol.Id, dbDa.InsertCommand))
                    notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de la catégorie " + category.ToString() + " pour l'ouvrage " + brol.ToString(), this));
 
            }
            foreach (SerieItem serieItem in brol.SerieItems)
            {
                if (!addSerieItemsRelation(serieItem, brol.Id, dbDa.InsertCommand))
                    notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion de la série " + serieItem.ToString() + " pour l'ouvrage " + brol.ToString(), this));
 
            }
 
            strDisp.Append("\" est ajouté.");
            if(displayNotifications)notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
            return brol.Id;
        }
 
        internal bool updateBrol(Brol brol, DbConnection dbCon, DbDataAdapter dbDa, bool displayNotifications, StringBuilder strDisp)
        {
            StringBuilder str = new StringBuilder();
            if (dbDa.UpdateCommand == null)
            {
                dbDa.UpdateCommand = dbCon.CreateCommand();
            }
            str.Append("UPDATE item SET itemTitle='");
            str.Append(AccessUtils.escapeAndTrim(brol.Title));
            str.Append("', synopsis='");
            str.Append(AccessUtils.escapeAndTrim(brol.Synopsis));
            str.Append("', comment='");
            str.Append(AccessUtils.escapeAndTrim(brol.Comment));
            str.Append("', cote=");
            str.Append(brol.Cotation);
            if (!brol.Date.Equals(new DateTime(0L)))
            {
                str.Append(", pubDate=");
                str.Append(AccessUtils.getAccessDate(brol.Date));
            }
            str.Append(", itemLocked=FALSE WHERE id=");
            str.Append(brol.Id);
            //no need to update type : a film may not became a book
            dbDa.UpdateCommand.CommandText = str.ToString();
            int r = 0;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                r = dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Mise à jour de l'ouvrage", e, this));
            }
            if (r < 1)
            {
                strDisp.Append("\" ne peut être mis à jour.");
                notify(new Notification(Notification.VERBOSE.opsResult, strDisp.ToString(), this));
                dbCon.Close();
                return false;
            }
            updateActors(brol, dbCon, dbDa);
            updateCategoriesRelations(brol, dbCon, dbDa);
            updateSerieItemsRelations(brol, dbCon, dbDa);
            if (displayNotifications)
            {
                strDisp.Append("\" est mis à jour (");
                strDisp.Append(r);
                strDisp.Append(" ouvrages mis à jour).");
            }
            return true;
        }
    }
}