コード例 #1
0
ファイル: Login.cs プロジェクト: Cmanka/17IT_COURSE_4
        private void bdList()
        {
            SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Integrated Security=true;");

            try
            {
                SqlCommand sqlCom = new SqlCommand();
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = "SELECT name FROM sys.databases " +
                                     "WHERE name not in (\'master\',\'tempdb\',\'model\',\'msdb\')";
                sqlCom.Connection = sqlCon;
                sqlCon.Open();
                SqlDataReader SqlDR;
                SqlDR = sqlCom.ExecuteReader();
                while (SqlDR.Read())
                {
                    comboBox1.Items.Add(SqlDR.GetString(0));
                }
                comboBox1.SelectedIndex = 0;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sqlCon.Close();
            }
        }
コード例 #2
0
ファイル: Login.cs プロジェクト: Cmanka/17IT_COURSE_4
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Database=\"" +
                                                     comboBox1.Text + "\";Integrated Security=true;");

            try
            {
                SqlCommand sqlCom = new SqlCommand();
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = "SELECT name FROM sys.database_principals " +
                                     "WHERE (type <> \'r\') AND (name NOT IN (\'dbo\', \'sys\', \'INFORMATION_SCHEMA\'))";
                sqlCom.Connection = sqlCon;
                sqlCon.Open();
                SqlDataReader SqlDR;
                SqlDR = sqlCom.ExecuteReader();
                while (SqlDR.Read())
                {
                    comboBox2.Items.Add(SqlDR.GetString(0));
                }
                if (comboBox2.Items.Count > 0)
                {
                    comboBox2.SelectedIndex = 0;
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sqlCon.Close();
            }
        }
コード例 #3
0
        /// <summary>
        /// Load the database names from the selected instance
        /// </summary>
        private void LoadDatabases()
        {
            this.Cursor = Cursors.WaitCursor;

            if (cn == null || cn.State != ConnectionState.Open)
            {
                InitConnection();
            }

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection  = cn;
            SqlCom.CommandType = CommandType.Text;
            SqlCom.CommandText = "select name from sys.databases";

            System.Data.SqlClient.SqlDataReader SqlDR;
            using (SqlDR = SqlCom.ExecuteReader())
            {
                comboBoxDatabases.Items.Clear();
                while (SqlDR.Read())
                {
                    comboBoxDatabases.Items.Add(SqlDR.GetString(0));
                }
            }

            if (comboBoxDatabases.Items.Count == 0)
            {
                comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDown;
            }
            else
            {
                comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDownList;
            }

            this.Cursor = Cursors.Default;
        }
コード例 #4
0
        public static List <Employe> GetEmployes()
        {
            List <Employe> lstEmployes = new List <Employe>();

            if (ConnexionBD.Instance().EstConnecte())
            {
                string requete = "SELECT c.nom nm, c.prenom prm, numEmploye numEmp, p.nom poste, e.idEmploye id " +
                                 "FROM employes e " +
                                 "INNER JOIN  postes p ON p.idPoste = e.idPoste " +
                                 "INNER JOIN citoyens c ON c.idCitoyen = e.idCitoyen;";
                ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
                {
                    lstEmployes.Add(new Employe
                    {
                        idEmploye  = SqlDR.GetInt16("id"),
                        NumEmploye = SqlDR.GetString("numEmp"),
                        Nom        = SqlDR.GetString("nm"),
                        Prenom     = SqlDR.GetString("prm"),
                        Poste      = SqlDR.GetString("poste")
                    });
                });
            }

            /*SELECT c.nom, c.prenom, numEmploye, p.nom
             *      FROM employes e
             *  INNER JOIN  postes p ON p.idPoste = e.idPoste
             *  INNER JOIN citoyens c ON c.idCitoyen = e.idCitoyen;*/

            return(lstEmployes);
        }
