.Net et l’accès aux données ADO.Net

1. .Net et l’accès aux donnéesADO.Net Michel RIVEILL riveill@unice.fr - http://www.essi.fr/~riveill Laboratoire I3S Ecole d’Ingénieur en Sciences Informatiques (ESSI)

2. Objectifs et plan • Objectifs • Rappel sur les bases de données relationnelles • Présentation des classes de ADO.NET • Utilisation de ADO.NET dans une application • Plan • Base de données • Base de données relationnelles • Survol d’ADO.NET • Présentation des classes d’ADO.NET

3. Base de données • Toutes applications nécessitent des outils pour conserver et retrouver des données structurées • E-Commerce: sauvegarder un ordre, remplir un ordre • Assistant personnel : données personnelles (contact, agenda, …) • … • Les services attendus varient en fonction des applications • Transaction/OLTP • Entrepôt de données/OLAP

4. Base de données Besoins • Conservation, lecture et modification des données • Copier, transformer des données • Sauvegarder et restaurer des données • Exprimer, vérifier des règles d’intégrité • Facteur d’échelle et disponibilité • Evolution du nombre d’utilisateurs • Volume important des données • Débit important et temps de réponse court • Être sûr • Facilité le développement des applications

5. Base de donnéesEvolution des technologies • Fichier • Bases de données hiérarchiques • Accès à distance • Bases de données relationelles (RDBMS) • Bases de données orientés objets • XML

6. Plan • Base de données • Base de données relationnelles • Survol d’ADO.NET • Présentation des classes d’ADO.NET

7. Base de données relationnellesTables • Table (relation, entity) • Une collection de données avec un type spécifique • Organisée en lignes et colonnes • Colonne (attribut, champ) • Décrit une partie d’un entité (e.g. FirstName) • Donnée typée (e.g. integer, character, binary) • Peut être nulle • ligne (tuple, enregistrement) • Instance d’une donnée dans une table • Chaque ligne est unique

8. Base de données relationnelles Relations • Les tables peuvent être liées par des clés primaires ou externes (e.g., a book has an author) • Clés primaires • Guarantissent l’unicité d’une ligne • Peut être composée d’une ou plusieurs colonnes • Permet de garantir l’intégrité des entités • Clés externes • Permettent d’établir des relations logiques entre des tables • Une ou plusieurs colonnes d’une table correspond à une clé primaire (ou externe) d’une autre table • Permettent de garantir l’intégrité de la base

9. Base de données relationnelles Relations • Schema diagram depicts tables, columns, primary keys, foreign keys 1 ∞ Schema Diagram

10. Base de données relationnelles Relations Books Table PrimaryKey PK/FK Relationship Foreign Key Authors Table

11. Base de données relationnelles Type de relations 1 1 • One-to-One (1:1) • Uneligne dans la table X correspond à une ligne dans la table Y • Un livre a au plus une entrée dans la bibliothèque • One-to-Many (1:M) • Une ligne dans la table X correspond à 0+ lignes dans la table Y • Un éditeur publie un ou plusieurs livres • Many-to-Many (M:N) • 1+ lignes dans la table X correspond à 1+ lignes dans la table Y • Un auteur écrit un ou plusieurs livres ; un livre est écrit par un ou plusieurs auteurs Books LoC Entries 1 M Publishers Books M N Authors Books

12. Base de données relationnelles Relations M:N • Les plus complexes • Peuvent nécessité la construction de très grandes bases (contenant des données répliquées) • Difficile d’assurer l’intégrité de la base • Le remède : créer une troisième table • La troisième table contient les clés primaires des deux tables d’origine (construction d’une clé composite) • Les données sont répétés dans cette troisième table, mais pas dans les deux tables originales Authors 1 M M 1 Books BookAuth

13. Base de données relationnelles Relations M:N 1 ∞ Data is duplicated here 1 1 ∞ ∞

14. Base de données relationnellesNormalisation/Dénormalisation • Normalisation • Processus qui permet de ‘casser’ une grande table en plusieurs petites • But : minimiser les données redondantes, augmenter la correction • Améliore les performances lors des mises à jours • Souhaitable pour des applications transactionnelles • Dénormalisation • Processus qui combine de petite table pour en construire une grande • But : améliorer les performances • Introduit des données redondantes • Améliore les performances lors des lectures • Souhaitable pour les entrepôts de données

