Connexion C# à MySQL avec MySqlConnector

CREATE DATABASE gsb_ordonnances CHARACTER SET utf8mb4;
USE gsb_ordonnances;

CREATE TABLE medecin (
    numMedecin  INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(50) NOT NULL,
    prenom      VARCHAR(50),
    numeroRpps  CHAR(11) UNIQUE,
    specialite  VARCHAR(50)
);

CREATE TABLE patient (
    numPatient    INT AUTO_INCREMENT PRIMARY KEY,
    nom           VARCHAR(50) NOT NULL,
    prenom        VARCHAR(50),
    dateNaissance DATE,
    numeroSecu    CHAR(13) UNIQUE
);

CREATE TABLE medicament (
    codeMedicament VARCHAR(10) PRIMARY KEY,
    nom            VARCHAR(80) NOT NULL,
    dosage         VARCHAR(30)
);

CREATE TABLE ordonnance (
    numOrdonnance INT AUTO_INCREMENT PRIMARY KEY,
    dateEmission  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    numMedecin    INT NOT NULL,
    numPatient    INT NOT NULL,
    FOREIGN KEY (numMedecin) REFERENCES medecin (numMedecin),
    FOREIGN KEY (numPatient) REFERENCES patient (numPatient)
);

CREATE TABLE contenir (
    numOrdonnance  INT NOT NULL,
    codeMedicament VARCHAR(10) NOT NULL,
    posologie      VARCHAR(100) NOT NULL,
    dureeJours     SMALLINT NOT NULL CHECK (dureeJours BETWEEN 1 AND 90),
    PRIMARY KEY (numOrdonnance, codeMedicament),
    FOREIGN KEY (numOrdonnance) REFERENCES ordonnance (numOrdonnance),
    FOREIGN KEY (codeMedicament) REFERENCES medicament (codeMedicament)
);

CREATE TABLE utilisateur (
    numUtilisateur INT AUTO_INCREMENT PRIMARY KEY,
    login          VARCHAR(30) NOT NULL UNIQUE,
    hashMotDePasse VARCHAR(100) NOT NULL,
    role           VARCHAR(20) NOT NULL DEFAULT 'secretariat'
);

INSERT INTO medecin (nom, prenom, numeroRpps, specialite) VALUES
  ('Martin', 'Paul', '10001234567', 'Médecine générale'),
  ('Nguyen', 'Claire', '10007654321', 'Pédiatrie');

INSERT INTO patient (nom, prenom, dateNaissance, numeroSecu) VALUES
  ('Dupont', 'Marie', '1990-05-12', '2900575123456'),
  ('Petit', 'Léa', '2012-09-30', NULL),
  ('Bernard', 'Hugo', '2008-11-03', '1081175123456');

INSERT INTO medicament (codeMedicament, nom, dosage) VALUES
  ('PARA1000', 'Paracétamol', '1000 mg'),
  ('AMOX500', 'Amoxicilline', '500 mg'),
  ('IBUP400', 'Ibuprofène', '400 mg');

INSERT INTO ordonnance (dateEmission, numMedecin, numPatient) VALUES
  ('2026-10-15 09:30:00', 1, 1),
  ('2026-10-20 17:10:00', 2, 2);

INSERT INTO contenir (numOrdonnance, codeMedicament, posologie, dureeJours) VALUES
  (1, 'PARA1000', '1 comprimé 3 fois par jour', 5),
  (1, 'AMOX500', '1 gélule matin et soir', 7),
  (2, 'PARA1000', '1 comprimé si fièvre, 3 par jour au plus', 3);

-- mot de passe de démonstration : Ordonnance-2026! (haché avec BCrypt, chapitre 12)
INSERT INTO utilisateur (login, hashMotDePasse, role) VALUES
  ('secretariat', '$2a$12$KaivI.GwRD8Nh62xfaOgIuRJf5gEFyf74DbwX3HvtRMiJXNE1UTvS', 'secretariat');

dotnet add Gsb.Donnees package MySqlConnector

Server=srv-bdd.cabinet.local;Port=3306;Database=gsb_ordonnances;User ID=gsb_app;Password=…;SslMode=Required

using MySqlConnector;

namespace Gsb.Donnees;