コード例 #5
0
        private void tableList()
        {
            SqlConnection sqlCon = new SqlConnection(Login.dbConStr);

            try
            {
                SqlCommand sqlCom = new SqlCommand();
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = "SELECT name FROM sysobjects " +
                                     "WHERE type = \'U\' and name != \'sysdiagrams\' ";
                sqlCom.Connection = sqlCon;
                sqlCon.Open();
                SqlDataReader SqlDR;
                SqlDR = sqlCom.ExecuteReader();
                while (SqlDR.Read())
                {
                    checkedListBox2.Items.Add(SqlDR.GetString(0));
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sqlCon.Close();
            }
        }
コード例 #6
0
        public static bool existQuart(QuartEmploye quart)
        {
            QuartEmploye qe = null;

            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT date dt, dep.nom nmDep, shift.periode typeQ " +
                    "FROM quarts qe " +
                    "INNER JOIN periodesjournee shift on shift.idPeriodeJournee = qe.idPeriodeJournee " +
                    "INNER JOIN departements dep on dep.idDepartement = qe.idDepartement " +
                    "WHERE qe.date = '" + quart.Date.ToShortDateString() + "' && shift.periode = '" + quart.TypeDeQuart + "' && dep.nom = '" + quart.DepartementAssocie.Nom + "';"

                    , SqlDR =>
                {
                    qe = new QuartEmploye
                    {
                        Date = SqlDR.GetDateTime("dt"),
                        DepartementAssocie = new Departement
                        {
                            Nom = SqlDR.GetString("nmDep")
                        },
                        TypeDeQuart = (TypeQuart)System.Enum.Parse(typeof(TypeQuart), SqlDR.GetString("typeQ"))
                    };
                });
            }

            return(qe != null);
        }
コード例 #7
0
        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            checkedListBox1.Items.Clear();
            SqlConnection sqlCon = new SqlConnection(Login.dbConStr);

            try
            {
                SqlCommand sqlCom = new SqlCommand();
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
                                     "inner join sysobjects so ON sc.id = so.id " +
                                     "WHERE so.name = \'" + listBox2.Text + "\'";
                sqlCom.Connection = sqlCon;
                sqlCon.Open();
                SqlDataReader SqlDR;
                SqlDR = sqlCom.ExecuteReader();
                while (SqlDR.Read())
                {
                    checkedListBox1.Items.Add(SqlDR.GetString(0));
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sqlCon.Close();
            }
        }
コード例 #8
0
        public static void GetUnCitoyenParLit(Lit lit)
        {
            Citoyen citoyen = new Citoyen();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel " +
                    "FROM citoyens c " +
                    "INNER JOIN genres g ON g.idGenre = c.idGenre " +
                    "INNER JOIN lits l ON l.idCitoyen = c.idCitoyen " +
                    "WHERE l.idLit = '" + lit._identifiant + "' "
                    , SqlDR => {
                    citoyen.Nom           = SqlDR.GetString("nomCit");
                    citoyen.Prenom        = SqlDR.GetString("prenomCit");
                    citoyen.AssMaladie    = SqlDR.GetString("AssMal");
                    citoyen.Genre         = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre"));
                    citoyen.DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss");
                    citoyen.Adresse       = SqlDR.GetString("uneAdresse");
                    citoyen.NumTelephone  = SqlDR.GetString("numTel");
                }
                    );
            }

            lit.Citoyen = citoyen;
        }
コード例 #9
0
        public static Citoyen GetUnCitoyen(Citoyen citoyen)
        {
            // On crée un citoyen venant de la BD
            Citoyen InfosCitoyen = new Citoyen();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel " +
                    "FROM citoyens c " +
                    "INNER JOIN genres g ON g.idGenre = c.idGenre " +
                    "WHERE c.numAssuranceMaladie = '" + citoyen.AssMaladie + "' "
                    , SqlDR => {
                    InfosCitoyen.Nom           = SqlDR.GetString("nomCit");
                    InfosCitoyen.Prenom        = SqlDR.GetString("prenomCit");
                    InfosCitoyen.AssMaladie    = SqlDR.GetString("AssMal");
                    InfosCitoyen.Genre         = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre"));
                    InfosCitoyen.DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss");
                    InfosCitoyen.Adresse       = SqlDR.GetString("uneAdresse");
                    InfosCitoyen.NumTelephone  = SqlDR.GetString("numTel");
                }
                    );
            }

            return(InfosCitoyen);
        }
コード例 #10
0
        public static List <Employe> GetLstEmployesDepartement(Departement depSelectionne)
        {
            List <Employe> lstEmployes = new List <Employe>();

            if (ConnexionBD.Instance().EstConnecte())
            {
                string requete = "SELECT c.nom nomCit, c.prenom prenomCit, e.numEmploye nEmploye " +
                                 "FROM citoyens c " +
                                 "INNER JOIN employes e ON e.idCitoyen = c.idCitoyen " +
                                 "INNER JOIN quartsEmployes qe ON qe.idEmploye = e.idEmploye " +
                                 "INNER JOIN quarts q ON q.idQuart = qe.idQuart " +
                                 "INNER JOIN departements d ON d.idDepartement = q.idDepartement " +
                                 "WHERE d.nom = '" + depSelectionne.Nom + "' " +
                                 "GROUP BY nEmploye " +
                                 "ORDER BY nomCit ";
                ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
                {
                    lstEmployes.Add(new Employe
                    {
                        Nom        = SqlDR.GetString("nomCit"),
                        Prenom     = SqlDR.GetString("prenomCit"),
                        NumEmploye = SqlDR.GetString("nEmploye"),
                    });
                });
            }

            return(lstEmployes);
        }