15. Base de données relationnellesJointures • Une jointure est la combinaison de données stockées dans plusieurs tables • Repose généralement sur les relations clés publiques/clés externes Vendor table Product Cost Vendor Vendor State Contact Widget $10 Acme Acme MA Linda A. Producttable Thingy $5 Acme Blecco WA Adam P. Widget $8 Blecco Foobar $25 Blecco Product Cost Vendor State Contact Widget $10 Acme MA Linda A. Thingy $5 Acme MA Linda A. Widget $8 Blecco WA Adam P. jointure Foobar $25 Blecco WA Adam P.

16. Base de données relationnelles Structured Query Language (SQL) • Langage standardisé pour acceder à une base de données relationnelles (ANSI); SQL-92 • Open, mais pas réellement • Les fonctions usuelles sont les mêmes dans les différents produits • La plupart des vendeurs ont des extensions propriétaires • Sous-ensemble de SQL • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL)

17. Base de données relationnellesDDL • Utilisé pour créer et modifier les objets d’une base de données CREATE DATABASE Bookstore CREATE TABLE tBooks ( BookID INT IDENTITY(1,1) PRIMARY KEY, Title VARCHAR(30) NOT NULL, PubDate DATE NOT NULL, [Description] VARCHAR(50), Category INT NOT NULL )

18. Base de données relationnellesDML • Selection de données SELECT * FROM tAuthors SELECT AuthID, FirstName, LastName FROM tAuthors SELECT AuthID, FirstName, LastName, Phone FROM tAuthors WHERE City = ‘Boston’ SELECT FirstName, LastName, Phone FROM tAuthors WHERE AuthID = 249

19. Base de données relationnellesDML • Construction d’une jointure SELECT AuthID, FirstName, LastName, Phone, BookID, Title, PubDate, Description FROM tAuthors, tBooks WHERE tAuthors.AuthID = tBooks.AuthID SELECT AuthID, FirstName, LastName, Phone, BookID, Title, PubDate, Description FROM tAuthors INNER JOIN tBooks ON tAuthors.AuthID = tBooks.AuthID

20. Base de données relationnellesDML • Insérer, modifier et supprimer des données INSERT INTO tBooks (Title, PubDate, [Description], Category) VALUES (‘Database Design’, GETDATE(), ‘How to design a database’, 3) UPDATE tAuthors SET Phone = ‘617-555-1234’ WHERE AuthID = 5 DELETE FROM tAuthors WHERE AuthID = 5

21. Base de données relationnellesDCL • Protéger les accès à la base GRANT INSERT, UPDATE, DELETE ON tAuthors TO Mary, John REVOKE CREATE TABLE FROM Joe DENY ALL ON tAuthors, tBooks TO Sally

22. Base de données relationnellesVues • Une vue est une table virtuelle • Abstraire la structure d’une table • Abstraire des requêtes (généralement complexes) • Abstraire les règles de protection d’une table • dans SQL Server 2000, une vue peut être • Indexée • Modifier et insérée dans la base CREATE VIEW vwCustomerOrders AS SELECT o.OrderId, c.CompanyName FROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerID ORDER BY o.OrderId

23. Base de données relationnellesUtilisation des vues SELECT * FROM vwCustomerOrders WHERE CompanyName = 'My Favorite Customer'

24. Base de données relationnellesProcédures stockées CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName • Un ensemble d’instructions SQL qui s’exécute ‘à coté de la base de données’ • Ne fait pas parti du standard SQL • Offre une meilleure performance • Permet de contrôler l’accès aux données • Peut accepter des paramètres • Peut retourner des données • Paramètres de sorties • Valeur de retour • Ensemble de retour exec CustOrderHist 'alfki'

25. Base de données relationnellesProcédures stockées • Utiliser l’instruction RETURN pour l’état • Valeur par défaut de SQL Server : 0 • Uniquement des valeurs numériques • Utiliser les paramètres OUTPUT pour les résultats RETURN 1 CREATE PROCEDURE MyProcedure @ReturnValue INT OUTPUT ... SELECT @ReturnValue = ColumnName FROM Table

26. Base de données relationnellesTriggers • Comme les procédures stockées, les ‘triggers’ sont du code qui s’exécute hors de la base de données • Ne peuvent pas être appelées directement par les utilisateurs • Exécutée à des moments ‘spécifiques’ de la vie d’une donnée particulière (INSERT, UPDATE or DELETE) • Permet de vérifier des règles d’intégrités • FOR AFTER : trigger exécuté après les actions ‘triguées’ • FOR INSTEAD OF : trigger exécuté à la place des actions ‘triguées’

