ADO.NET

Ado.net est une technologie d'accès aux données qui fournit un ensemble des classes permettant d'accéder aux données relationnelles. Même si Microsoft a repris le nom d'ADO, déjà présent en VB6, la stratégie d'accès aux données est totalement différente; on retrouve néanmoins une certaine facilité d'accès.

Ado.net propose deux modes d'accès, le mode connecté et le mode déconnecté.

Le mode connecté.

Ce mode classique maintient la connexion à la base de données, il permet de créer un mécanisme de "curseur" permettant de parcourir les données ligne à ligne. Ado.net ne propose qu'un accès en lecture -en avant seulement- avec ce mode. Il est approprié pour parcourir des tables volumineuses rapidement.

Le mode déconnecté.

C'est la grande nouveauté de l'architecture .net. Après une connexion et le chargement de données, tout se passe en mémoire. Ceci libère les resources du serveur de donnnées, par contre le mécanisme de cohérence et d'intégrité des données est exigeant.

Nous allons étudier ce dernier type d'accès.

ADO en mode déconnecté.

.net propose un ensemble de classes. Un classe générique, DataSet, et des classes qui sont dépendantes du fournisseur de base de données ( SqlDbConnection pour sqlServer, OleDbConnection pour moteur Jet - Accès par exemple- OdbcDbConnection pour une source Odbc, par exemple pour établir une connexion). On ne peut que regretter ce choix de rendre le code très dépendant de la source de données; notons que la prochaine version d'ado.net prévoit de modifier cela...

Nous allons partir d'un cas pour décrire les classes mises en oeuvre.

Présentation du cas.

Nous allons mettre en œuvre ces notions à partir d'un exemple très simplifié de gestion d'une auto-école :

ConduiteAuto93 est une auto-école récemment installée en Seine-St-Denis, elle propose des forfaits incluant un nombre de leçons dépendant du forfait choisi, des séances de révision du code et une inscription au permis. ConduiteAuto dispose de six véhicules. Chaque leçon dure une ou deux heures.

fig. 1 Schéma relationnel

Remarques :

• Le champ effectuee dans la table Leçon prendra la valeur "vrai" si la leçon a réellement eu lieu
• Le champ creditHoraire de la table Elève enregistre les heures de conduite restantes, prévues dans le forfait. A chaque leçon suivie ce champ est mis à jour.

La base de données est sous SQL-Server.

Différents traitements seront proposés dans différents formulaires. Le formulaire d'accueil se présente ainsi :

fig. 2 formulaire d'accueil

Télécharger les fichiers (base SQL et application Visual Studio vide)

          Les classes d'ADO.NET

Trois principales classes seront utilisées :

• Une classe qui connecte l'application à une base de données. Classe de type Connection
• Une classe qui contient en mémoire les lignes des tables de la base de données. Classe DataSet
• Une classe qui gère (à la demande) la liaison entre la base et le DataSet. classe de type Adapter

Classe Connection

La première étape est de créer une connexion à la base de données : la base de données EcoleAuto93 créée sous SQL-Server.

Ado.net propose une classe de connexion dépendant du type de fournisseur d'accès, ici nous utilisons le fournisseur SqlServer. Il existe plusieurs fournisseurs d'accès, Oracle, ODBC, OleDB, SQLServer

Pour créer une connexion, il faut créer une instance de la classe <type de connexion>Connection en lui fournissant une chaîne de connexion :

Par exemple :

OleDbConnection maConnexion = new OleDbConnection(<chaîne>);

maConnexion.open();        //ouverture

L'environnement VisualStudio permet de simplifier cette étape en utilisant l'explorateur de serveurs : Affichage/Explorateur de serveurs.

fig 3 explorateur de serveurs

En faisant un click droit sur Connexions de données on peut ajouter une nouvelle connexion, une boite de dialogue s'ouvre:

fig 3. configuration de la connexion

Après avoir renseigné la connexion et validé les choix, la connexion est disponible dans l'explorateur de serveurs :

fig 4 connexion disponible

Il suffira ensuite de déposer (cliquer/glisser) la connexion dans un formulaire pour pouvoir en disposer dans la page.

Mise en oeuvre :

Nous créons un nouveau formulaire FrmVehicule, s'ouvrant lorsque l'on clique sur le menu Véhicule du formulaire d'accueil :