コード例 #11
0
        // On rend static la fonction pour être en mesure de l'utiliser partout
        public static List <Prescription> GetPrescriptionsCitoyens(String NumAssMaladie)
        {
            // On crée une liste de citoyen venant de la BD
            List <Prescription> lstPrescriptions = new List <Prescription>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT produit prod, posologie poso, prescriptions.dateDebut Ddebut, nbJour nbj " +
                    "FROM prescriptions " +
                    "INNER JOIN evenements e on e.idEvenement = prescriptions.idEvenement " +
                    "INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
                    "INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
                    "WHERE c.numAssuranceMaladie = '" + NumAssMaladie +
                    "' AND (prescriptions.dateDebut + INTERVAL nbJour DAY >= CURDATE() OR nbJour = 0) "

                    , SqlDR => {
                    lstPrescriptions.Add(new Prescription
                    {
                        Produit   = SqlDR.GetString("prod"),
                        Posologie = SqlDR.GetString("poso"),
                        DateDebut = SqlDR.GetDateTime("Ddebut"),
                        NbJour    = SqlDR.GetUInt16("nbj")
                    });
                }
                    );
            }

            return(lstPrescriptions);
        }
コード例 #12
0
        // ****** FÉLIX, tu peux utiliser cette requête pour l'infirmière-chef!! *******
        // On rend static la fonction pour être en mesure de l'utiliser partout
        public static List <Citoyen> GetTousCitoyensDepartement(Departement departementSelectionne)
        {
            // On crée une liste de citoyen venant de la BD
            List <Citoyen> lstCitoyen = new List <Citoyen>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, c.dateNaissance DNaissance, c.telephone Tel, c.Adresse AdresseCit, " +
                    "g.nom NomGenre, " +
                    "d.nom nomDep, " +
                    "ch.nom nomCh, " +
                    "l.numero numeroLit, " +
                    "el.nom EtLitNom " +
                    "FROM citoyens c " +
                    "INNER JOIN genres g ON g.idGenre = c.idGenre " +
                    "INNER JOIN lits l ON l.idCitoyen = c.idCitoyen " +
                    "INNER JOIN etatslits el ON el.idEtatLit = l.idEtatLit " +
                    "INNER JOIN chambres ch ON ch.idChambre = l.idChambre " +
                    "INNER JOIN departements d ON d.idDepartement = ch.idDepartement " +
                    "WHERE d.nom = '" + departementSelectionne.Nom + "' "
                    , SqlDR => {
                    lstCitoyen.Add(new Citoyen
                    {
                        Nom           = SqlDR.GetString("nomCit"),
                        Prenom        = SqlDR.GetString("prenomCit"),
                        AssMaladie    = SqlDR.GetString("AssMal"),
                        Genre         = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("NomGenre")),
                        DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("DNaissance"),
                        NumTelephone  = SqlDR.GetString("Tel"),
                        Adresse       = SqlDR.GetString("AdresseCit"),

                        Lit = new Lit
                        {
                            Numero  = SqlDR.GetString("numeroLit"),
                            EtatLit = (EtatLit)Enum.Parse(typeof(EtatLit), SqlDR.GetString("EtLitNom")),
                            Chambre = new Chambre
                            {
                                Numero        = SqlDR.GetString("nomCh"),
                                UnDepartement = new Departement
                                {
                                    Nom = SqlDR.GetString("nomDep"),
                                }
                            }
                        }
                    });
                }
                    );
            }

            return(lstCitoyen);
        }
