Consulter et executer des requêtes SQL sur une base SQL CE

Depuis Windows Phone 7 Mango nous avons à notre disposition une base SQL CE et pour pouvoir interagir avec celle-ci il faut utiliser Linq To SQL. Cette façon de fonctionner est très pratique lorsque l’on développe mais parfois on est confronté à des bugs pour lesquels on aimerait pouvoir exécuter du code SQL sur la base de données afin de vérifier les conditions de jointures, les groupements ou tout simplement son contenu.

Dans cet article je vais vous présenter une méthode pour accéder à cette base de données et l’extraire du téléphone afin de pouvoir exécuter des requêtes SQL.

Extraction de la base de données

La première étape consiste à récupérer la base de données contenue dans le téléphone ou dans votre émulateur. Attention cependant, afin que cette méthode fonctionne il ne faut pas que la base de données ait été cryptée. Comme les fichiers SQL CE sont stockés dans l’IsolatedStorage de l’application l’utilisation d’un outil pour y accéder est nécessaire. Dans cet article j’utilise les Windows Phone Power Tools.

Tout d’abord il faut lancer l’outil et choisir l’appareil ou l’émulateur cible.

SqlCe1

Une fois votre choix effectué il faut aller dans la liste des applications. Notez bien que seules les applications installées hors du Store comme avec Visual Studio sont accessibles.

SqlCe2

Localisez le fichier .sdf dans l’IsolatedStorage, c’est la base de données que l’on cherche. Ensuite il ne reste qu’à sélectionner le fichier et appuyer sur GET pour le récupérer. Dans mon cas j’ai sauvegardé le fichier sur le bureau.

Lecture de la base de données

Maintenant que la base de données se trouve sur le pc il faut pour la lire. Pour cela il faut installer SQL Server Compact Toolbox. Cet outil va ajouter une entrée dans la section Outils de votre Visual Studio.

SqlCe3

En cliquant sur le menu tel montré ci-dessus et vous accèderez à l’extension.

SqlCe4

Sélectionnez le nœud SQL Server Compact Data Connections et faites un clic droit dessus pour pouvoir ajouter le fichier .sdf.

SqlCe5

En cliquant sur « Browse » et l’explorateur Windows s’ouvre pour que vous puissiez sélectionner votre fichier (ici sur le bureau)

SqlCe6

Une fois ceci fait il n’y a plus qu’à cliquer sur OK et vous constaterez qu’un nouvel élément a été rajouté à l’arbre précédent.

SqlCe7

Vous pouvez ainsi voir les différentes table de la base, les clefs, les index etc…
Pour lister le contenu d’une table sélectionnez-la et faites un clic droit.

SqlCe8

Le menu qui s’ouvre vous permet de choisir entre plusieurs opérations possibles. Ici nous choisirons « Script as SELECT » pour ouvrir une console SQL avec une requête de sélection pré-remplie.

SqlCe9

Il ne reste maintenant plus qu’à cliquer sur « ! Execute » pour avoir le résultat. Biensûr il est possible de modifier cette requête.

Conclusion

Grâce à ces outils nous pouvons maintenant exécuter toutes les requêtes dont nous avons besoin et donc de pouvoir vérifier la cohérence des données de la base ou encore de tester des requêtes sans avoir à débuguer depuis le téléphone à chaque fois.

Liens utiles

Focus sur SQL CE for Windows Phone

Introduction

Avec le déploiement de Windows Phone 7.5 (Mango), une nouvelle fonctionnalité très attendue est venue enrichir l’environnement de développement sur Windows Phone 7 : la base de données locale.

Depuis son lancement, de nombreux développeurs se sont heurtés à des problèmes de performance en utilisant cette version de SQL Compact Edition spécialement dédiée à Windows Phone.

Cet article vise à rassembler et à organiser un certain nombre d’astuces et de bonnes pratiques glanées sur internet dans le but de tirer le meilleur de cette base en terme de performance.

Bref historique de SQL Compact Edition for Windows Phone

Initialement disponible et éprouvé sur Windows mobile 6.5 , SQL CE Mobile brillait par son absence sur Windows Phone 7.

Plusieurs raisons pouvaient expliquer une telle situation :

  • Véritable volonté de réduire au maximum le stockage de grande quantité de données sur le téléphone au profit de synchronisation avec serveur distants?
  • Manque de temps à l’issue du gigantesque chantier qui a mené MS à concevoir Windows Phone 7 en un peu plus d’un an seulement?