private void menuItem3_Click(object sender, System.EventArgs e)
{
        FrmVehicule frmVehicule= new FrmVehicule();
        frmVehicule.Show();
}

Déposons une connexion -celle qui vient d'être précédemment définie- sur le formulaire FrmVehicule :

fig 4 dépot d'une connexion

N'oubliez pas de nommer la connexion, ici MaConnexion.

L'objet maConnexion a été créé (à l'aide de l'opérateur new) par VisualStudio (voir dans la méthode InitializeComponent)

La classe DataSet.

C'est la classe générique qui nous permet d'avoir une représentation tabulaires de données (une ou plusieurs tables, un fichier XML, etc...); c'est pourquoi elle est constituée d'une arborescence de collections de classes :

fig.5 extrait de la structure d'un DataSet

Le diagramme de classes suivant indique les relations entre les classes :

fig. 6 diagramme de classe partiel

Par exemple, pour modifier le champ "couleur" du deuxième véhicule de la table "Véhicule" :

monDataSet.Tables["Vehicule"].Rows[1]["couleur"]="bleu";

Nous sommes ainsi bien loin de la manipulation traditionnelle d'un jeu d'enregistrements!!

La classe DataAdapter.

Son rôle est de remplir le DataSet et de mettre à jour la base de données physique. Le DataSet nous offre une représentation tabulaire (et nettement objet) des données, le DataAdapter s'occupe de la liaison aux données :

        string req="select * from vehicule";
        SqlDataAdapter monAdapterVehicule = new SqlDataAdapter(req , maConnexion);

Le DataAdapter va se charger ensuite de remplir le DataSet :

               monAdapterVehicule.Fill(monDataSet,"voiture");

Remarques :

• les classes de type Adapter de données fournissent une version par type de connexion
• la méthode Fill prend deux paramètres, le DataSet et un nom logique qui permettra de manipuler la table (ou la requête) comme un objet nommé de la collection Tables des tables du DataSet.
• après le chargement du DataSet, la connexion est automatiquement fermée, tout se passe en mémoire.
• le code de connexion et de remplissage du Dataset sera placé dans le constructeur du formulaire.

                     Gestion des véhicules.

           Navigation simple

Le menu Véhicule du formulaire d'accueil ouvre un autre formulaire qui permet de gérer les voitures du parc de l'auto-école, lecture, mise à jour et suppression. Nous allons commencer par naviguer dans la table Véhicule :

fig.7 formulaire de gestion des véhicules

Pour accéder aux données, nous allons utiliser un Adapter ; déposer un SqlDataAdapter dans le formulaire et configurez-le en utilisant les boites de dialogues :

Utiliser le générateur de requêtes :

fig 8 configuration de l'adapteur

N'oubliez pas de le renommer : adapterVehicule.

Définissez ensuite un DataSet en faisant un click droit sur l'adapter/ générer groupes de données :

fig 9 création du DataSet

Remarques :