コード例 #13
0
        /// <summary>
        /// Load the srid of the selected data table
        /// </summary>
        private void LoadSRID()
        {
            if (cn == null || cn.State != ConnectionState.Open)
            {
                InitConnection();
            }

            if (cn.Database != comboBoxDatabases.Text)
            {
                cn.ChangeDatabase(comboBoxDatabases.Text);
            }

            string[] tableName = comboBoxDataTable.Text.Trim().Split(new char[] { '.' });
            string   geomCol   = comboBoxGeomCol.Text.ToLower().Replace("(geometry)", "").Replace("(geography)", "").Trim();

            if (geomCol.Length == 0)
            {
                return;
            }

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection  = cn;
            SqlCom.CommandType = CommandType.Text;
            if (tableName.Length > 1)
            {
                SqlCom.CommandText = "select top 1 [" + geomCol + "].STSrid, [" + geomCol + "].STGeometryType() from [" + tableName[0] + "].[" + tableName[1] + "] where [" + geomCol + "] is not null";
            }
            else
            {
                return;
            }

            this.Cursor = Cursors.WaitCursor;

            System.Data.SqlClient.SqlDataReader SqlDR;
            using (SqlDR = SqlCom.ExecuteReader())
            {
                while (SqlDR.Read())
                {
                    if (!SqlDR.IsDBNull(0))
                    {
                        textBoxSRID.Text = SqlDR.GetInt32(0).ToString();
                    }
                    else
                    {
                        textBoxSRID.Text = "";
                    }

                    geometryType = SqlDR.GetString(1);
                    break;
                }
            }
            this.Cursor = Cursors.Default;
        }
コード例 #14
0
ファイル: Estructura.cs プロジェクト: EduardAl/COMPILADORES
        private void BD_Load(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(strCon);
            SqlCon.Open();

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection  = SqlCon;
            SqlCom.CommandType = CommandType.StoredProcedure;
            SqlCom.CommandText = "sp_databases";

            System.Data.SqlClient.SqlDataReader SqlDR;
            SqlDR = SqlCom.ExecuteReader();

            List <String> listBD = new List <String>();

            while (SqlDR.Read())
            {
                listBD.Add(SqlDR.GetString(0));
            }
            SqlCon.Close();

            treeViewList = new List <TreeViewItem>();
            int pi = 0;
            int id = 1;

            foreach (String list in listBD)
            {
                SqlCon.Open();

                SqlCommand    cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.CommandText = "SELECT * FROM " + list + ".INFORMATION_SCHEMA.TABLES;";
                cmd.CommandType = CommandType.Text;
                cmd.Connection  = SqlCon;
                reader          = cmd.ExecuteReader();

                treeViewList.Add(new TreeViewItem()
                {
                    ParentID = 0, ID = id, Text = list
                });
                while (reader.Read())
                {
                    treeViewList.Add(new TreeViewItem()
                    {
                        ParentID = id, ID = id + 10000, Text = reader.GetString(2)
                    });
                }
                id++;
                SqlCon.Close();
            }
            PopulateTreeView(0, null);
        }
コード例 #15
0
        private void checkedListBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            checkedListBox1.Items.Clear();
            SqlConnection sqlCon = new SqlConnection(Login.dbConStr);

            try
            {
                SqlCommand sqlCom = new SqlCommand();
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
                                     "inner join sysobjects so ON sc.id = so.id " +
                                     "WHERE so.name = \'" + checkedListBox2.Text + "\'";
                sqlCom.Connection = sqlCon;
                sqlCon.Open();
                SqlDataReader SqlDR;
                SqlDR = sqlCom.ExecuteReader();
                while (SqlDR.Read())
                {
                    checkedListBox1.Items.Add(SqlDR.GetString(0));
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sqlCon.Close();
            }
            Load = true;
            for (int i = 0; i < table.Count; i++)
            {
                if (table[i].name == checkedListBox2.Items[checkedListBox2.SelectedIndex].ToString())
                {
                    for (int j = 0; j < table[i].column.Count; j++)
                    {
                        for (int r = 0; r < checkedListBox1.Items.Count; r++)
                        {
                            if (table[i].column[j] == checkedListBox1.Items[r].ToString())
                            {
                                checkedListBox1.SetItemChecked(r, true);
                                break;
                            }
                        }
                    }
                    break;
                }
            }
            Load = false;
        }
