fichecollege/Core/CG67.FicheCollege.Entrepot/EntrepotDotation.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CG67.FicheCollege.Domaine;
using CG67.FicheCollege.Interface;

namespace CG67.FicheCollege.Entrepot
{
    public class EntrepotDotation : EntrepotBase, IEntrepotDotation
    {
        public Dotation GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            Dotation resultat = new Dotation();

            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "Select * from Dotation INNER JOIN TypeDotation ON IdDotation = id WHERE CodeRNE = @RNE AND Annee = @Annee";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        command.Parameters.AddWithValue("@Annee", annee);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                switch (dr["Libelle"].ToString())
                                {
                                    case "Viabilisation":
                                        resultat.Viabilisation = Convert.ToDouble(dr["Montant"].ToString());
                                        resultat.Annee = dr["annee"].ToString();
                                        break;

                                    case "Entretien":
                                        resultat.Entretien = Convert.ToDouble(dr["Montant"].ToString());
                                        resultat.Annee = dr["annee"].ToString();
                                        break;

                                    case "Autres dépenses":
                                        resultat.AutresDepenses = Convert.ToDouble(dr["Montant"].ToString());
                                        resultat.Annee = dr["annee"].ToString();
                                        break;

                                    case "Réfaction":
                                        resultat.Refaction = Convert.ToDouble(dr["Montant"].ToString());
                                        resultat.Annee = dr["annee"].ToString();
                                        break;

                                    default:
                                        Console.WriteLine("{0}", "Erreur dotation");
                                        break;
                                }
                            }
                        }
                    }
                }
                catch (Exception erreurInterne)
                {
                    throw new Exception(" " + erreurInterne);
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }

            return resultat;
        }
    }
}