27. Base de données relationnellesTransactions • Transaction : une séquence SQL qui constitue une unité logique • Respect des propriétés ACID • Atomicité : toute la séquence est exécutée avec succès, ou en cas d’erreur on revient à l’état initial • Consistance : laisse les données dans un état consistant (respect des règles d’intégrités) • Isolation : ne peut pas voir les modifications effectuées par des transactions concurrentes • Durabilité : même en cas de panne, les effets de la transaction ne peuvent pas être effacés un fois qu’elle a validé

28. Base de données relationnellesConcurrence • Niveau d’isolation • Lecture avant commit • Lecture après commit • Lecture multiple • Sérialisation • Choix : concurrence vs. intégrité des données • Verrouillage • Permet de garantir l’intégrité des transactions / la consistance de la base de données • Permet de se prémunir des données fantôme (i.e. on lit une valeur qui n’a jamais existé) • Peut créer des inter blocages

29. Plan • Base de données • Base de données relationnelles • Survol d’ADO.NET • Présentation des classes d’ADO.NET

30. Survol d’ADO.NETRappel • ODBC (Open Database Connectivity) • Interopérabilité avec de nombreuses bases de données (DBMS) • API largement acceptée • utilise SQL comme langage d’accès aux données • DAO (Data Access Objects) • Interface de programmation pour les bases de données JET/ISAM • Utilise automation (ActiveX, OLE automation) • RDO (Remote Data Objects) • Fortement couplé avec ODBC • Adapté au modèle client-serveur (vs. DAO) • OLE DB • Accès aux données relationnelles ou non • Construit pour COM • Pas restreint au langage SQL • Peut utiliser des drivers ODBC • Interface de bas niveau (C++) • ADO (ActiveX Data Objects) • Interface orientée objet, Ensemble de composants d’accès • Défini un modèle de programmation pour OLE DB accessible pour tous les langages

31. Survol d’ADO.NETChaîne d’accès ADO Votre application ADO OLE DB ODBC Provider Simple Provider Native Provider ODBC OLE DB Provider OLE DB Provider ODBC Driver TextFile Mainframe Database Database

32. Survol d’ADO.NETADO • ADO a été défini comme un modèle fortement couplé (mode connecté) • Approprié pour les architectures client/serveur • Défini pour le modèle relationnel • Par pour le modèle hiérarchique (XML) • La conception objet d’ADO laisse à désirer • Plusieurs manière de faire la même chose • Une même objet a plusieurs utilisations • Pas défini pour les applications réparties (n-tier architecture)

33. Survol d’ADO.NETADO.NET • ADO .NET est une collection de classes, interfaces, structures et de types énumérés pour accéder à des données relationnelles dans le framework .NET • La collection est organisées dans différentes bibliothèques • System.Data, System.Data.OleDb, System.Data.SqlClient, etc. • ADO .NET est une évolution d’ADO. • Ne partage pas le même modèle objet • Mais partage plusieurs paradigmes ou fonctionnalités

34. Survol d’ADO.NETObjectifs d’ADO.NET • Excellente conception • Gestion robuste du modèle déconnecté • Supporter aussi bien le modèle hiérarchique que le modèle relationnel • support des données XML • Permettre l’accès aux données à l’aide d’HTTP • Maintenir la familiarité avec le modèle de programmation d’ADO • Rendre ADO disponible via .NET • Interoperabilité avec COM

35. Survol d’ADO.NETProviders supportés • Fusionner ADO et OLEDB en un seul niveau • Chaque provider contient un ensemble de classe qui implémentent une interface commune • Implémentations initiales • ADO Managed Provider : permet l’accès ay base de données de type OLE • SQL Server Managed Provider : fournit des performance optimale pour l’utilisation d’un serveur SQL • Exchange Managed Provider : permet la recherche et la modification de donnée dans Microsoft Exchange

36. Survol d’ADO.NETProviders supportés Votre application ADO.NET Managed Provider SQL Managed Provider ADO Managed Provider OLE DB Provider SQL ServerDatabase Database

37. Survol d’ADO.NETAccès aux données • Connecté : Forward-only, read-only • Les applications requêtent les données, les rapatrient et les utilisent • “Firehose” cursor • DataReader • Déconnecté • Les applications requêtent les données, les sauvegardent pour pouvoir les traiter • Minimise le temps de connexion avec la base • DataSet

