using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.Windows.Forms;

​

namespace CRUDPerso

{

    public class gestionBDD

    {

        private static SqlConnection Cnx = new SqlConnection();

        private static SqlCommand Cmd = new SqlCommand();

        private static SqlDataAdapter DataAdapt = new SqlDataAdapter();

        private static DataSet MonDataSet = new DataSet();

        private static string MaChaine;

        private static int cptvue = 0;

​

        #region "Connexion"

​

        //Aucun paramètre pour l'instant, ajouter plus tard : login As String, mdp As String

        public static void Se_Connecter_SqlServer()

        {

            MaChaine = "Data Source=sqlserver.montpellier.epsi.fr,4433;Initial Catalog=projetcsharp;User ID=lucas.reynes;Password=epsi561GZZ";

            try

            {

                Cnx.ConnectionString = MaChaine;

                Cnx.Open();

                Cmd.CommandType = CommandType.Text;

                Cmd.Connection = Cnx;

            }

            catch (Exception ex)

            {

                MessageBox.Show("Erreur... Contacter votre Administrateur Système : " + ex.Message);

            }

        }

        #endregion

​

        public static void Se_Deconnecter_SqlServer()

        {

            Cnx.Close();

        }

​

        #region "Requêtes Select et Action"

        public static int Generer_Cle_Primaire(string nomcle, string nomtable)

        {

            Cmd.CommandText = "SELECT Max(" + nomcle + ") FROM " + nomtable;

            DataAdapt.SelectCommand = Cmd;

            return Convert.ToInt32(Cmd.ExecuteScalar());

            // ExecuteScalar : Cas des requêtes renvoyant un résultat unique!

        }

​

        public static void Executer_Requete_Action(string marequeteaction)

        {

            try

            {

                Cmd.CommandText = marequeteaction;

                Cmd.ExecuteNonQuery();

                MessageBox.Show("Requête Action Correctement Exécutée...!", "Action", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, "Erreur dans la tentative d'action", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

        }

​

        public static DataTable Executer_Requete_Select(string marequeteselect)

        {

            DataTable result = new DataTable();

            string MaVue = "MaVue" + cptvue;

            try

            {

                Cmd.CommandText = marequeteselect;

                DataAdapt.SelectCommand = Cmd;

                DataAdapt.Fill(MonDataSet, MaVue);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            result = MonDataSet.Tables[MaVue];

            cptvue = cptvue + 1;

            return result;

        }

        #endregion

​

        #region "Gestion de l'interface"

        public static void AlimenterComboBox(ref ComboBox macombo, string marequete, string mavaleur)

        {

            macombo.DataSource = Executer_Requete_Select(marequete);

            if (!string.IsNullOrEmpty(mavaleur))

            {

                macombo.ValueMember = mavaleur;

            }

        }

        #endregion

​

        #region "Gestion du DataSet"

        public static string GetChamp_DataTable(DataTable lesdonnees, int ligne, int colonne)

        {

            return Convert.ToString(lesdonnees.Rows[ligne][colonne]);

        }

​

        public static void Vider_Dataset()

        {

            MonDataSet.Clear();

            cptvue = 0;

        }

        #endregion

    }

}

​