public class Connexion(string chaineConnexion)
{
    public async Task<MySqlConnection> OuvrirAsync()
    {
        var conn = new MySqlConnection(chaineConnexion);
        await conn.OpenAsync();
        return conn;
    }
}

await using MySqlConnection conn = await connexion.OuvrirAsync();
// … utilisation
// à la fin de la méthode, la connexion est fermée automatiquement

const string sql = "SELECT numPatient, nom FROM patient WHERE nom LIKE @debut ORDER BY nom";
await using var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@debut", txtRecherche.Text + "%");

using Gsb.Metier;
using MySqlConnector;

namespace Gsb.Donnees;

public class PatientDao(Connexion connexion) : IPatientRepository
{
    public async Task<List<Patient>> ListerAsync()
    {
        const string sql = """
            SELECT numPatient, nom, prenom, dateNaissance, numeroSecu
            FROM patient
            ORDER BY nom, prenom
            """;
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand(sql, conn);
        await using MySqlDataReader lecteur = await cmd.ExecuteReaderAsync();

        var patients = new List<Patient>();
        while (await lecteur.ReadAsync())
        {
            patients.Add(Lire(lecteur));
        }
        return patients;
    }

    public async Task<Patient?> TrouverAsync(int numPatient)
    {
        const string sql = """
            SELECT numPatient, nom, prenom, dateNaissance, numeroSecu
            FROM patient
            WHERE numPatient = @num
            """;
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@num", numPatient);
        await using MySqlDataReader lecteur = await cmd.ExecuteReaderAsync();
        return await lecteur.ReadAsync() ? Lire(lecteur) : null;
    }

    public async Task<bool> NumeroSecuExisteAsync(string numeroSecu, int saufNumPatient)
    {
        const string sql = """
            SELECT COUNT(*) FROM patient
            WHERE numeroSecu = @secu AND numPatient <> @sauf
            """;
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@secu", numeroSecu);
        cmd.Parameters.AddWithValue("@sauf", saufNumPatient);
        long nombre = (long)(await cmd.ExecuteScalarAsync())!;
        return nombre > 0;
    }

    public async Task<int> AjouterAsync(Patient patient)
    {
        const string sql = """
            INSERT INTO patient (nom, prenom, dateNaissance, numeroSecu)
            VALUES (@nom, @prenom, @naissance, @secu)
            """;
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand(sql, conn);
        AjouterParametres(cmd, patient);
        await cmd.ExecuteNonQueryAsync();
        return (int)cmd.LastInsertedId;   // la valeur AUTO_INCREMENT attribuée
    }

    public async Task ModifierAsync(Patient patient)
    {
        const string sql = """
            UPDATE patient
            SET nom = @nom, prenom = @prenom, dateNaissance = @naissance, numeroSecu = @secu
            WHERE numPatient = @num
            """;
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand(sql, conn);
        AjouterParametres(cmd, patient);
        cmd.Parameters.AddWithValue("@num", patient.NumPatient);
        int lignes = await cmd.ExecuteNonQueryAsync();
        if (lignes == 0) throw new RegleMetierException("Ce patient n'existe plus.");
    }

    public async Task SupprimerAsync(int numPatient)
    {
        await using MySqlConnection conn = await connexion.OuvrirAsync();
        await using var cmd = new MySqlCommand("DELETE FROM patient WHERE numPatient = @num", conn);
        cmd.Parameters.AddWithValue("@num", numPatient);
        try
        {
            await cmd.ExecuteNonQueryAsync();
        }
        catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.RowIsReferenced2)
        {
            throw new RegleMetierException("Ce patient a des ordonnances : impossible de le supprimer.");
        }
    }

    private static void AjouterParametres(MySqlCommand cmd, Patient p)
    {
        cmd.Parameters.AddWithValue("@nom", p.Nom);
        cmd.Parameters.AddWithValue("@prenom", (object?)p.Prenom ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@naissance", (object?)p.DateNaissance ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@secu", (object?)p.NumeroSecu ?? DBNull.Value);
    }

    private static Patient Lire(MySqlDataReader l) => new()
    {
        NumPatient = l.GetInt32("numPatient"),
        Nom = l.GetString("nom"),
        Prenom = l.IsDBNull(l.GetOrdinal("prenom")) ? null : l.GetString("prenom"),
        DateNaissance = l.IsDBNull(l.GetOrdinal("dateNaissance")) ? null : l.GetDateTime("dateNaissance"),
        NumeroSecu = l.IsDBNull(l.GetOrdinal("numeroSecu")) ? null : l.GetString("numeroSecu"),
    };
}