コード例 #16
0
        // On rend static la fonction pour être en mesure de l'utiliser partout
        public static List <Citoyen> GetCitoyens()
        {
            // On crée une liste de citoyen venant de la BD
            List <Citoyen> lstCitoyen = new List <Citoyen>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Ça plante, puisque des éléments sont null comme le lit, la chambre et le département...

                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.Nom GenreCit, c.dateNaissance DNaiss, c.telephone Tel, c.adresse AdresseCit, d.nom nomDep, ch.nom nomCh, l.numero numeroLit, el.nom EtLitNom " +
                    "FROM citoyens c " +
                    "INNER JOIN genres g ON g.idGenre = c.idGenre " +
                    "LEFT JOIN lits l ON l.idCitoyen = c.idCitoyen " +
                    "LEFT JOIN etatslits el ON el.idEtatLit = l.idEtatLit " +
                    "LEFT JOIN chambres ch ON ch.idChambre = l.idChambre " +
                    "LEFT JOIN departements d ON d.idDepartement = ch.idDepartement "
                    , SqlDR => {
                    lstCitoyen.Add(new Citoyen
                    {
                        Nom           = SqlDR.GetString("nomCit"),
                        Prenom        = SqlDR.GetString("prenomCit"),
                        AssMaladie    = SqlDR.GetString("AssMal"),
                        Genre         = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("GenreCit")),
                        DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("DNaiss"),
                        NumTelephone  = SqlDR.GetString("Tel"),
                        Adresse       = SqlDR.GetString("AdresseCit"),
                        Lit           = SqlDR.IsDBNull(SqlDR.GetOrdinal("numeroLit")) ? new Lit() : new Lit
                        {
                            Numero  = SqlDR.GetString("numeroLit"),
                            EtatLit = (EtatLit)Enum.Parse(typeof(EtatLit), SqlDR.GetString("EtLitNom")),
                            Chambre = new Chambre
                            {
                                Numero        = SqlDR.GetString("nomCh"),
                                UnDepartement = new Departement
                                {
                                    Nom = SqlDR.GetString("nomDep"),
                                }
                            }
                        }
                    });
                }
                    );
            }

            return(lstCitoyen);
        }
コード例 #17
0
        public static List <Citoyen> GetCitoyenDemandeTraitement(Departement departement)
        {
            List <Citoyen> lstCitoyen = new List <Citoyen>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel, l.idCitoyen litCit, l.numero litNum, ch.nom chNum " +
                    "FROM citoyens c " +
                    "INNER JOIN genres g ON g.idGenre = c.idGenre " +
                    "LEFT JOIN lits l ON l.idCitoyen = c.idCitoyen " +
                    "LEFT JOIN chambres ch ON ch.idChambre = l.idChambre " +
                    "INNER JOIN hospitalisations h ON h.idCitoyen = c.idCitoyen " +
                    "INNER JOIN hospitalisationstraitements ht ON ht.idHospitalisation = h.idHospitalisation " +
                    "INNER JOIN traitements t ON t.idTraitement = ht.idTraitement " +
                    "INNER JOIN departements d ON d.idDepartement = t.idDepartement " +
                    "WHERE d.nom = '" + departement.Nom + "' " +
                    "AND ht.estEnCours = true "
                    , SqlDR => {
                    lstCitoyen.Add(new Citoyen
                    {
                        Nom           = SqlDR.GetString("nomCit"),
                        Prenom        = SqlDR.GetString("prenomCit"),
                        AssMaladie    = SqlDR.GetString("AssMal"),
                        Genre         = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre")),
                        DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss"),
                        Adresse       = SqlDR.GetString("uneAdresse"),
                        NumTelephone  = SqlDR.GetString("numTel"),
                        Lit           = SqlDR.IsDBNull(SqlDR.GetOrdinal("litCit")) ? new Lit() : new Lit
                        {
                            Numero  = SqlDR.GetString("litNum"),
                            Chambre = new Chambre
                            {
                                Numero = SqlDR.GetString("chNum")
                            }
                        }
                    });
                }

                    );
            }

            lstCitoyen.RemoveAll(citoyen => citoyen.Lit.Numero != null);

            return(lstCitoyen);
        }