• Vous pouvez aussi faire click droit à l'intérieur du foormulaire
• monDS est une classe, Visual Studio crée également une instance monDS1, visible au bas du formulaire
• En créant une classe monDS et pas seulement une instance de DataSet, vous avez défini un DataSet Typé. Cette manière de construire un DataSet (par l'environnement graphique) vous permettra une utilisation simplifiée des objets.
• Vous pouvez visualiser le schéma XML de description de la classe : fichier monDS.xsd
• Un DataSet est une ensemble de tables ou de vues, aussi l'application de devra contenir qu'un seul DataSet typé. Toutes les données seront atteintes grâce à ce DataSet.
• Ne pas oublier de remplir le DataSet à l'aide de la méthode Fill de l'adapter (cf plus haut). Ce code est à mettre dans le constructeur.

Liaison des données.

On désire faire apparaître dans des composants graphiques des informations issues de bases de données. Visual Studio propose un mécanisme performant appelé la liaison de données : dataBinding. Pour en savoir plus    

Pour lier un composant d'affichage de type TextBox avec un champ d'une table, il faut renseigner la propriété de liaison -ici la propriété Text- :

fig 10 liaison de données

Il faut procéder ainsi pour chaque zône de texte et les lier à un champ -column- d'une table -DataTable- du DataSet monDS1.

En lançant l'application on voit apparaître un premier véhicule :

fig 11 utilisation de la liaison des données

Nous allons circuler dans la DataTable du DataSet à l'aide des 4 boutons. Pour naviguer nous allons utiliser la propriété BindingContext du formulaire; cette propriété permet de se positionner dans la DataTable liée :

Par exemple, pour se placer en première position :

private void btnPremier_Click(object sender, System.EventArgs e)
{
                this.BindingContext[monDS1,"Vehicule"].Position=0;
}

Pour se placer en dernière position :

private void btnDernier_Click(object sender, System.EventArgs e)
{
              int n = this.BindingContext[monDS1,"Vehicule"].Count;
              this.BindingContext[monDS1,"Vehicule"].Position=n-1;
}

Pour les deux derniers boutons :

private void btnSuivant_Click(object sender, System.EventArgs e)
{
              this.BindingContext[monDS1,"Vehicule"].Position++;
}

private void btnPrecedent_Click(object sender, System.EventArgs e)
{
             this.BindingContext[monDS1,"Vehicule"].Position--;
}

Pour afficher la valeur du compteur d'enregistrement :

fig 12 mise en oeuvre du compteur

on écrit une méthode privée :

private void affCompteur()
{
      string ch="";
      int rang=this.BindingContext[monDS1,"Vehicule"].Position+1;
      int n =this.BindingContext[monDS1,"Vehicule"].Count;
      ch= rang.ToString() +"/"+n.ToString();
      txtRang.Text=ch;
}

Cette méthode sera appelée pour chaque bouton ainsi que dans le constructeur (pour le premier affichage) :

                   Mise à jour, suppession, insertion

Nous terminons le formulaire en ajoutant les boutons de modification :

fig 13 ajout, suppression

La propriété BindingContext va nous permettre de gérer les modifications.

Le bouton Nouveau.

private void btnAjout_Click(object sender, System.EventArgs e)
{
            this.BindingContext[monDS1,"Vehicule"].AddNew();
            txtImma.Text="";
            txtModele.Text="";
            txtCouleur.Text="";
}

Le bouton Supprimer.

private void btnSup_Click(object sender, System.EventArgs e)
{
            if(this.BindingContext[monDS1,"Vehicule"].Count>0)
           {
                   int pos =this.BindingContext[monDS1,"Vehicule"].Position;
                   this.BindingContext[monDS1,"Vehicule"].RemoveAt(pos);
            }
}

Le bouton Confirmer.

private void btnConfirmer_Click(object sender, System.EventArgs e)
{
         this.BindingContext[monDS1,"Vehicule"].EndCurrentEdit();
}

La modification peut être aussi prise en compte si l'on change d'enregistrement.

Le bouton Annuler.

private void btnAnnuler_Click(object sender, System.EventArgs e)
{
          this.BindingContext[monDS1,"Vehicule"].CancelCurrentEdit();
}

Remarque : on peut modifier la valeur d'un champ directement à partir de l'interface, cette modification est prise en compte avec le bouton Confirmer ou en changeant d'enregistrement.

Mise à jour de la base.

Toutes les actions menées l'ont été seulement en mémoire, rappelons que le DataSet est un objet en mémoire ; le DataBinding facilitant seulement la gestion de l'affichage des données en mémoire. Mais rien n'a été encore fait pour mettre à jour la base de données. C'est à nouveau le DataAdapter qui va se charger de la synchronisation.

private void btnEnregistrer_Click(object sender, System.EventArgs e)
{
               adapterVehicule.Update(monDS1,"Vehicule");
}

         Gestion des erreurs

Le bouton Confirmer valide dans le DataSet les mises à jours. La table "Vehicule" du DataSet intègre les contraintes du modèle relationnel, ici l'unicité de la valeur de la clé. Si nous saisissons une valeur existante comme numéro d'immatriculation nous produisons une erreur d'exécution. Pour éviter cela nous allons mettre en oeuvre la gestion des exceptions proposée par C#. Une exception est une erreur générée lors de l'exécution du code. C# propose un mécanisme analogue à C ou java : un bloc try contient les instructions susceptibles de provoquer des erreurs, un bloc catch contient le code qui s'exécute lorsque une erreur (gérable) survient.

private void btnConfirmer_Click(object sender, System.EventArgs e)
{
          try
          {
                   this.BindingContext[monDS1,"Vehicule"].EndCurrentEdit();
           }
          catch (Exception ex)
         {
                   MessageBox.Show(ex.Message);
          }
}

           Vision tabulaire de la table Véhicule : le DataGrid

Le framework dotnet propose différents contrôles permettant une visualisation des données, le DataGrid figure à une bonne place parmi ceux-ci.

Nous voulons visualiser les véhicules sous forme tabulaire :

fig 14 un DataGrid pour visualiser les véhicules

Créer un nouveau formulaire (FrmListeVehicule) qui s'ouvrira à partir du formulaire d'accueil sur ou nouvelle option du menu.

Installer ensuite une connexion (maConnexion), un adapter (adapterVehicule) et générer le groupe de donner en utilisant le DataSet typé (monDS). Après avoir déposé un DataGrid (dGridVehicule), l'interface en mode conception se présente ainsi :

fig 15 formulaire en mode conception

Nous allons utiliser le mécanisme du DataBinding pour lier le DataGrid à la table Véhicule du Dataset. Le code est très simple :

public FrmListeVehicule()
{
        InitializeComponent();
        try
        {
               adapterVehicule.Fill(monDS1);
               dGrigVehicule.DataSource=monDS1.VEHICULE;
         }
         catch (Exception e)
        {
               MessageBox.Show(e.Message);
         }
}

Remarques :

• Nous avons mis en oeuvre le DataBinding par le code, en utilisant la propriété DataSource du DataGrig
• Remarquez la syntaxe "objet" monDS1.VEHICULE , cettes syntaxe est possible car nous avons utilisé un DataSet typé (construit à partir de l'assistant), cf plus haut.
• Le bouton de mise à jour de la base lancera la méthode Update de l'adapter.
• Dans le cas où on saisit un numéro d'immatriculation existant pour un nouveau véhicule, la bloc try/catch retourne une erreur :

fig 16 violation de l'unicité de la valeur de clé

                             Gestion des élèves, utilisation de procédures stockées

Nous allons maintenant utiliser une autre technique pour ajouter un nouvel élève. Regardons le formulaire de création d'un nouvel élève :

fig 17 création d'un nouvel élève

Il s'agit à partir de ce formulaire de créer un nouvel élève dans la base de données. L'identifiant de l'élève est numérique, ce n'est pas à l'utilisateur de donner la valeur de ce nouvel identifiant (qui par ailleurs n'est pas signifiant). D'autre part dans un contexte multi-utilisateurs, nous n'aurions pas la garanti de l'unicité de cette valeur. Nous allons donc déporter la responsabilité de gestion de la valeur de l'identifiant à la base de données; ceci se fera sous la forme de procédures stockées. La procédure stockée aura ainsi deux responsabilités ; d'une part générer la valeur de l'identifiant et d'autre part mettre en oeuvre la requête d'insertion.

Avant cette étape, attachons nous à la gestion de l'interface :

• Le forfait horaire prendra des valeurs numériques 10, 15, 20, 25, 30, 35, 40 ; ces valeurs seront chargées dans le constructeur :

                                               for (int i=5;i<40;i+=5)
                                                              cmbForfait.Items.Add(i);

• Le TextBox "Date d'inscription" sera chargé à l'aide d'un contrôle MonthCalendar ; ce contrôle permet de récupérer facilement une date (ou une plage de dates). Pour limiter la sélection à une seule date il faut fixer la propriété MaxSelectionCount à 1. La valeur de la date sélectionnée est récupérée grâce à l'événement :
                                      private void calendar_DateSelected(object sender, System.Windows.Forms.DateRangeEventArgs e)
                                     {
                                             txtDate.Text =calendar.SelectionStart.ToShortDateString();
                                      }

               Gestion de la procédure stockée.

Nous allons utiliser le "l'explorateur de serveur" (Affichage/Explorateur de serveurs) :

fig 18 parcours de l'explorateur de serveurs

En faisant un click droit sur Procédures stockées/Nouvelle procédure stockée on peut faire apparaître la page de code; effacer le code fourni par défaut et remplacer le par :

/****** Objet : Procédure stockée dbo.pEleve_INSERT */
CREATE PROC pEleve_INSERT
                @nom nvarchar(20)
                ,@dateInscription smallDateTime
                ,@prenom nvarchar(15)
                ,@adresse nvarchar(30)
                ,@creditHoraire int
          AS
               Declare @code smallint
               SELECT @code = (select max(code) from eleve)
                  select @code = (@code+1)
                INSERT eleve (
                         code
                         ,nom
                         ,dateInscription
                         ,prenom
                         ,adresse
                         ,creditHoraire
                )
                 VALUES (
                             @code
                             ,@nom
                             ,@dateInscription
                             ,@prenom
                             ,@adresse
                             ,@creditHoraire
                  )

Enregistrer la procédure (Fichier/Enregistrer). Elle est maintenant associée à la base de données SqlServer.

Pour exécuter la procédure, il faudra créer un objet particulier, une commande :

1. maConnexion.Open();
2. SqlCommand cmd=new SqlCommand("pEleve_INSERT",(SqlConnection)maConnexion);
3. cmd.CommandType = CommandType.StoredProcedure;
4. SqlParameter pNom = cmd.Parameters.Add("@nom", SqlDbType.NVarChar, 20);
5. pNom.Value = txtNom.Text;
   SqlParameter pPrenom = cmd.Parameters.Add("@prenom", SqlDbType.NVarChar, 15);
   pPrenom.Value = txtPrenom.Text;
   SqlParameter pAdresse = cmd.Parameters.Add("@adresse", SqlDbType.NVarChar, 30);
   pAdresse.Value=txtAdresse.Text;
   SqlParameter pDi = cmd.Parameters.Add("@DateInscription", SqlDbType.SmallDateTime);
   pDi.Value = calendar.SelectionStart;
   SqlParameter pCredit = cmd.Parameters.Add("@creditHoraire", SqlDbType.Int);
   pCredit.Value=Convert.ToInt32(cmbForfait.Text);
6. cmd.ExecuteNonQuery();
7. maConnexion.Close();

Commentaire des lignes :

1. Ouverture de la connexion
2. Création de l'objet SqlCommand
3. Indiquer que l'objet SqlCommand va exécuter une procédure stockée
4. Ajout d'un premier paramètre à la procédure stockée.
5. Valorisation de la propriété Value du paramètre ( à partir des saisies ).
   ... étape 4 et 5 pour chaque paramètre
   
6. Exécution de la procédure stockée
7. Fermeture de la connexion

• Le code ci-dessus sera écrit dans une procédure privée Enregistre et appelée dans l'événement Click du bouton de mise à jour. Ne pas oublier de déposer une connexion (maConnexion) dans le formulaire.
  
  
• Il faut ajouter les clause using :
  using System.Data;
  using System.Data.SqlClient;
  
  
• Ne pas oublier de mettre ce code "sensible" dans un try/catch

Exécuter le programme et vérifier l'insertion d'un nouvel élève.

                      Création d'une leçon

Nous allons créer le formulaire de création de leçon. Ajouter un nouveau formulaire attaché à l'option nouvelle leçon du menu Leçon. Le formulaire se présente ainsi :

fig 19 saisie de leçon

Un contrôle DataTimePicker a été ajouté.

                  Gestion du ComboBox d'élèves et du crédit horaire : le DataView

Ajoutons un adapter (SqlDataAdapter), déposé dans le formulaire. Configurer en utilisant la connexion existante, préciser la table Eleve et limiter les champs à utiliser :

fig 20 configuartion de l'adapter

Après avoir valider la requête, cliquer sur Options avancées et décocher la génération des ordres update, delete :

fig 20 décocher la génération d'ordre SQL dans Options avancées

Renommer la connexion générée maConnexion et l'adapter adapterEleve.

Générer le groupe de donnée (monDS1) en ajoutant la table créée. Ne pas oublier de remplir le DataSet dans le constructeur du formulaire : méthode Fill de l'adapter.

  Le combobox contient les valeurs des noms des élèves.

fig 21 combobox élève et zône de texte indiquant les heures restantes

Nous allons charger le ComboBox à l'aide d'un objet d'accès aux données le DataView ; il permet un accès en lecture aux données (d'un DataSet). Il faudra privilégier cet objet pour des accès en lecture uniquement (par exemple le chargement de ComboBox).