public async Task<List<OrdonnanceLisible>> ListerLisiblesAsync()
{
    const string sql = """
        SELECT o.numOrdonnance, o.dateEmission,
               CONCAT(p.nom, ' ', COALESCE(p.prenom, '')) AS patient,
               CONCAT('Dr ', m.nom) AS medecin
        FROM ordonnance o
        JOIN patient p ON p.numPatient = o.numPatient
        JOIN medecin m ON m.numMedecin = o.numMedecin
        ORDER BY o.dateEmission DESC
        """;
    await using MySqlConnection conn = await connexion.OuvrirAsync();
    await using var cmd = new MySqlCommand(sql, conn);
    await using MySqlDataReader l = await cmd.ExecuteReaderAsync();
    var liste = new List<OrdonnanceLisible>();
    while (await l.ReadAsync())
    {
        liste.Add(new OrdonnanceLisible(
            l.GetInt32("numOrdonnance"),
            l.GetDateTime("dateEmission"),
            l.GetString("patient").Trim(),
            l.GetString("medecin")));
    }
    return liste;
}

public async Task<int> EnregistrerAsync(Ordonnance ordonnance)
{
    await using MySqlConnection conn = await connexion.OuvrirAsync();
    await using MySqlTransaction tx = await conn.BeginTransactionAsync();
    try
    {
        await using var cmd = new MySqlCommand("""
            INSERT INTO ordonnance (dateEmission, numMedecin, numPatient)
            VALUES (@date, @medecin, @patient)
            """, conn, tx);
        cmd.Parameters.AddWithValue("@date", ordonnance.DateEmission);
        cmd.Parameters.AddWithValue("@medecin", ordonnance.NumMedecin);
        cmd.Parameters.AddWithValue("@patient", ordonnance.NumPatient);
        await cmd.ExecuteNonQueryAsync();
        int numOrdonnance = (int)cmd.LastInsertedId;

        foreach (LigneOrdonnance ligne in ordonnance.Lignes)
        {
            await using var cmdLigne = new MySqlCommand("""
                INSERT INTO contenir (numOrdonnance, codeMedicament, posologie, dureeJours)
                VALUES (@num, @code, @posologie, @duree)
                """, conn, tx);
            cmdLigne.Parameters.AddWithValue("@num", numOrdonnance);
            cmdLigne.Parameters.AddWithValue("@code", ligne.CodeMedicament);
            cmdLigne.Parameters.AddWithValue("@posologie", ligne.Posologie);
            cmdLigne.Parameters.AddWithValue("@duree", ligne.DureeJours);
            await cmdLigne.ExecuteNonQueryAsync();
        }

        await tx.CommitAsync();
        ordonnance.NumOrdonnance = numOrdonnance;
        return numOrdonnance;
    }
    catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.NoReferencedRow2)
    {
        await tx.RollbackAsync();
        throw new RegleMetierException("Un médicament de l'ordonnance n'existe pas dans le référentiel.");
    }
    catch
    {
        await tx.RollbackAsync();
        throw;   // on annule, puis on laisse l'appelant traiter l'erreur
    }
}

catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.DuplicateKeyEntry)
{
    throw new RegleMetierException("Ce numéro de sécurité sociale est déjà utilisé.");
}

using MySql.Data.MySqlClient;

public List<Patient> ObtenirTous()
{
    var liste = new List<Patient>();
    using (var conn = new MySqlConnection(chaineConnexion))
    {
        conn.Open();
        var cmd = new MySqlCommand("SELECT numPatient, nom, prenom FROM patient ORDER BY nom", conn);
        using (MySqlDataReader r = cmd.ExecuteReader())
        {
            while (r.Read())
            {
                liste.Add(new Patient
                {
                    NumPatient = r.GetInt32("numPatient"),
                    Nom = r.GetString("nom"),
                    Prenom = r.IsDBNull(r.GetOrdinal("prenom")) ? null : r.GetString("prenom"),
                });
            }
        }
    }
    return liste;
}