Introduction à ADO.NET 2.0

Qu'est-ce qu'ADO.NET ?
Notion de fournisseurs de données
Notre base de données
Créer un code indépendant d'un fournisseur de données
Lire le contenu d'une table
Utiliser des requêtes SQL
Utilisation d'un cache pour stocker des données

Le but de cet article est de présenter le développement avec la technologie de bases de données ADO.NET. Nous nous intéresserons plus particulièrement à SQL Server 2005.
Pour développer nos exemples, nous utiliserons trois produits gratuits et téléchargeables via les liens suivants:

• Visual C# 2005 Express Edition pour rédiger nos programmes C#.
• SQL Server 2005 Express Edition, une version allégée du SGBD phare de Microsoft.
• Microsoft SQL Server Management Studio Express pour gérer nos bases SQL.

Qu'est-ce qu'ADO.NET ?

ADO.NET est le successeur d'ADO (ActiveX Data Object), la technologie que l'on devait utiliser, via un ensemble de classes, pour accéder aux bases de données en environnement Microsoft. L'acronyme ADO.NET représente l'ensemble des classes du Framework .NET qui permettent la manipulation de données contenues dans des SGBDR.

Notion de fournisseurs de données (providers)

Lorsque vous souhaitez établir une communication entre une application et un SBGDR spécifique, vous devez faire appel à une couche logicielle que l'on nomme fournisseur de données ou providers. Le Framework .NET propose par défaut quatre fournisseurs de données dont les spécificités sont résumées dans le tableau suivant.

Notre base de données