Déposer un DataView dans le formulaire. Renseigner la propriété Table en l'associant à monDS1.Eleve

Configurer le ComboBox (cmbEleve), lier le au DataView :

fig 22 liaison du ComboBox avec le DataView

Remarque :

• La propriété DisplayMembre représente la valeur du DataView qui sera visible
• La propriété ValueMember représente celle qui sera associée réellement (mais non visible). Ceci nous permettra de récupérer très facilement le numéro d'un élève sélectionné en récupérant cmbEleve.SelectedValue

La zône de texte contenant le crédit horaire restant est rempli en utilisant le DataBinding :

fig 23 configuration de la zone de texte : liaison du champ Text au DataView

            Remplissage du ComboBox des horaires.

Dans le constructeur, écrire le code :

       for(int i = 8;i<22;i++)
                 cmbHeure.Items.Add(i);

           Gestion du ComboBox des véhicules : création d'une Datatable par le code

Après avoir sélectionné l'élève, le jour et l'heure de la leçon, il faut s'assurer des véhicules disponibles à ce moment, c'est à dire ceux non utilisés pour une leçon.

Comme le résultat dépend de saisies de l'utilisateur nous ne pouvons pas utiliser l'assistant en mode conception. Il faudra ajouter une DataTable dans le DataSet à partir d'une requête paramétrée par les saisies :

