(Ce billet fait suite à l’article : Abstraction des Accès BDD dans une Factory )
Entrons un peu plus dans le détail maintenant que nous avons une vue générale de ce que Microsoft à mis à notre disposition. nous allons donc réaliser une Classe nommée ConnectDb encapsulant l’utilisation des types vus précédemment :
La classe ConnectDb contient un ensemble de méthodes statiques qui couvrent les opérations de base pour accéder à une base de données.
Personnellement j’ai choisi de la définir en abstract puisque toutes ses méthodes sont statiques et qu’elle n’a pas vocation à être instanciée. On aurait pu mettre le constructeur par défaut en privé, le résultat serait le même, je pense que c’est surtout une question de goût…
Voyons en détail l’implémentation de cette classe.
Implémentation
Gestion des paramètres de connexion
Pour ma part j’ai choisi de stocker la chaîne de connexion, ainsi que le provider choisi dans un fichier de configuration (Pour savoir comment créer votre propre fichier de configuration,vous pouvez consulter ce billet).
Voici à quoi ressemble mon fichier App.config :
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="MyDefaultConnectionString"
connectionString="Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=MyDataBase;Integrated Security=True;Pooling=False"/>
</connectionStrings>
<appSettings>
<add key="MyDefaultProvider" value="System.Data.SqlClient"/>
</appSettings>
</configuration>
Pour mon exemple j’ai choisi d’utiliser une base de données SQL Server et le provider correspondant : Sytem.Data.SqlClient . Bien entendu, la suite du code fonctionnera avec n’importe quel provider implémentant les Types abstraits du namespace System.Data.Common.
static readonly String _connectionString;
static readonly String _provider;
static ConnectDb()
{
_connectionString = ConfigurationManager.ConnectionStrings["MyDefaultConnectionString"].ConnectionString;
_provider = ConfigurationManager.AppSettings["MyDefaultProvider"].ToString();
}
J’ai défini un constructeur statique pour récupérer les valeurs de la chaîne de connexion et le provider choisi depuis le fichier de configuration. Un constructeur static est appelé automatiquement à la première référence du type. Il ne nécessite donc pas d’instanciation explicite .
Utilisation de la factory
La récupération de la factory pour un provider spécifique se fait à l’aide de la classe DbProviderFactories. Pour ma part j’ai choisi de l’encapsuler dans une Propriété “Factory” :
protected static DbProviderFactory Factory
{ get
{
return DbProviderFactories.GetFactory(_provider);
}
}
A ce stade nous disposons donc d’une instance d’un type implémentant DbProviderFactory, spécifique au provider que nous avons choisi. (dans mon cas il s’agit en interne d’une instance de System.Data.SqlClient.SqlClientFactory).
Parmi l’ensemble des méthodes que nous propose l’objet DbProviderFactory , nous n’en utiliserons que deux dans le cadre de cet article :
//Crée une instance d'un objet de type DbCommand
Factory.CreateCommand();
//Crée une instance d'un objet de type DbConnection
Factory.CreateConnection();
voyons comment les utiliser :
Connexion à la Base de données
Rien de bien compliqué ici, on appelle la méthode CreateConnection() de la factory, puis on affecte la Chaîne de connection à l’objet DbConnection que l’on a récupéré.
protected static DbConnection GetConnection()
{
DbConnection conn = Factory.CreateConnection();
conn.ConnectionString = ConnectionString;
return conn;
}
Attention, l’objet connexion retourné est FERME. la connexion à la base n’est donc pas active. J’ai choisit de ne pas retourné d’objet systématiquement ouvert pour ne pas risquer d’oublier de le refermer. Cela permet donc de garder le contrôle sur le pool de connexion ouvertes.
Manipulation des objets DbCommand
Création
Une fois la connexion à la base de donnée gérée, il ne reste plus qu’à envoyer les requêtes. C’est là que l’objet DbCommand entre en jeu. Il y a 2 façons de créer un objet commande: en utilisant la factory, ou en passant par un objet DbConnection :
public static DbCommand CreateCommand()
{
DbCommand cmd = Factory.CreateCommand();
return cmd;
}
public static DbCommand CreateCommand()
{
DbConnection conn = GetConnection();
DbCommand cmd = conn.CreateCommand();
return cmd;
}
Pour ma part je préfère la première version. C’est encore une fois assez discutable, la raison est que je préfère garder mes objets indépendants les uns des autres le plus longtemps possible.
Envoi d’une requête
Une fois notre Objet DbCommand créé nous pouvons lui affecter une requête simple à l’aide de la propriété CommandText (nous verrons par la suite comment faire pour créer des commandes paramétrées") :
DbCommand cmd = ConnectDb.CreateCommand();
cmd.CommandText = "SELECT Champ1, Champ2 FROM MATABLE WHERE Champ3='toto'";
Attention : La syntaxe des requêtes SQL peut différer d’un SGBD à l’autre, c’est pourquoi, si l’on veut conserver la souplesse de notre factory, l’affectation d’une requête SQL à une DbCommand doit être effectuée EN DEHORS de la classe ConnectDb, par exemple dans une classe de DAO classique.
Nous pouvons regrouper les différents types de requête en 3 catégories :
- Les requêtes de Sélection qui renvoient un ensemble de données (SELECT .. FROM… JOIN.. WHERE)
- Les ordres DDL qui renverront la plupart du temps un nombre d’enregistrements affectés (Update, Delete,Insert…)
- Les requêtes Scalaires qui retournent un champ unique dans un enregistrement unique (ex : “SELECT Count(*) FROM matable”, “SELECT ORACLE_SEQUENCE.NEXTVAL FROM dual”)
Typiquement nous pouvons donc créer 3 méthodes de requêtage pour gérer les différents cas. Le code de ces méthodes diffère assez peu, dans tous les cas, il nous faut récupérer un objet DbConnection, l’ouvrir et l’affecter à l’objet DbCommand contenant la requête.
Comme un exemple vaut mieux qu’un long discours, voici l’implémentation de ces fonctions :
public static DbDataReader ExecuteQuery(DbCommand cmd)
{
DbDataReader ret;
DbConnection conn = GetConnection();
conn.Open();
cmd.Connection = conn;
cmd.cr
ret = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return ret;
}
L’objet DbDataReader est un Curseur sur un ensemble d’enregistrement en provenance de la base de donnée en mode connecté, un peu à la manière des vieux recordset. c’est donc dans cet objet que sont renvoyés les enregistrements sélectionnés par la commande.
Le paramètre passé à la méthodes ExecuteReader() de l’objet DbCommand est une énumération qui permet de s’assurer que la connexion sera fermée en même temps que le DataReader. En effet, pour que ce dernier fonctionne correctement, la connexion sur laquelle il repose doit rester ouverte jusqu’au dernier moment. On ne peut donc pas fermer la connexion immediatement après la création du DataReader.
public static int ExecuteNonQuery(DbCommand cmd)
{
int ret;
using (DbConnection conn = GetConnection())
{
conn.Open();
cmd.Connection = conn;
ret = cmd.ExecuteNonQuery();
conn.Close();
return ret;
}
}
Ici le code est assez similaire à la méthode précédente, on notera cette fois la présence du bloc using autour de l’objet DbConnection. En effet Microsoft à eu la bonne idée d’implémenter IDisposable sur ces objets. Ainsi, dans le cas de l’objet DbConnection, on est sûr que quoiqu’il arrive, la méthode Dispose() sera appelée, et que la connexion à la base, fermée.
public static object ExecuteScalar(DbCommand cmd)
{
object ret;
using (DbConnection conn = GetConnection())
{
conn.Open();
cmd.Connection = conn;
ret = cmd.ExecuteScalar();
conn.Close();
return ret;
}
}
Encore une fois, le code ne diffère pas énormément de la méthode ExecuteQuery(), la seule chose à noter est que le type de retour de la méthode est de type object. il vous faudra donc penser lors de l’utilisation, à effectuer un cast sur la valeur retournée en fonction du type que vous attendez.
Maintenant que nous avons nos 3 méthodes, l’exécution d’une requête est simplissime :
ex:
//requête simple
DbCommand cmd = ConnectDb.CreateCommand();
cmd.CommandText = "SELECT * FROM Personne where prenom = ‘toto’";
DbDataReader reader = ConnectDb.ExecuteQuery(cmd);
while (reader.Read())
{
System.Console.WriteLine("Nom : {0} ,Prenom : {1} , mail : {2}",reader["nom"],reader["prenom"],reader["mail"]);
}
reader.Close();
Le parcours d’un objet DbDataReader se fait grâce à la méthode booléenne Read(). A la construction de l’objet le curseur pointe AVANT le tout premier enregistrement. A chaque appel de Read(), le curseur passe à l’enregistrement suivant, si l’on est arrivé à la fin du DataReader ou si la requête n’a aucun résultat , Read() renvoi False.
Pour accéder aux champs des enregistrement, vous pouvez soit utiliser la méthode “tableaux” à l’aide de crochets : reader[“nomColonne”] ou reader[NumColonne"], sans oublier de caster correctement les valeurs de retour.
Ou bien encore utiliser toute les méthodes reader.GetXXXX() pour récupérer directement une valeur typée.
Nous avons vu qu’il était très simple d’envoyer une requête à la base de données, cependant il peut parfois être utile de paramétrer une requête, c’est à dire, dissocier les valeurs des champs. Cela permet un traitement plus automatisé, plus optimisé et plus sûr des requêtes, notamment au niveau des attaques par injection de code SQL. Voyons ensemble comment écrire de telles requêtes.
Création d’une requête paramétrée
Créer une requête paramétrée n’est pas bien plus difficile que de créer une requête sans paramètre : reprenons un objet DbCommand, et voyons quelles autres méthodes s’offrent à nous :
Nous avons une Méthode CreateParameter() et une propriété Parameters qui contient la liste de tous les paramètres associés à cette commande.
Avant d’associer des paramètres à une DbCommand, il faut tout d’abord référencer ces paramètres dans la requête elle même. cela se fait grâce à un mot clé choisi par vous-même, précédé d’un symbole qui indiquera au provider que la chaîne de caractère suivante est un paramètre. Ce symbole varie d’un provider à l’autre, voici un tableau récapitulant ceux que je connais :
| Oracle |
‘:’ + NomParametre |
| SqlServer |
‘@’+ NomParametre |
| Access |
‘?’+ NomParametre |
Il nous faut ensuite créer les paramètres à proprement parler et leur donner leur valeur. rien de plus simple, on crée un objet DbParameter à l’aide de la méthode CreateParameter de la DbCommand et on affecte les propriétés suivantes :
-
ParameterName : chaîne de caractères correspondant au paramètre dans la requête (sans le symbole)
-
Value : valeur à affecter au paramètre
-
DbType (facultatif) : Type à affecter au paramètre
Puis on ajoute le paramètre créé à la Liste des paramètres de la commande. A partir de là la commande s’exécute exactement comme une commande normale, à l’aide des 3 méthodes que nous avons définies plus haut.
Exemple d’une requête d’insertion: on suppose que l’on dispose d’une table personne dans notre base de données :
//requête Parametrée
DbCommand cmd2 = ConnectDb.CreateCommand();
cmd2.CommandText = "INSERT INTO Personne (mail,nom,prenom)VALUES (@mail,@nom,@prenom)";
DbParameter mailParam = cmd2.CreateParameter();
mailParam.ParameterName = "mail";
mailParam.Value = "uneadressemail@kkpart.com";
cmd2.Parameters.Add(mailParam);
DbParameter nomParam = cmd2.CreateParameter();
nomParam.ParameterName = "nom";
nomParam.Value = "COVILLE";
cmd2.Parameters.Add(nomParam);
DbParameter prenomParam = cmd2.CreateParameter();
prenomParam.ParameterName = "prenom";
prenomParam.Value = "Thomas";
cmd2.Parameters.Add(prenomParam);
ConnectDb.ExecuteNonQuery(cmd2);
Et voilà, vous disposez désormais d’une classe complète permettant de gérer tout vos accès base en mode connecté, de façon totalement indépendante du SGBD choisi !
Nous pouvons cependant aller encore plus loin grâce à la généricité. En effet pourquoi se contenter d’utiliser un DbDataReader, quand on peut facilement disposer d’une Liste d’objets directement prêts à l’usage ? C’est ce que nous allons faire de suite :
La Methode Find<T>(…)
Implémentation
L’objectif de cette méthode, est d’obtenir, en se basant sur une DbCommand on ne peut plus classique, une liste d’objets de type T (générique donc).
Pour ce Faire nous allons utiliser la notion de delegate et plus particulièrement le Type Func<Targ0, TResult> ajouté dans le framework 3.5 . Grosso modo, c’est une référence vers une fonction qui prendrait un paramètre de type Targ0 et renverrait un objet de type TResult.
L’intérêt pour nous, c’est que nous allons donc pouvoir ajouter en paramètre de notre méthode Find, un delegate qui prendrait en Parametre un DbDataReader et renverrait un objet de Type T remplit avec les données du DbDataReader. Pour faire simple, le delegate serait un “traducteur” pour passer d’un DbDataReader, vers une List<T>.
Voici donc à quoi ressemble notre Méthode Find :
public static List<T> Find<T>(DbCommand cmd, Func<DbDataReader, T> ParseObject)
{
using (DbDataReader rs = ExecuteQuery(cmd))
{
List<T> retList = new List<T>();
while (rs.Read())
{
T oRow = ParseObject(rs);
retList.Add(oRow);
}
return retList;
}
}
Tout comme le type DbConnection, le type DbDataReader implémente IDisposable. On ne se privera donc pas de l’entourer d’une directive using, pour s’assurer que les connections à la base seront correctement fermées. A l’aide de la méthode ExecuteQuery() que nous avons codée précédemment, nous récupérons le DbDataReader correspondant à notre DbCommand.
Puis, pour chaque Enregistrement du DataReader, nous appelons le delegate passé en paramètre à notre méthode find (Parseobject()). Il ne nous reste plus qu’à ajouter l’objet de Type T à la List<T> et à retourner cette liste une fois le reader entièrement parcouru.
Très bien me direz vous, mais comment ça s’utilise ce machin ? démonstration :
Utilisation
Considérons tout d’abord que dans notre Base de données, nous avons une table Personne contenant les champs suivants :
Reprenons l’exemple de notre table Personne :
Nous réalisons une classe Personne dans notre programme pour la manipulation des données :
public class Personne
{
public String NomPrenom
public String Mail;
}
J’ai fait exprès de Concaténer dans ma Classe les champs Nom et Prenom et d’omettre le champ Age, pour bien montrer que les structure des classes du programme peuvent être différente de celles des tables de la base.
nous créons notre requête de sélection :
DbCommand cmd = ConnectDb.CreateCommand();
cmd.CommandText = "SELECT * FROM Personne";
Il ne reste plus qu’à appeler notre fameuse méthode Find() . Avant cela je dois introduire la notion d’Anonymous Delegate. Grâce à ce concept nous allons pouvoir passer directement à notre Func<DbDataReader,T>, un bloc de code qui sera interprété comme une fonction :
List<Personne> personnes = ConnectDb.Find(cmd, delegate(DbDataReader rs)
{
Personne p = new Personne();
p.mail = rs["mail"].ToString();
p.NomPrenom = rs["Nom"].ToString() + rs["prenom"].ToString();
return p;
});
Le code est donc dérisoirement simple, on passe donc en paramètre au find notre delegate et directement à la ligne, grâce au délégué anonyme nous mettons notre bloc de traitement.
Remarquez une chose assez intéressante, je n’ai pas spécifié le type générique utilisé pour la méthode Find. Alors que pourtant, le prototype de la fonction est bien Find<T>(…). En fait, le compilateur C# est “intelligent”, et déduit du code, le type générique utilisé et est capable de déterminer si il est correct ou non. Ce mécanisme s’appelle Inférence de Type.
Rentrons maintenant dans une amélioration introduite par le framework 3.5, et que permet d’utiliser Func<Targ,TResult> : Les lambda Expressions. L’explication détaillée des lambda expressions n’entre pas dans le cadre de cet article, je me permet juste de vous montrer comment aurait pu s’écrire l’appel à la méthode Find en utilisant ce mécanisme, libre à vous d’approfondir vos recherches sur ce sujet :
List<Personne> personnesLambda = ConnectDb.Find(cmd, rs => new Personne()
{
mail = rs["mail"].ToString(),
NomPrenom = rs["Nom"].ToString() + rs["prenom"].ToString()
});
Les lambda expressions reposent en grande partie sur l’Inférence de type dont je vous parlais plus haut. Décomposons les grandes lignes de ce code :
Tout d’abord le rs => new Personne(). J’ai volontairement conservé les nom des variables pour que l’exemple soit plus clair:
Par inférence de type, le compilateur déduit que la variable rs doit être de type DbDataReader. la flèche (=>) introduit ensuite une ligne de code, devant nécessairement retourner dans notre cas, un objet de Type Personne (toujours par inférence de type). Je n’ai donc qu’à instancier mon objet personne et à initialiser ses membres publics.
Ceci n’est qu’un exemple extrêmement simpliste de ce qu’il est possible de faire à l’aide des lambda Expressions, si vous souhaitez approfondir cette voie, je vous conseille d’effectuer des recherches sur LINQ introduit également au framework 3.5, au sujet duquel j’écrirais sans doute un article sous peu.
Conclusion
Nous avons donc désormais entre les mains, une classe permettant d’abstraire tous les accès à une base de données, assez souple pour interagir avec n’importe quel provider .NET implémentant les classes du namespace System.Data.Common.
Pour conclure, je joint à cet article le code source (librement utilisable, distribuable, commercialisable, etc) complet de cette classe avec les exemples que nous avons vu, et reste à votre entière disposition si vous avez des questions, ou simplement envie de débattre d’une autre méthode d’implémentation.
Notez bien que tout le code que nous avons écrit ne manipule QUE les objets abstraits du namespace System.Data.Common. EN AUCUN CAS la classe ConnectDb ne doit contenir de référence à un provider spécifique. Nous perdrions d’emblée tout l’intérêt de l’utilisation d’une factory.
