Пример #1
0
    {/// <summary>
     /// Récupère un lecteur d'après son Id.
     /// </summary>
     /// <param name="affiliateId"></param>
     /// <param name="AffToFill"></param>
        public static void GetAffiliateById(int affiliateId, ref Affiliate AffToFill)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.GetAffiliateByCardNum", connection))
                    {
                        Affiliate    affTemp = new Affiliate();
                        SqlParameter param1  = new SqlParameter("@number", affiliateId);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        sLog.Append("Open");
                        connection.Open();
                        SqlDataReader dtReader = command.ExecuteReader();
                        sLog.Append("Read");
                        while (dtReader.Read())
                        {
                            affTemp.CardNum = dtReader.GetInt32(0);
                            if (!dtReader.IsDBNull(1))
                            {
                                affTemp.CardValidity = dtReader.GetDateTime(1);
                            }
                            // else affTemp.CardValidity = default(DateTime);
                            affTemp.MainLibraryId = dtReader.GetInt32(2);
                            affTemp.FirstName     = dtReader.GetString(3);
                            affTemp.LastName      = dtReader.GetString(4);
                            if (!dtReader.IsDBNull(5))
                            {
                                affTemp.BirthDate = dtReader.GetDateTime(5);
                            }
                        }
                        AffToFill = affTemp;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #2
0
        /// <summary>
        /// mise à jour d'un client.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="surName"></param>
        /// <param name="birthDay"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        public static void UpdteCstmr(int id, string name, string surName, DateTime birthDay, string email, string phone) //, DateTime LastModified
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder  _sLog      = new StringBuilder();
                SqlParameter   param0     = new SqlParameter("@id", id);
                SqlParameter   param1     = new SqlParameter("@name", name);
                SqlParameter   param2     = new SqlParameter("@surName", surName);
                SqlParameter   param3     = new SqlParameter("@birthDate", birthDay);
                SqlParameter   param4     = new SqlParameter("@email", email);
                SqlParameter   param5     = new SqlParameter("@phone", phone);
                SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5 };
                try
                {
                    using (SqlCommand command = new SqlCommand("SchCommon.AddCustomer", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        _sLog.Append("Open");
                        connection.Open();
                        _sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected == 0)
                        {
                            throw new CstmEx(ExType.dtaUpdate);                    //  La modification n'a pas pu être effectuée ! \n Veuillez réessayer.
                        }
                        if (rowsAffected > 1)
                        {
                            throw new CstmEx(ExType.sqlLineCount);                    // nbre de lignes affectées erroné
                        }
                    }
                }
                #region Catch
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", _sLog);

                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new CstmEx(ExType.badDB, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new CstmEx(ExType.badPWD, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new CstmEx(ExType.notHandledSql, sqlEx);     //"Erreur SQL non traitée !" L'exception sera rError_Layerancée.
                    }
                }
                catch (Exception ex)
                {
                    ex.Data.Add("Log", _sLog);
                    throw new CstmEx(ExType.dtaUpdate, ex); //"La modification n'a pas pu être effectuée !"
                }
                #endregion Catch
            }
        }
