AccessSerieDao.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using be.gaudry.observer;
 
namespace be.gaudry.bibliobrol.model.dao.msaccess
{
    class AccessSerieDao : Observable, ISerieDao
    {
        #region singleton
        static AccessSerieDao instance = null;
        static readonly object padlock = new object();
        private String conStr;
        private DbProviderFactory dbpf;
        AccessSerieDao()
        {
            notify(new Notification(Notification.VERBOSE.debug, "AccessSerieDao singleton call", this));
            dbpf = ((AccessFactory)AccessFactory.Instance).getDbpf();
            conStr = ((AccessFactory)AccessFactory.Instance).getConnectionString();
        }
        public static AccessSerieDao Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new AccessSerieDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region ISerieDao Membres
 
        public bool insertSerie(Serie serie)
        {
            if (serie.Id > 0)
            {
                notify(new Notification(Notification.VERBOSE.opsResult, "Impossible d'insérer la série.", this));
                return false;
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            dbDa.InsertCommand.CommandText = string.Format(
                "INSERT INTO serie (name) VALUES ('{0}');",
                AccessUtils.escapeAndTrim(serie.Name)
            );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
            try
            {
                dbDa.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Insertion d'une série", e, this));
                return false;
            }
            finally
            {
                dbCon.Close();
            }
            notify(new Notification(Notification.VERBOSE.opsResult, "La série \"" + serie.Name + "\" est ajoutée", this));
            return true;
        }
 
        public bool updateSerie(Serie serie)
        {
            if (serie.Id <= 0)
            {
                return insertSerie(serie);
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
 
            dbDa.UpdateCommand.CommandText = string.Format(
                "UPDATE serie SET name='{0}' WHERE id={1};",
                AccessUtils.escapeAndTrim(serie.Name),
                serie.Id
            );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Modification d'une série", e, this));
                return false;
            }
            finally
            {
                dbCon.Close();
            }
            notify(new Notification(Notification.VERBOSE.opsResult, "La série \"" + serie.Name + "\" est modifiée", this));
            return true;
        }
        public bool deleteSerie(int serieId)
        {
            if (serieId <= 0)
            {
                notify(new Notification(Notification.VERBOSE.opsResult, "Impossible de supprimer la série.", this));
                return false;
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            dbDa.DeleteCommand.CommandText = "DELETE FROM serie WHERE id=" + serieId;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
            try
            {
                dbDa.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Suppression d'une série", e, this));
                return false;
            }
            finally
            {
                dbCon.Close();
            }
            notify(new Notification(Notification.VERBOSE.opsResult, "La série est supprimée", this));
            return true;
        }
 
        public List<Serie> loadSeries()
        {
            List<Serie> series = new List<Serie>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            String str = "SELECT * FROM serie ORDER BY name";
            dbDa.SelectCommand.CommandText = str;
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable dt = new DataTable();
            try
            {
                dbDa.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    series.Add(new Serie(
                        (int)row["id"],
                        (row["name"] is DBNull) ? "Sans nom" : (String)row["name"]));
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.lowError,"Chargement des séries", e, this));
            }
            dt.Dispose();
            dbCon.Close();
            return series;
        }
 
        #endregion
    }
}