Quelles qu’en soient les raisons, le besoin s’est rapidement fait sentir pour le développement d’applications en tout genre et la communauté de développeurs Windows Phone s’est rapidement mobilisée pour proposer des solutions.

En voici une liste non exhaustives :

La mise à disposition de SQL CE for Windows Phone a sonné le glas de bon nombre de ces projets. En effet, l’intérêt de solutions managées semblent limité face à ce port natif de SQL CE 3.5 spécialement conçu pour Windows Phone et officiel qui plus est.

Des alternatives sur Windows Phone 7?

Pour autant et à l’image de Sterling, les développements et la popularisation de certains projets se sont poursuivis car ils se sont démarqués pour des usages bien particuliers.

Sterling avec son orientation NoSQL et le soin tout particulier apporté à l’optimisation des opérations de sérialisation / désérialisation se distingue par exemple par sa simplicité d’utilisation et ses performances en lecture.

Néanmoins sur Windows Phone 7 et parmi les solutions existantes, SQL CE de par son implémentation native parait être le choix le plus adapté lorsqu’il s’agit de travailler sur de grande quantité de données à la fois en lecture et en écriture.

Pour une comparaison entre SQL CE for Windows Phone et Sterling, rendez vous à cette adresse.

Astuces et bonnes pratiques générales

Gardez un œil sur les requêtes soumises à la base

L’une des première surprise rencontrée lors de l’utilisation de SQL CE for Windows Phone est que l’exécution directe de Transact-SQL n’est pas supporté.

Le recours à LINQ to SQL sur Windows Phone est donc obligatoire pour communiquer avec sa base de données locale relationnelle. Ce dernier a la responsabilité de traduire les requêtes de LINQ vers Transact-SQL et de matérialiser les résultats retournés par la base.

Pour éviter l’effet boîte noire, il est possible de logger les requêtes générées par LINQ to SQL et soumises à la base.

Pour ce faire, tout se joue au niveau de l’objet DataContext qui comporte une propriété Log. Si cette dernière est affectée à un StreamWriter, toute l’activité du DataContext concerné sera loggée par son intermédiaire.

using (var context = new MyDataContext(MyDataContext.DBConnectionString))
using (context.Log = new StreamWriter(IsolatedStorageFile.GetUserStoreForApplication().CreateFile("logs.txt")))
{
    // Ecrivez ici les insert, update, delete dont vous avez besoin...
    context.SubmitChanges();
}

Il ne vous reste plus qu’à récupérer le fichier de log de votre émulateur ou téléphone vers votre PC à l’aide d’outils de développement spécifiques tels que IsoStorageSpy ou encore Windows Phone Power Tools.

Chiffrez votre base dégrade les performances

SQL CE for Windows Phone supporte le chiffrement des données contenues, il vous suffit d’ajouter le paramètre Password dans votre connection string. Mais n’oubliez pas que cette sécurité a un prix en terme de performance : des opérations supplémentaires de chiffrement et déchiffrement viendront respectivement s’ajouter à chaque opération d’écriture et de lecture en base.

Maitrisez la durée de vie de votre DataContext

Pour faire face aux problèmes de performance rencontrés avec SQL CE for Windows Phone, le DataContext présente un atout majeur  : son cache.  Les résultats des requêtes effectuées avec un DataContext donné sont conservés et réutilisés par la suite pour limiter les allers et retours avec la base. Ce mécanisme permet ainsi d’accélérer les prochaines requêtes pointant sur des données déjà chargées en mémoire.  Pour résumer, plus le cache de votre DataContext contient d’objets, plus les chances sont grandes pour que les données que vous souhaitez obtenir soient déjà chargées en mémoire, plus les résultats vous seront retournés rapidement.  Afin de tirer parti au maximum de ce cache, la tentation est donc grande de ne créer qu’une seule instance de DataContext pour l’ensemble de votre application.
Cependant, il peut être intéressant de rappeler certaines contraintes qui s’appliquent à notre cher DataContext :
  • Votre application Windows Phone 7 ne peut occuper plus de 90 mo en mémoire. A cause de son cache, votre instance de DataContext pourrait bien dépasser ce quota si vous n’y prenez pas garde.
  • Une instance de DataContext n’est pas thread-safe, les accès concurrents produisent une InvalidOperationException. En revanche, SQL CE supporte les accès concurrents de plusieurs DataContexts différents.