Пример #3
0
        /// <summary>
        /// Insert un volume (sans les auteurs ! Doit être inclus dans une transaction).
        /// </summary>
        /// <param name="volumeToIns"></param>
        public static void InsertVolume(Volume volumeToIns)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();

                SqlParameter   param1     = new SqlParameter("@Isbn", volumeToIns.Isbn);
                SqlParameter   param2     = new SqlParameter("@Title", volumeToIns.Title);
                SqlParameter   param3     = new SqlParameter("@Cover", volumeToIns.Cover);
                SqlParameter[] parameters = { param1, param2, param3 };
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.InsertVolume", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        sLog.Append("Open");
                        connection.Open();
                        sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected > 1)
                        {
                            throw new EL.CstmError(3);                    // nbre de lignes affectées erroné
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 2601:
                        goto case 2627;

                    case 2627:
                        throw new EL.CstmError(5, sqlEx);       //Cet enregistrement existe déjà ! violation d'unicité d'index.

                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 12; //" Un problème est survenu à l'ajout du volume ou de son / ses auteur(s) !".
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #4
0
        /// <summary>
        /// Clôture un emprunt.
        /// </summary>
        /// <param name="EmpruntToCloseID"></param>
        /// <param name="LastModified"></param>
        public static void CloseEmprunt(int EmpruntToCloseID, DateTime LastModified)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();

                SqlParameter   param1     = new SqlParameter("@Id", EmpruntToCloseID);
                SqlParameter   param2     = new SqlParameter("@LastModified", LastModified);
                SqlParameter[] parameters = { param1, param2 };
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.CloseEmprunt", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        sLog.Append("Open");
                        connection.Open();
                        sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected == 0)
                        {
                            throw new EL.CstmError(13);                    //  La modification n'a pas pu être effectuée ! \n Veuillez réessayer.
                        }
                        if (rowsAffected > 1)
                        {
                            throw new EL.CstmError(3);                    // nbre de lignes affectées erroné
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 10; //" Un problème est survenu à la modification des données !".
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #5
0
        /// <summary>
        /// Récupération des promos liées à un type véhicule.
        /// pour clients et employés.
        /// </summary>
        /// <param name="vehicleTypeId"></param>
        /// <returns>ALL =  PromotionModel_Id, VehicleType_Id,[Name],Office_Name,[StartDate],[EndDate],[PercentReduc],[FixedReduc</returns>
        public static DataTable GetPromoByVehicle(int vehicleTypeId, string officeName)
        {
            DataTable _dataToReturn = null;

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder _sLog  = new StringBuilder();
                SqlParameter  param1 = new SqlParameter("@vehicleTypeId", vehicleTypeId);
                SqlParameter  param2 = new SqlParameter("@officeName", officeName);

                try
                {
                    using (SqlCommand command = new SqlCommand("SchCommon.GetPromoByVehicle", connection))
                    {
                        DataTable dataTemp = new DataTable();
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        command.Parameters.Add(param2);
                        SqlDataAdapter datadapt = new SqlDataAdapter(command);
                        _sLog.Append("Open");
                        connection.Open();
                        _sLog.Append("Fill");
                        datadapt.Fill(dataTemp);
                        _dataToReturn = dataTemp;
                    }
                }
                #region Catch
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", _sLog);

                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new CstmEx(ExType.badDB, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new CstmEx(ExType.badPWD, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new CstmEx(ExType.notHandledSql, sqlEx);     //"Erreur SQL non traitée !" L'exception sera rError_Layerancée.
                    }
                }
                catch (Exception ex)
                {
                    ex.Data.Add("Log", _sLog);
                    throw new CstmEx(ExType.dtaRead, ex); //"Problème à la récupération des données par la DAL !"
                }
                #endregion Catch
            }
            return(_dataToReturn);
        }
