Cours VB.NET

Image non disponible


précédentsommairesuivant

XVII-A. Notions sur les bases de données

Image non disponible

Comment lire et écrire des informations complexes et structurées?

XVII-A-1. Généralités

Pour travailler avec du texte, des octets, des données très simple (sans nécessité d'index, de classement..), on utilise les fichiers séquentiels, aléatoires, binaires .

Mais dès que les informations sont plus structurées, il faut utiliser les bases de données (Data Base en anglais).

Une base de données peut être:

  • locale: utilisable sur un ordinateur par un utilisateur.
  • répartie, c'est-à-dire que la base est stockée sur des machines distantes et accessibles par réseau.

Plusieurs utilisateurs peuvent accédés à la base simultanément.

Exemple de type de base de données:

Dbase Format très utilisé, qui date maintenant un peu, les fichiers contenant ses bases ont l'extension .dbf

Paradox

FileMaker

FoxPro

Interbase

Access Format très répandu, les fichiers contenant ses bases ont l'extension .mdb

SQLServeur les fichiers contenant ses bases ont l'extension .dbo

SyBase

MySql

Oracle..

Pour pouvoir contrôler les données, l'accès à ces données et les utilisateurs utilisant une base de données, un système de gestion est nécessaire. La gestion de la base de données se fait grâce à un système appelé SGBD (système de gestion de bases de données), si la base de données est relationnelle (Existence de relation entre les tables) on parle de SGBDR (système de gestion de bases de données relationnelles)

Un SGBD est un logiciel qui joue le rôle d'interface entre les utilisateurs et la base de données.

Un SGBD permet de décrire, manipuler et interroger les données d'une 'Base de Données'.

XVII-A-2. Tables

Dans une base de données, il y a des tables:

Une table sert à stocker physiquement des données sous forme d'un tableau comportant des lignes (rows) et des colonnes (columns).

XVII-A-3. Exemple

Une base de données Access nommée Cabinet.mdb contient les patients d'un cabinet, leurs consultations, les ordonnances, les médicaments..

Dans cette base il y a plusieurs tables: une table patient, une table consultation...

Examinons la table patient:

Sur chaque ligne (row), il y a un patient,.

Chaque colonne (column) représente un type de données (première colonne= civilité, seconde=nom, troisième=prénom, quatrième= numéro interne propre à chaque patient. )

L'ancienne terminologie parlait d'enregistrements (lignes) et de champs (colonnes)

Image non disponible

Ici la seconde ligne (le 2eme enregistrement, le second row) contient la civilité, le nom, le prénom, le numéro du patient Dupont Josette.

Chaque colonne à un type bien définie: dans notre cas la première colonne contient du texte, ainsi que la seconde, la troisième; la quatrième colonne contient un numérique long par exemple.

Examinons la table consultation:

Sur chaque ligne, il y a une consultation,.

Chaque colonne représente un type de données (première colonne= numéro correspondant au patient, seconde=date, troisième=texte de la consultation, quatrième= Courrier. )

Image non disponible

Il n'est pas question pour chaque consultation d'enregistrer de nouveau le nom et le prénom du patient, cela enregistrerait 2 fois la même information puisque le nom et le prénom du patient sont déjà dans la table 'patient'. On va donc, pour éviter les redondances, utiliser un numéro interne: chaque patient a un numéro unique (4éme champ de la table 'nom'); il suffit de noter dans chaque consultation le numéro du patient.

Ensuite, si je consulte le patient Durand Luc, sachant que son numéro interne est '1', il suffit de rechercher dans la table consultation les consultations dont le premier champ est 1: Durand Luc à 2 consultations.

Le nom de la colonne est souvent nommé en utilisant le terme Id (pas ici) , 'IdPatient' par exemple, synonyme de 'numéro patient', cela permet de repérer les champs 'numéro interne'.

XVII-A-4. Type de colonne

Il existe des types de colonne (de champs) alphanumériques

  • de longueur fixe (pour le champ 'nom' je prévois 30 caractères par exemple).
  • de longueur variable (champ mémo dans la base Dbase par exemple)

Il existe aussi

  • des champs numériques,
  • des champs dates
  • et dans certains base de données des champs booléens, image...

XVII-A-5. Clé primaire

Quand il est nécessaire de différencier chaque enregistrement de manière unique, il faut définir un champ comme clé primaire.

Ce champ doit être unique pour chaque enregistrement (il ne doit pas y avoir de doublons: 2 enregistrements ne peuvent pas avoir la même clé primaire), et la valeur de la clé primaire ne peut pas être égale à null.

Dans notre exemple de la table patient, on ne peut pas utiliser le champ 'nom' comme clé primaire car plusieurs patients peuvent avoir le même nom, il est judicieux de choisir le champ 'numéro interne' comme clé primaire car chaque patient (donc chaque enregistrement) à un numéro interne unique.

Quand on enregistre une nouvelle fiche patient, il faut donc donner un nouveau 'numéro interne' qui n'a jamais été utilisé, en pratique:
Il existe des champs numériques dont la valeur s'incrémente automatiquement d'une unité: Quand on crée un nouvel enregistrement, le champ 'numéro interne' prend automatiquement la plus grande valeur déjà présente dans la base+1.

XVII-A-6. Index

Un index permet d'optimiser les recherches dans une table, de les rendre beaucoup plus rapide.

Expliquons:

Si j'ai une table contenant les noms des médecins utilisateurs et que je veux chercher un nom, comme il y a au maximum 5 à 6 médecins dans un cabinet, pour rechercher un nom, il suffit de lire successivement chaque enregistrement et de voir si c'est celui recherché. C'est suffisamment rapide.

Par contre si je recherche dans la table patient un patient, comme il y a 4000 à 8000 enregistrements, je ne peux pas les lire un à un , c'est trop long, aussi je crée un index: c'est comme l'index d'un livre, le nom me donne directement l'endroit ou se trouve l'enregistrement correspondant.

On peut combiner plusieurs champs pour en faire la base d'un index.

Pour ma table 'patient', je peux créer un index nommé IndexPatient qui sera indexé sur Nom +Prenom.

Il peut y a voir plusieurs index sur une même table.

Image non disponible

Les index accélèrent les recherches mais s'il y en a trop, cela alourdit le fonctionnement; on ne peut pas tout indexer!!

XVII-A-7. Relations entre les tables : différents types de relations

On a déjà vu que 2 tables peuvent être liées et avoir un champ commun présent dans les 2 tables.

Sur ce champ commun, il peut exister plusieurs types de relation:

Relation 1 à N

Relation 1 à 1

Relation N à M

Voyons cela en détail:

XVII-A-7-a. 1 à N (relation un à plusieurs)

Dans notre exemple la table 'patient' et la table 'consultation' ont chacune un champ numéro interne. Ce qui permet de lier à l'enregistrement du patient de numéro interne X toutes les consultations pour ce patient (elles ont dans leurs champs 'numéro interne' la valeur X.

Comme pour UN patient il peut y avoir N consultations, on parle de relation 1 à N.

Un enregistrement unique est lié à plusieurs enregistrements de l'autre table par un champ présent dans les 2 tables.

On remarque que le champ 'numéro interne' du coté patient est une clé primaire, pas du coté consultation.

Table 'patients'

Image non disponible

Le patient Durand Luc a 2 consultations : le 02/12/2003 et le 05/04/2004 (Le numéro interne de ce patient est 1, mais l'utilisateur final n'a pas à le savoir ni à le gérer: la relation utilisant le numéro interne est transparente pour l'utilisateur final)

Il existe aussi les relations:

XVII-A-7-b. 1 à 1

Un enregistrement unique est lié à un autre enregistrement unique par un champ présent dans les 2 tables.

On peut imaginer dans notre exemple, créer une table Antécédents contenant aussi un champ numéro interne; pour chaque enregistrement de la table patient, il y a un enregistrement unique dans la table Antécédents, de même numéro interne contenant les antécédents du patient.

Image non disponible

Enfin existe les relations:

XVII-A-7-c. N à M

Relation plusieurs à plusieurs. Plusieurs enregistrements de la première table peuvent être liés à plusieurs de la seconde table et vice versa.

Exemple:

J'ai une table 'ordonnances' qui peut contenir plusieurs médicaments, et une table 'médicaments' dont les médicaments peuvent être utilisé dans plusieurs ordonnances différentes.

Il faut dans ce cas avoir la table 'ordonnances' avec une clé primaire sur un numéro d'ordonnance (numéro d'ordonnance unique s'incrémentant à chaque nouvelle ordonnance), une table 'médicaments' avec une clé primaire sur le numéro unique du médicament, et créer une troisième table gérant la relation ordonnance-médicament.

Image non disponible

Ici le patient de numéro interne 2 (Dupont Josette) a une ordonnance visible dans la table 'Ordonnances'(numéro interne: 2; numéro de l'ordonnance: 1); si on cherche dans la table 'Contenu ordonnance' (Index crée sur le numéro d'ordonnance) on retrouve 2 enregistrements (ayant un numéro d'ordonnance 1), on constate que l'ordonnance contient les médicaments 1 et 2 qui correspondent (table 'médicaments') à de l'amoxicilline et de l'oméprazone.

On remarque qu'une ordonnance peut avoir autant de médicaments que l'on veut.

XVII-A-7-d. Relation N à M avec N fixe et petit

Dernier cas non décrit dans les livres:

J'explique: si chaque ordonnance à au maximum 3 médicaments ( que la sécu serait contente si c'était vrai!!), il est possible de créer une table 'ordonnances' contenant 3 champs médicaments. Dans ce cas on se passe de la 3eme table.

Image non disponible

XVII-A-8. Contraintes

Un champ peut avoir certaines contraintes:

  • On peut interdire la valeur Null: Cela empêche d'enregistrer un champ vide. On peut aussi donner une valeur par défaut.
  • On peut empêcher les doublons.
  • On peut exiger l'intégrité référentielle: La valeur d'un champ doit exister dans le champ d'une autre table.(On ne peut pas enregistrer une consultation pour le patient de numéro interne 2000 s'il n'existe pas de fiche patient ayant le numéro 2000)
  • On peut exiger des règles de validation pour un champ: interdire les valeurs négatives par exemple.

XVII-A-9. Serveur de fichier, Client serveur

Si plusieurs utilisateurs sont connectés à une base Access à travers un réseau, chaque utilisateur a sur son poste un 'moteur' Access, qui récupère l'ensemble des données à utiliser et qui les traite en local.

On parle de serveur de fichier.

Le moteur d'accès est présent sur chaque poste.

Si plusieurs utilisateurs sont connectés à une base SQLServer: la base est sur le serveur avec le logiciel SQLServeur.

Un logiciel utilisateur situé sur un autre ordinateur(le client) envoie au serveur une requête.

Le logiciel SQLServer traite la requête sur le serveur et retourne au logiciel client uniquement le résultat de la requête.

On parle d'architecture Client-serveur.

Le moteur d'accès est présent uniquement sur le serveur.

Si on cherche un enregistrement parmi 60 000 enregistrements, en serveur de fichiers, les 60 000 enregistrements sont envoyées par le réseau vers le moteur Access de l'utilisateur ; le moteur les traite pour en sortir un.

En client serveur, le logiciel utilisateur envoie une requête au serveur, le logiciel serveur cherche sur le serveur dans la base l'enregistrement, il le trouve et envoie à travers le réseau vers le logiciel client uniquement un enregistrement.

XVII-A-10. Opérations sur les enregistrements

De manière générale, on peut:

Ouvrir une base de données (Open)

Ajouter un enregistrement (Add)

Effacer un enregistrement (Delete)

Modifier un enregistrement (Update)

Chercher un ou des enregistrements.

Fermer la base. (Close)

Avant:

Il y a bien longtemps, on choisissait un index, on cherchait un enregistrement (avec Seek), on le lisait, le modifiait, on avançait (MoveNext) ou on reculait (MovePrevious) d'un enregistrement dans la base, mais c'est de l'histoire ancienne!!

Chaque type de base de données avait ses propres commandes.

Ensuite , il y a eu les RecordSet, sorte de tableau avec un curseur pointant un élément.

Image non disponible

Avec ADO.NET :

Maintenant quelle que soit la base de données, on utilise un langage unique: le 'SQL' pour faire une requête sur la base de donnée: extraction de certains enregistrements ou de certains champs en fonction de critères), le résultat (un ensemble d'enregistrements ou de champs) se retrouvant dans un DataSet (sorte de tableau situé en local sur lequel on lit on modifie, on ajoute ou on enlève des lignes, les modifications étant répercutées sur la base de donnée du départ).

XVII-B. Généralités sur ADO.NET

Jusqu'en vb6 on utilisait DAO et ADO pour travailler sur les bases de données. En vb.Net on utilise ADO.NET.

Comment donc travailler sur les Base de données en VB.NET? Avec ADO.NET

XVII-B-1. Généralités

Pour avoir accès à partir de VB.NET aux bases de données il faut utiliser ADO.NET.

ADO veut dire Activex Database Objet .

C'est la couche d'accès aux bases de données, le SGBD (Système de Gestion de Base de Données) de VB.

ADO.NET est ".NET" donc managé et géré par le CLR.

Il est indépendant de la base de donnée: alors que initialement chaque type de gestionnaire de base de données avait ses instructions, sa manière de fonctionner, ADO.NET à un langage unique pour ouvrir, interroger, modifier une base de données quelle que soit la base de données.

Le langage de requête est le SQL.

En VB il y a 2 manières d'écrire un programme qui utilise une base de données:

- Ecrire du code pour créer des objets Ado.net, écrire du code pour ouvrir la base, créer la liaison entre la base et un DataSet avec des critères de sélection écrits en SQL.
- Utiliser l' 'Assistant de configuration de source de base de données' qui crée les objets et le code à votre place. On peut même lier une table à une list par exemple qui sera 'remplie' automatiquement par la table (On parle de Binding).

XVII-B-2. Les Managed Providers

Pour avoir accès aux données il faut charger les DRIVERS (ou providers).

Comme d'habitude, il faut:

  • Charger les références des drivers (les Dll)
  • Importer les espaces de nom.

Ainsi on a accès aux objets Ado.Net correspondant.

Voyons cela:

  • OLE DB Managed Provider est fourni dans 'System'; après avoir importé le NameSpace System.Data.OLEDB, on peut travailler sur des bases Access par exemple.
  • SQL Server Managed Provider est fourni dans 'System'; après avoir importé le NameSpace System.Data.SqlClient, on peut travailler sur des bases SqlServeur.
  • Un composant ODBC et un composant ORACLE sont disponible sur le site MSDN , il faudra charger la référence de la Dll puis le NameSpace.
  • Pour travailler sur une base MySQL lisez le très bon didacticiel MySQLDotNet(sur developpez.com bien sur);il utilise le Managed ProviderByteFX

Exemple, pour travailler sur une base Access, il faudra taper:

Imports System.Data.OLEDB

Avec Visual Basic Express 2010, vous pouvez accéder à trois types de bases de données : Microsoft SQL Server Compact Edition, Microsoft SQL Server Express ou Microsoft Access.

XVII-B-3. Les Objets ADO.NET

Il faut disposer d'un objet Connexion pour avoir accès à la base de données, on met dans la propriété ConnectionString les paramètres de la base de données (nom de la base de données, chemin, mot de passe..).

En fonction de la BD les paramètres sont différents. Un site nommé ConnetionStrings.com donne une mine de renseignements pour écrire les paramètres de connexion pour une BD.

Avec la méthode Open on ouvre la base.

on peut ensuite travailler de 2 manières:

A- On envoie une requête Sql 'SELECT' à la base, on récupère le résultat dans un objet.

  • Avec un objet DataReader on extrait les données en lecture seule: une requête SQL (sur un objet command) charge le DataReader. c'est rapide; on peut lire uniquement les données et aller à l'enregistrement suivant. Il travaille en mode connecté. Pour gérer un DataReader on a besoin d'un objet Command.
  • Avec un objet DataSet on manipule les données: une requête SQL (sur un objet command) charge le DataSet avec des enregistrements ou des champs, on travaille sur les lignes et colonnes du DataSet en local, en mode déconnecté(une fois que le DataSet est chargé, la connexion à la base de données est libérée). Pour alimenter un DataSet on a besoin d'un objet DataAdapter qui fait l'intermédiaire entre la BD et le DataSet.

B- On manipule directement la base. (sans retour de résultats)

  • Avec un objet Command on peut manipuler directement la BD (en SQL avec UPDATE, INSERT, DELETE CREATE DROP..), on utilise la propriété ExecuteNonQuery pour cela.

Avec la méthodes Close on ferme la base.

Résumons les différents objets nécessaires pour travailler sur une BD:

Image non disponible

Noter bien le sens des flèches:

  • le DataReader est en lecture seule, les données lues dans la BD sont accessibles dans le DataReader.
  • le DataSet peut lire et écrire des données dans la BD, il faut un DataAdapter en plus de la connexion.
  • l'objet Command peut modifier la BD.

Ce schéma souligne aussi les objets intermédiaires nécessaires:

  • un objet connexion dans tous les cas,
  • un objet Command pour le DataReader,
  • un objet DataAdapter plus un objet Command pour le DataSet .

L'objet Command permet d'envoyer des ordres en SQL à la BD et de la modifier, il permet aussi, quand on utilise un DataSet, d'envoyer une requête SELECT en SQL afin de remplir le DataSet avec le résultat de la requête.

Enfin certains contrôles comme les DataGrid, les ListBox par exemple peuvent afficher des données à partir d'un DataSet.

Pour mettre à jour la base après modification du DataSet ou de la Grid il faut un objet CommandBuilder.

Mode connecté ou déconnecté:

  • le DataReader fonctionne en mode connecté,la connexion entre la BD et le DataReader est ouverte tant que le DataReader fonctionne.
  • le DataSet peut travailler en mode déconnecté: on ouvre la connexion, on charge le DataSet, on ferme la connexion (il faut le faire, ce n'est pas automatique), on travaille sur le DataSet, on peut le rouvrir plus tard pour les mises à jour.

Remarque: En fonction du provider, le nom des objets change:

Avec le provider OleDb, après Imports System.Data.OleDb

on utilisera OleDbConnexion, OleDbAdapter...

Avec le provider SQL, après Imports System.Data.SqlClient

on utilisera SqlConnexion, SqlAdapter...

Un site nommé ConnetionStrings.com donne une mine de renseignements pour écrire les paramètres pour une BD.

XVII-B-4. Le DataReader

Le DataReader permet donc de lire très rapidement une table, enregistrement par enregistrement, du début à la fin.

Il n'y a pas possibilité d'écrire dans la base.

XVII-B-5. Le DataSet

Le DataSet a la structure d'une base de données mais en local; il contient:

Des DataTable qui contiennent des DataRow et des DataColumn.

Pour utiliser DataSet, DataTable, DataRow.. il faut importer l'espace de nom Data:

Imports System.Data

On peut créer un Dataset de toutes pièces, mais la plupart du temps, on charge le DataSet à partir d'une base de données.

Une requête SQL charge le DataSet, on travaille sur les lignes et colonnes du DataSet en local ( sur des enregistrements ou des champs), en mode déconnecté (une fois que le DataSet est chargé, la connexion à la base de données peut être libérée).

Image non disponible

La structure de données du DataSet reflétera automatiquement et exactement celle des données extraites. Si j'extrais 2 colonnes de données avec l'instruction Sql fournis à l'objet Command, le DataSet aura une table (DataTable) de 2 colonnes avec les données extraites.

Exemple :

Avec ADO.NET je lance une requête SQL demandant toutes les fiches de la table 'nom' dont le champ 'prénom' est 'Philippe', je récupère un DataSet local contenant toutes les fiches (Le DataColumn "Prénom" ne contient que des 'Philippe'). Je peux modifier en local le DataSet, (modifier une date de naissance par exemple) et mettre à jour automatiquement la base de données distante.

Pour être complet il existe aussi les DataView qui représentent une vue d'un DataTable. (Un DataView peut contenir un champ d'une table d'un DataSet, ou les enregistrements répondant à un critère).

XVII-C. Syntaxe SQL (Généralités)

Image non disponible

Comment adresser une requête vers une Base de données de ADO.NET? Avec SQL

Ici on va voir des généralités sur le langage SQL, on utilisera ce langage dans les requêtes ADO.net . En Ado.Net on verra qu'on exécute ces requêtes par la méthode 'ExecuteNoQuery' d'un objet Command. On verra plus bas qu'il est aussi possible d'interroger une base de données grâce à LINQ qui permet d'interroger en VB avec une syntaxe proche de SQL.

XVII-C-1. Généralités

SQL veut dire Structured Query Language : Langage d'interrogation structurée

SQL grâce au couplage avec un SGBD relationnelle permet un traitement interactif des requêtes.

SQL est le langage unique qui permet de décrire, manipuler, contrôler l'accès et interroger les bases de données relationnelles.

C'est un langage déclaratif, qui est régi par une norme (ANSI/ISO) qui assure la portabilité du langage sur différentes plates-formes aussi bien matérielles que logicielles. Une commande SQL écrite dans un environnement Windows sous ACCESS peut, souvent sans modification, être utilisée directement dans un environnement ORACLE sous Unix...

SQL est utilisé dans ADO.NET pour manipuler toutes les bases de données.

XVII-C-2. Les commandes SQL

Image non disponible

XVII-C-3. SELECT : Interrogation

Permet d'extraire ,de sélectionner des données.

Syntaxe simplifiée:

SELECT champ FROM table WHERE condition

Dans la table 'table' sélectionner les enregistrements vérifiant la condition 'condition' et en afficher les champs 'champs'

Exemple

Image non disponible

Exemple:

Soit la table patient:

Image non disponible

SELECT Nom FROM Patient

Cela signifie: dans la table Patient extraire les champs 'nom'

Image non disponible

SELECT Nom FROM Patient WHERE Prenom='Luc'

WHERE ajoute un critère de sélection.

Cela signifie: dans la table Patient extraire le champ Nom de tous les enregistrements dont le prénom est "Luc" .

Image non disponible

SELECT Nom, Prenom FROM Patient WHERE Sexe='F'

Cela signifie: dans la table Patient extraire le champ Nom et prénom de tous les enregistrements dont le champ sexe est "F"( F comme féminin) .

Dans l'exemple on obtient :

Image non disponible

SELECT * FROM Patient WHERE Datenais>=#01/01/1950#

Cela signifie: dans la table Patient extraire tous les champs de tous les enregistrements dont le champ date de naissance est supérieur ou égal à 01/01/1950 .

Dans l'exemple on obtient

Image non disponible

On remarque que

* signifie : extraire tous les champs.

Pour utiliser les dates , il faut les entourer de "#".

Les dates sont au format mm/jj/aaaa

SELECT * FROM Patient WHERE Datenais>= #01/01/1950# AND Datenais<= #01/01/1980#

Cela signifie: dans la table Patient extraire tous les champs de tous les enregistrements dont le champ date de naissance est supérieur ou égal à 01/01/1950 et inférieur ou égal à 01/01/1980 .

On remarque que on peut utiliser avec Where, les opérandes

AND OR NOT.

Il est bien sur possible de combiner des conditions sur des champs différents:

Sexe='M' AND Prenom='Luc"

SELECT * FROM Patient WHERE BETWEEN #01/01/1950# AND #01/01/1980#

Même signification que le précèdent mais en utilisant BETWEN AND qui est plus performant.

SELECT Nom FROM Patient WHERE Prenom IN ('Luc' , 'Pierre', 'Paul')

Cela signifie qu'il faut extraire les enregistrements dont le prénom est Luc, Pierre ou Paul .

SELECT Nom FROM Patient WHERE Prenom LIKE 'D%'

Cela signifie qu'il faut extraire les enregistrements dont le prénom commence par un 'D'.

LIKE recherche des chaînes de caractères avec l'aide de caractères génériques:

% représente une chaîne de caractères même vide.

_ représente un caractère.

On peut spécifier une série de caractères en les mettant entre ""

Exemple :

LIKE 'D%' commence par D

LIKE '%D%' contient D

LIKE '[DF]%' commence par D ou F

LIKE '___' contient 3 caractères

SELECT Nom FROM Patient WHERE SEXE IS NULL

Cela signifie qu'il faut extraire les enregistrements dont le sexe n'a pas été enregistré.

SELECT DISTINCT Nom FROM Patient WHERE SEXE IS NULL

DISTINCT permet d'éviter les doublons

Si dans les Noms extraits il y a 2 fois le même (2 membres d'une même famille) , il n'en est gardé qu'un.

XVII-C-4. Tri des enregistrements

ORDER BY sert à trier les enregistrements.

Il est placé à la fin.

DESC sert à trier par ordre décroissant.

SELECT Nom, Prenom , Sexe, DateNais FROM Patient WHERE Sexe='F' ORDER BY DateNais

Trie les enregistrements de sexe 'F' par date de naissance

SELECT Nom, Prenom, DatNais, NumInt FROM Patient WHERE Sexe='F' ORDER BY DateNais DESC, NumInt

Trie les enregistrements de sexe 'F' par date de naissance mais décroissante et pour une même date de naissance par numéro interne croissant.

XVII-C-5. Statistiques

SELECT COUNT(*) AS NombrePatient FROM Patient

Compte le nombre total d'enregistrement dans la table Patient et met le résultat dans le champ NombrePatient

On peut aussi utiliser:

MIN retourner la plus petite valeur.

MAX retourner la plus grande valeur.

SUM retourner la somme.

AVG retourner la moyenne.

VAR retourner la variance

STDEV retourner l'écart type.

SELECT Prenom ,COUNT(*) AS NombrePrenom FROM Patient GROUP BY Prenom

Extrait la liste des prénom avec le nombre de fois que le prénom est utilisé.

GROUP BY regroupe les enregistrements par valeur.

SELECT Prenom ,COUNT(*) AS NombrePrenom FROM Patient GROUP BY Prenom HAVING CONT(*)>3

Extrait la liste des prénoms avec le nombre de fois que le prénom est utilisé. S'il est utilisé plus de 3 fois..

HAVING rajoute un critère au regroupement.

XVII-C-6. Extraction de données sur plusieurs tables

Parfois on a besoin d'extraire des champs de plusieurs tables différentes, mais ayant une relation (un champ commun); pour cela on utilise une jointure.

Pour chaque enregistrement de la première table, on affiche en regard les enregistrements de la 2eme table qui ont la même valeur de jointure.

Exemple:

Soit la table patient

Image non disponible

Comment récupérer les champs Nom et ville (pas le numéro)?

SELECT Patient.Nom, Ville.NomVille From Patient INNER JOIN Ville ON Patient.NuméroVille= Ville.NuméroVille

On obtient:

Image non disponible

En ADO.Net, on verra qu'on passe la chaîne SQL à un objet Command.

Ces données extraites à partir d'une base de données grâce à l'instruction SQL vont se retrouver dans un DataSet (sorte de Bd en local, en mémoire).

XVII-C-7. Ajout, suppression, modification d'enregistrement

Il est impossible d'insérer, de modifier ou de supprimer dans plusieurs tables simultanément

Une requête de mise à jour (INSERT, UPDATE ou SELECT) est une transaction ,tout doit être réalisé, sinon rien ne se passe (en particulier si une seule donnée viole une contrainte, toutes les opérations sont annulées )

Insertion d'enregistrement:

Syntaxe:

INSERT [INTO] nomdelatable [(listedescolonnes)]

{VALUES (listedesvaleurs) | requêteselect | DEFAULT VALUES }

Exemple:

INSERT INTO TablePatient (Civilité, Nom, Prenom, NumeroVille, Datenais, Sex)

VALUES ('M', 'Dupont', 'Pierre', '2',' 02/12/51/, 'M')

Effacement d'enregistrement:

Syntaxe:

DELETE [FROM] nomtable [WHERE condition]

Exemple:

DELETE FROM Patient WHERE Nom LIK'%d'

Mise à jour d'enregistrement:

Syntaxe:

UPDATE nomtable SET colonne1 = valeur1 , colonne2 = valeur2 ... WHERE condition

WHERE condition est facultatif et permet de sélectionner les enregistrements correspondant

à un critère et de modifier ceux là.

En Ado.Net on verra qu'il y a 2 méthodes pour modifier ajouter, supprimer un enregistrement:

  • On peut effectuer directement les commandes en SQL: on exécute ces commandes par la méthode ExecuteNoQuery d'un objet Command.
  • Il est plus simple de lier la base à un DataSet, de modifier le DataSet et de mettre à jour la base par un Update (du DataAdapter). Dans ce cas, on n'écrit pas de commande SQL.

XVII-C-8. Ajout de table

On utilise CREATE TABLE puis le nom de la table, on ajoute les différents champs (entre parenthèses et séparés par des virgules)avec pour chaque champ les conditions (NOT NULL..) et le type.

CREATE TABLE PARENT (CLI_ID INTEGER NOT NULL PRIMARY KEY, CLI_NOM CHAR(32) NOT NULL, CLI_PRENOM VARCHAR(32))

En Ado.Net on verra qu'on exécute ces commandes par la méthode ExecuteNoQuery d'un objet Command.

Pour aller plus loin:

Série d'articles sur SQL chez developpez.com:

http://sql.developpez.com/


précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © . Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.