Commençons par ouvrir Management Studio Express afin de créer notre base de données. Connectons-nous au serveur localhost\sqlexpress avec une Authentification Windows. Dans le volet Explorateur d'objets, cliquons-droit sur le noeud Bases de données pour créer une nouvelle base de données que l'on nommera UNIVERSITE. Nous allons maintenant créer deux tables DEPARTEMENTS et PERSONNES via une Nouvelle Requête (à gauche dans la barre d'outils). Voici le script Transact-SQL pour les fans du clavier:

CREATE TABLE [dbo].[DEPARTEMENTS]
(
	[DeptID] [char] (3) NOT NULL,
	[Departement]	[nvarchar] (30) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[PERSONNES]
(
	[PersonneID] [int] IDENTITY(1,1),
	[DeptID] [char] (3) NOT NULL,
	[Nom] [nvarchar] (30) NOT NULL,
	[Prenom] [nvarchar] (30) NOT NULL,
	[Categorie] [nvarchar] (20) NOT NULL
) ON [PRIMARY]

ALTER TABLE PERSONNES ADD CONSTRAINT PK_PersonneID PRIMARY KEY (PersonneID)
ALTER TABLE DEPARTEMENTS ADD CONSTRAINT PK_DeptID PRIMARY KEY (DeptID)
ALTER TABLE PERSONNES ADD CONSTRAINT Appartient_A FOREIGN KEY (DeptID)
	REFERENCES DEPARTEMENTS(DeptID)

Les plus productifs utiliseront directement l'interface graphique selon le chemin suivant: noeud UNIVERSITE, clique-droit sur le noeud Tables, puis Nouvelle Table.... La partie attributs/types de données ne pose pas de problèmes particuliers. Pour définir un attribut en tant que clé primaire, il suffit d'un clic-droit sur l'attribut en question. La propriété IDENTITY est modifiable dans l'onglet Propriétés des colonnes (juste en-dessous de la table): section Concepteur de tables, indiquer Oui pour Spécification du compteur (Est d'identité).
Remplissons notre base avec le script suivant:

INSERT INTO DEPARTEMENTS VALUES('MAT', 'Mathématiques')
INSERT INTO DEPARTEMENTS VALUES('BIO', 'Biologie')
INSERT INTO DEPARTEMENTS VALUES('PHY', 'Physique')

SET IDENTITY_INSERT PERSONNES OFF

INSERT INTO PERSONNES VALUES('PHY', 'Goley', 'Jerry', 'Chercheur')
INSERT INTO PERSONNES VALUES('MAT','Provist','Alain', 'Etudiant')
INSERT INTO PERSONNES VALUES('MAT','Némarre','Jean', 'Etudiant')
INSERT INTO PERSONNES VALUES('BIO','Denisse','Brice', 'Etudiant')

Notons que nous ne remplissons pas la colonne PersonneID. C'est en fait le SGBD qui se charge de calculer ces valeurs car nous avons utilisé la propriété IDENTITY(1,1) dans la définition de l'attribut PersonneID. Cette propriété ainsi utilisée indique que la valeur 1 est attribuée à la première  ligne insérée dans la table et que, pour chaque nouvel enregistrement, la valeur s'incrémente d'une unité.
Avant de passer à la partie la plus captivante, on pourra vérifier que nos tables contiennent bien nos enregistrements:

select * from PERSONNES
select * from departements

Créer un code indépendant d'un fournisseur de données

Dans nos exemples, nous utiliserons le fournisseur de données pour SQL Server, SqlClient. Dans l'absolu, il est plus judicieux d'en faire un maximum pour découpler l'application du fournisseur de données. Par exemple, nous pourrions être tentés de créer une connexion de la façon suivante:

using System;
using System.Data.Common;
using System.Data.SqlClient;




class Program
{
    static void Main()
    {
        // Se connecter à la base de données
        DbConnection connection;



        // Tester si on travaille avec SQL Server
        if (true)
        {
            connection = new SqlConnection();
            // ...
        }
    }
}

Le problème est que nous couplons notre application avec le fournisseur de données SqlClient dès lors que nous affectons connection. En dehors du test, connection est bien indépendante du SGBD sous-jacent mais, à un endroit ou un autre, il est nécessaire d'expliciter la classe à utiliser lors de la création des objets ADO.NET. 
La solution se trouve dans System.Data.Common, nouvel espace de noms du Framework 2.0: la classe abstraite DbProviderFactory.

using System;
using System.Data.Common;


class Program
{
    static void Main()
    {
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        DbConnection connection = factory.CreateConnection();
    }
}

Comme vous l'aurez surement remarqué, nous n'avons plus besoin de l'espace de noms System.Data.SqlClient.
En effet, l'espace de noms System.Data.Common a été créé dans le but de permettre au développeur d'écrire du code ADO.NET qui travaille avec n'importe quel fournisseur de données du Framework .NET. Concrètement, les classes qui permettent cette abstraction ont été conçues selon un modèle de conception logicielle (on parle de Design Patterns) nommé Fabrique Abstraite. L'idée à retenir est que ce pattern propose de définir une interface, la fabrique abstraite, qui permet de créer des objets sans avoir à spécifier explicitement leur classe concrète. Le framework .NET adopte le plan de nommage xxxFactory pour désigner ses classes abstraites Fabrique Abstraite, une variante de la Fabrique Abstraite classique. Ainsi, on accède à la classe abstraite fabrique DbProviderFactory via le singleton* factory, auquel on indique le provider choisi grâce à la méthode GetFactory de la classe DbProviderFactories qui représente une fabrique de fabrique. Cette méthode reçoit en paramètre une chaîne indiquant l'espace de noms correspondant au provider choisi et retourne une nouvelle instance de SqlConnection dans notre cas.

* Singleton est un autre design pattern selon lequel une et une seule instance d'une classe est autorisée.
Pour en savoir plus sur les Design Patterns, je vous conseille de consulter l'ouvrage UML 2 et les Design Patterns.

Pour obtenir un code totalement indépendant du fournisseur de données, nous devons faire appel à l'élément connectionString du fichier du configuration. Si cela n'est pas déjà fait, ajoutons un fichier de configuration à notre projet en sélectionnant l'option Add New Item... du menu Project. Voici le contenu de notre fichier de configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
	<add name="Une BDD"
	connectionString="Server=localhost\\sqlexpress;Database=UNIVERSITE; Trusted_Connection=yes;"
	providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

Nous voici donc munis du code parfait ;)

static void Main()
{
    // Récupérer les attributs de l'élément ConnectionStrings du fichier de configuration.
    ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["Une BDD"];

    Console.WriteLine("Attributs de l'élément connectionStrings du fichier de configuration");
    Console.WriteLine("Name: " + css.Name);
    Console.WriteLine("ConnectionString: " + css.ConnectionString);
    Console.WriteLine("ProviderName: " + css.ProviderName);
    
    DbProviderFactory factory = DbProviderFactories.GetFactory(css.ProviderName);
    DbConnection connection = factory.CreateConnection();
    connection.ConnectionString = css.ConnectionString;

    Console.Write("\nLa base " + connection.Database);
    Console.WriteLine(" se trouve sur le serveur " + connection.DataSource);
}

Remarque: il sera peut-être nécessaire de rajouter une référence vers system.configuration.dll afin de pouvoir utiliser la classe ConnectionStringSettings. Pour régler le problème, il faudra se rendre dans Solution Explorer, cliquer-droit sur la racine du projet et sélectionner Add Reference...

Pour finir cette petite introduction à ADO.NET 2.0, penchons-nous sur trois exemples pratiques qui montrent comment nous pouvons très facilement manipuler nos tables à l'aide des nombreuses classes du Framework .NET.

Lire le contenu d'une table

Comme nous le montre l'exemple suivant, nous pouvons récupérer le contenu d'une table grâce à la classe DbDataReader.

using System.Data.Common;
using System.Data.SqlClient;

...




static void Main()
{
    string stringConnection = "Server=localhost\\sqlexpress;Database=UNIVERSITE; Trusted_Connection=yes;";
    string stringCommand = "SELECT * FROM PERSONNES";

    Console.WriteLine("Prénom\t\tNom");
   
    using (DbConnection connection = new SqlConnection(stringConnection))
    {
        using (DbCommand command = new SqlCommand(stringCommand, connection as SqlConnection))
        {
            connection.Open();
            using ( DbDataReader ddr = command.ExecuteReader() )
            {
                while (ddr.Read())
                {
                    Console.WriteLine(ddr.GetString(3) + "\t\t" + ddr.GetString(2));
                }
            }
        }
    }
}

Nous commençons par définir une chaîne de connexion, nommée stringConnection, qui permet de stocker des informations pour localiser notre base de données et pour s'y connecter. Nous devons utiliser le mot-clé Server pour définir le serveur qui héberge notre base de données. Il s'agit ici du serveur local localhost\sqlexpress. Nous indiquons la table à laquelle nous souhaitons nous connecter, UNIVERSITE, grâce au mot-clé Database. L'attribut Trusted_Connection défini à yes indique que nous souhaitons nous authentifier selon une Authentification Windows. Dans le cas contraire, nous aurions dû fournir un nom d'utilisateur et un mot de passe, via les mots-clés UID et PWD, pour une Authentification SQL Server. Vous trouverez ICI la liste des mots-clés disponibles pour le fournisseur de données SQL Server. Attention, les mots-clés sont différents selon les providers: liste des mots-clés pour les providers Oracle, OLE DB et ODBC.
Nous déclarons ensuite une chaîne qui comporte du texte SQL, le même que celui que nous utiliserions pour écrire un script.
La classe DbDataReader nous permet de lire nos enregistrements sous la forme d'un flux. Nous obtenons un résultat selon les informations contenues dans l'objet command, instance de SqlCommand. Cet objet dit que nous lisons la base UNIVERSITE (objet connection) et que nous sélectionnons tous les enregistrements de la table PERSONNES. Comme nous n'affichons que les colonnes 2 et 3, i.e. celles correspondant aux attributs Nom et Prenom, nous obtenons le résultat suivant:

Prénom          Nom
Jerry           Goley
Jean            Némarre
Brice           Denisse
Alain           Provist

Utiliser des requêtes SQL

Comme nous venons de le voir, nous pouvons utiliser des requêtes SQL dans un prorgamme C#. Les commandes SQL les plus fréquemment utilisées sont la mise à jour (UPDATE), la sélection (SELECT), l'insertion (INSERT) et la suppression (DELETE). La méthode ExecuteNonQuery permet d'exécuter ce type de commandes. Cette méthode ne renvoie pas d'informations sur les enregistrements mais le nombre de lignes affectées pour la mise à jour, l'insertion et la suppression, et la valeur -1 pour les autres types de commandes. Dans l'exemple suivant, nous insérons un département informatique dans la table DEPARTEMENTS, puis nous le supprimons.

using System.Data.Common;
using System.Data.SqlClient;

...

static void Main()
{
    string command1 = "INSERT INTO DEPARTEMENTS VALUES ('INF', 'Informatique')";
    string command2 = "DELETE FROM DEPARTEMENTS WHERE DeptID = 'INF'";
    string SELECTCom = "SELECT * FROM DEPARTEMENTS";
    string connectionString = "Server=localhost\\sqlexpress;Database=UNIVERSITE; Trusted_Connection=yes;";

    using (DbConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        DbCommand command = new SqlCommand(command1, connection as SqlConnection);
        DbCommand DbSELECTCom = new SqlCommand(SELECTCom, connection as SqlConnection);

        int nombreDeLigneAffectees = (int)command.ExecuteNonQuery();
        AfficherTableDept(DbSELECTCom, "insertion", nombreDeLigneAffectees);
        
        command.CommandText = command2;
        nombreDeLigneAffectees = (int)command.ExecuteNonQuery();
        AfficherTableDept(DbSELECTCom, "suppression", nombreDeLigneAffectees);
    }
}

public static void AfficherTableDept(DbCommand commande, string operation, int ligne)
{
    Console.Write("Table DEPARTEMENTS après " + operation + ": ");
    Console.WriteLine(ligne + " ligne(s) affectée(s)");
    Console.WriteLine("----------------------------------");

    using (DbDataReader ddr = commande.ExecuteReader())
    {
        while (ddr.Read())
        {
            Console.WriteLine(ddr.GetString(0) + "\t\t" + ddr.GetString(1));
        }
    }

    Console.WriteLine("\n");
}

La méthode AfficherTable est utilisée pour afficher la table DEPARTEMENTS après une opération et le nombre de lignes affectées par une requête. Ce programme fournit le résultat suivant:

Table DEPARTEMENTS après insertion: 1 ligne(s) affectée(s)
----------------------------------
BIO             Biologie
INF             Informatique
MAT             Mathématiques
PHY             Physique


Table DEPARTEMENTS après suppression: 1 ligne(s) affectée(s)
----------------------------------
BIO             Biologie
MAT             Mathématiques
PHY             Physique

Utilisation d'un cache pour stocker des données

Comme dans le premier exemple, créeons un programme qui affiche la liste des personnes (nom, prénom) de la table PERSONNES.

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

...

static void Main()
{
    string stringCommand = "SELECT * FROM PERSONNES";
    string stringConnection = "SERVER=localhost\\sqlexpress; DATABASE=UNIVERSITE; TRUSTED_CONNECTION=yes";
    
    // Créer un cache de données en mémoire.
    DataSet ds = new DataSet();

    using (DbDataAdapter dDAdapter = new SqlDataAdapter(stringCommand, stringConnection))
    {
        dDAdapter.Fill(ds);
    }

    // Créer une table de données en mémoire.
    DataTable dt = ds.Tables[0];

    foreach (DataRow dr in dt.Rows)
        Console.WriteLine(dr["Prenom"] + " " + dr["Nom"]);
}

On obtient:

Jerry Goley
Alain Provist
Jean Némarre
Brice Denisse

Contrairement à notre premier exemple, nous ne lisons pas directement les données dans la table PERSONNES. Nous utilisons la classe DataSet qui permet de créer un cache de données en mémoire. Pour remplir ce cache, nous faisons appel à la méthode Fill de la classe SqlDataAdapter. Cette classse représente un ensemble de commandes et une connexion, utilisés pour remplir un cache. Enfin, nous instancions la classe DataTable afin de créer une table de données en mémoire pour récupérer notre table. En regardant bien notre code, il reste une importante remarque à faire. Dans notre premier exemple, nous sommes connectés à la base lorsque nous affichons les données de la table PERSONNES. Ici, nous nous connectons uniquement pour remplir notre cache et les données sont traitées en mode déconnecté. Ce mode correspond à de nombreuses situations dont celle que nous venons de voir où l'application récupère des données de la base, puis se déconnecte de la base pour traiter les données. Un autre cas serait celui où l'application se reconnecterait à la base pour envoyer des données qu'elle aurait exploité et modifié. Au contraire, l'application travaille en mode connecté si elle reste toujours connectée à la base, en envoyant les données au fur et à mesure qu'elles sont traitées.

Voici donc un minuscule aperçu des possibilités qu'offre ADO.NET 2.0 pour exploiter les bases de données dans vos développements. Pour aller bien plus loin, je vous laisse découvrir l'espace de noms System.Data sur le site de Microsoft.

Les bases de donées Microsoft c'est aussi:

• le bilan de la session d'Aix-en-Provence des SQL Days

C-O - Jeudi 25 Mai 2006