private void btnVehicule_Click(object sender, System.EventArgs e)
{
          try
          {
               1- string dateFr=dtPicker.Text;
               2- string req="select numImma from Vehicule where numImmaVehicule not in (";
                    req+="select numImmaVehicule from Lecon where ";
                    req+= "date = '"+dateFr+"' and heure ="+Convert.ToInt32(cmbHeure.Text)+")";
               3- SqlDataAdapter adapterVehicule=new SqlDataAdapter(req,maConnexion);
               4- adapterVehicule.Fill(monDS1,"vehiculesDispo");
               5- cmbVehicule.DataSource=monDS1.Tables["vehiculesDispo"];
               6- cmbVehicule.DisplayMember=monDS1.Tables["vehiculesDispo"].Columns[0].ColumnName;
         }
         catch(Exception ex)
        {
               MessageBox.Show(ex.Message);
        }
}

Commentaires des lignes :

1. Récupération de la valeur sélectionnée dans le DateTimePicker
2. Construction de la requête SQL
3. Création (par le code) d'un adapter , noter les 2 paramètres : la chaîne sql et la connexion
4. Remplissage du DataSet, la DataTable créée portera le nom "VehiculesDispo"
5. Paramétrage du ComboBox : association de la source de données
6. Paramétrage du ComboBox : sélection du champ à faire apparaître : noter la syntaxe