コード例 #18
0
        public static List <QuartEmploye> GetHoraire(Employe employe)
        {
            List <QuartEmploye> horaire = new List <QuartEmploye>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT date dt, cit.nom nm, cit.prenom pm, dep.nom nmDep, shift.periode typeQ " +
                    "FROM quartsemployes qe " +
                    "INNER JOIN quarts q on qe.idQuart = q.idQuart " +
                    "INNER JOIN employes em  on em.idEmploye = qe.idEmploye " +
                    "INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
                    "INNER JOIN periodesjournee shift on shift.idPeriodeJournee = q.idPeriodeJournee " +
                    "INNER JOIN departements dep on dep.idDepartement = q.idDepartement " +
                    "WHERE em.idEmploye = " + employe.idEmploye + ";"

                    , SqlDR => {
                    horaire.Add(new QuartEmploye
                    {
                        TypeDeQuart        = (TypeQuart)System.Enum.Parse(typeof(TypeQuart), SqlDR.GetString("typeQ")),
                        Date               = SqlDR.GetDateTime("dt"),
                        DepartementAssocie = new Departement
                        {
                            Nom = SqlDR.GetString("nmDep")
                        },
                        Employe = new Employe
                        {
                            Nom    = SqlDR.GetString("nm"),
                            Prenom = SqlDR.GetString("pm")
                        }
                    });
                });
            }

            /*SELECT  date, cit.nom, prenom, dep.nom, periode
             *            FROM quartsEmployes qe
             *                INNER JOIN quarts q on qe.idQuart = q.idQuart
             *                INNER JOIN employes em  on em.idEmploye = qe.idEmploye
             *                INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen
             *                INNER JOIN periodesjournee shift on shift.idPeriodeJournee = q.idPeriodeJournee
             *                INNER JOIN departements dep on dep.idDepartement = q.idDepartement
             *                    WHERE qe.idEmploye = 4;*/

            return(horaire);
        }
コード例 #19
0
        public static void SetIdEmployeUsagerConnecte()
        {
            Employe employe = new Employe();

            if (ConnexionBD.Instance().EstConnecte())
            {
                string requete = "SELECT e.idEmploye emp, p.nom pos FROM Employes e " +
                                 "INNER JOIN Postes p ON p.idPoste = e.idPoste " +
                                 "INNER JOIN Usagers us ON e.idEmploye = us.idEmploye " +
                                 "WHERE us.nomUtilisateur = '" + UsagerConnecte.Usager.NomUtilisateur + "' ";
                ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
                {
                    UsagerConnecte.Usager.idEmploye = SqlDR.GetInt32("emp");
                    UsagerConnecte.Usager.Poste     = SqlDR.GetString("pos");
                });
            }
        }
コード例 #20
0
        public static List <ResultatLabo> GetResultatsLaboCitoyens(String NumAssMaladie, DateTime dateDebut)
        {
            // On crée une liste de citoyen venant de la BD
            List <ResultatLabo> lstResultatLabo = new List <ResultatLabo>();


            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                string test = dateDebut.ToString();
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT resultatslabo.nomAnalyse nA, resultatslabo.lienImage lIm, e.dateHeure dthr, cit.nom nm, cit.prenom pm, em.numEmploye numEm " +
                    "FROM resultatslabo " +
                    "INNER JOIN evenements e on e.idEvenement = resultatslabo.idEvenement " +
                    "INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
                    "INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
                    "INNER JOIN employes em  on em.idEmploye = e.idEmploye " +
                    "INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
                    "WHERE c.numAssuranceMaladie = '" + NumAssMaladie + "' AND h.dateDebut = '" + dateDebut.ToString() + "'"

                    , SqlDR =>
                {
                    lstResultatLabo.Add(new ResultatLabo
                    {
                        NomAnalyse      = SqlDR.GetString("nA"),
                        LienImage       = SqlDR.GetString("lIm"),
                        DateEvenement   = SqlDR.GetDateTime("dthr"),
                        EmployeImplique = new Employe
                        {
                            Nom        = SqlDR.GetString("nm"),
                            Prenom     = SqlDR.GetString("pm"),
                            NumEmploye = SqlDR.GetString("numEm")
                        }
                    });
                });

                foreach (ResultatLabo result in lstResultatLabo)
                {
                    result.Resultats = new BitmapImage(new Uri(result.LienImage));
                }
            }

            return(lstResultatLabo);
        }
コード例 #21
0
        public static int GetidEmploye(Employe employeRecherche)
        {
            int idEmployeTrouve = new int();

            if (ConnexionBD.Instance().EstConnecte())
            {
                string requete = "SELECT idEmploye idEmp FROM Employes e " +
                                 "JOIN Citoyens c ON c.idCitoyen = e.idCitoyen " +
                                 "WHERE e.numEmploye = '" + employeRecherche.NumEmploye + "' ";

                ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
                {
                    idEmployeTrouve = SqlDR.GetInt32("idEmp");
                });
            }

            return(idEmployeTrouve);
        }