Ces limitations ne plaident pas en faveur d’une instance de DataContext unique. Les pistes plus pérennes pourraient être les suivantes :
  • Suivre les bonnes pratiques LINQ to SQL et Entity Framework : Regroupez et encapsulez les accès à la base dans des classes et des méthodes dédiées en fonction de vos besoins techniques et/ou fonctionnels. Instanciez et détruisez un nouveau DataContext dans chaque méthode (avec un using), ils sont conçus pour avoir une durée de vie assez courte. Pour plus d’informations, voir ici.
  • Définir des instances de DataContext pour des usages bien spécifiques et garantir un accès synchronisé à chacune d’entre elle.
Il n’existe pas de solution idéale universelle, tout dépend de vos besoins et de la complexité de votre application.

Veillez à toujours effectuer vos appels à la base sur un thread en arrière plan

Ce n’est certainement ni la première, ni la dernière fois que vous le lisez : Il est important de ne pas bloquer votre thread UI avec des traitements lourds.

Ceci a pour conséquence de bloquer l’affichage de votre application, comportement qui mène à de nombreux points négatifs :

  • Utilisateurs frustrés ne pas savoir ce qu’il se passe
  • Impression générale de manque de finitions
  • Clicks compulsifs jusqu’à ce que « quelque chose se passe » qui empirent la situation!

De plus pour les traitements massifs sur la base, il semblerait que l’utilisation d’un thread en arrière plan accélère assez significativement les choses, voir ce lien.

Améliorer les temps d’exécution en lecture

Stockez sous forme de blobs lorsque que la situation s’y prête

Dans certains cas, il est plus rentable de stocker un bloc entier de données sérialisées (en XML ou binaire) directement en base sous la forme d’un blob dans une colonne de type text, varbinary, binary par exemple.

Cela permet de limiter autant le nombre de requêtes et d’allers et retours avec le base que la complexité et la quantité de données à requêter sont grandes.

Créez des index sur les propriétés utilisées fréquemment

Un index est automatiquement crée pour chaque colonne de la base définie en tant que clé primaire. Mais vous pouvez également créer vos propres index sur les propriétés souvent utilisées dans vos requêtes LINQ, y compris celles utilisées pour trier les résultats des requêtes.

[Index(Column=”OrderID ASC, Quantity DESC”)]

Compilez vos requêtes LINQ

Lors de chaque exécution de requêtes LINQ, LINQ to SQL traduit l’arbre d’expression défini vers le code Transact-SQL correspondant. Ce mode de fonctionnement s’avère peu optimal pour les requêtes LINQ qui sont exécutées de nombreuses fois au cours de la durée de vie de l’application.

Pour éviter que cette opération de traduction ne soit exécutée encore et encore, il est possible de compiler votre requête LINQ à l’aide de CompiledQuery.Compile.

Cette méthode produit une requête Transact-SQL paramétrée qu’il vous sera possible de réutiliser avec des paramètres d’entrés différents par l’intermédiaire de la Func retournée.

Effectuez les jointures par vos propres moyens?

Cela peut sembler étrange, mais lorsque l’on travaille avec de grandes volumétries, récupérer les données dont vous avez besoin avec des requêtes très simples puis effectuer les jointures par ses propres moyens se révèle bien souvent plus efficace que de laisser faire SQL CE. A condition bien sûr, que la quantité de données ne soit pas excessive! (quota de 90 mo par application sur Windows Phone 7)

Le post suivant y fait d’ailleurs référence dans sa conclusion.

Améliorer les temps d’exécution en écriture

Ajoutez la colonne RowVersion pour améliorer les updates et deletes

Avant d’effectuer des updates ou des deletes, LINQ to SQL doit s’assurer qu’il n’y pas de conflits dus à des accès concurrentiels.

Afin de vérifier que les entités reflètent bien les valeurs présentes en base en moment du SubmitChanges, ces opérations sont effectuées avec une clause where supplémentaire qui vérifie les valeurs de chacune des colonnes. Cette dernière alourdit donc autant les requêtes que la table concernée contient de colonnes.

Pour éviter ces traitements supplémentaires, il est possible de définir une colonne de type RowVersion.

[Column(IsVersion=true)]
private Binary _version;