Remarque :

La requête vérifie la disponibilité du véhicule pour une heure et un jour donné, elle ne prend pas en compte la durée de la leçon (une ou deux heures). Dans le cas où la leçon dure deux heures il faudrait en tout état de cause vérifier la disponibilité sur l'heure suivante, le test devient alors :

string req="";
if(rad1.Checked)
{
             req="select numImma from Vehicule where numImma not in (";
             req+="select numImmaVehicule from Lecon where ";
             req+= "date = '"+dateFr+"' and heure ="+Convert.ToInt32(cmbHeure.Text);
             req+= "union select numImmaVehicule from Lecon where ";
             req+= "date = '"+dateFr+"' and heure
             ="+(Convert.ToInt32(cmbHeure.Text)-1)+"and duree=2)";
}
else
{
             req="select numImma from Vehicule where numImma not in (";
             req+="select numImmaVehicule from Lecon where ";
             req+= "date = '"+dateFr+"' and heure ="+Convert.ToInt32(cmbHeure.Text);
             req+= "union select numImmaVehicule from Lecon where ";
             req+= "date = '"+dateFr+"' and heure ="+(Convert.ToInt32(cmbHeure.Text)-1) +"and duree=2 ";
             req+= "union select numImmaVehicule from Lecon where ";
             req+= "date = '"+dateFr+"' and heure ="+(Convert.ToInt32(cmbHeure.Text)+1) +")";
}

            Enregistrement de la leçon

Nous allons procéder comme pour la création d'un élève : déporter cette responsabilité vers la base de données en créant une procédure stockée qui générera une nouvelle valeur de l'identifiant et insèrera la nouvelle leçon dans la table.

Ajouter une nouvelle procédure stockée en ouvrant l'explorateur de serveurs :

/****** Objet : Procédure stockée dbo.pLECON_INSERT */
CREATE PROC pLECON_INSERT
         @date smallDateTime
         ,@codeEleve smallint
         ,@heure smallint
         ,@duree smallint
         ,@effectuee bit
         ,@numImmaVehicule nvarchar(8)
        AS
        Declare @numero smallint
                  SELECT @numero = (select max(numero) from lecon)
                    select @numero = (@numero+1)
                  INSERT lecon (
                                 numero
                                 ,date
                                 ,codeEleve
                                 ,heure
                                 ,duree
                                 ,effectuee
                                 ,numImmaVehicule
                                 )
                  VALUES (
                                @numero
                                ,@date
                                ,@codeEleve
                                ,@heure
                                ,@duree
                                ,@effectuee
                                ,@numImmaVehicule
                 )