Пример #6
0
        /// <summary>
        /// Retourne un volume par son ID.
        /// </summary>
        /// <param name="VolumeId"></param>
        /// <param name="VolToFill"></param>
        public static void GetVolumeById(int VolumeId, ref Volume VolToFill)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.GetVolumeById", connection))
                    {
                        Volume       VolTemp = new Volume();
                        SqlParameter param1  = new SqlParameter("@Id", VolumeId);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        sLog.Append("Open");
                        connection.Open();
                        SqlDataReader dtReader = command.ExecuteReader();
                        sLog.Append("Read");
                        while (dtReader.Read())
                        {
                            VolTemp.Id    = dtReader.GetInt32(0);
                            VolTemp.Isbn  = dtReader.GetString(1);
                            VolTemp.Title = dtReader.GetString(2);
                            VolTemp.Cover = dtReader.SafeGetString(3);

                            VolToFill = VolTemp;
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données par la DAL !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #7
0
        /// <summary>
        /// Récupère les auteurs correspondants au volume.
        /// </summary>
        /// <param name="VolumeIsbn"></param>
        /// <returns></returns>
        public static DataTable GetVolumeAuthors(string VolumeIsbn)
        {
            DataTable      dataToReturn = null;
            SqlDataAdapter datadapt     = new SqlDataAdapter();

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.GetAuthorByVolumeIsbn", connection))
                    {
                        DataTable    dataTemp = new DataTable();
                        SqlParameter param1   = new SqlParameter("@VolumeIsbn", VolumeIsbn);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        sLog.Append("Open");
                        connection.Open();
                        datadapt.SelectCommand = command;
                        sLog.Append("Fill");
                        datadapt.Fill(dataTemp);
                        dataToReturn = dataTemp;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données par la DAL !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
            return(dataToReturn);
        }
Пример #8
0
        /// <summary>
        /// retourne les emprunts du lecteur.
        /// Sous forme de datatable.
        /// </summary>
        /// <param name="cardNum"></param>
        /// <param name="emprunts"></param>
        public static void GetEmpruntsByCardNum(int cardNum, bool SelectClosed, ref DataTable emprunts)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("[SchAdmin].[GetEmpruntById]", connection))
                    {
                        DataTable    dataTemp = new DataTable();
                        SqlParameter param1   = new SqlParameter("@Id", cardNum);
                        SqlParameter param2   = new SqlParameter("@SelectClosed", SelectClosed);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        command.Parameters.Add(param2);
                        SqlDataAdapter datadapt = new SqlDataAdapter(command);
                        sLog.Append("Open");
                        connection.Open();
                        sLog.Append("Fill");
                        datadapt.Fill(dataTemp);
                        emprunts = dataTemp;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #9
0
        public static void GetAllAuthorsNames(ref DataTable tableToFill)
        {
            SqlDataAdapter datadapt = new SqlDataAdapter();

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("[SchAdmin].[GetAllAuthors]", connection))
                    {
                        DataTable dataTemp = new DataTable();
                        command.CommandType = CommandType.StoredProcedure;
                        sLog.Append("Open");
                        connection.Open();
                        datadapt.SelectCommand = command;
                        sLog.Append("Fill");
                        datadapt.Fill(dataTemp);
                        tableToFill = dataTemp;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #10
0
        /// <summary>
        /// Crée un nouveau client.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="surName"></param>
        /// <param name="birthDay"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public static int CreateCstmr(string name, string surName, DateTime birthDay, string email, string phone)
        {
            int _newId = 0;

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder _sLog  = new StringBuilder();
                SqlParameter  param0 = new SqlParameter("@new_ID", SqlDbType.Int, 0); // 0 en output par défaut.
                param0.Direction = System.Data.ParameterDirection.Output;
                SqlParameter   param1     = new SqlParameter("@name", name);
                SqlParameter   param2     = new SqlParameter("@surName", surName);
                SqlParameter   param3     = new SqlParameter("@birthDate", birthDay);
                SqlParameter   param4     = new SqlParameter("@email", email);
                SqlParameter   param5     = new SqlParameter("@phone", phone);
                SqlParameter[] parameters = { param1, param2, param3, param4, param5 };
                try
                {
                    using (SqlCommand command = new SqlCommand("SchCommon.AddCustomer", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        _sLog.Append("Open");
                        connection.Open();
                        _sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected == 0)
                        {
                            throw new CstmEx(ExType.dtaWrite);                         // La création a échoué.
                        }
                        if (rowsAffected > 1)
                        {
                            throw new CstmEx(ExType.sqlLineCount);                    // nbre de lignes affectées erroné.
                        }
                        _newId = (int)command.Parameters["@new_ID"].Value;
                    }
                }
                #region Catch
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", _sLog);

                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new CstmEx(ExType.badDB, sqlEx);

                    case 18456:
                        throw new CstmEx(ExType.badPWD, sqlEx);

                    default:
                        throw new CstmEx(ExType.notHandledSql, sqlEx);
                    }
                }
                catch (Exception ex)
                {
                    ex.Data.Add("Log", _sLog);
                    throw new CstmEx(ExType.dtaWrite, ex);
                }
                #endregion Catch
            }
            return(_newId);
        }
Пример #11
0
        /// <summary>
        /// Retourne un volume par son ISBN.
        /// </summary>
        /// <param name="VolumeIsbn"></param>
        /// <param name="VolToFill"></param>
        public static void GetVolumeByIsbn(string VolumeIsbn, ref Volume VolToFill)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.GetVolumeByIsbn", connection))
                    {
                        Volume       VolTemp = new Volume();
                        SqlParameter param1  = new SqlParameter("@isbn", VolumeIsbn);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        sLog.Append("Open");
                        connection.Open();
                        SqlDataReader dtReader = command.ExecuteReader();
                        sLog.Append("Read");
                        while (dtReader.Read())
                        {
                            VolTemp.Id    = dtReader.GetInt32(0);
                            VolTemp.Isbn  = dtReader.GetString(1);
                            VolTemp.Title = dtReader.GetString(2);
                            if (!dtReader.IsDBNull(3))
                            {
                                VolTemp.Cover = dtReader.GetString(3);
                            }
                            else
                            {
                                VolTemp.Cover = string.Empty;
                            }
                        }

                        if (VolTemp.Id != null)
                        {
                            DataTable dtAuthors = GetVolumeAuthors(VolTemp.Isbn);
                            if (dtAuthors != null)
                            {
                                List <Author> listauthors = new List <Author>();
                                foreach (DataRowView row in dtAuthors.DefaultView)
                                {
                                    Author author = new Author();
                                    author.PersId    = (Int32)row["Pers_Id"];
                                    author.FirstName = row["FirstName"].ToString();
                                    author.LastName  = row["LastName"].ToString();
                                    listauthors.Add(author);
                                }
                                VolTemp.Authors = listauthors;
                            }
                            else
                            {
                                VolTemp.Authors = null;
                            }
                        }
                        VolToFill = VolTemp;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #12
0
        /// <summary>
        /// retourne les emprunts du lecteur.
        /// Sous forme de liste d'objets Emprunt.
        /// </summary>
        /// <param name="cardNum"></param>
        /// <param name="listToReturn"></param>
        public static void GetEmpruntsByCardNum(int cardNum, bool SelectClosed, ref List <Emprunt> listToReturn)
        {
            DataTable dataTemp = new DataTable();

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("[SchAdmin].[GetEmpruntByAffiliate]", connection))
                    {
                        SqlParameter param1 = new SqlParameter("@Id", cardNum);
                        SqlParameter param2 = new SqlParameter("@SelectClosed", SelectClosed);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        command.Parameters.Add(param2);
                        SqlDataAdapter datadapt = new SqlDataAdapter(command);
                        sLog.Append("Open");
                        connection.Open();
                        sLog.Append("Fill");
                        datadapt.Fill(dataTemp);
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
            try
            {
                if (dataTemp.Rows.Count != 0)
                {
                    List <Emprunt> listTemp = new List <Emprunt>();
                    foreach (DataRowView row in dataTemp.DefaultView)
                    {
                        Emprunt emprunt = new Emprunt();
                        emprunt.Id          = (int)row["IdEmprunt"];
                        emprunt.CardNum     = (int)row["CardNum"];
                        emprunt.ItemId      = (int)row["Item_Id"];
                        emprunt.ItemCode    = row["Code"].ToString();
                        emprunt.LibraryId   = (int)row["Item_Id"];
                        emprunt.LibraryName = row["NameLibrary"].ToString();
                        emprunt.TarifName   = row["NameTarif"].ToString();
                        emprunt.VolumeTitle = row["Title"].ToString();
                        emprunt.StartDate   = (DateTime)row["StartDate"];
                        emprunt.Duration    = int.Parse(row["Duration"].ToString());
                        if (row["ReturnDate"] != DBNull.Value)
                        {
                            emprunt.ReturnDte = (DateTime)row["ReturnDate"];
                        }
                        emprunt.Fee          = (decimal)row["Fee"];
                        emprunt.DailyPenalty = (decimal)row["DailyPenalty"];
                        emprunt.LastModified = (DateTime)row["LastModified"];
                        listTemp.Add(emprunt);
                    }
                    listToReturn = listTemp;
                }
                //else throw new EL.CstmError(11); // " Aucun résultat ne correspond à cette recherche !"
            }
            catch (Exception ex)
            {
                int DefaultError = 7; //"Problème à la récupération des données !"
                throw new EL.CstmError(DefaultError, ex);
            }
        }
Пример #13
0
        /// <summary>
        /// Retourne un emprunt par le code de l'exemplaire emprunté.
        /// </summary>
        /// <param name="code"></param>
        /// <param name="EmpruntToReturn"></param>
        public static void GetEmpruntByCode(string code, bool SelectClosed, ref Emprunt EmpruntToReturn)
        {
            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder sLog = new StringBuilder();
                try
                {
                    using (SqlCommand command = new SqlCommand("SchAdmin.GetEmpruntByCode", connection))
                    {
                        Emprunt      emprunt = new Emprunt();
                        SqlParameter param1  = new SqlParameter("@Code", code);
                        SqlParameter param2  = new SqlParameter("@SelectClosed", SelectClosed);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(param1);
                        command.Parameters.Add(param2);
                        sLog.Append("Open");
                        connection.Open();
                        SqlDataReader dtReader = command.ExecuteReader();
                        sLog.Append("Read");
                        while (dtReader.Read())
                        {
                            emprunt.Id          = dtReader.GetInt32(0);
                            emprunt.CardNum     = (int)dtReader["CardNum"];
                            emprunt.ItemId      = (int)dtReader["Item_Id"];
                            emprunt.ItemCode    = dtReader["Code"].ToString();
                            emprunt.LibraryId   = (int)dtReader["Library_Id"];
                            emprunt.LibraryName = dtReader["NameLibrary"].ToString();
                            emprunt.TarifName   = dtReader["NameTarif"].ToString();
                            emprunt.VolumeTitle = dtReader["Title"].ToString();
                            emprunt.StartDate   = (DateTime)dtReader["StartDate"];
                            emprunt.Duration    = int.Parse(dtReader["Duration"].ToString());
                            if (dtReader["ReturnDate"] != DBNull.Value)
                            {
                                emprunt.ReturnDte = (DateTime)dtReader["ReturnDate"];
                            }
                            emprunt.Fee          = (decimal)dtReader["Fee"];
                            emprunt.DailyPenalty = (decimal)dtReader["DailyPenalty"];
                            emprunt.LastModified = (DateTime)dtReader["LastModified"];
                        }
                        EmpruntToReturn = emprunt;
                    }
                }
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", sLog);
                    int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new EL.CstmError(1, sqlEx);     //"Mauvaise base de données"

                    case 18456:
                        throw new EL.CstmError(2, sqlEx);     //"Mauvais mot de passe"

                    default:
                        throw new EL.CstmError(DefaultSqlError, sqlEx);     //"Erreur SQL non traitée !" L'exception sera relancée.
                    }
                }
                catch (Exception ex)
                {
                    int DefaultError = 7; //"Problème à la récupération des données !"
                    throw new EL.CstmError(DefaultError, ex);
                }
            }
        }