Lorsqu’elle est présente, LINQ to SQL se base uniquement sur la valeur contenue dans ce champ pour déterminer si une entrée a changé ou non et la clause where supplémentaire, devenue inutile, disparaît.

Pour plus d’informations sur le sujet, rendez-vous ici et ici.

Quelques pistes pour améliorer les performances des inserts

Pour optimiser un scénario dans lequel une grande quantité de données doit être insérée en base vous pouvez utiliser le paramètre « Max Buffer Size ». Par défaut, sa valeur est de 384 ko et 5120 ko au maximum. Ce paramètre indique à la base la plus grande quantité de données qu’elle peut stocker en mémoire avant de l’écrire sur le disque, 1024 KB semble être un bon compromis. Attention toutefois à ce pas trop l’augmenter, au delà d’une certaine valeur, cela n’aura plus d’impacts positifs mais négatifs! Pour plus d’informations voir cet article.

La présence de la colonne RowVersion fait gagner du temps sur les opérations d’updates et de deletes. A l’inverse, dans le cas d’un insert, RowVersion constitue une donnée de plus à insérer en base et à générer par LINQ to SQL. Si vous souhaitez privilégier les inserts sur les updates/deletes et les accélérer sensiblement, supprimez cette colonne. A noter que cette optimisation ne concerne à nouveau que les cas d’insertions massifs de données. Pour plus d’informations voir ce post traduit.

Privilégiez l’utilisation de InsertAllOnSubmit à InsertOnSubmit pour de nombreux inserts consécutifs

Enfin, si vous cherchez à injecter rapidement une grande quantité de données dans votre base, sachez qu’il existe un portage de SqlBulkCopy pour Windows Phone 7.5, vous le trouverez à cette adresse.

Minimiser la consommation mémoire

Désactivez le suivi de modifications lorsqu’il n’est pas utile

Par défaut, vos instances de DataContext sont configurées de manière à ce qu’une copie de toutes les entités issues de la base soit effectuée et conservée. Ces copies originales sont utilisées par la suite lors de l’appel à la méthode SubmitChanges pour déterminer ce qui a changé et générer les requêtes Transact-SQL adéquates (insert, update, delete etc…).

Ce mécanisme peut être désactivé pour les scénarios dans lesquels les données sont consultables et non modifiables et ainsi économiser la mémoire utilisée par ces copies. Pour ce faire, assignez false à la propriété ObjectTrackingEnabled de votre DataContext courant.

Implémentez l’interface INotifyPropertyChanging

L’interface INotifyPropertyChanging permet de modifier quelque peu la manière dont LINQ to SQL assure le suivi des modifications afin de réduire l’empreinte mémoire de votre application.

Sans implémentation de cette interface sur vos objet POCOs liés à LINQ to SQL, ce dernier est contraint de dupliquer toutes les entités lues à partir de la base de données afin d’en conserver une copie originale. Lors de l’appel à la méthode SubmitChanges, il détermine si une entité a changé et qu’est-ce qui a changé en comparant ses valeurs actuelles avec celles de sa copie originale.

En revanche, si l’interface INotifyPropertyChanging est implémentée, LINQ to SQL est directement informé de la modification des entités et seules ces dernières sont dupliquées. Ce qui réduit grandement le nombre de copies effectuées.

Conclusion

Après s’être longtemps fait désirée, Mango apporte enfin une solution de stockage de données native sur Windows Phone avec une version SQL CE spécialement dédiée à Windows Phone.

Malgré ses défauts et certaines alternatives viables, SQL CE for Windows Phone constitue une solution particulièrement adaptée pour les applications amenées à travailler sur de grandes quantités de données à la fois en lecture et en écriture sur Windows Phone 7. D’autant qu’iI existe bon nombres de solutions et d’astuces pour améliorer les temps d’exécutions ainsi que l’empreinte mémoire de l’indissociable couple SQL CE/LINQ to SQL.

L’arrivée de Windows Phone 8 souffle un vent nouveau dans le domaine du stockage de données sur les appareils mobiles Microsoft. Avec la possibilité de développer directement en natif, d’autres solutions de stockage, jouant désormais à armes égales avec SQL CE, pourraient bien faire leur apparition et représenter de très sérieuses alternatives. SQLlite for Windows Phone 8 en est déjà un exemple concret. Si vous souhaitez en savoir plus sur le sujet, je vous invite à consulter cet article ou encore celui-ci.