Ecrire une méthode privée Enregistre :

     private void Enregistre()
     {
              try
             {
                  int codeEleve = Convert.ToInt32(cmbEleve.SelectedValue);
                 DateTime date = Convert.ToDateTime(dtPicker.Text);
                 int heure = Convert.ToInt32(cmbHeure.Text);
                 int duree;
                 if(rad1.Checked)
                         duree=1;
                else
                         duree=2;
                 bool Effectuee=false;
                 string numImma = cmbVehicule.Text;
                 maConnexion.Open();
                 SqlCommand cmd=new SqlCommand("pLECON_INSERT",(SqlConnection)maConnexion);
                 cmd.CommandType = CommandType.StoredProcedure;
                 SqlParameter pDate = cmd.Parameters.Add("@date", SqlDbType.SmallDateTime);
                 pDate.Value=date;
                 SqlParameter pCodeEleve = cmd.Parameters.Add("@codeEleve", SqlDbType.Int);
                 pCodeEleve.Value=codeEleve;
                 SqlParameter pHeure = cmd.Parameters.Add("@heure", SqlDbType.Int);
                 pHeure.Value=heure;
                 SqlParameter pDuree = cmd.Parameters.Add("@duree", SqlDbType.Int);
                 pDuree.Value=duree;
                 SqlParameter pEffectuee = cmd.Parameters.Add("@effectuee", SqlDbType.Bit);
                 pEffectuee.Value=Effectuee;
                 SqlParameter pNumImma = cmd.Parameters.Add("@numImmaVehicule",SqlDbType.NVarChar,8);
                 pNumImma.Value = numImma;
                 cmd.ExecuteNonQuery();
                 maConnexion.Close();
           }
              catch (Exception ex)
          {
                 MessageBox.Show(ex.Message);
           }
    }

Commentaires : voir plus haut les commentaires concernant la précédente procédure stockée.

Cette méthode sera appelée dans l'événement click du bouton Enregistrer.

                          Visualisation des leçons d'un élève : utilisation d'un trigger

Nous voulons obtenir le formulaire suivant :

fig 24 leçons prises ou planifiées pour un élève

Créer un nouveau formulaire, ajouter une option "Leçon" dans le menu "Elève". Appeler le nouveau formulaire à partir du click de ce sous-menu.

            Gestion du ComboBox des élèves

C'est la même opération que pour le formulaire précédent : adapter avec l'assistant-->connexion -->DataSet --> DataView. Dans l'assistant de l'adapter on ne retient que les colonnes code et nom de l'élève.

Déposer un ComboBox, renseignez la propriété DataSource avec le DataView, la propriété DisplayMembre avec le champ nom et la propriété ValueMember avec le champ code. (voir la figure 22)

            Gestion du DataGrid

Nous ne mettons dans le DataGrid que les leçons de l'élève sélectionné. Le code doit s'écrire dans l'événement :

private void cmbEleve_SelectedIndexChanged(object sender, System.EventArgs e)
{
      1- string req="select * from lecon where codeEleve ="+Convert.ToInt32(cmbEleve.SelectedValue);
      2- adapterLecon =new SqlDataAdapter(req,maConnexion);
      3- if(monDS1.Tables["leconEleve"] !=null)
                  monDS1.Tables["leconEleve"].Clear();
      4- adapterLecon.Fill(monDS1,"leconEleve");
      5- dtGridLecon.DataSource=monDS1.Tables["leconEleve"].DefaultView;
      6- monDS1.Tables["leconEleve"].DefaultView.AllowDelete=false;
      7- monDS1.Tables["leconEleve"].DefaultView.AllowNew=false;
}

Commentaires :

1- construction de la requête

2- génération par le code de l'adapter

3- ce test permet de nettoyer le DataGrid si on sélectionne un nouvel élève. Notez que ce n'est pas le DataGrid que l'on "nettoie" mais la DataTable. Le DataGrid n'est qu'un objet de présentation