Пример #14
0
        /// <summary>
        /// Met à jour / Clôture une réservation.
        /// Seul un employé peut le faire
        /// </summary>
        /// <param name="id"></param>
        /// <param name="vehicle_Id"></param>
        /// <param name="customer_Id"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="toPay"></param>
        /// <param name="isClosed"></param>
        /// <param name="paid"></param>
        public static void UpdateRent(int id, int vehicle_Id, int customer_Id, DateTime startDate, DateTime endDate, decimal toPay, bool isClosed, decimal paid)
        {
            int _newId = 0;

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder  _sLog      = new StringBuilder();
                SqlParameter   param0     = new SqlParameter("@id", id);
                SqlParameter   param1     = new SqlParameter("@Vehicle_Id", vehicle_Id);
                SqlParameter   param2     = new SqlParameter("@Customer_Id", customer_Id);
                SqlParameter   param3     = new SqlParameter("@StartDate", startDate);
                SqlParameter   param4     = new SqlParameter("@EndDate", endDate);
                SqlParameter   param5     = new SqlParameter("@ToPay", toPay);
                SqlParameter   param6     = new SqlParameter("@isClosed", isClosed);
                SqlParameter   param7     = new SqlParameter("@Paid", paid);
                SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5, param6, param7 };
                try
                {
                    using (SqlCommand command = new SqlCommand(cmdText: "SchEmployee.UpdateReservation", connection: connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        _sLog.Append("Open");
                        connection.Open();
                        _sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected == 0)
                        {
                            throw new CstmEx(ExType.dtaUpdate);
                        }
                        if (rowsAffected > 1)
                        {
                            throw new CstmEx(ExType.sqlLineCount);
                        }
                        _newId = (int)command.Parameters["@new_ID"].Value;
                    }
                }
                #region Catch
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", _sLog);

                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new CstmEx(ExType.badDB, sqlEx);

                    case 18456:
                        throw new CstmEx(ExType.badPWD, sqlEx);

                    default:
                        throw new CstmEx(ExType.notHandledSql, sqlEx);
                    }
                }
                catch (Exception ex)
                {
                    ex.Data.Add("Log", _sLog);
                    throw new CstmEx(ExType.dtaUpdate, ex);
                }
                #endregion Catch
            }
        }
