Nous supposons que notre arbre binaire de recherche ne possède que des éléments tous distincts (pas de redondance).
Code C# d'une classe générique d'arbre binaire ArbreBin<T0> avec parcours en profondeur, traduit à partir des algorithmes précédents :
InfoLoc = default(T0); fg = default(ArbreBin<T0>); fd = default(ArbreBin<T0>);
if ( value != null ) fg = new ArbreBin<T0>(); else
if ( value != null ) fd = new ArbreBin<T0>(); else
Cette classe C# permet de construire et de parcourir en profondeur selon les trois parcours précédemment étudié. Ci-après un exemple d'utilisation de cette classe T0 = int :
treeRac.filsG = new ArbreBin<int>(20); treeRac.filsD = new ArbreBin<int>(30); treeRac.filsG.filsG = new ArbreBin<int>(40); treeRac.filsG.filsD = new ArbreBin<int>(50); Console.WriteLine(" = " + );
On ajoute à la classe précédente le parcours en largeur, pour cela nous déclarons deux nouveaux champs privés dans la classe pour représenter la FIFO et le premier élément de cette FIFO. Pour la file FIFO, nous utilisons la classe générique de file de .Net Queue<T0>, le type d'élément T0 étant alors ArbreBin<T> :
private Queue<ArbreBin<T>> Fifo = new Queue<ArbreBin<T>>(); private ArbreBin<T> Premier;
Voici uniquement le code rajouté relatif au parcours en largeur, dans la classe ArbreBin<T>, le reste est identique au code défini dans les pages précédentes :
private Queue<ArbreBin<T>> Fifo = new Queue<ArbreBin<T>>(); private ArbreBin<T> Premier; public void largeur()
Premier = Fifo.Dequeue(); Console.WriteLine(); if (Premier.filsG != null) Fifo.Enqueue(Premier.filsG); if (Premier.filsD != null) Fifo.Enqueue(Premier.filsD);
Ci-après, la reprise de l'exemple d'utilisation de cette classe avec T0 = int :
treeRac.filsG = new ArbreBin<int>(20); treeRac.filsD = new ArbreBin<int>(30); treeRac.filsG.filsG = new ArbreBin<int>(40); treeRac.filsG.filsD = new ArbreBin<int>(50); Console.WriteLine(" = " + );
Console.WriteLine("--- parcours postfixe ---"); treeRac.postfixe();
Console.WriteLine("--- arbre recherche en Largeur ---"); treeRech.racine.largeur();
Console.WriteLine("--- arbre recherche en prefixe ---"); treeRech.racine.prefixe();
Console.WriteLine("--- arbre recherche en postfixe ---"); treeRech.racine.postfixe();
Console.WriteLine("--- arbre recherche en infixe ---"); treeRech.racine.infixe(); ();
3°) Méthodesupprimerpour lasuppressiondans un arbre binaire de recherche :
Méthodes C# | Algorithme - rappel |
public void supprimer(T clef) { if (racine != null) supprimerR(racine, null, 'X', clef); } private void supprimerR(ArbreBin<T> tree, ArbreBin<T> parent, char branche, T clef) { ArbreBin<T> noeudMax; if (tree == null) return;//clef non trouvée else if (clef.CompareTo() < 0) { //on cherche à gauche : this.supprimerR(tree.filsG, tree, 'G', clef); } else if (clef.CompareTo() > 0) { //on cherche à droite : this.supprimerR(tree.filsD, tree, 'D', clef); } else //l'élément est dans ce nœud: if (tree.filsD == null && tree.filsG != null) {//sous-arbre droit vide //remplacer arbre par son sous-arbre gauche : = ; tree.filsD = tree.filsG.filsD; tree.filsG = tree.filsG.filsG; } else if (tree.filsG == null && tree.filsD != null) {//sous-arbre gauche vide //remplacer arbre par son sous-arbre droit : = ; tree.filsD = tree.filsD.filsD; tree.filsG = tree.filsD.filsG; } else if (tree.filsG != null && tree.filsD != null) {// le noeud a 2 fils //le max du fils gauche : | Cette méthode suit très exactement l'algorithme proposé plus haut. La méthode supprimerR possède comme dans les deux traitements précédents les deux paramètres ArbreBin<T> tree et T clef, elle possède en plus 2 autres paramètres : • ArbreBin<T> parent = la réfernce du raent du nœud actuel. L'implantation du remplacement d'un nœud par son fils gauche s'effectue ainsi : = ; tree.filsD = tree.filsG.filsD; tree.filsG = tree.filsG.filsG; L'implantation du remplacement d'un nœud par son fils droit s'effectue ainsi : = ; tree.filsD = tree.filsD.filsD; tree.filsG = tree.filsD.filsG; |
noeudMax = maxClef(tree.filsG); = ; //Å remplacer clef //on cherche à gauche : this.supprimerR(tree.filsG, tree, 'G', ); } else // le noeud est une feuille: on le détruit { if (branche == 'D') parent.filsD = null; else if (branche == 'G') parent.filsG = null; } } private ArbreBin<T> maxClef(ArbreBin<T> tree) { if (tree.filsD == null) return tree; else return maxClef(tree.filsD); } | L'implantation de la destruction du nœud contenant la clef est effectuée par la suppression dans le parent de la référence pointant vers ce neoud. Le caractère "branche" permet de savoir si ce nœud est le fils gauche ( branche == 'G' ) ou bien le fils droit du parent ( branche == 'D' ). fils gauche => parent.filsG = null; fils droit => parent.filsD = null; |
int clef = 20;
Console.WriteLine("--- suppression clef : " + clef + " ---"); treeRech.supprimer(clef); treeRech.racine.largeur( );
Principes des bases de données
1. Introduction et Généralités 2. Le modèle de données relationnelles 3. Principes fondamentaux d'une algèbre relationnelle 4. SQL et Algèbre relationnelle
1. Introduction et Généralités
1.1Notion de système d'information
La notion de fichier est apparue en premier : le fichier regroupe tout d'abord des objets de même nature, des enregistrements. Pour rendre facilement exploitables les données d'un fichier, on a pensé à différentes méthodes d'accès (accès séquentiel, direct, indexé).
Toute application qui gère des systèmes physiques doit disposer de paramètres sémantiques décrivant ces systèmes afin de pouvoir en faire des traitements. Dans des systèmes de gestion de clients les paramètres sont très nombreux (noms, prénoms, adresse, n°Sécu, sport favori, est satisfait ou pas,..) et divers (alphabétiques, numériques, booléens,
).
Dès que la quantité de données est très importante, les fichiers montrent leurs limites et il a fallu trouver un moyen de stocker ces données et de les organiser d'une manière qui soit facilement accessible.
Base de données (BD)
Une BD est composée de données stockées dans des mémoires de masse sous une forme structurée, et accessibles par des applications différentes et des utilisateurs différents. Une BD doit pouvoir être utilisée par plusieurs utilisateurs en "même temps".
Une base de données est structurée par définition, mais sa structuration doit avoir un caractère universel : il ne faut pas que cette structure soit adaptée à une application particulière, mais qu'elle puisse être utilisable par plusieurs applications distinctes. En effet, un même ensemble de données peut être commun à plusieurs systèmes de traitement dans un problème physique (par exemple la liste des passagers d'un avion, stockée dans une base de données, peut aussi servir au service de police à vérifier l'identité des personnes interdites de séjour, et au service des douanes pour associer des bagages aux personnes….).
Les entités qui composent un système d'information
L'organisation d'un SI relève plus de la gestion que de l'informatique et n'a pas exactement sa place dans un document sur la programmation. En revanche la cheville ouvrière d'un système d'information est un outil informatique appelé un SGBD (système de gestion de base de données) qui repose essentiellement sur un système informatique composé traditionnellement d'une BD et d'un réseau de postes de travail consultant ou mettant à jour les informations contenues dans la base de données, elle-même généralement située sur un ordinateur-serveur.
Système de Gestion de Base de Données (SGBD)
Un SGBD est un ensemble de logiciels chargés d'assurer les fonctions minimales suivantes :
? Le maintien de la cohérence des données entre elles,
? le contrôle d'intégrité des données accédées,
? les autorisations d'accès aux données,
? les opérations classiques sur les données (consultation, insertion , modification, suppression)
La cohérence des données est subordonnée à la définition de contraintes d'intégrité qui sont des règles que doivent satisfaire les données pour être acceptées dans la base. Les contraintes d'intégrité sont contrôlées par le moteur du SGBD :
• au niveau de chaque champ, par exemple le : prix est un nombre positif, la date de naissance est obligatoire.
• Au niveau de chaque table - voir plus loin la notion de clef primaire : deux personnes ne doivent pas avoir à la fois le même nom et le même prénom.
• Au niveau des relations entre les tables : contraintes d'intégrité référentielles.
On organise actuellement les SGBD selon deux modes :
L'organisation locale selon laquelle le SGBD réside sur la machine où se trouve la base de données :
L'organisation client-serveur selon laquelle sur le SGBD est réparti entre la machine serveur locale supportant la BD (partie SGBD serveur) et les machines des utilisateurs (partie SGBD client). Ce sont ces deux parties du SGBD qui communiquent entre elles pour assurer les transactions de données :
Le caractère généraliste de la structuration des données induit une description abstraite de l'objet BD (Base de données). Les applications étant indépendantes des données, ces dernières peuvent donc être manipulées et changées indépendamment du programme qui y accédera en implantant les méthodes générales d'accès aux données de la base, conformément à sa structuration abstraite.
Une Base de Données peut être décrite de plusieurs points de vue, selon que l'on se place du côté de l'utilisateur ou bien du côté du stockage dans le disque dur du serveur ou encore du concepteur de la base.
Il est admis de nos jours qu'une BD est décrite en trois niveaux d'abstraction : un seul niveau a une existence matérielle physique et les deux autres niveaux sont une explication abstraite de ce niveau matériel.
Les 3 niveaux d'abstraction définis par l'ANSI depuis 1975
? | Niveau externe : correspond à ce que l'on appelle une vue de la BD ou la façon dont sont perçues au niveau de l'utilisateur les données manipulées par une certaine application (vue abstraite sous forme de schémas) |
? | Niveau conceptuel : correspond à la description abstraite des composants et des processus entrant dans la mise en œuvre de la BD. Le niveau conceptuel est le plus important car il est le résultat de la traduction de la description du monde réel à l'aide d'expressions et de schémas conformes à un modèle de définition des données. |
? |
Figurons pour l'exemple des passagers d'un avion, stockés dans une base de données de la compagnie aérienne, sachant qu'en plus du personnel de la compagnie qui a une vue externe commerciale sur les passagers, le service des douanes peut accéder à un passager et à ses bagages et la police de l'air peut accéder à un passager et à son pays d"embarquement.
Le niveau conceptuel forme l'élément essentiel d'une BD et donc d'un SGBD chargé de gérer une BD, il est décrit avec un modèle de conception de données MCD avec la méthode française Merise qui est très largement répandu, ou bien par le formalisme des diagrammes de classes UML qui prend une part de plus en plus grande dans le formalisme de description conceptuelle des données (rappelons qu'UML est un langage de modélisation formelle, orienté objet et graphique ; Merise2 a intégré dans Merise ces concepts mais ne semble pas beaucoup être utilisé ). Nous renvoyons le lecteur intéressé par cette partie aux très nombreux ouvrages écrits sur Merise ou sur UML.
Dans la pratique actuelle les logiciels de conception de BD intègrent à la fois la méthode Merise 2 et les diagrammes de classes UML. Ceci leur permet surtout la génération automatique et semi-automatique (paramétrable) de la BD à partir du modèle conceptuel sous forme de scripts (programmes simples) SQL adaptés aux différents SGBD du marché (ORACLE, SYBASE, MS-SQLSERVER,…) et les différentes versions de la BD ACCESS.
Les logiciels de conception actuels permettent aussi la rétro-génération (ou reverse engeneering) du modèle à partir d'une BD existante, cette fonctionnalité est très utile pour reprendre un travail mal documenté.
C'est en particulier le cas du logiciel français WIN-DESIGN dont une version démo est disponible à et de son rival POWER-AMC (ex AMC-DESIGNOR).
L'exemple ci-après schématise en UML le mini-monde universitaire réel suivant : ? un enseignant pilote entre 1 et 3 groupes d'étudiants,
? un enseignant demande à 1 ou plusieurs étudiants de rédiger un mémoire,
? un enseignant peut conseiller aux groupes qu'il pilote d'aller assister à une conférence, ? un groupe est constitué d'au moins 3 étudiants, ? un étudiant doit s'inscrire à au moins 2 groupes.
Si le niveau conceptuel d'une BD est assis sur un modèle de conceptualisation de haut niveau (Merise, UML) des données, il est ensuite fondamentalement traduit dans le Modèle Logique de représentation des Données (MLD). Ce dernier s'implémentera selon un modèle physique des données.
Il existe plusieurs MLD Modèles Logiques de Données et plusieurs modèles physiques, et pour un même MLD, on peut choisir entre plusieurs modèles physiques différents.
Il existe 5 grands modèles logiques pour décrire les bases de données.
Les modèles de données historiques
(Prenons un exemple comparatif où des élèves ont des cours donnés par des professeurs leur enseignant certaines matières (les enseignants étant pluridisciplinaires)
• Le modèle hiérarchique: l'information est organisée de manière arborescente, accessible uniquement à partir de la racine de l'arbre hiérarchique. Le problème est que les points d'accès à l'information sont trop restreints.
• Le modèle réseau: toutes les informations peuvent être associées les unes aux autres et servir de point d'accès. Le problème est la trop grande complexité d'une telle organisation.
• Le modèle relationnel: toutes les relations entre les objets contenant les informations sont décrites et représentées sous la forme de tableaux à 2 dimensions.
• Le modèle par déduction : comme dans le modèle relationnel les données sont décrites et représentées sous la forme de tableaux à 2 dimensions. La gestion des données (insertion, extraction,…) fonctionne selon la théorie mathématique du calcul dans la logique des prédicats. Il ne semble exister de SGBD commercial directement basé sur ce concept. Mais il est possible de considérer un programme Prolog (programmation en logique) comme une base de données car il intègre une description des données. Ce sont plutôt les logiciels de réseaux sémantiques qui sont concernés par cette approche (cf. logiciel AXON).
• Le modèle objet : les données sont décrites comme des classes et représentées sous forme d'objets, un modèle relationnel-objet devrait à terme devenir le modèle de base.
L'expérience montre que le modèle relationnel s'est imposé parce qu'il était le plus simple en terme d'indépendance des données par rapport aux applications et de facilité de représenter les données dans notre esprit. C'est celui que nous décrirons succinctement dans la suite de ce chapitre.
2. Le modèle de données relationnelles
Défini par EF Codd de la société IBM dès 1970, ce modèle a été amélioré et rendu opérationnel dans les années 80 sous la forme de SBGD-R (SGBD Relationnels). Ci-dessous une liste non exhaustive de tels SGBD-R : Access de Microsoft,
Oracle,
DB2 d'IBM,
Interbase de Borland,
SQL server de microsoft,
Informix,
Sybase,
MySQL,
PostgreSQL, ….
Nous avons déjà vu dans un précédent chapitre, la notion de relation binaire : une relation binaire R est un sousensemble d'un produit cartésien de deux ensembles finis E et F que nous nommerons domaines de la relation R :
R ? E x F
Cette définition est généralisable à n domaines, nous dirons que R est une relation n-aire sur les domaines E1 , E2 , … , En si et seulement si :
Ek = { x / (x est entier) et( x ? [1, 20] ) } en compréhension
Notation
si nous avons: R = { (v1 , v2 … , vn) } ,
Au lieu d'écrire : (v1 , v2 … , vn) ? R , on écrira R(v1 , v2 , … , vn)
Exemple de déclarations de relations :
Passager ( nom, prénom, n° de vol, nombre de bagages) , cette relation contient les informations utiles sur un passager d'une ligne aérienne.
Personne ( nom, prénom) , cette relation caractérise une personne avec deux attributs
Enseignement ( professeur, matière) , cette relation caractérise un enseignement avec le nom de la matière et le professeur qui l'enseigne.
Schéma d'une relation
On appelle schéma de la relation R : R( a1 : E1, a2 : E2 ,…, an : En )
Où (a1 , a2 … , an) sont appelés les attributs, chaque attribut ak indique comment est utilisé dans la relation R le domaine Ek , chaque attribut prend sa valeur dans le domaine qu'il définit, nous notons val(ak)= vk où vk est un élément (une valeur) quelconque de l'ensemble Ek (domaine de l'attribut ak ).
Convention : lorsqu'il n'y a pas de valeur associée à un attribut dans un n-uplet, on convient de lui mettre une valeur spéciale notée null, indiquant l'absence de valeur de l'attribut dans ce n-uplet.
Degré d'une relation
On appelle degré d'une relation, le nombre d'attributs de la relation.
Exemple de schémas de relations :
Passager ( nom : chaîne, prénom : chaîne, n° de vol : entier, nombre de bagages : entier) relation de degré 4.
Personne ( nom : chaîne, prénom : chaîne) relation de degré 2.
Enseignement ( professeur : ListeProf, matière : ListeMat) relation de degré 2.
Attributs : prenons le schéma de la relation Enseignement
Enseignement ( professeur : ListeProf, matière : ListeMat). C'est une relation binaire (degré 2) sur les deux domaines ListeProf et ListeMat. L'attribut professeur joue le rôle d'un paramètre formel et le domaine ListeProf celui du type du paramètre.
ListeProf ={ Poincaré, Einstein, Lavoisier, Raimbault , Planck } ListeMat = { mathématiques, poésie , chimie , physique }
L'attribut professeur peut prendre toutes valeurs de l'ensemble ListeProf :
Val(professeur) = Poincaré, …. , Val(professeur) = Raimbault
Si l'on veut dire que le poste d'enseignant de chimie n'est pas pourvu on écrira :
Le couple ( null , chimie ) est un couple de la relation Enseignement.
Enregistrement dans une relation
Un n-uplet (val(a1), val(a2) … , val(an) ) ? R est appelé un enregistrement de la relation R. Un enregistrement est donc constitué de valeurs d'attributs.
Dans l'exemple précédent (Poincaré , mathématiques), (Raimbault , poésie ) , ( null , chimie ) sont trois enregistrements de la relation Enseignement.
Clef d'une relation
Si l'on peut caractériser d'une façon bijective tout n-uplet d'attributs (a1 , a2 … , an) avec seulement un sous-ensemble restreint (ak1 , ak2 … , akp) avec p < n , de ces attributs, alors ce sous-ensemble est appelé une clef de la relation. Une relation peut avoir plusieurs clefs, nous choisissons l'une d'elle en la désignant comme clef primaire de la relation.
Clef minimale d'une relation
On a intérêt à ce que la clef primaire soit minimale en nombre d'attributs, car il est clair que si un sousensemble à p attributs (ak1 , ak2 … , akp) est une clef, tout sous-ensemble à p+m attributs dont les p premiers sont les (ak1 , ak2 … , akp) est aussi une clef : (ak1 , ak2 … , akp , a0 ,a1 )
(ak1 , ak2 … , akp , a10 , a5 , a9 , a2 ) sont aussi des clefs etc…
Il n'existe aucun moyen méthodique formel général pour trouver une clef primaire d'une relation, il faut observer attentivement. Par exemple :
• Le code Insee est une clef primaire permettant d'identifier les personnes.
Convention : on souligne dans l'écriture d'une relation dont on a déterminé une clef primaire, les attributs faisant partie de la clef.
Clef secondaire d'une relation
Tout autre clef de la relation qu'une clef primaire (minimale) , exemple :
Si (ak1 , ak2 … , akp) est un clef primaire de R
(ak1 , ak2 … , akp , a0 ,a1 ) et (ak1 , ak2 … , akp , a10 , a5 , a9 , a2 ) sont des clefs secondaires.
Clef étrangère d'une relation
Soit (ak1 , ak2 … , akp) un p-uplet d'attributs d'une relation R de degré n. [ R( a1 : E1, a2 : E2 ,…, an : En ) ]
Si (ak1 , ak2 … , akp) est une clef primaire d'une autre relation Q on dira que (ak1 , ak2 … , akp) est une clef étrangère de R.
Convention : on met un # après chaque attribut d'une clef étrangère.
Exemple de clef secondaire et clef étrangère :
Passager ( nom# : chaîne, prénom# : chaîne , n° de vol : entier, nombre de bagages : entier, n° client :
entier ) relation de degré 5.
Personne ( nom : chaîne, prénom : chaîne , âge : entier, civilité : Etatcivil) relation de degré 4.
n° client est une clef primaire de la relation Passager.
( nom, n° client ) est une clef secondaire de la relation Passager.
( nom, n° client , n° de vol) est une clef secondaire de la relation Passager….etc
(nom , prénom ) est une clef primaire de la relation Personne, comme (nom# , prénom# ) est aussi un couple d'attributs de la relation Passager, c'est une clef étrangère de la relation Passager.
On dit aussi que dans la relation Passager, le couple d'attributs ( nom# , prénom# ) réfère à la relation Personne.
Règle d'intégrité référentielle
Toutes les valeurs d'une clef étrangère (vk1 , vk2 … , vkp) se retrouvent comme valeur de la clef primaire de la relation référée (ensemble des valeurs de la clef étrangère est inclus au sens large dans l'ensemble des valeurs de la clef primaire)
Reprenons l'exemple précédent
Règle d'intégrité d'entité
Aucun des attributs participant à une clef primaire ne peut avoir la valeur null.
Nous définirons la valeur null, comme étant une valeur spéciale n'appartenant pas à un domaine spécifique mais ajoutée par convention à tous les domaines pour indiquer qu'un champ n'est pas renseigné.
Représentation sous forme tabulaire
Reprenons les relations Passager et Personne et figurons un exemple pratique de valeurs des relations.
Passager ( nom# : chaîne, prénom# : chaîne, n° de vol : entier, nombre de bagages : entier, n° client : entier ).
Personne ( nom : chaîne, prénom : chaîne, âge : entier, civilité : Etatcivil) relation de degré 4.
Nous figurons les tables de valeurs des deux relations
Nous remarquons que la compagnie aérienne attribue un numéro de client unique à chaque personne, c'est donc un bon choix pour une clef primaire.
Les deux tables (relations) ont deux colonnes qui portent les mêmes noms colonne nom et colonne prénom, ces deux colonnes forment une clef primaire de la table Personne, c'est donc une clef étrangère de Passager qui réfère Personne.
En outre, cette clef étrangère respecte la contrainte d'intégrité référentielle : la liste des valeurs de la clef étrangère dans Passager est incluse dans la liste des valeurs de la clef primaire associée dans Personne.
Diagramme UML modélisant la liaison Passager-Personne
Ne pas penser qu'il en est toujours ainsi, par exemple voici une autre relation Passager2 dont la clef étrangère ne respecte pas la contrainte d'intégrité référentielle :
Clef étrangère, réfère Personne
Passager2 :
nom prénom N°vol Nbr bagages N° client
Einstein Albert 75906 null 858547
Picasso Pablo 12896 5 458023
En effet, le couple (Picasso, Pablo) n'est pas une valeur de la clef primaire dans la table Personne.
Principales règles de normalisation d'une relation
1ère forme normale :
Une relation est dite en première forme normale si, chaque attribut est représenté par un identifiant unique (les valeurs ne sont pas des ensembles, des listes,…) .Ci-dessous une relation qui n'est pas en 1ère forme normale car l'attribut n° vol est multivalué (il peut prendre 2 valeurs) :
nom prénom N°vol Nbr N° client
bagage
Einstein Albert 45622 , 75906 2 154565 Lavoisier Antoine 45644 , 45622 1 785154 Raimbault Arthur 12896 2 544552 Poincaré Henri 45644 3 781201 Picasso Pablo 12896 5 458023
En pratique, il est très difficile de faire vérifier automatiquement cette règle, dans l'exemple proposé on pourrait imposer de passer par un masque de saisie afin que le N°vol ne comporte que 5 chiffres.
2ème forme normale :
nom | prénom | âge | Civilité |
Einstein | Albert | 45 | marié |
Lavoisier | Antoine | 41 | marié |
Planck | Max | 52 | veuf |
Raimbault | Arthur | 25 | célibataire |
Poincaré | Henri | 45 | marié |
Car l'attribut âge ne dépend que du nom et du prénom, de même pour l'attribut civilité.
La relation Personne3 ( nom : chaîne, prénom : chaîne , age : entier , civilité : Etatcivil) qui a pour clef primaire ( nom , âge ) n'est pas en deuxième forme normale :
nom | prénom | âge | Civilité |
Einstein | Albert | 45 | marié |
Lavoisier | Antoine | 41 | marié |
Planck | Max | 52 | veuf |
Raimbault | Arthur | 25 | célibataire |
Poincaré | Henri | 45 | marié |
Car l'attribut Civilité ne dépend que du nom et non pas de l'âge ! Il en est de même pour le prénom, soit il faut changer de clef primaire et prendre ( nom, prénom) soit si l'on conserve la clef primaire (nom , âge) , il faut décomposer la relation Personne3 en deux autres relations Personne31 et Personne32 :
nom | âge | Civilité | Einstein | 45 | marié | Lavoisier | 41 | marié | Planck | 52 | veuf | Raimbault | 25 | célibataire | Poincaré | 45 | marié | | |
nom | âge | prénom | Einstein | 45 | Albert | Lavoisier | 41 | Antoine | Planck | 52 | Max | Raimbault | 25 | Arthur | Poincaré | 45 | Henri | | |
En pratique, il est aussi très difficile de faire vérifier automatiquement la mise en deuxième forme normale. Il faut trouver un jeu de données représentatif.
3ème forme normale :
L'attribut salaire dépend de l'attribut civilité, ce que nous écrivons salaire = f(civilité), mais l'attribut civilité ne fait pas partie de la clef primaire clef = (nom , âge), donc Personne4 n'est pas en 3ème forme normale :
Il faut alors décomposer la relation Personne4 en deux relations Personne41 et Personne42 chacune en troisième forme normale:
En pratique, il est également très difficile de faire contrôler automatiquement la mise en troisième forme normale.
Remarques pratiques importantes pour le débutant :
• Les spécialistes connaissent deux autres formes normales. Dans ce cas le lecteur intéressé par l'approfondissement du sujet, trouvera dans la littérature, de solides références sur la question.
• Si la clef primaire d'une relation n'est composée que d'un seul attribut (choix conseillé lorsque cela est possible, d'ailleurs on trouve souvent des clefs primaires sous forme de numéro d'identification client, Insee,…) automatiquement, la relation est en 2ème forme normale, car chaque autre attribut non clef étrangère, ne dépend alors que de la valeur unique de la clef primaire.
• Penser dès qu'un attribut est fonctionnellement dépendant d'un autre attribut qui n'est pas la clef ellemême à décomposer la relation (créer deux nouvelles tables).
• En l'absence d'outil spécialisé, il faut de la pratique et être très systématique pour contrôler la normalisation.
Base de données relationnelles BD-R: Ce sont des données structurées à travers : • Une famille de domaines de valeurs, • Une famille de relations n-aires, • Les contraintes d'intégrité sont respectées par toute clef étrangère et par toute clef primaire. • Les relations sont en 3ème forme normale. (à minima en 2ème forme normale) Les données sont accédées et manipulées grâce à un langage appelé langage d'interrogation ou Système de Gestion de Base de Données relationnel : C'est une famille de logiciels comprenant : • Une BD-R. • Un langage d'interrogation. • Une gestion en interne des fichiers contenant les données et de l'ordonnancement de ces données. • Une gestion de l'interface de communication avec les utilisateurs. • La gestion de la sécurité des accès aux informations contenues dans la BD-R. |
Le schéma relation d'une relation dans une BD relationnelle est noté graphiquement comme ci-dessous :
Voici dans le SGBD-R Access, la représentation des schémas de relation ainsi que la liaison sans intégrité des deux relations précédentes Passager et Personne :
Access et la représentation des enregistrements de chaque table :
Les besoins d'un utilisateur d'une base de données sont classiquement ceux que l'on trouve dans tout ensemble de données structurées : insertion, suppression, modification, recherche avec ou sans critère de sélection. Dans une BDR, ces besoins sont exprimés à travers un langage d'interrogation. Historiquement deux classes de langages relationnels équivalentes en puissance d'utilisation et de fonctionnement ont été inventées : les langages algébriques et les langages des prédicats.
Un langage relationnel n'est pas un langage de programmation : il ne possède pas les structures de contrôle de base d'un langage de programmation (condition, itération, …). Très souvent il doit être utilisé comme complément à l'intérieur de programmes Delphi, Java, C#, ...
Les langages d'interrogation prédicatifs sont des langages fondés sur la logique des prédicats du 1er ordre, le plus ancien s'appelle Query By Example QBE.
3. Principes fondamentaux d'une l'algèbre relationnelle
Une algèbre relationnelle est une famille d'opérateurs binaires ou unaires dont les opérandes sont des relations. Nous avons vu que l'on pouvait faire l'union, l'intersection, le produit cartésien de relations binaires dans un chapitre précédent, comme les relations n-aires sont des ensembles, il est possible de définir sur elle une algèbre opératoire utilisant les opérateurs classiques ensemblistes, à laquelle on ajoute quelques opérateurs spécifiques à la manipulation des données.
Remarque pratique :
La phrase "tous les n-uples sont distincts, puisqu'éléments d'un même ensemble nommé relation" se transpose en pratique en la phrase " toutes les lignes d'une même table nommée relation, sont distinctes (même en l'absence de clef primaire explicite)".
Nous exhibons les opérateurs principaux d'une algèbre relationnelle et nous montrerons pour chaque opération, un exemple sous forme.
Union de 2 relations
Remarque : (Planck, 52, veuf) ne figure qu'une seule fois dans la table R ? Q.
Intersection de 2 relations
Différence de 2 relations
Soient R et Q deux relations de même domaine et de même degré on peut calculer la nouvelle relation S = R ? Q de même degré et de même domaine contenant les enregistrements qui sont présents dans R mais qui ne sont pas dans Q ( on exclut de R les enregistrements qui appartiennent à R ? Q) :
Produit cartésien de 2 relations
Soient R et Q deux relations de domaine et de degré quelconques (degré(R)=n, degré(Q)=p), avec Domaine(R) ? Domaine(Q) = ? (pas d'attributs en communs).
On peut calculer la nouvelle relation S = R x Q de degré n + p et de domaine égal à l'union des domaines de R et de Q contenant tous les couples d'enregistrements à partir d'enregistrements présents dans R et d'enregistrements présents dans Q :
Einstein 45 marié S = R x Q Paris 874
Lavoisier 41 marié Rome 920 Planck 52 veuf
S :
nom âge Civilité ville km
Einstein 45 marié Paris 874 Einstein 45 marié Rome 920 Lavoisier 41 marié Paris 874 Lavoisier 41 marié Rome 920 Planck 52 Veuf Paris 874
Planck 52 Veuf Rome 920
Selection ou Restriction d'une relation
Soit R une relation, soit R( a1 : E1, a2 : E2 ,…, an : En ) le schéma de cette relation.
Soit Cond(a1, a2 , … , an ) une expression booléenne classique (expression construite sur les attributs avec les connecteurs de l'algèbre de Boole et les opérateurs de comparaison < , > , = , >= , <= , <> )
On note S = select (Cond(a1, a2 , … , an ) , R), la nouvelle relation S construite ayant le même schéma que R soit S( a1 : E1, a2 : E2 ,…, an : En ), qui ne contient que les enregistrements de R qui satisfont à la condition booléenne Cond(a1, a2 , … , an ).
R :
nom | âge | Civilité | ville | km |
Einstein | 45 | marié | Paris | 874 |
Mozart | 32 | marié | Rome | 587 |
Gandhi | 64 | célibataire | Paris | 258 |
Lavoisier | 41 | marié | Rome | 124 |
Lupin | 42 | Veuf | Paris | 608 |
Planck | 52 | Veuf | Rome | 405 |
Select ( { âge > 42 et ville=Paris}, R ) signifie que l'on
Cond(a1, a2 , … , an ) = { âge > 42 et ville=Paris } enregistrements de R constitués des personnes ayant séjourné à S : Paris et plus âgées que 42 ans.
nom | âge | Civilité | ville | km |
Einstein | 45 | marié | Paris | 874 |
Gandhi | 64 | célibataire | Paris | 258 |
Projection d'une relation
Soit R une relation, soit R( a1 : E1, a2 : E2 ,…, an : En ) le schéma de cette relation.
On appelle S = proj(ak1 , ak2 … , akp) la projection de R sur un sous-ensemble restreint (ak1 , ak2
… , akp) avec p < n , de ses attributs, la relation S ayant pour schéma le sous-ensemble des attributs S(ak1 : Ek1, ak2 : Ek2 ,…, akp : Ekp ) et contenant les enregistrements différents obtenus en ne considérant que les attributs (ak1 , ak2 … , akp).
Exemple
nom | ville |
Einstein | Paris |
Mozart | Rome |
Lupin | Paris |
Einstein | Venise |
Gandhi | Paris |
Lavoisier | Rome |
Planck | Rome |
nom | Civilité | | nom | âge |
Einstein | marié | Einstein | 45 |
Mozart | marié | Mozart | 32 |
Lupin | Veuf | Lupin | 42 |
Gandhi | célibataire | Gandhi | 64 |
Lavoisier | marié | Lavoisier | 41 |
Planck | Veuf | Planck | 52 |
Lors de la recopie des enregistrements de R dans S2 on a ignoré les attributs âge, ville et km, le couple (Einstein, marié) ne doit se retrouver qu'une seule fois car une relation est un ensemble et ses éléments sont tous distincts.
Jointure de deux relations
Soit Soient R et Q deux relations de domaine et de degré quelconques (degré(R) = n, degré(Q) = p), avec Domaine(R) ? Domaine(Q) = ? (pas d'attributs en communs).
soit R x Q leur produit cartésien de degré n + p et de domaine D union des domaines de R et de Q. Soit un ensemble (a1, a2 , … , an+p ) d'attributs du domaine D de R x Q.
Une jointure couramment utilisée en pratique, est celle qui consiste en la sélection selon une condition d'égalité entre deux attributs, les personnes de "l'art relationnel" la dénomment alors l'équi-jointure.
2°) S = select ( km < 900 et Civilité = veuf, R x Q )
nom | âge | Civilité | ville | km |
Planck | 52 | veuf | Paris | 874 |
Nous nous plaçons maintenant du point de vue pratique, non pas de l'administrateur de BD mais de l'utilisateur uniquement concerné par l'extraction des informations contenues dans une BD-R.
Un SGBD permet de gérer une base de données. A ce titre, il offre de nombreuses fonctionnalités supplémentaires à la gestion d'accès simultanés à la base et à un simple interfaçage entre le modèle logique et le modèle physique : il sécurise les données (en cas de coupure de courant ou autre défaillance matérielle), il permet d'accéder aux données de manière confidentielle (en assurant que seuls certains utilisateurs ayant des mots de passe appropriés, peuvent accéder à certaines données), il ne permet de mémoriser des données que si elles sont du type abstrait demandé : on dit qu'il vérifie leur intégrité (des données alphabétiques ne doivent pas être enregistrées dans des emplacements pour des données numériques, )
Actuellement, une base de données n'a pas de raison d'être sans son SGBD. Aussi, on ne manipule que des bases de données correspondant aux SGBD qui les gèrent : il vous appartient de choisir le SGBD-R qui vous convient (il faut l'acheter auprès de vendeurs qui généralement, vous le fournissent avec une application de manipulation visuelle, ou bien utiliser les SGBD-R qui vous sont livrés gratuitement avec certains environnements de développement comme Borland studio ou Visual Studio ou encore utiliser les produits gratuits comme mySql).
Une application doit pouvoir "parler" au SGBD : elle le fait par le moyen d'un langage de manipulation des données. Nous avons déjà précisé que la majorité des SGBD-R utilisait un langage relationnel ou de requêtes nommé SQL pour manipuler les données.
4. SQL et Algèbre relationnelle
Requête
Les requêtes sont des questions posées au SGBD, concernant une recherche de données contenues dans une ou plusieurs tables de la base.
Par exemple, on peut disposer d'une table définissant des clients (noms, prénoms, adresses, n°de client) et d'une autre table associant des numéros de clients avec des numéros de commande d'articles, et vouloir poser la question suivante : quels sont les noms des clients ayant passé des commandes ?
Une requête est en fait, une instruction de type langage de programmation, respectant la norme SQL, permettant de réaliser un tel questionnement. L'exécution d'une requête permet d'extraire des données en provenance de tables de la base de données : ces données réalisent ce que l'on appelle une projection de champs (en provenance de plusieurs tables). Le résultat d'exécution d'une requête est une table constituée par les réponses à la requête.
Le SQL permet à l'aide d'instructions spécifiques de manipuler des données présentes à l'intérieur des tables :
Instruction SQL | Actions dans la (les) table(s) |
INSERT INTO <…> | Ajout de lignes |
DELETE FROM <…> | Suppression de lignes |
TRUNCATE TABLE <…> | Suppression de lignes |
UPDATE <…> | Modification de lignes |
SELECT <…>FROM <…> | Extraction de données |
Ajout, suppression et modification sont les trois opérations typiques de la mise à jour d'une BD. L'extraction concerne la consultation de la BD.
Traduction en SQL des opérateurs relationnels
C'est l'instruction SQL "SELECT <…>FROM <…>" qui implante tous ces opérateurs. Tous les exemples utiliseront la relation R = TableComplete suivante et l'interpréteur SQL d'Access :
Projection d'une relation R
Une autre projection sur la même table :
Sélection-Restriction
S = select (Cond(a1, a2 , … , an ) , R)
SQL :SELECT * FROM R WHERE Cond(a1, a2 , … , an)
Le symbole * signifie toutes les colonnes de la table (tous les attributs du schéma)
Instruction SQL version opérateur algèbre : SELECT *FROM Tablecomplete WHERE âge > 42 AND ville = Paris Lancement de la requête SQL : | Table obtenue après requête : On a sélectionné toutes les personnes de plus de 42 ans ayant séjourné à Paris. |
Combinaison d'opérateur projection-restriction |
Projection distincte et sélection : SELECT DISTINCT nom , civilité, âge FROM Tablecomplete WHERE âge >= 45 Lancement de la requête SQL : | Table obtenue après requête : On a sélectionné toutes les personnes d'au moins 45 ans et l'on ne conserve que leur nom, leur civilité et leur âge. |
Intersection, union, différence,
S = R ? Q
SQL :SELECT * FROM R INTERSECTSELECT * FROM Q
S = R ? Q
SQL :SELECT * FROM R UNIONSELECT * FROM Q
S = R ? Q
SQL :SELECT * FROM R MINUSSELECT * FROM Q
Produit cartésien
S = R x Q
SQL :SELECT * FROM R , Q
Afin de ne pas présenter un exemple de table produit trop volumineuse, nous prendrons comme opérandes du produit cartésien, deux tables contenant peu d'enregistrements :
Jointure de deux relations
Remarque pratique importante
Le langage SQL est plus riche en fonctionnalités que l'algèbre relationnelle. En effet SQL intègre des possibilités de calcul (numériques et de dates en particulier).
Soit une table de tarifs de produit avec des prix hors taxe:
2°) Usage de la fonction intégrée SUM : calcul du total des prix HT.
ADO .Net : données relationnelles de .Net 2.0
1. Le modèle DataSet dans 2.0
DataSet
DataTable
DataColumn
DataRow
DataRelation
2. Création et utilisation d'un DataSet
Création d'un DataSet
Création de 2 DataTable
Définition du schéma de colonnes de chaque table par des DataColumn.
Définition d'une clef primaire.
Ajout des 2 tables au DataSet.
Ajout d'une relation entre les deux tables.
Exemple de création de données dans le DataSet
Affichage des données d'un DataSet
Sauvegarde des données du DataSet aux formats XML et XSL
Introduction
Nous avons vu dans un chapitre précédent que les fichiers simples peuvent être accédés par des flux, dès que l'organisation de l'information dans le fichier est fortement structurée les flux ne sont plus assez puissants pour fournir un accès souple aux données en particulier pour des applications à architecture multi-tiers (client/serveur, internet,…) et spécialement aux bases de données.
ADO .NET est un regroupement de types (classes, interfaces, …) dans l'espace de nom construits par Microsoft afin de manipuler des données structurées dans le .NET Framework.
Le modèle ADO .NET du .NET Framework fournit au développeur un ensemble d'éléments lui permettant de travailler sur des données aussi bien en mode connecté qu'en mode déconnecté (ce dernier mode est le mode préférentiel d' ADO .NET car c'est celui qui est le plus adapté aux architectures multi-tiers). ADO .NET est indépendant du mode de stockage des données : les classes s'adaptent automatiquement à l'organisation
ADO .NET permet de traiter des données situées dans des bases de données selon le modèle relationnel mais il supporte aussi les données organisées selon le modèle hiérarchique.
ADO .NET échange toutes ses informations au format XML
1. Le modèle DataSet dans 2.0
Le principe de base du DataSet est de se connecter à une source de données (par exemple une base de données) de charger toutes ses tables avec leur relations, puis ensuite de travailler en mode déconnecté sur ces tables en mémoire et enfin se reconnecter pour effectuer la mise à jour éventuelle des données.
Les classes mises en jeu lors d'une telle opération sur les données sont les suivantes :
.DataSet | |
.DataTable | .DataTableCollection |
.DataColumn | .DataColumnCollection |
.DataRow | .DataRowCollection |
.DataRelation | .DataRelationCollection |
.DataConstraint .DataView | .ConstraintCollection |
Un DataSet est en fait une collection de tables représentée par un objet de collection de la classe DataTableCollection et une collection de relations entres ces tables représentée par un objet de collection de la classe DataRelationCollection.
Une DataTableCollection est une collection (famille) d'une ou plusieurs DataTable qui représentent chacunes une table dans le DataSet.
Une table générale est une entité composée de :
? Colonnes
? Lignes
Un objet de classe DataTable est composé en particulier des objets suivants :
? Une propriété Columns = Collection de colonnes (collection d'objets DataColumn)
? Une propriété Rows = Collection de lignes (collection d'objets DataRow)
Le schéma général de la table d'un objet DataTable est donné par la famille des colonnes (l'objet Columns) chaque objet DataColumn de la collection représente une colonne de la table :
Pour résumer, un DataSet contient pour l'essentiel deux types d'objets :
1°) des objets DataTable inclus dans une DataTableCollection
2°) des objets DataRelation inclus dans une DataRelationCollection
Une DataRelationCollection est une collection d'une ou plusieurs DataRelation qui représentent chacunes une relation entre deux DataTable dans le DataSet.
La DataTableCollection du DataSet :
La DataRelationCollection du DataSet :
1. Création et utilisation d'un DataSet
Nous passons maintenant à la pratique d'utilisation d'un DataSet dans un programme C# de création de données selon la démarche suivante :
Phase de mise en place : ? Création d'un DataSet (un magasin). ? Création de 2 DataTable (une table client, une table achats). ? Définition du schéma de colonnes de chaque table par des DataColumn. ? Définition d'une clef primaire. ? Ajout des 2 tables au DataSet. ? Ajout d'une relation entre les deux tables. Phase de manipulation des données proprement dites : ? Ajouter, supprimer ou modifier des données ligne par ligne dans chaque table. |
Création d'un DataSet ( un magasin ) :
private DataSet unDataSet = new DataSet( );
unDataSet.DataSetName = "Magasin" ;
Création des deux tables :
private DataTable tabClients = new DataTable( "Clients" ); private DataTable tabAchats = new DataTable( "Achats" );
Définition du schéma de 3 colonnes de la table Clients :
DataColumn colClientID = new DataColumn( "ClientID", typeof(int) );
DataColumn colClientNom = new DataColumn( "ClientNom" ); DataColumn colPayed = new DataColumn( "Payé", typeof(bool) ); (colClientID); (colClientNom);
(colPayed);
Définition du schéma de 4 colonnes de la table Achats :
DataColumn colID = new DataColumn("ClientID", typeof(int));
DataColumn colArticle = new DataColumn("Article",typeof(string));
DataColumn colDateAchat = new DataColumn( "DateAchat",typeof(DateTime));
DataColumn colMontantAchat = new DataColumn("MontantAchat", typeof(decimal)); (colID); (colArticle); (colMontantAchat);
(colDateAchat);
Définition d'une clef primaire composée de 2 colonnes :
tabClients.PrimaryKey = new DataColumn[ ] {colClientID, colClientNom};
Ajouter les 2 tables au DataSet :
(tabClients);
(tabAchats);
Ajouter une relation "Liste des achats" entre les 2 tables :
DataRelation dr = new DataRelation("Liste des achats", colClientID , colID);
// ajouter cette relation dans le DataSet (dr);
// imbrication de relation écrite dans le fichier xml:
dr.Nested=true;
La validation de toutes les modifications apportées au DataSet depuis son chargement s'effectue grâce à la méthode AcceptChanges(), on termine donc le code par la ligne :
unDataSet.AcceptChanges();
Le DataSet ainsi construit peut maintenant travailler en lecture et en écriture sur une structure de données construite sous forme de deux tables Clients et Achats reliées entre elles comme figuré ci-dessous :
Exemple de création de données dans le DataSet :
enum typArticle {vélo, télévision, radio, cuisinière }
//-- remplissage direct des tables
// Déclaration d'un référence de ligne de données
DataRow newRow1;
// Création de 6 lignes de clients dans la table "Clients" for(int i = 1; i <= 6; i++) {
// Création de la ligne newRow1 possédant le même schéma que tabClients newRow1 = tabClients.NewRow(); newRow1["ClientID"] = 1000+i;
newRow1["ClientNom "] = "….";; newRow1["Payé "] = false;
// Ajouter la ligne newRow1 à la table tabClients
(newRow1);
}
// Remplissage de la colonne "ClientNom" de chaque ligne de clients créée
[0]["ClientNom"] = "Legrand"; [1]["ClientNom"] = "Fischer"; [2]["ClientNom"] = "Dupont"; [3]["ClientNom"] = "Durand"; [4]["ClientNom"] = "Lamiel";
[5]["ClientNom"] = "Renoux";
// Remplissage de la colonne "ClientNom" de chaque ligne de clients créée [0]["Payé"] = true; [1]["Payé"] = true; [2]["Payé"] = false; [3]["Payé"] = false; [4]["Payé"] = true;
[5]["Payé"] = false;
// Déclaration d'un référence de ligne de données
DataRow newRow2;
/* pour chacun des 6 clients remplissage aléatoire des 4 colonnes selon un maximum de 7 lignes d'achats différents.
*/
Random gen = new Random();
for(int j = 1; j < max; j++){
// création d'une nouvelle ligne d'achats newRow2 = tabAchats.NewRow(); newRow2["ClientID"]= 1000+i; newRow2["DateAchat"]= new DateTime(2005, (12)+1, (29)+1); newRow2["MontantAchat"] = Math.Round(gen.NextDouble()*1000,2); newRow2["Article"]= Enum.GetName(typeof(typArticle),(5));
// Ajouter la ligne à la table Achats (newRow2);
}
do{max = (8);}while(max==0);
}
Affichage des données d'un DataSet
Voici affiché dans un composant visuel de NetFramework1.1 et NetFramework 2.0, le DataGrid, la table des Clients avec ses trois colonnes remplies par le programme précédent :
Voici affiché dans un autre composant visuel DataGrid, la table des Achats avec ses quatre colonnes remplies par le programme précédent :
Le composant DataGrid destiné à afficher une table de données, est remplacé dans la version 2.0 par le DataGridView, mais est toujours présent et utilisable, nous verrons illustrerons l'utilisation d'un DataGridView plus loin dans ce document avec des données provenant d'une Base de Données.
private System.Windows.Forms.DataGrid DataGridSimple;
Voici deux façons de lier ce composant visuel à la première table (la table Clients) du DataSet du programme de gestion du Magasin :
// Lie directement par le nom de la table le DataGrid au DataSet. DataGridSimple.SetDataBinding(unDataSet, "Clients");
// Lie indirectement par le rang, le DataGrid au DataSet sur la première table :
DataGridSimple.SetDataBinding(unDataSet, unDataSet.Tables[0].TableName);
Sauvegarde des données du DataSet aux formats XML et XSL :
// Stockage uniquement du schéma général du DataSet dans un fichier séparé unDataSet.WriteXmlSchema("");
// Stockage à la fois dans un même fichier du schéma et des données
unDataSet.WriteXml("", XmlWriteMode.WriteSchema);
Fichier "" obtenu au format XSL
<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="; xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="fr-FR">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Clients">
<xs:complexType>
<xs:sequence>
<xs:element name="ClientID" type="xs:int" />
<xs:element name="ClientNom" type="xs:string" />
<xs:element name="Payé" type="xs:boolean" minOccurs="0" />
<xs:element name="Achats" minOccurs="0" maxOccurs="unbounded"> <xs:complexType>
<xs:sequence>
<xs:element name="ClientID" type="xs:int" minOccurs="0" />
<xs:element name="Article" type="xs:string" minOccurs="0" />
<xs:element name="MontantAchat" type="xs:decimal" minOccurs="0" />
<xs:element name="DateAchat" type="xs:dateTime" minOccurs="0" /> </xs:sequence>
</xs:complexType>
</xs:element> </xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1">
<xs:selector xpath=".//Clients" />
<xs:field xpath="ClientID" />
</xs:unique>
<xs:unique name="Constraint2" msdata:PrimaryKey="true">
<xs:field xpath="ClientID" />
<xs:field xpath="ClientNom" />
</xs:unique>
<xs:keyref name="Liste_x0020_des_x0020_achats" refer="Constraint1" msdata:IsNested="true">
<xs:selector xpath=".//Achats" />
<xs:field xpath="ClientID" />
</xs:keyref>
</xs:element>
</xs:schema>
Fichier "" obtenu format XML
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="; xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="fr-FR">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Clients">
<xs:complexType>
<xs:sequence>
<xs:element name="ClientID" type="xs:int" />
<xs:element name="ClientNom" type="xs:string" />
<xs:element name="Payé" type="xs:boolean" minOccurs="0" />
<xs:element name="Achats" minOccurs="0" maxOccurs="unbounded"> <xs:complexType>
<xs:sequence>
<xs:element name="ClientID" type="xs:int" minOccurs="0" />
<xs:element name="Article" type="xs:string" minOccurs="0" />
<xs:element name="MontantAchat" type="xs:decimal" minOccurs="0" /> <xs:element name="DateAchat" type="xs:dateTime" minOccurs="0" /> </xs:sequence>
</xs:complexType>
</xs:element> </xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1">
<xs:selector xpath=".//Clients" />
</xs:unique>
<xs:unique name="Constraint2" msdata:PrimaryKey="true">
<xs:selector xpath=".//Clients" />
<xs:field xpath="ClientID" />
<xs:field xpath="ClientNom" />
</xs:unique>
<xs:keyref name="Liste_x0020_des_x0020_achats" refer="Constraint1" msdata:IsNested="true"> <xs:selector xpath=".//Achats" />
<xs:field xpath="ClientID" />
</xs:keyref>
</xs:element>
</xs:schema>
<Clients>
<ClientID>1001</ClientID>
<ClientNom>Legrand</ClientNom>
<Payé>true</Payé>
<Achats>
<ClientID>1001</ClientID>
<Article>cuisinière</Article>
<MontantAchat>456.74</MontantAchat>
<DateAchat>2005-12-12T00:00:00.0000000+01:00</DateAchat> </Achats>
</Clients>
<Clients>
<ClientID>1002</ClientID>
<ClientNom>Fischer</ClientNom>
<Payé>true</Payé>
<Achats>
<ClientID>1002</ClientID>
<Article>radio</Article>
<MontantAchat>297.58</MontantAchat>
<DateAchat>2005-02-25T00:00:00.0000000+01:00</DateAchat> </Achats>
<Achats>
<ClientID>1002</ClientID>
<Article>télévison</Article>
<MontantAchat>715.1</MontantAchat>
<DateAchat>2005-07-19T00:00:00.0000000+02:00</DateAchat> </Achats>
<Achats>
<ClientID>1002</ClientID>
<Article>télévison</Article>
<MontantAchat>447.55</MontantAchat>
<DateAchat>2005-08-16T00:00:00.0000000+02:00</DateAchat>
<Achats>
<ClientID>1002</ClientID>
<Article>cuisinière</Article>
<MontantAchat>92.64</MontantAchat>
<DateAchat>2005-09-23T00:00:00.0000000+02:00</DateAchat>
</Achats>
<Achats>
<ClientID>1002</ClientID>
<Article>cuisinière</Article>
<MontantAchat>171.07</MontantAchat>
<DateAchat>2005-01-23T00:00:00.0000000+01:00</DateAchat>
</Achats>
</Clients>
<Clients>
<ClientID>1003</ClientID>
<ClientNom>Dupont</ClientNom>
<Payé>false</Payé>
<Achats>
<ClientID>1003</ClientID>
<Article>aspirateur</Article>
<MontantAchat>445.89</MontantAchat>
<DateAchat>2005-02-11T00:00:00.0000000+01:00</DateAchat>
</Achats>
</Clients>
<Clients>
<ClientID>1004</ClientID>
<ClientNom>Durand</ClientNom>
<Payé>false</Payé>
<Achats>
<ClientID>1004</ClientID>
<Article>télévison</Article>
<MontantAchat>661.47</MontantAchat>
<DateAchat>2005-11-15T00:00:00.0000000+01:00</DateAchat>
</Achats>
</Clients>
<Clients>
<ClientID>1005</ClientID>
<ClientNom>Lamiel</ClientNom>
<Payé>true</Payé>
</Clients>
<Clients>
<ClientID>1006</ClientID>
<ClientNom>Renoux</ClientNom>
<Payé>false</Payé>
<Achats>
<ClientID>1006</ClientID>
<MontantAchat>435.17</MontantAchat>
<DateAchat>2005-06-22T00:00:00.0000000+02:00</DateAchat>
</Achats>
<Achats>
<ClientID>1006</ClientID>
<Article>cuisinière</Article>
<MontantAchat>491.3</MontantAchat>
<DateAchat>2005-12-25T00:00:00.0000000+01:00</DateAchat>
</Achats>
<Achats>
<ClientID>1006</ClientID>
<Article>cuisinière</Article> <MontantAchat>388.81</MontantAchat>
<DateAchat>2005-10-13T00:00:00.0000000+02:00</DateAchat>
</Achats>
<Achats>
<ClientID>1006</ClientID>
<Article>radio</Article>
<MontantAchat>864.93</MontantAchat>
<DateAchat>2005-07-23T00:00:00.0000000+02:00</DateAchat>
</Achats>
</Clients>
</NewDataSet>
ADO .Net et SQL serveur 2005
1. Rappel sur l'installation de SQL Serveur 2005
2. Accès lecture en mode connecté à une BD
Exemple de lecture en mode connecté dans une BD Access
Exemple de lecture en mode connecté dans une BD SQL serveur 2005
mode déconnecté : Affichage avec le DataGridView
1. DataGridView lié directement en lecture et écriture à un DataTable
2. Construire les lignes et les colonnes d'un DataGridView par programme
3. DataGridView lié à un DataSet
mode déconnecté : modifications de données à partir d'un DataGridView lié à un DataSet lui-même connecté à une BD
Exercices :
• Gestion simplifiée d'un petit magasin
• Amélioration de la gestion du petit magasin : clef étrangère et delete/update en cascade
1. Rappel sur l'installation de SQL Serveur 2005
Installation de SQL server 2005 :
• installer SQL server 2005 éd. Developer ( 2 x CD-ROM ).
1°) Vérifiez SQL server 2005 en lançant « SQL server configuration manager » :
Le gestionnaire de configuration doit vous indiquer que SQL server (nommé ici MSSQLSERVER) est dans l’état « Running » :
La figure précédente montre qu'il est possible d'installer sur la même machine "SQL server express" (version gratuite) et SQL server 2005 (version payante).
2°) Accéder à une BD SQL server 2005 en lançant « SQL server Management Studio » :
3°) Attacher une BD type *.mdf à SQL server 2005 (bouton droit de souris sur Databases) :
Sélectionner le fichier de la BD déjà existante ( ici « » ) :
Pour manipuler SQL serveur 2005, voir le manuel fournit par Microsoft ou bien des ouvrages spécialisés sur le sujet. Par la suite, après avoir installé SQL serveur 2005 sur notre machine, nous avons pour objectif de montrer comment accéder à une base de données en local à partir d'un programme écrit en C# en utilisant les outils d'.
2. Accès lecture en mode connecté à une BD
Rappelons qu' 2.0 est composé de deux ensembles d'outils principaux de gestion des données :
o Les objets de stockage de données : objets déconnectés stockant les données sur la machine locale.
o Les objets fournisseurs (objets connectés directement) : gérant la communication entre le programme et la base de données.
Nous avons exploré dans le chapitre précédent les possibilités offertes par les objets de données comme principalement le DataSet dont nous rappelons l'organisation ci-dessous :
Nous explorons dans ce chapitre, les objets du type fournisseurs et leurs utilisations dans un programme.
Ado .Net met propose plusieurs classes de fournisseurs permettant de gérer l'accès à diverses sources de données, toutes situées dans le namespace :
Ci-dessous les principaux objets d' 2.0 en liaison avec les BD :
Objets | Fonctionnalités |
OdbcConnection SqlConnection OracleConnection ……… | Assurent la gestion des connexions physiques vers la base de données du type concerné ( ODBC, SQL server, Oracle, ….) |
OdbcCommand SqlCommand OracleCommand ……… | Assure le fonctionnement des commandes SQL (SELECT, INSERT, UPDATE, DELETE) vers la base de données. L’exécution de la commande (aussi appelée requête) s’effectue grâce à 4 méthodes qui se dénomment Executexxx selon le type de traitement à effectuer ( par ex : ExecuteReader sert au mode connecté). |
OdbcDataReader SqlDataReader OracleDataReader ……… | Mode connecté : permet de lire les résultats des requêtes renvoyés par l’exécution de la commande SQL (en lecture seule). Ce mode de travail dans lequel le programme est connecté directement sur la BD, est déconseillé dès que l'application est complexe (par exe : modifications fréquentes). |
Mode déconnecté : permet de lire et de travailler les résultats des requêtes renvoyés par l’exécution de la commande SQL indépendamment de la BD. Le programme effectue un accès à la BD et range les données et leurs relations stockées en mémoire centrale dans un objet DataSet en cache. Ce mode est conseillé car l'application travaille alors sur une "image" des données et de leurs relations. Il faut ensuite effectuer une validation des modifications apportées aux données dans le cache.
Exemple de lecture en mode connecté dans une BD Access
Lancement de la lecture d'une table " Magasin " dans une BD Access nommée "":
Nous nous prioposons d'écrire un programme lisant et affichant le contenu de cette BD, enregistrement par enregistrement.
Le schéma général qui est adopté pour une telle action est le suivant :
• Création d'un objet de connexion physique à la BD.
• Création d'un objet de commande avec une requête.
• Exécution de la commande en mode connecté.
• Lecture et affichage des champs résultats de la commande.
• Fermeture de la connexion physique à la BD.
• Libération des ressources utilisées.
Code source d'un programme affichant les résultats d'une requête dans un textBox :
// chemin vers la BD (Access): string urlAcess = "Driver={Microsoft Access Driver (*.mdb)};"
// libérer les ressources : connexion.Dispose(); connexion = null;
Exemple de lecture en mode connecté dans une BD SQL serveur 2005
Reprenons la même action sur la BD de démonstration gratuite fournie par Microsoft avec SQL serveur 2005 " " et lançons la lecture des 16 enregistrements de la table "HumanResources.Departement":
// chemin vers la BD (SQL serveur 2005):
string urlSqlServer = @"Data Source=(local);Initial Catalog=AdventureWorks;" + "Integrated Security=SSPI;";
Le mécanisme reste le même que celui qui a été utilisé pour la BD Access, seuls les objets permettant la connexion, la commande, la lecture sont différents mais opèrent d'une manière identique. Ci-dessous les lignes de code qui diffèrent selon le type de BD utilisée :
// objet de connexion : SqlConnection connexion; connexion = new SqlConnection(urlSqlServer); |
….
// préparation de la requête effective :
string Requete = "SELECT * FROM HumanResources.Department"; SqlCommand cmdSqlServ = new SqlCommand (Requete, connexion);
….
//lecture de plusieurs lignes : SqlDataReader lignesRead; lignesRead = cmdSqlServ.ExecuteReader(CommandBehavior.CloseConnection); textBoxAffiche.Clear(); while (()) { textBoxAffiche.AppendText(Convert.ToString(lignesRead.GetInt16(0)) + " -- " + lignesRead.GetString(1) + " -- " + lignesRead.GetString(2) + " -- " + } |
Code source complet du programme affichant les résultats de la requête dans un textBox :
// chemin vers la BD (SQL serveur 2005):
string urlSqlServer = @"Data Source=(local);Initial Catalog=AdventureWorks;" + "Integrated Security=SSPI;";
// objet de connexion : SqlConnection connexion;
connexion = new SqlConnection(urlSqlServer);
Console.WriteLine();
// ouvrir la connexion : ();
// traitement de la BD :
string Requete = "SELECT * FROM HumanResources.Department"; SqlCommand cmdSqlServ = new SqlCommand(Requete, connexion);
//lecture de plusieurs lignes : SqlDataReader lignesRead;
lignesRead = cmdSqlServ.ExecuteReader(CommandBehavior.CloseConnection); textBoxAffiche.Clear(); while (())
{
textBoxAffiche.AppendText(Convert.ToString(lignesRead.GetInt16(0)) + " -- " + lignesRead.GetString(1) + " -- " + lignesRead.GetString(2) + " -- " + lignesRead.GetDateTime(3).ToString() + "\r\n");
}
// fermer la connexion :
connexion.Close();
// libérer les ressources : connexion.Dispose();
connexion = null;
Affichage obtenu par ce programme :
Remarque pratique de paramétrage de SQL serveur 2005 :
Si dans une utilisation pratique des exemples traités ci-après vous obtenez un message d’erreur indiquant que la connexion TCP/IP n’a pas pu avoir lieu, vérifiez que le statut du protocole TCP/IP est « enabled » en exécutant le programme SQL Server Config.Manager de SQL serveur 2005 :
N’oubliez pas ensuite de redémarrer SQL Server, à partir du même SQL Server Config. :
mode déconnecté : Affichage avec le DataGridView
Un DataGridView permet d'afficher des données provenant de différentes sources grâce à sa propriété de liaison de données (data-bind) DataSource en lecture ou écriture, qui est une référence vers l'objet qui contient les données à afficher.
1°) On peut lier un DataGridView directement en lecture et écriture à un DataTable :
// création d'un DataTable :
DataTable table = new DataTable("Personnes"); ("Numéro", typeof(string)); table.Columns[0].Unique = true; ("Nom", typeof(string)); ("Prénom", typeof(string)); ("age", typeof(byte));
("Revenus", typeof(double));
/* Data-bind du dataGridView1:
* après cette instruction le dataGridView1 est lié à la table : * toute ligne entrée manuellement dans le dataGridView1 est * automatiquement ajoutée dans la table.
* */
dataGridView1.DataSource = table;//on relie à l'objet table
//affichage de la table (vide pous l'instant)
/* Réciproquement, toute donnée ajoutée à la table est immédiatement * visualisée dans le dataGridView1.
* */
(888, "fffff", "xxxxx", 88, 8888); (999, "ggg", "yyyy", 99, 9999);
//affichage de la table (avec les deux lignes qui viennent d'être ajoutées)
2°) On peut construire les lignes et les colonnes d'un DataGridView par programme :
DataGridView dataGridView1;………
// chargement par programme du dataGridView :
dataGridView1.ColumnCount = 5; dataGridView1.Columns[0].Name = "un"; dataGridView1.Columns[1].Name = "deux"; dataGridView1.Columns[2].Name = "trois"; dataGridView1.Columns[3].Name = "quatre"; dataGridView1.Columns[4].Name = "cinq"; (123, "aaa", "bbb", 25, 1235.58); ("xxxx", true, -23.6, "rrrr", 45);
(4);
/* Remarque : le type n'est pas fixé tout est converti en string. */
//affichage des lignes et des colonnes entrées par programme
3°) DataGridView lié à un DataSet :
Le principe d'utilisation des outils déconnectés est simple avec , il est fondé sur une architecture MVC (modèle-vue-contrôleur) dans laquelle un DataSet est connecté à une BD et gère le modèle des données en mémoire centrale, un DataGridView est alors relié à une table du DataSet, il est ainsi chargé de gérer la vue et les interactions événementielles avec l'utilisateur.
D'une manière générale, un xxxDataAdapter est utilisé lors d'un échange avec un DataSet ou un DataTable (d'un DataSet ou non), un objet xxxDataAdapter est chargé d'assurer la liaison entre la BD physique et le DataSet ou le DataTable :
• Il contient des propriétés de commande SQL :
o public xxxCommand SelectCommand{get; set;}, o public xxxCommand InsertCommand {get; set;}, o public xxxCommand UpdateCommand {get; set;},
o public xxxCommand DeleteCommand {get; set;}, permettant de lancer ces commandes à partir du DataTable sur la BD.
• Il contient réciproquement des méthodes permettant de charger un DaTable ou un DataSet contenant un ou plusieurs DataTable (public int Fill( DataTable datTable), public int Fill( DataSet datSet),….)
Un xxxDataAdapter ouvre et ferme la connexion automatiquement (Open et Close).
| DataGridViewlié à un DataSet avec fichier XML | |
Nous montrons ici comment sauvegarder tout le contenu d'un DataGridView nommé dataGridView1 dans un fichier nommé "" en utilisant un DataSet nommé dsTable.
a) Utilisons le DataTable nommé "Personnes" que nous avons créé précédemment :
DataTable table = new DataTable("Personnes"); ("Numéro", typeof(string)); table.Columns[0].Unique = true; ("Nom", typeof(string)); ("Prénom", typeof(string)); ("age", typeof(byte)); ("Revenus", typeof(double)); (888, "fffff", "xxxxx", 88, 8888); (999, "ggg", "yyyy", 99, 9999);
b) Ajoutons cette table "Personnes" au DataSet dsTable :
DataSet dsTable = new DataSet();// création d'un DataSet
(table); //ajout de la table "Personnes" au DataSet
c) Le dataGridView1 est lié au DataSet nommé dsTable par sa propriété DataSource et plus précisément à la première table du DataSet par sa propriété DataMember :
dataGridView1.DataSource = dsTable; dataGridView1.DataMember = dsTable.Tables[0].TableName;
dsTable.WriteXmlSchema(""); dsTable.WriteXml("");
Code d'un click sur un bouton permettant de sauvegarder au format XML :
DataSet dsTable = new DataSet();// création d'un DataSet
Button buttonSaveDataSet : // bouton de sauvegarde au format XML
private void buttonSaveDataSet_Click(object sender, EventArgs e) {
// sauvegarde au format XML par DataSet du contenu du dataGridView : if (dsTable.Tables.Count != 0)// si le DataSet contient quelque chose !
{
dataGridView1.DataSource = dsTable;
dataGridView1.DataMember = dsTable.Tables[0].TableName; dsTable.WriteXmlSchema("");
dsTable.WriteXml(""); = "nbr lignes = " +
dsTable.Tables[0].Rows.Count.ToString();
}
}
Le dataGridView1 a été chargé par le code a) , b) et c) :
engendre deux fichiers au format XML :
Voici le contenu du fichier "":
On remarquera que la contrainte d'unicité pour les données de la colonne 0 est présente dans le schéma.
Voici le contenu du fichier"" :
Nous montrons maintenant comment récupérer les données et leur structure dans un DataGridView nommé dataGridView1 à partir des fichiers précédents "" et
"".
Le DataSet posède des méthodes permettant de lire les données brutes au format XML (méthode
ReadXml), et de lire sa structure générale stockée au format XML (méthode ReadXmlSchema). On peut dès lors, afficher dans le dataGridView1 le contenu des fichiers nommés "" et
" ", en utilisant les méthodes ReadXml et ReadXmlSchema puis en liant le dataGridView1 à la table du DataSet :
Code d'un click sur un bouton permettant de charger les fichiers XML :
Button buttonLoadDataSet : // bouton de chargement du fichier XML
private void buttonLoadDataSet_Click(object sender, EventArgs e) {
// récupération au format XML par DataSet du contenu du dataGridView : RAZ_DataGridView();// on efface
dataGridView1.DataMember = dsTable.Tables[0].TableName; }
| DataGridViewlié à un DataSet connecté à une BD | |
Le principe reste le même que celui qui a été décrit précédemment du moins en ce qui concerne la liaison du DataGridView avec le DataSet.
La connexion aux données ne s'effectue pas directement comme avec des données au format
XML, mais à travers un fournisseur de connexion à la BD adapté au type de la BD
(SqlDataAdapter pour une BD SQL serveur, OracleDataAdapter pour une BD Oracle, …)
Exemple de code pour affichage de la table"HumanResources.Departement"dans la BD""de Microsoft :
Nous avons précédemment lancé la lecture des 16 enregistrements de cett table en mode connecté, ici nous montrons comment afficher cette même table en mode déconnecté.
private void loadDataGridViewFromDataSet() {/*----- Liaison par DataSet -----*/
//--pour SQL server Ed.Developper 2005 : string urlSqlServer = @"Data Source=(local);Initial Catalog=AdventureWorks;" +"Integrated Security=SSPI;";
// objet de connexion :
SqlConnection connexion = new SqlConnection(urlSqlServer);
try
{
// unSqDataAdapter : objet de communication et d'échange de données entre DataSet et la BD SqlDataAdapter unSqlAdapter = new SqlDataAdapter("Select * From HumanResources.Department ", connexion); //Remplissage du DataSet avec la table Person.Address : (unDataSet); |
// Liaison du dataGridView1 à la table du DataSet : dataGridView1.DataSource = unDataSet.Tables[0];
}
catch (SqlException ex)
{
(ex.Message,"Erreur SQL", , MessageBoxIcon.Exclamation);
}
}
Résultat obtenu dans le DataGridView :
mode déconnecté: modifications de données à partir d'unDataGridViewlié à unDataSetlui-même connecté à une BD
Soit une BD BD SQL serveur nommée "Boutique" possédant une table "Articles" :
Un DataSet charge la table "Articles" de la BD SQL serveur nommée "" à travers un objet de communication SqlDataAdapter :
On se propose d'écrire une IHM simple contenant :
• un DataGridView nommé dataGridView1 affichant et autorisant les modifications de celulles,
• un bouton buttonLoad, de chargement de la table "Articles" à partir de la BD ( à travers un DataSet lui-même connectée à la BD par un SqlDataAdapter),
• un bouton buttonValider, de sauvegarde et mise à jour dans la BD des modifications apportées dans le dataGridView1.
public partial class FormDatSetBD : Form
{
public FormDatSetBD()
{
InitializeComponent();
}
string urlSqlServer = @"Data Source=(local);Initial Catalog=Boutique;" + "Integrated Security=SSPI;";
DataSet undatSet = new DataSet(); // objet de connexion SqlConnection :
SqlConnection connexion;
//objet de communication et d'échange de données SqlDataAdapter dataAdapteur = null;
Chargement des données de la table"Articles"dans ledataGridView1:
private void buttonLoad_Click(object sender, EventArgs e)
{
// initialisation de l'objet de connexion SqlConnection :
connexion = new SqlConnection(urlSqlServer);
//objet de communication et d'échange branché sur la table Articles dataAdapteur = new SqlDataAdapter("Select * From Articles", connexion); // conseillé par Microsoft pour accéder à l'information de clef primaire dataAdapteur.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//Céation et remplissage d'une table 'LesArticles' dans le DataSet : (undatSet, "LesArticles");
//liaison de données de la table "LesArticles" avec le dataGridView1 :
dataGridView1.DataSource = undatSet;
buttonLoad.Enabled = false;
buttonValider.Enabled = true;
}
Modification par envoi automatique d'une commande Transact-SQL :
La dernière ligne est entrée manuellement dans le dataGridView1 :
//ce bouton valide les données dans la BD
private void buttonValider_Click(object sender, EventArgs e)
{
// construction et lancement de la commande Transact-SQL insert, update ou delete: SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapteur); dataAdapteur.Update(undatSet, undatSet.Tables[0].TableName);
/* validation effective des changements apportés (mettre après la commande Transact-SQL) * undatSet.AcceptChanges(); est automatiquement lancé par la méthode Update du dataAdapteur.
* */
buttonLoad.Enabled = true;
}
La méthode Update lance une commande SQL de type INSERT, après cette validation, la table "Articles" de la BD contient physiquement la nouvelle ligne :
Supprimons l'article craie dans le DataGridView :
Ensuite nous cliquons sur le bouton Valider, la méthode Update lance alors une commande SQL du type DELETE :
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapteur); dataAdapteur.Update(undatSet, undatSet.Tables[0].TableName);
Voici le nouvel état de la BD après cette validation :
La ligne 110, craie, … a bien été effacée de la BD.
En conclusion :
Toute modification (insertion d'une ou plusieurs lignes, effacement d'une ou plusieurs lignes, changement des données incluses dans ou plusieurs cellules) est notifiée par le DataGridView au DataSet qui l'envoi au SQLDataAdapter qui lui-même génère les commandes Transact-SQL adéquates vers la BD afin que les mises à jours soient effectives.
Exercice : Gestion simplifiée d'un petit magasin
Soit une BD SQL serveur 2005 de gestion de stock nommée "" et comportant 2 tables liées entre elles :
La table « Magasin » :
La table « PrixArticle » :
Programmation en « mode déconnecté » :
Implémentons en C#, les actions suivantes sur la BD "" :
1°) Le contenu de n’importe laquelle des cellules peut être modifié par l’utilisateur.
2°) Dès que le contenu d’une cellule est modifié la BD est mise-à-jour immédiatement.
3°) Dès que l’on clique dans une cellule, le contenu de la table est rafraîchi à partir de celui de la BD.
4°) L’application permet d’obtenir deux fichiers XML à partir de la BD :
4.1°) La totalité de la BD au format XML. 4.2°) Le schéma de la BD au format XSD.
On utilise un DataSet.
L'IHM de la gestion de stock affiche les deux tables et du XML obtenu à partir de la BD :
Une solution
using System; using System.Collections.Generic; using System.ComponentModel;
using ; using System.Drawing; using ; using System.Windows.Forms; using .SqlClient;
using .OleDb;
namespace ReadWriteMagasin
{
public partial class FStock : Form
{
private .DataSet dataSetStoreCCI;
private string urlSqlServer = null; private SqlConnection connexion = null; private SqlDataAdapter magasinSqlAdapter = null; private SqlDataAdapter prixSqlAdapter = null;
public FStock()
{
InitializeComponent();
dataSetStoreCCI = new DataSet();//new CCI_StoreDataSet(); dataSetStoreCCI.DataSetName = "NewDataSetCCI";
}
private DataTable loadTable(string nomTable, out SqlDataAdapter dataAdapteur)
{
DataTable table = new DataTable();
//objet de communication et d'échange de données entre DataTable et la source de données (ici la BD) dataAdapteur = new SqlDataAdapter("Select * From " + nomTable, connexion); dataAdapteur.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//Remplissage de la table 'nomTable' : (table);
}
private void displayTableMagasin()
{
//Remplissage du DataTable avec la table Magasin :
DataTable table = loadTable("magasin", out magasinSqlAdapter); (table);
//visualisation de la table "magasin" dans le dataGridViewMagasin : dataGridViewMagasin.DataSource = table; dataSetStoreCCI.Merge(table);
dataSetStoreCCI.WriteXml("");
}
private void displayTablePrixArticle()
{
//Remplissage du DataTable avec la table PrixArticle :
DataTable table = loadTable("PrixArticle", out prixSqlAdapter); (table);
//visualisation de la table "PrixArticle" dans le dataGridViewPrix : dataGridViewPrix.DataSource = table; dataSetStoreCCI.Merge(table); dataSetStoreCCI.WriteXml("");
}
private void buttonCharger_Click(object sender, EventArgs e)
{ try
{
//--plusieurs chaînes de chemin de connexion possibles équivalents pour SQL server 2005 :
//-- (le serveur de l'auteur se nomme DUAL-CORE et se trouve à l'adresse réseau 10.5.8.1) :
//string urlSqlServer = @"Data Source=(local);Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=localhost;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=127.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=10.5.8.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;"; //string urlSqlServer = @"Data Source=DUAL-CORE;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;"; urlSqlServer = @"Data Source=127.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
// objet de connexion SqlConnection : connexion = new SqlConnection(urlSqlServer);
displayTableMagasin();
displayTablePrixArticle();
= dataSetStoreCCI.GetXmlSchema();
//par sécurité : validation effective des changements apportés dataSetStoreCCI.AcceptChanges();
}
catch (SqlException ex)
{
(ex.Message, "Erreur SQL", , MessageBoxIcon.Exclamation);
} finally
{
if (connexion != null) connexion.Close();
}
}
private void buttonSaveXML_Click(object sender, EventArgs e)
{
string FileName = ""; dataSetStoreCCI.WriteXml(FileName, XmlWriteMode.IgnoreSchema); dataSetStoreCCI.WriteXmlSchema(FileName.Replace(".xml", ".xsl")); = dataSetStoreCCI.GetXml();
}
private void buttonEffacer_Click(object sender, EventArgs e)
{
dataGridViewMagasin.DataSource = null;
dataGridViewPrix.DataSource = null;
toolStripButtonXML.Enabled = false;
}
/* ----- modifications de données dans la base ----- */
private void dataGridViewMagasin_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{ // modifications dans la table Magasin (sauf nouvelle ligne) if (e.RowIndex < dataSetStoreCCI.Tables[0].Rows.Count) //si pas nouvelle ligne
{
= dataSetStoreCCI.Tables[0].Columns[e.ColumnIndex].ColumnName; = Convert.ToString(dataGridViewMagasin.CurrentCell.Value);
//modification du dataSetStoreCCI :
dataSetStoreCCI.Tables[0].Rows[e.RowIndex][e.ColumnIndex]=
dataGridViewMagasin[e.ColumnIndex,e.RowIndex].Value;
// construction et lancement de la commande Transact-SQL:
SqlCommandBuilder builder = new SqlCommandBuilder(magasinSqlAdapter); magasinSqlAdapter.Update(dataSetStoreCCI, "magasin");
// visualiser la commande Transact-SQL:
Console.WriteLine(builder.GetUpdateCommand().CommandText);
//par sécurité : validation effective des changements apportés dataSetStoreCCI.AcceptChanges();
}
}
private void dataGridViewPrix_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
{
= dataSetStoreCCI.Tables[1].Columns[e.ColumnIndex].ColumnName; = Convert.ToString(dataGridViewPrix.CurrentCell.Value);
//modification du dataSetStoreCCI :
dataSetStoreCCI.Tables[1].Rows[e.RowIndex][e.ColumnIndex] =
dataGridViewPrix[e.ColumnIndex, e.RowIndex].Value;
// construction et lancement de la commande Transact-SQL:
SqlCommandBuilder builder = new SqlCommandBuilder(prixSqlAdapter); prixSqlAdapter.Update(dataSetStoreCCI, "PrixArticle");
// visualiser la commande Transact-SQL:
Console.WriteLine(builder.GetUpdateCommand().CommandText);
//par sécurité : validation effective des changements apportés dataSetStoreCCI.AcceptChanges();
}
}
private void dataGridViewMagasin_CellClick(object sender, DataGridViewCellEventArgs e)
{
// rafraîchissement des données présentes dans la base :
displayTableMagasin();
if (e.RowIndex < dataSetStoreCCI.Tables[0].Rows.Count) //si pas nouvelle ligne
if (e.RowIndex >= 0 & e.ColumnIndex >= 0) //car si ColumnIndex=-1, RowIndex=-1 on click alors dans les entêtes
dataGridViewMagasin.CurrentCell = [e.RowIndex].Cells[e.ColumnIndex];
}
private void dataGridViewPrix_CellClick(object sender, DataGridViewCellEventArgs e)
{
// rafraîchissement des données présentes dans la base : displayTablePrixArticle();
if (e.RowIndex < dataSetStoreCCI.Tables[1].Rows.Count) //si pas nouvelle ligne
if (e.RowIndex >= 0 & e.ColumnIndex >= 0) //car si ColumnIndex=-1, RowIndex=-1 on click alors dans les entêtes
dataGridViewPrix.CurrentCell = [e.RowIndex].Cells[e.ColumnIndex];
}
}
}
Lors de l'exécution du programme précédent
Tentons de changer la référence B004 en référence B008 déjà existante :
.Net lance une exception du type ConstraintException :
Tentons de créer une nouvelle référence B009 à la place de la référence B004 déjà existante :
.Net lance une exception du type SqlException :
UPDATE créé un conflit avec la clef étrangère de nom FK_PrixArticle_Magasin…
Si nous tentons de créer une nouvelle référence B009 à la place de la référence B004 déjà existante, mais cette fois-ci dans la tableMagasin:
.Net lance le même type SqlException :
UPDATE créé un conflit avec la clef étrangère de nom FK_PrixArticle_Magasin…
La colonne CodeArticle de la table Magasin est liée à la colonne Article de la table PrixArticle, c'est une clef ètrangère de la table Magasin qui réfère à la table PrixArticle, dans les deux derniers cas nous n'avons pas respecté la règle d'intégrité référentielle (toutes les valeurs d'une clef étrangèredoiventse retrouver comme valeur de la clef primaire de la relation référée) :
.Net renvoie les exceptions associées aux problèmes d'intégrités.
Amélioration de la gestion du petit magasin :
clef étrangère etdelete/updateen cascade
Soit les deux tables de l'exercice sur un petit magasin :
Microsoft SQL Server Management Studio permet de visualiser les deux clefs primaires de chacune des tables, et le fait que FK_PrixArticle_Magasin est une clef étrangère :
Il est possible avec SQL Server Management Studio et Visual Studio, de programmer visuellement la propagation de la mise à jour automatiquement de la clef étrangère aux relations aux quelles elle réfère (ici à la clef primaire de la table PrixArticle).
1°) Il faut soit sélectionner la clef étrangère FK_PrixArticle_Magasin et faire apparaître le menu pop-up associé par un click droit de souris et lancer la commande Modify :
Ces deux opérations conduisent au même résultat : faire apparaître une fenêtre d'édition de propriétés pour les relations actuelles. Nous pouvons alors dans cet éditeur de propriétés, modifier les spécifications des commandes
UPDATE et DELETE.
Pour chacune des commandes UPDATE et DELETE 4 choix sont possibles (No Action est le choix par défaut).
Nous choisissons de propager un DELETE dans la table Magasin à la table PrixArticle référée par la clef étrangère en utilisant le choix Cascade :
Dans le programme C#, la commande DELETE dans la table magasin :
sendCommandTransact_SQL("supprimer", "magasin");
produit un DELETE sur la table Magasin et produit en cascade un DELETE sur la même ligne de la table prixArticle.
Sécurisez le programme de petit magasin comme suit :
• Chaque modification d'un ou plusieurs champs de la table Magasin est immédiatement et automatiquement validée dans la BD, sauf dans le cas de la création d'une nouvelle ligne où la validation des modifs sera proposée par l'activation d'un bouton.
• En empêchant toute modification du code Article dans la table PrixArticle à partir de l'IHM.
• En autorisant les modifications d'un ou plusieurs champs de la table Magasin, lorsqu'il s'agit du champ CodeArticle autoriser la propagation de la modification à la table PrixArticle.
• En permettant de supprimer avec confirmation, par un click droit de souris, une ligne entière de la table Magasin la propagation de la modification à la table PrixArticle.
• Lors de la création d'une nouvelle entrée dans la table Magasin, prévoir de ne pas mettre à jour le prix de l'article (qui est automatiquement mis à 0.00 € ) tant que la ligne entière n'a pas été sauvegardée dans la BD.
Une solution de l'mplantation
using System; using System.Collections.Generic; using System.ComponentModel;
using .SqlClient;
namespace ModifMagasin
{ public partial class FModifStock : Form
{
private .DataSet dataSetStoreCCI;
private string urlSqlServer = null; private SqlConnection connexion = null; private SqlDataAdapter magasinSqlAdapter = null; private SqlDataAdapter prixSqlAdapter = null; //--pour SQL server Ed.Developper 2005 :
//string urlSqlServer = @"Data Source=(local);Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=localhost;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=127.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=10.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
//string urlSqlServer = @"Data Source=DUAL-CORE;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;";
public FModifStock()
{
InitializeComponent(); dataSetStoreCCI = new DataSet();
dataSetStoreCCI.DataSetName = "NewDataSetCCI";
}
private DataTable loadTable(string nomTable, out SqlDataAdapter dataAdapteur)
{
DataTable table = new DataTable();
//objet de communication et d'échange de données entre DataTable et la source de données (ici la BD) dataAdapteur = new SqlDataAdapter("Select * From " + nomTable, connexion); dataAdapteur.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//Remplissage de la table 'nomTable' : (table);
table.TableName = nomTable; return table;
}
private void displayTableMagasin()
{ try
{
urlSqlServer = @"Data Source=127.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;"; // objet de connexion SqlConnection :
connexion = new SqlConnection(urlSqlServer);
//Remplissage du DataTable avec la table Magasin :
//visualisation de la table "magasin" dans le dataGridViewMagasin :
dataGridViewMagasin.DataSource = table; dataSetStoreCCI.Merge(table); dataSetStoreCCI.WriteXml("");
}
catch (SqlException ex)
{
(ex.Message, "Erreur SQL", , MessageBoxIcon.Exclamation);
} finally
{
if (connexion != null) connexion.Close();
}
}
private void displayTablePrixArticle()
{ try
{
urlSqlServer = @"Data Source=127.0.0.1;Initial Catalog=CCI_Store;" + "Integrated Security=SSPI;"; // objet de connexion SqlConnection : connexion = new SqlConnection(urlSqlServer); //Remplissage du DataTable avec la table PrixArticle :
DataTable table = loadTable("PrixArticle", out prixSqlAdapter); (table);
//visualisation de la table "PrixArticle" dans le dataGridViewPrix : dataGridViewPrix.DataSource = table; dataGridViewPrix.Columns[0].ReadOnly = true; dataSetStoreCCI.Merge(table); dataSetStoreCCI.WriteXml("");
}
catch (SqlException ex)
{
(ex.Message, "Erreur SQL", , MessageBoxIcon.Exclamation);
} finally
{
if (connexion != null) connexion.Close();
}
}
private void buttonCharger_Click(object sender, EventArgs e)
{
displayTableMagasin();
displayTablePrixArticle();
// visualisation du schéma XML du dataSetStoreCCI toolStripButtonXML.Enabled = true;
= dataSetStoreCCI.GetXmlSchema();
}
private void buttonSaveXML_Click(object sender, EventArgs e)
{
string FileName = "";
dataSetStoreCCI.WriteXml(FileName, XmlWriteMode.IgnoreSchema); dataSetStoreCCI.WriteXmlSchema(FileName.Replace(".xml", ".xsl")); = dataSetStoreCCI.GetXml();
}
private void buttonEffacer_Click(object sender, EventArgs e)
{
toolStripButtonXML.Enabled = false;
}
private void sendCommandTransact_SQL(string idCommand, string nomTable)
{
SqlDataAdapter SqlDataAdapteur;
if (nomTable == "magasin")
SqlDataAdapteur = magasinSqlAdapter; else
SqlDataAdapteur = prixSqlAdapter;
// construction et lancement de la commande Transact-SQL insert, update ou delete:
SqlCommandBuilder builder = new SqlCommandBuilder(SqlDataAdapteur); SqlDataAdapteur.Update(dataSetStoreCCI, nomTable);
// visualiser la commande Transact-SQL: switch (idCommand)
{
case "inserer": Console.WriteLine(builder.GetInsertCommand().CommandText); break; case "modifier": Console.WriteLine(builder.GetUpdateCommand().CommandText); break; case "supprimer": Console.WriteLine(builder.GetDeleteCommand().CommandText); break;
}
}
/* ----- modifications de données dans la base ----- */
/*
* Lorsque l'on entre une nouvelle ligne à la fin (nouvel article) dans * la table "magasin", il y a création d'une nouvelle ligne avec la même * clef article et un prix à 0,00€ dans la table "prixarticle".
* */
private void dataGridViewMagasin_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
// modifications dans la table Magasin = dataSetStoreCCI.Tables[0].Columns[e.ColumnIndex].ColumnName; = Convert.ToString(dataGridViewMagasin.CurrentCell.Value); if (e.RowIndex <= dataSetStoreCCI.Tables[0].Rows.Count - 1)// mode modification de données d'une ligne existante
{
//modification du dataSetStoreCCI : dataSetStoreCCI.Tables[0].Rows[e.RowIndex][e.ColumnIndex] = dataGridViewMagasin[e.ColumnIndex, e.RowIndex].Value;
// construction et lancement de la commande Transact-SQL:
sendCommandTransact_SQL("modifier", "magasin");
// <=>
//magasinSqlAdapter.Update(dataSetStoreCCI, "magasin");
// visualiser la commande Transact-SQL:
//Console.WriteLine(builder.GetUpdateCommand().CommandText);
//par sécurité : validation effective des changements apportés(mettre après la commande Transact-SQL) dataSetStoreCCI.AcceptChanges();
if (e.ColumnIndex == 0)//on vient de changer un code Article, alors on réaffiche les données
{
dataSetStoreCCI = new DataSet(); dataSetStoreCCI.DataSetName = "NewDataSetCCI"; displayTableMagasin();
displayTablePrixArticle();
}
}// sinon création d'une nouvelle ligne en fin de tableau else {
//modification de la table magasin du dataSetStoreCCI :
if (dataSetStoreCCI.Tables[0].Rows.Count < dataGridViewMagasin.RowCount)
{
// on crée la ligne dans la table magasin
DataRow Line = dataSetStoreCCI.Tables[0].NewRow();
Line[e.ColumnIndex] = dataGridViewMagasin[e.ColumnIndex, e.RowIndex].Value; dataSetStoreCCI.Tables[0](Line);
// construction et lancement de la commande Transact-SQL:
sendCommandTransact_SQL("modifier", "magasin");
// on crée la ligne correspondante dans la table PrixArticle
if (dataGridViewMagasin[e.ColumnIndex, e.RowIndex].Value != DBNull.Value)
{
DataRow newLine = dataSetStoreCCI.Tables[1].NewRow(); newLine[0] = Line[0];
newLine[1] = 0; dataSetStoreCCI.Tables[1](newLine);
// construction et lancement de la commande Transact-SQL: sendCommandTransact_SQL("modifier", "PrixArticle"); displayTablePrixArticle();
}
}
buttonSave.Enabled = true; dataGridViewPrix.ReadOnly = true;// aucune opération acceptée tant que la validation n'a pas eu lieu
}
}
private void dataGridViewPrix_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
| e.RowIndex == dataSetStoreCCI.Tables[1].Rows.Count - 1)// mode modification de données d'une ligne existante
{
// modification du dataSetStoreCCI :
dataSetStoreCCI.Tables[1].Rows[e.RowIndex][e.ColumnIndex] = dataGridViewPrix[e.ColumnIndex, e.RowIndex].Value;
// construction et lancement de la commande Transact-SQL: sendCommandTransact_SQL("modifier", "PrixArticle");
// <=>
//SqlCommandBuilder builder = new SqlCommandBuilder(prixSqlAdapter);
//prixSqlAdapter.Update(dataSetStoreCCI, "PrixArticle");
// visualiser la commande Transact-SQL:
//Console.WriteLine(builder.GetUpdateCommand().CommandText);
// validation effective des changements apportés (mettre après la commande Transact-SQL) dataSetStoreCCI.AcceptChanges(); displayTablePrixArticle();
}// sinon création d'une nouvelle ligne interdite else {
//rien pour l'instant .
}
}
private void dataGridViewMagasin_MouseDown(object sender, MouseEventArgs e) {// on supprime une ligne sélectionnée par click droit de souris sur cette ligne :
int nbrRow = dataGridViewMagasin.SelectedRows.Count; int numRow = dataGridViewMagasin.CurrentCell.RowIndex; if (nbrRow != 0 & e.Button == MouseButtons.Right)
{
if ((this, "Confirmez-vous la suppression de cet article de la Base ?", "Suppression de la ligne CodeArticle : "
+ [numRow].Cells[0].Value + "demandée.", MessageBoxButtons.YesNo) == )
{
//dataSetStoreCCI.Tables[0].Rows.RemoveAt(numRow); // ne produit pas de DELETE du magasinSqlAdapter dataSetStoreCCI.Tables[0].Rows[numRow].Delete();
dataGridViewMagasin.DataSource = dataSetStoreCCI.Tables[0]; //dataGridViewMagasin.Update();
/* DELETE-UPDATE en cascade dans la définition de la clef étrangère "FK_PrixArticle_Magasin"
* dans la table PrixArticle.(cf )
* */
sendCommandTransact_SQL("supprimer", "magasin");
// <=>
//SqlCommandBuilder builder = new SqlCommandBuilder(magasinSqlAdapter);
//magasinSqlAdapter.Update(dataSetStoreCCI, "magasin");
// visualiser la commande Transact-SQL:
//Console.WriteLine(builder.GetUpdateCommand().CommandText);
// validation effective des changements apportés (mettre après la commande Transact-SQL) dataSetStoreCCI.AcceptChanges(); dataSetStoreCCI = new DataSet(); dataSetStoreCCI.DataSetName = "NewDataSetCCI";
displayTableMagasin();
displayTablePrixArticle();
}
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
// construction et lancement de la commande Transact-SQL insert, update ou delete: SqlCommandBuilder builder = new SqlCommandBuilder(magasinSqlAdapter); magasinSqlAdapter.Update(dataSetStoreCCI, "magasin");
builder = new SqlCommandBuilder(prixSqlAdapter); prixSqlAdapter.Update(dataSetStoreCCI, "PrixArticle");
// validation effective des changements apportés (mettre après la commande Transact-SQL) dataSetStoreCCI.AcceptChanges(); displayTableMagasin(); displayTablePrixArticle(); buttonSave.Enabled = false;
dataGridViewPrix.ReadOnly = false;
}
private void FStock_FormClosing(object sender, FormClosingEventArgs e)
{
//buttonSave_Click(sender, new EventArgs());
}
}
}