38. Survol d’ADO.NETLiaison aux données (data binding) • Composant clé du framework ‘Web Forms’ • Flexible et facile à utiliser • Permet de lier n’importe quel type de données stocké dans une base • Permet de contrôler les transferts de données entre la base et l’application • Contrôle simple pour l’accès à une valeur simple • Contrôle complexe pour l’accès à une donnée structurée <asp:Label runat=server Text='<%# CustList(0).FirstName %>'/>

39. Plan • Base de données • Base de données relationnelles • Survol d’ADO.NET • Présentation des classes d’ADO.NET

40. ADO.NETInterface IDbConnection • Créer une session unique avec un source de données • Implémenté par SqlDbConnection and OleDbConnection • Fonctionnalités • Ouvrir, fermer les connections • Démarrer les transactions • IDbTransaction fournit les méthodes Commit et Rollback • Utilisé avec les objets IDbCommand et IDataAdapter • D’autres propriétés, méthodes et collections peuvent être offerts selon le provider

41. ADO.NETInterface IDbCommand • Représente une instruction qui peut être envoyée à une source de données • Peut être du SQL… mais pas nécessairement • Implémenter par OleDbCommand et SqlCommand • Fonctionnalités • Défini une instruction à exécuter • Exécute l’instruction • Passe et récupère les paramètres • Crée et prépare une version compilée de la commande • ExecuteReader retourne une ligne, ExecuteNonQuery ne retourne rien, ExecuteScalar retourne une valeur simple • D’autres propriétés, méthodes et collections peuvent être offerts selon le provider

42. ADO.NETInterface IDataReader • Accès en mode forward-only, read-only à un flux (stream) de données • Implémenté par SqlDataReader et OleDbDataReader • Créé par la méthode ExecuteReader de IDbCommand • Les opérations sur les objets associés à la connexion (IDbConnection) ne sont pas autorisé jusqu’à la fermeture de la lecture

43. ADO.NETNamespace System.Data.OleDb • À utiliser avec des providers OLEDB (namespace managed) • SQLOLEDB (SQL Server) – utilise System.Data.SQL • MSDAORA (Oracle) • JOLT (Jet) • OLEDB for ODBC providers • Classes OleDbConnection, OleDbCommand et OleDbDataReader • Classes pour la gestion des erreurs • Classes pour la gestion des connexions

44. ADO.NETUn exemple : DataReader string sConnString = “Provider=SQLOLEDB.1;” + “User ID=sa;Initial Catalog=Northwind;” + “Data Source=MYSERVER”; OleDbConnection conn = new OleDbConnection(sConnString); conn.Open(); string sQueryString = “SELECT CompanyName FROM Customers”; OleDbCommand myCommand = new OleDbCommand(sQueryString, conn); OleDbDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { Console.WriteLine(myReader.GetString(0)); } myReader.Close(); conn.Close();

45. ADO.NET Démonstration : DataReader, Insert

46. ADO.NETNamespace System.Data • Contient les classes au coeur de l’architecture ADO.NET • La classe DataSet, qui gère le mode déconnecté est centrale • Supporte tous les types d’applications • Internet • ASP.NET • XML • Windows • Contient les classes définies ou surchargées par les différents providers • IDbConnection, IDbCommand, IDbDataReader

47. ADO.NETDataSet • Une collection de tables • N’a pas la connaissance de la source de données • Capture toutes les relations entre les tables • Modèle de programmation complet (objets pour les tables, les colonnes, les relations, etc.) • Mémorise l’état original et l’état courant des données • Peu modifier dynamiquement les données et les méta-données • Permet de sérialiser les schémas en XML • Défini dans le namespace System.Data

48. ADO.NET DataSet DataSet DataTable DataColumn DataRow DataRelation

49. ADO.NETNamespace System.Data.SqlClient • Défini de manière native par le serveur SQL (managed code) • Construit à l’aide de TDS (Tabular Data Stream) pour de bonne performance dans le serveur SQL • Défini les classes SqlConnection, SqlCommand and SqlDataReader • Défini aussi des classes pour • Gestion des erreurs • La gestion des connections par pool (fonctionnement par défaut) • System.Data.SqlTypes fournit les classes pour la gestion des types du serveur SQL

50. ADO.NET Interface IDataAdapter • Publie ou envoie les modification à un DataSet • Implémenté par OleDbDataAdapter et SqlDataAdapter • Fonctionnement sans connexion • Permet un fonctionnement en mode asynchrone • Contient quatre objets commandes par défaut • Select, Insert, Update, et Delete