Пример #15
0
        /// <summary>
        /// Crée une réservation (Rent).
        /// différentie le schéma utilisé si un n° d'employé à été renseigné ou non.
        /// Un client ne peut pas non plus changer le montant de 0 pour "paid".
        /// </summary>
        /// <param name="vehicle_Id"></param>
        /// <param name="customer_Id"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="toPay"></param>
        /// <param name="paid"></param>
        /// <param name="employeeID"></param>
        /// <returns></returns>
        public static int CreateRent(int vehicle_Id, int customer_Id, DateTime startDate, DateTime endDate, decimal toPay, decimal paid = 0, int employeeID = 0)
        {
            int _newId = 0;

            using (SqlConnection connection = UtilsDAL.GetConnection())
            {
                StringBuilder _sLog  = new StringBuilder();
                SqlParameter  param0 = new SqlParameter("@new_ID", SqlDbType.Int, 0); // 0 en output par défaut.
                param0.Direction = System.Data.ParameterDirection.Output;
                SqlParameter   param1     = new SqlParameter("@Vehicle_Id", vehicle_Id);
                SqlParameter   param2     = new SqlParameter("@Customer_Id", customer_Id);
                SqlParameter   param3     = new SqlParameter("@StartDate", startDate);
                SqlParameter   param4     = new SqlParameter("@EndDate", endDate);
                SqlParameter   param5     = new SqlParameter("@ToPay", toPay);
                SqlParameter   param6     = new SqlParameter("@Paid", paid);
                SqlParameter   param7     = new SqlParameter("@Employee_Id", paid);
                SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5 };
                string         cmdText    = "\"SchCustomer.AddReservation\"";

                if (employeeID != 0)
                {
                    parameters = new SqlParameter[] { param1, param2, param3, param4, param5, param6, param7 };
                    cmdText    = "\"SchEmployee.AddReservation\"";
                }
                try
                {
                    using (SqlCommand command = new SqlCommand(cmdText, connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(parameters);
                        _sLog.Append("Open");
                        connection.Open();
                        _sLog.Append("ExecuteNonQuery");
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected == 0)
                        {
                            throw new CstmEx(ExType.dtaWrite);
                        }
                        if (rowsAffected > 1)
                        {
                            throw new CstmEx(ExType.sqlLineCount);
                        }
                        _newId = (int)command.Parameters["@new_ID"].Value;
                    }
                }
                #region Catch
                catch (SqlException sqlEx)
                {
                    sqlEx.Data.Add("Log", _sLog);

                    switch (sqlEx.Number)
                    {
                    case 4060:
                        throw new CstmEx(ExType.badDB, sqlEx);

                    case 18456:
                        throw new CstmEx(ExType.badPWD, sqlEx);

                    default:
                        throw new CstmEx(ExType.notHandledSql, sqlEx);
                    }
                }
                catch (Exception ex)
                {
                    ex.Data.Add("Log", _sLog);
                    throw new CstmEx(ExType.dtaWrite, ex);
                }
                #endregion Catch
            }
            return(_newId);
        }