4- on remplit le dataSet, la DataTable s'appelle "leconEleve"

5- on lie le DataGrid au DataView "par défaut" cf pour en savoir plus

6- afin d'interdire la suppression de ligne, on intervient sur le DataView

7- idem pour l'ajout

nous n'utilisons pas l'assistant pour générer l'adapter (adapterLecon) ; comme nous en aurons besoin dans une autre méthode on a déclaré l'objet avec les attributs privés de la classe :

private SqlDataAdapter adapterLecon; // déclaré dans les attributs de classe

Remarques :

• La documentation concernant la gestion des DataGrid est très volumineuse compte tenu des possibilités offertes par ce contrôle. On peut notamment modifier le style d'affichage, voici un exemple où l'on crée un objet DataGridStyle afin de personnaliser le contrôle :

  DataGridTableStyle ts = new DataGridTableStyle();
  ts.BackColor=Color.Pink;                     // Alterne les lignes en insérant une ligne rose
  ts.AllowSorting=false;                            // interdit le tri
  ts.GridColumnStyles[0].Width=0;         // permet de cacher la première colonne
  ts.GridColumnStyles[1].ReadOnly = true; // met en mode lecture uniquement la deuxième colonne
  ts.GridColumnStyles[6].Alignment=HorizontalAlignment .Center; // centrage du contenu de la 7 ième colonne

  Ensuite on ajoute l'objet

  dtGridLecon.TableStyles.Add(ts);

• Blocage des champs. En toute rigueur il faudrait bloquer toutes champs du DataGrid excepté la case à côcher. Ceci peut se faire dans l'événement :
  private void dtGridLecon_CurrentCellChanged(object sender, System.EventArgs e)
        {
         DataGridCell dt =dtGridLecon.CurrentCell;
         monDS1.Tables["leconEleve"].DefaultView.AllowEdit=(dt.ColumnNumber==5);
         }

  Cet événement se déclenche à chaque changement de cellule. On récupère la cellule courante -celle qui a le focus- et on teste son numéro de colonne ; si c'est 5 (colonne de la case à côcher) on bascule le DataView par défaut en mode édition, sinon on bascule en mode "readonly".

           Enregistrement dans la base, trigger

L'écran sert à visualiser et aussi à valider le champ effectuee (case à cocher) de la table Leçon. Dans ce dernier cas il faut décrémenter les crédit horaire de l'élève concerné. Ceci peut se faire à l'aide d'un trigger (déclencheur) sur une clause update de la table Leçon.

Pour créer le trigger, utiliser l'explorateur de serveurs et ajouter un déclencheur sur la table Leçon.

CREATE TRIGGER LECON_Trigger1
                    ON dbo.LECON
                     FOR UPDATE
                    AS
                              declare @codeEleve smallint,
                                          @duree smallint,
                                           @Oeffectuee bit,
                                           @Neffectuee bit
                              select @codeEleve = codeEleve from inserted
                              select @duree = duree FROM inserted
                              select @Oeffectuee = effectuee FROM deleted
                              select @Neffectuee = effectuee FROM inserted
                              IF @Oeffectuee <> @Neffectuee
                              BEGIN
                                 IF @Neffectuee = 1
                                     BEGIN
                                          UPDATE Eleve Set creditHoraire = creditHoraire - @duree
                                                            WHERE code = @codeEleve
                                       END

                                 ELSE
                                       BEGIN
                                           UPDATE Eleve Set creditHoraire = creditHoraire + @duree
                                                              WHERE code = @codeEleve
                                         END
                                END

Ce trigger se déclenchera à chaque modification de la table Leçon, testera si le champ effectuee a été modifié, si c'est le cas dans quel sens il l'a été et modifiera en conséquence le crédit horaire de l'élève concerné.

Pour valider la mise à jour dans la base de donnée il faut faire appel à un objet particulier : SqlCommandBuilder. En effet, comme nous n'avons pas utilisé l'assistant -celui-ci créant par défaut des objet de mise à jour- il faut créer "à la main" les objets qui vont prendre en charge les mises à jour.

private void btnMaj_Click(object sender, System.EventArgs e)
       {
              try
              {
                   SqlCommandBuilder commande = new SqlCommandBuilder(adapterLecon);
                   adapterLecon.Update(monDS1,"leconEleve");
               }
             catch(Exception ex)
             {
                  MessageBox.Show(ex.Message);
              }
     }