Plan Accès aux données dans le framework .NET 1. ADO.NET Mode connecté Mode non connecté ADO.NET – Entity Framework – LINQ 2. Entity Framework 3. LINQ Lionel Seinturier Université Lille 1 [email protected] 27/3/14 1 2 Lionel Seinturier ADO.NET ADO.NET ActiveX Data Object .NET (ADO.NET) ActiveX Data Object .NET (ADO.NET) API d'accès (local ou distant) à une source de données : SGBD, tableur, fichier, messagerie, … • API d'interaction avec un SGBD • nombreuses utilisations - sauvegarde de données de manière sûre - exploration du contenu d'un SGBD - client/serveur 3 tiers Fonctionne selon un principe client/serveur (local ou distant) • client = le programme (C#, VB, C++, …) • serveur = la source de données présentation Principe • le programme ouvre une connexion • il envoie des requêtes SQL • il récupère les résultats • ... • il ferme la connexion ADO.NET Lionel Seinturier traitement donnée ADO.NET client 3 Lionel Seinturier ADO.NET serveur d'applications 4 SGBD Lionel Seinturier ADO.NET Historique ADO.NET Fournisseur (provider) et connection string ODBC Implémentation de l'API pour un type de sources de données Un provider par type de base de données (SQL Server, MySQL, etc.) évolutions DAO C++ VB RDO unification Connection string : identifie la source de données à laquelle on se connecte OLE-DB ts langages – API COM • chaîne de caractères • liste de couples propriété=valeur • format dépend du provider simplification (+ haut niveau) Exemple : "Server=...;Database=...;" ADO framework .NET ADO.NET ADO.NET 5 Lionel Seinturier 6 ADO.NET ADO.NET Lionel Seinturier ADO.NET Utilisation Utilisation de ADO.NET (suite) L'API ADO.NET est définie dans System.Data 3. Récupération du résultat 1. Ouverture d'une connexion avec la base test reader.Read() SqlConnection cx = new SqlConnection("Server=localhost;Database=test;"); cx.Open(); retourne vrai tant qu'il reste des enregistrements dans le résultat et positionne le curseur sur l'enregistrement suivant 2. Envoi d'une requête SELECT (ex. : reader.GetString(0) ) retourne la valeur de la colonne 0 de type String de l enregistrement courant SqlCommand cmd = new SqlCommand("SELECT * FROM ages",cx); SqlDataReader reader = cmd.ExecuteReader(); GetInt32, GetBoolean, GetByte, GetDouble, GetFloat reader.GetString(int column) idem pour des colonnes de type int, boolean, byte, double ou float Envoi d'une requête CREATE, INSERT ou UPDATE while( reader.Read() ) { String nom = reader.GetString(0); int age = reader.GetInt32(1); Console.WriteLine( nom + " a " + age + " ans" ); } SqlCommand cmd = new SqlCommand("INSERT INTO ages VALUES ('toto',12)",cx); cmd.ExecuteNonQuery(); ADO.NET 7 Lionel Seinturier ADO.NET 8 Lionel Seinturier ADO.NET ADO.NET Utilisation de ADO.NET (code complet) Types de requêtes SQL using System.Data; using System.Data.SqlClient; • "normale" - interprétée à chaque exécution public class TestADONet { • précompilée - paramétrable - préparée pour être exécutée plusieurs fois - gérée par le programme public static void Main( String[] args ) { SqlConnection cx = new SqlConnection("Server=localhost;Database=test;"); cx.Open(); • procédure stockée - paramétrable - écrite dans le langage interne du SGBD (ex SQL Server Transac-SQL) - gérée par le SGBD SqlCommand cmd = new SqlCommand("SELECT * FROM ages",cx); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string nom = reader.GetString(0); int age = reader.GetInt32(1); Console.WriteLine( nom + " a " + age + " ans" ); } + masque schéma base + meilleures perf + validées par rapport schéma base cx.Close(); - langage propriétaire (- évolution) - risque de mélange logiques traitement/donnée } } 9 ADO.NET Lionel Seinturier ADO.NET ADO.NET 10 Lionel Seinturier ADO.NET Requêtes SQL précompilées Procédures stockées 1. Possibilité de définition de 1 ou +sieurs paramètres ! caractères ? Exemple de procédure stockée Transact-SQL (SQL Server) SqlCommand cmd = new SqlCommand ("SELECT * FROM ages WHERE nom=? AND age>?",cx); CREATE PROCEDURE [pubs].[GetRange] @age int AS SELECT nom FROM ages WHERE age < @age GO 2. Valeurs des paramètres ajoutés à la commande cmd.Parameters.Add(new Parameter("Bob"),cx); cmd.Parameters.Add(new Parameter(Convert.ToInt32(5)),cx); Le code d appel de la procédure • new Parameter( string name, object value ) • paramètres ajoutés dans l'ordre de leur définition dans la requête • name non significatif dans ce contexte (voir procédure stockée) SqlCommand cmd = new SqlCommand("GetRange",cx); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add( new Parameter("age",Convert.ToInt32(5) ); 3. Exécution de la requête SqlDataReader reader = cmd.ExecuteReader(); ... SqlDataReader reader = cmd.ExecuteReader(); ... ADO.NET 11 Lionel Seinturier ADO.NET 12 Lionel Seinturier ADO.NET ADO.NET Transactions Transactions Groupes de requêtes devant être exécutés de façon indivisible Exemple La transaction doit être CREATE TABLE comptes (nom VARCHAR(30) PRIMARY KEY, solde FLOAT CHECK(solde>=0) ); - validée (commit) ! les résultats ne sont visibles qu'à partir de ce moment - ou annulée (rollback) SqlCommand cmd1, cmd2; cmd1 = new SqlCommand("UPDATE comptes SET solde=solde+montant WHERE nom='Paul'",cx); cmd2 = new SqlCommand("UPDATE comptes SET solde=solde-montant WHERE nom='Bob'",cx); SqlTransaction trans = cx.BeginTransaction(); SqlCommand cmd1 = new SqlCommand("INSERT INTO ages VALUES ('Pierre',12)",cx); SqlCommand cmd2 = new SqlCommand("UPDATE ages SET age=15 WHERE nom='Joe'",cx); SqlTransaction trans = cx.BeginTransaction(); cmd1.Transaction = trans; cmd2.Transaction = trans; cmd1.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); déclaration du début de la transaction trans.Commit(); validation de la transaction 13 ADO.NET Lionel Seinturier try { cmd1.Transaction = trans; cmd2.Transaction = trans; cmd1.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); trans.Commit(); } catch( Exception e ) { trans.Rollback(); } 14 ADO.NET ADO.NET ADO.NET Accès aux données en mode déconnecté • par défaut c/s connecté vers SGBD + 1 seule copie des données (SGBD) + mises à jour simples Lionel Seinturier Accès aux données en mode déconnecté • datasets : représentation mémoire des données d'un SGBD SELECT … adapter.Update(dataset) rés eau DataSet connecté vs non connecté n messages petite taille vs 1 message grande taille SELECT … rés eau Data Adapter SGBD adapter.Fill(dataset) DataAdapter : gère liaison mémoire (DataSet) – SGBD ! contient les requêtes SQL (select, update, insert) associées aux données Déconnecté • pouvoir consulter/modifier les données off line • économiser les ressources réseaux (connexions moins longues) • travailler sur des données en mémoire plutôt que directement sur un SGBD ADO.NET 15 Lionel Seinturier ADO.NET 16 Lionel Seinturier ADO.NET ADO.NET DataSet Exemple d'utilisation d'un DataSet Un DataSet contient SqlConnection cx = new SqlConnection("Server=localhost;Database=pubs;"); cx.Open(); • des DataTable données sous forme de table - des DataColumn - nom, type, propriétés (autoincrement, unique, readonly, maxlength, …), … - des DataRow - valeurs - des DataConstraint • des DataRelation relation entre 2 DataTable • une DefaultView Un DataSet peut être • consulté • modifié (valeurs, lignes) • sauvegardé/chargé en XML ! mise à jour BD lors de Update() 17 ADO.NET Lionel Seinturier SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand("SELECT * FROM comptes",cx); DbCommandBuilder builder = new SqlCommandBuilder(adapter); DataSet dataset = new DataSet(); adapter.Fill(dataset); cx.Close(); for( int i=0 ; i<dataset.Tables.Count ; i++ ) { DataTable table = dataset.Tables[i]; DataColumnCollection columns = table.Columns; for( int j=0 ; j<table.Rows.Count ; j++ ) { DataRow row = table.Rows[j]; Console.WriteLine("Row "+j+": "+row["nom"]+" "+row["solde"]); } } 18 ADO.NET ADO.NET Lionel Seinturier ADO.NET Mise à jour d'un DataSet DataView Modification d'une valeur Vue (pas de copie des données) sur une Datatable • séléction • tri table.Rows[0][0] = "Bill"; adapter.Update(dataset); Ajout d'une ligne Mise à jour données dans la vue = maj des données dans la DataTable DataRow myDataRow = table.NewRow(); myDataRow["nom"] = "John"; myDataRow["solde"] = 123; table.Rows.Add(myDataRow); adapter.Update(dataset); DataView view = new DataView(table); view.RowFilter = "nom='Bob'"; // sélection de(s) Bob for( int i=0 ; i < view.Count ; i++ ) { view.Delete(i); // suppression aussi dans la DataTable } view.Sort = "nom, age DESC"; // d'abord pas nom puis par age décroissant Expression de sélection "à la SQL" - opérateur LIKE - fonctions sum, avg, count, min, max ADO.NET 19 Lionel Seinturier ADO.NET nom LIKE '*ob*' 20 Lionel Seinturier ADO.NET ADO.NET Comparaison ADO.NET - JDBC c/s accès désignation initialisation connexion commande curseur résultat curs. multi-dir curs. maj deconnecté transaction ≠ niv. isolation Comparaison ADO.NET - JDBC ADO.NET JDBC oui provider Server=… new … Open() …Command …DataReader non non DataSet cx.BeginTransaction() trans.Commit() trans.Rollback() oui oui driver jdbc:mysql://… Class.forName("…") DriverManager.getConnection Statement/PreparedStatement/CallableState ResultSet oui oui RowSet (JDBC 3.0) cx.setAutoCommit(false) cx.commit() cx.rollback() oui 21 ADO.NET Lionel Seinturier méta-données batch pool de cx ADO.NET JDBC oui non oui (provider) oui oui oui (JNDI) 22 ADO.NET Plan Lionel Seinturier Entity Framework Entity Framework (EF) 1. ADO.NET Mode connecté Mode non connecté Mapping objet relationnel • mise en correspondance d'objets (C#, VB, etc.) et SGBD Avantages (par rapport à ADO.NET) • typage • manipulation d'objets métier (plutôt que SQL) 2. Entity Framework 3. LINQ Stockage des données • une table par classe • données utilisables indifféremment EF, ADO.NET, "directement" en SQL 23 Lionel Seinturier Entity Framework 24 Lionel Seinturier Entity Framework Entity Framework Principe de base Relations entre tables class Blog { public int BlogId { get; set; } public string Name { get; set; } } class Blog { public int BlogId { get; set; } public string Name { get; set; } public virtual List<Post> Posts { get; set; } } Notion de contexte pour faire le lien entre objets et SGBD class Post { public int PostId { get; set; } public string Title { get; set; } public string Content { get; set; } public virtual Blog Blog { get; set; } } using System.Data.Entity; class BlogContext : DbContext { DbSet<Blog> Blogs { get; set; } } Utilisation var db = new BlockContext(); var blog = new Blog { BlogId=1; Name=".NET Blog"; }; db.Blogs.Add( blog ); db.SaveChanges(); Stockage • base : <nom projet • table : Blogs Entity Framework • relations 1-1, 1-n ou n-m • virtual = chargement des données uniquement si accédées VSudio>.BlogContext 25 Lionel Seinturier 26 Entity Framework Entity Framework Lionel Seinturier Entity Framework Annotations Recherches Précisent la mise en correspondance objet – relationnel • redéfinissent les noms par défaut pour les tables, colonnes • renseignent les informations liées aux clés • autres : renseignent types SQL, valeurs auto-générées, index • à partir de clé primaire • à partir d'une fonction de filtrage var blog = db.Blogs.find(3); // null si la clé 3 n'existe pas [Table("Journal")] class Blog { [Key] public int BlogId { get; set; } [Column="TitreDuJournal"] public string Name { get; set; } public virtual List<Post> Posts { get; set; }} var blogs = db.Blogs.Where( b => b.Name=="Bob" ); // résultat de type collection class Post { [Key] public int PostId { get; set; } public string Title { get; set; } public string Content { get; set; } public virtual Blog Blog { get; set; } [ForeignKey("BlogId"] public int BlogId {get; set; }} Entity Framework 27 Lionel Seinturier Entity Framework 28 Lionel Seinturier Entity Framework Plan Modifications 1. ADO.NET Mode connecté Mode non connecté • ajouts, modifications, suppressions • SaveChange() pour prendre en compte modifications (une ou plusieurs) var blog = new Blog { BlogId=1; Name=".NET Blog"; }; db.Blogs.Add( blog ); db.SaveChanges(); 2. Entity Framework 3. LINQ var blog = db.Blogs.find(3); blog.Name = "John Doe"; db.SaveChanges(); var blog = db.Blogs.find(3); db.Blogs.Remove( blog ); db.SaveChanges(); 29 Entity Framework 30 Lionel Seinturier LINQ LINQ LINQ (Language INTegrated Query) LINQ (Language INTegrated Query) Langage de requêtage SQL intégré au langage de programmation (C#, VB, …) Collections Nouveaux mots-clés • from : variable d'itération • in : collection sur laquelle s'effectue l'itération • where : condition de sélection de l'élément courant • select : valeur à sélectionner var people = new List<User>() { new User { Name = "Bob", Age = 24, Tel = "06" }, new User { Name = "Anne", Age = 26, Tel = "07" } }; var persons = from p in people where p.Age > 25 && p.Name.StartsWith("A") select p; int[] tab = new int[]{ 6, 3, 1, 2, 5, 4, 6 }; IEnumerable<int> pairs = from number in tab where number % 2 == 0 select number; Type des données retournées (classe anonyme) var persons = from p in people select new { p.Nom, p.Tel }; foreach(int i in pairs) { ... } LINQ 31 Lionel Seinturier Lionel Seinturier LINQ 32 Lionel Seinturier LINQ LINQ LINQ (Language INTegrated Query) LINQ (Language INTegrated Query) Jointures Tri et regroupement var addresses = new List<Address>() { new User { Name = "Bob", Ville = "Lille" }, new User { Name = "Pat", Ville = "Paris" } }; var persons = from p in people orderby p.Name, p.Age select p; var knownAdresses = from p in people from a in addresses where p.Name == a.Name select p.Name; LINQ var knownAdresses = from p in people from a in addresses where p.Name == a.Name group p by p.Ville select p.Name; 33 Lionel Seinturier 34 LINQ LINQ LINQ LINQ (Language INTegrated Query) LINQ (Language INTegrated Query) Opérateurs • OfType<T> • Min, Max, Sum, Average Opérateurs • Where(λ) • Count(λ) • ToArray, ToList object[] values = { 1, "Tom", 'T', 12.5, 3, true, 20 }; var results = values.OfType<int>(); foreach(int i in results) { Console.WriteLine(i); } var values = new object[] { 1, "Tom", 'T', 12.5, 3, true, 20 }; var val = values.Where( v => v.ToString().Length >= 3 ); int i = values.Count( v => v.ToString().Length >= 3 ); int[] IntegerValues = { 0, 2, 5, 6, 7 }; int max = IntegerValues.Max(); int min = IntegerValues.Min(); int sum = IntegerValues.Sum(); double average = IntegerValues.Average(); LINQ 35 Lionel Seinturier object[] array = values.ToArray(); List<object> list = array.ToList(); Lionel Seinturier LINQ 36 Lionel Seinturier