コード例 #22
0
        private void LoadDatabaseName()
        {
            try
            {
                string        server = cbServer.Text.Trim();
                SqlConnection SqlCon = new SqlConnection(@"server=.;integrated security=true;");
                SqlCon.Open();
                SqlCommand SqlCom = new SqlCommand();
                SqlCom.Connection  = SqlCon;
                SqlCom.CommandType = CommandType.StoredProcedure;
                SqlCom.CommandText = "sp_databases";

                SqlDataReader SqlDR;
                SqlDR = SqlCom.ExecuteReader();
                string strSystemDB = "master,model,msdb,tempdb";
                cbDatabase.Items.Clear();
                while (SqlDR.Read())
                {
                    string dbName = SqlDR.GetString(0);
                    if (strSystemDB.Contains(dbName) == false)
                    {
                        cbDatabase.Items.Add(dbName);
                    }
                }

                if (cbDatabase.Items.Count > 0)
                {
                    cbDatabase.SelectedIndex = 0;
                    btnSave.Enabled          = true;
                }
                else
                {
                    cbDatabase.Items.Add("Không có CSDL nào");
                    cbDatabase.SelectedIndex = 0;
                    btnSave.Enabled          = false;
                }
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show("Lỗi:" + ex.Message, "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                btnSave.Enabled = true;
            }
        }
コード例 #23
0
        // On rend static la fonction pour être en mesure de l'utiliser partout
        public static List <NoteInfirmiere> GetNotesInfirmiereCitoyens(String NumAssMaladie, DateTime dateDebut)
        {
            // On crée une liste de citoyen venant de la BD
            List <NoteInfirmiere> lstNoteInfirmiere = new List <NoteInfirmiere>();
            List <Employe>        lstEmp            = new List <Employe>();


            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                string test = dateDebut.ToString();
                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT notesInfirmiere.note nte, e.dateHeure dthr, cit.nom nm, cit.prenom pm, em.numEmploye numEm " +
                    "FROM notesInfirmiere " +
                    "INNER JOIN evenements e on e.idEvenement = notesInfirmiere.idEvenement " +
                    "INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
                    "INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
                    "INNER JOIN employes em  on em.idEmploye = e.idEmploye " +
                    "INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
                    "WHERE c.numAssuranceMaladie = '" + NumAssMaladie + "' AND h.dateDebut = '" + dateDebut.ToString() + "'"

                    , SqlDR =>
                {
                    lstNoteInfirmiere.Add(new NoteInfirmiere
                    {
                        NotesInf        = SqlDR.GetString("nte"),
                        DateEvenement   = SqlDR.GetDateTime("dthr"),
                        EmployeImplique = new Employe
                        {
                            Nom        = SqlDR.GetString("nm"),
                            Prenom     = SqlDR.GetString("pm"),
                            NumEmploye = SqlDR.GetString("numEm")
                        }
                    });
                });
            }


            return(lstNoteInfirmiere);
        }
コード例 #24
0
        public void LlenarComboBox()
        {
            System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(CCadena);
            SqlCon.Open();

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection  = SqlCon;
            SqlCom.CommandType = CommandType.StoredProcedure;
            SqlCom.CommandText = "sp_databases";

            System.Data.SqlClient.SqlDataReader SqlDR;
            SqlDR = SqlCom.ExecuteReader();

            List <String> listBD = new List <String>();

            while (SqlDR.Read())
            {
                comboBox1.Items.Add(SqlDR.GetValue(0));
            }
        }
コード例 #25
0
        public static List <Departement> GetNomsDepartements()
        {
            List <Departement> lstDepartement = new List <Departement>();

            if (ConnexionBD.Instance().EstConnecte())
            {
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT nom nm " +
                    "FROM departements"
                    , SqlDR => {
                    lstDepartement.Add(new Departement
                    {
                        Nom = SqlDR.GetString("nm")
                    });
                }
                    );
            }

            return(lstDepartement);
        }
コード例 #26
0
        /// <summary>
        /// Load the table names of the selected database
        /// </summary>
        private void LoadDataTables()
        {
            this.Cursor = Cursors.WaitCursor;

            if (cn == null || cn.State != ConnectionState.Open)
            {
                InitConnection();
            }

            if (cn.Database != comboBoxDatabases.Text)
            {
                cn.ChangeDatabase(comboBoxDatabases.Text);
            }

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection  = cn;
            SqlCom.CommandType = CommandType.Text;
            SqlCom.CommandText = "select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.Tables";

            System.Data.SqlClient.SqlDataReader SqlDR;
            using (SqlDR = SqlCom.ExecuteReader())
            {
                comboBoxDataTable.Items.Clear();
                while (SqlDR.Read())
                {
                    comboBoxDataTable.Items.Add(SqlDR.GetString(0) + "." + SqlDR.GetString(1));
                }
            }

            if (comboBoxDataTable.Items.Count == 0)
            {
                comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDown;
            }
            else
            {
                comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDownList;
            }

            this.Cursor = Cursors.Default;
        }
コード例 #27
0
        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtServer.Text) || string.IsNullOrEmpty(txtUID.Text) || string.IsNullOrEmpty(txtPassword.Text))
                {
                    MessageBox.Show("Please enter the required details.");
                    IsConnected = false;
                }
                else
                {
                    lstDbs.Items.Clear();
                    SqlConnection SqlCon = new SqlConnection("server=" + txtServer.Text + ";uid=" + txtUID.Text + ";pwd=" + txtPassword.Text);
                    SqlCon.Open();
                    SqlCommand SqlCom = new SqlCommand();
                    SqlCom.Connection  = SqlCon;
                    SqlCom.CommandType = CommandType.StoredProcedure;
                    SqlCom.CommandText = "sp_databases";

                    System.Data.SqlClient.SqlDataReader SqlDR;
                    SqlDR = SqlCom.ExecuteReader();

                    while (SqlDR.Read())
                    {
                        lstDbs.Items.Add(SqlDR.GetString(0));
                    }

                    SqlDR.Close();
                    SqlCon.Close();

                    IsConnected = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                IsConnected = false;
            }
        }
コード例 #28
0
        public static List <Departement> GetDepartements()
        {
            List <Departement> lstDepartement = new List <Departement>();

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                List <int> idEmployesChefs = new List <int>();

                // Si oui, on execute la requête que l'on veut effectuer
                // SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT * " +
                    "FROM departements"
                    , SqlDR => {
                    lstDepartement.Add(new Departement
                    {
                        _identifiant = int.Parse(SqlDR.GetString("idDepartement")),
                        Nom          = SqlDR.GetString("nom"),
                        Abreviation  = SqlDR.GetString("abreviation")
                    });
                    idEmployesChefs.Add(SqlDR.IsDBNull(SqlDR.GetOrdinal("idEmploye")) ? -1 : int.Parse(SqlDR.GetString("idEmploye")));
                }
                    );

                for (int i = 0; i < lstDepartement.Count; i++)
                {
                    lstDepartement[i].PersonnelMedicalEnChef = (idEmployesChefs[i] == -1 ? null : DataModelEmploye.GetEmploye(idEmployesChefs[i]));
                }

                for (int i = 0; i < lstDepartement.Count; i++)
                {
                    lstDepartement[i].Chambres = new ObservableCollection <Chambre>(DataModelChambre.GetChambres(lstDepartement[i]._identifiant.ToString(), "lits, equipements"));
                }
            }

            return(lstDepartement);
        }
コード例 #29
0
        public static Employe GetEmploye(int idEmp)
        {
            Employe employe = new Employe();

            if (ConnexionBD.Instance().EstConnecte())
            {
                string requete = "SELECT nom, prenom, numEmploye FROM Employes e " +
                                 "JOIN Citoyens c ON c.idCitoyen = e.idCitoyen " +
                                 "WHERE e.idEmploye = " + idEmp + ";";

                ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
                {
                    employe = new Employe
                    {
                        NumEmploye = SqlDR.GetString("numEmploye"),
                        Nom        = SqlDR.GetString("nom"),
                        Prenom     = SqlDR.GetString("prenom")
                    };
                });
            }

            return(employe);
        }
コード例 #30
0
        public static Departement GetDepartementInfChef(Employe employe)
        {
            Departement departement = new Departement();

            int idEmployeRecherche = DataModelEmploye.GetidEmploye(employe);

            // On vérifie si la BD est connecté
            if (ConnexionBD.Instance().EstConnecte())
            {
                ConnexionBD.Instance().ExecuterRequete(
                    "SELECT d.idDepartement _id, d.nom depNom, d.abreviation depAbrev " +
                    "FROM departements d " +
                    "WHERE d.idEmploye = " + idEmployeRecherche + " "
                    , SqlDR => {
                    departement._identifiant = int.Parse(SqlDR.GetString("_id"));
                    departement.Nom          = SqlDR.GetString("depNom");
                    departement.Abreviation  = SqlDR.GetString("depAbrev");
                }
                    );
            }

            return(departement);
        }