Comment utiliser une base SQLite
Présentation
SQLite est sans aucun doute le système de gestion de base de données relationnelles (SGBDR) embarqué et open source le plus connu. Écrit en C, SQLite ne requiert aucune configuration et presque aucune librairie externe pour fonctionner, si ce n'est un compilateur C. Aucun serveur n'est nécessaire, contrairement aux autres SGBDR. Avec SQLite, les applications (via Windows) accèdent directement aux fichiers représentant la base de données sur le disque. La communication entre les deux parties est donc immédiate et très performante. SQLite est également transactionnel, c'est-à-dire qu'il respecte les principes ACID : opérations atomiques, consistance des données, isolation des processus et longévité des données. Enfin, SQLite est public et donc gratuit d'utilisation, pour les projets personnels comme les projets commerciaux.
SQLite a beau être un outil pratique et très puissant, elle n'en reste pas moins une base de données embarquée. Aussi, si vous souhaitez pouvoir créer des procédures stockées, faire des requêtes avec des jointures sur des centaines de tables ou gérer les accès aux différents éléments de la base, vous devrez vous orienter vers une autre solution. Si, en revanche, vous souhaitez créer quelques tables, stocker des données tout en assurant leur intégrité, et y accéder rapidement, SQLite répondra parfaitement à vos attentes.
Au sein d'une application Windows Store, SQLite peut être utilisé pour plusieurs scénarios :
Créer un système de cache : plutôt que de sérialiser les données de vos applications sous forme XML ou JSON, vous pouvez utiliser SQLite et LINQ pour interroger les données stockées en base.
Créer un mode offline et effectuer une synchronisation avec un serveur : dans le cas d'applications business, il y a fort à parier que votre client vous demande d'utiliser une base de données existante (SQL Server, Oracle ou autre). Dans ce cas, vous pourrez utiliser SQLite pour doter votre application d'un mode offline en stockant les données récupérées dans la base de données de l'entreprise. Vous pourrez par la suite améliorer votre application en créant un mécanisme de synchronisation lorsque votre application repasse online.
D'un point de vue technique, l'utilisation de SQLite peut s'apparenter à l'utilisation d'Entity Framework Code First. Cela signifie que vous allez devoir dans un premier temps créer des classes représentant vos objets métier, puis leur associer des attributs permettant de définir leur fonction (clé primaire, auto incrément, longueur des champs, etc.). Par la suite, les tables sont créées dynamiquement en fonction de ces propriétés. Les données peuvent ensuite être récupérées en exécutant des requêtes SQL. Plusieurs wrappers ont été développés et permettent d'utiliser LINQ et d'exécuter des méthodes asynchrones pour ajouter, modifier ou supprimer des données.
Utilisation de SQLite
1 Création du modèle de données
La première étape pour utiliser une base SQLite est de créer le modèle de données qui sera utilisé. Ce modèle doit être représenté par un ensemble de classes qui correspondront par la suite aux tables de votre base de données. Les propriétés de ces classes représenteront donc les colonnes de vos tables.
Plusieurs attributs peuvent être associés aux classes et propriétés de vos modèles afin de définir leur comportement. Voici une liste exhaustive de ces attributs :
TableAttribute : identifie une table. Possède un paramètre Name permettant de définir le nom de la table à créer.
ColumnAttribute : identifier une colonne. Possède un paramètre Name permettant de définir le nom de la colonne à créer.
PrimaryKeyAttribute : identifie la clé primaire de la table.
AutoIncrementAttribute : définit un champ à auto-incrémenter. Ce champ doit être un entier et doit également posséder l'attribut Primary-Key.
IndexedAttribute : définit un champ sur lequel un index doit être créé.
IgnoreAttribute : définit une propriété à ignorer lors de la création de la table.
UniqueAttribute : définit une colonne où chaque valeur doit être unique.
MaxLengthAttribute : définit la taille maximale d'une donnée qu'une colonne peut accepter.
CollationAttribute : définit l'interclassement d'une colonne. Possède une propriété Value.
L'exemple ci-dessous montre la création d'une classe Recette possédant un ensemble d'attributs permettant de définir le comportement de la classe et de ses propriétés lorsqu'ils seront traités par SQLite.
[Table("Recette")]
public class Recette
{
[PrimaryKey]
[AutoIncrement]
public int ID { get; set; }
[MaxLength(200)]
public string Nom { get; set; }
[Column("Illustration")]
public string Image { get; set; }
public int TempsPreparation { get; set; }
[Ignore]
public int TempsCuisson { get; set; }
[Indexed]
public int NombrePersonnes { get; set; }
}
N'oubliez pas que SQLite ne prend pas en charge les types complexes. Aussi, si vous souhaitez établir une référence d'une table vers une autre table, vous devrez passer par une propriété contenant un ID (clé étrangère) dans la table de référence.
2 Initialisation de la base et création des tables
Une fois le modèle défini, il faut créer la base de données et les tables. L'initialisation se fait en créant une connexion à une base de données. Si, lors de cette connexion, la base de données n'existe pas, celle-ci est aussitôt créée. La connexion à une base de données SQLite est représentée par la classe SQLiteAsyncConnection. Son constructeur prend en paramètre le chemin où doit être stockée la base de données. Dans une application Windows Store, il est conseillé de choisir le LocalFolder pour cela. Si vous optez pour le RoamingFolder, gardez à l'esprit que les données synchronisées sont limitées à 100 Ko.
private SQLiteAsyncConnection _connexion;
private void CreerBaseDeDonnees()
{
string chemin =
Path.Combine(ApplicationData.Current.LocalFolder.Path,
"db.sqlite");
_connexion = new SQLiteAsyncConnection(chemin);
}
En toute logique, la connexion à la base de données doit être effectuée avant toute autre action. Il est donc conseillé d'effectuer ces étapes le plus tôt possible dans le cycle de vie de l'application, dans la méthode OnLaunched de la classe App par exemple.
La connexion est l'élément qui vous permettra par la suite de créer ou récupérer des données. Il est donc important de garder une référence unique à cette connexion durant toute la vie de l'application. Il est par exemple possible de créer une variable statique de type SQLiteAsyncConnection dans la classe App.
Créer une table se fait grâce à la méthode CreateTableAsync. Cette méthode est générique. Le type qui lui est passé correspond à la table à créer.
private async Task CreerTablesAsync()
{
await _connexion.CreateTableAsync<Recette>();
}
Dans l'exemple précédent, une table basée sur le modèle Recette est créée. Lors de cette opération, le moteur SQLite regarde les attributs de la classe Recette pour créer une table correspondant aux attentes.
3 Insertion de données
L'insertion des données peut se faire par groupe ou séparément grâce aux méthodes suivantes :
InsertAsync : prend en paramètre un objet de type Object. Lorsque cette méthode est exécutée, le type de l'objet passé en paramètre est récupéré et est inséré dans la table correspondante. Si aucune table n'existe pour l'objet passé en paramètre, une exception est levée. Cette méthode retourne un entier indiquant le nombre de lignes créées.
InsertAllAsync : prend en paramètre un IEnumerable. La liste d'objets passés en paramètres est insérée de la même façon qu'avec la méthode InsertAsync. Cette méthode retourne un entier indiquant le nombre de lignes créées.
Le code suivant utilise ces deux méthodes pour insérer des données. À l'exécution, aucune n'est plus performante que l'autre.
private async Task InsererDonneesAsync()
{
Recette recette = new Recette()
{
Nom = "Poulet au curry",
NombrePersonnes = 4,
TempsCuisson = 10,
TempsPreparation = 20,
Image = "ms-appx:///Images/pouletcurry.png",
};
IEnumerable<Recette> recettes = new List<Recette>
{
new Recette
{
Nom = "Moelleux au chocolat",
NombrePersonnes = 6,
TempsCuisson = 20,
TempsPreparation = 15,
Image = "ms-appx:///Images/moelleuxchocolat.png"
},
new Recette
{
Nom = "Filet mignon",
NombrePersonnes = 5,
TempsCuisson = 30,
TempsPreparation = 20,
Image = "ms-appx:///Images/filetmignon.png"
}
};
await _connexion.InsertAsync(recette);
await _connexion.InsertAllAsync(recettes);
}
Une fois les données insérées, celles-ci sont persistantes. Cela signifie que vous pouvez redémarrer votre application, et même votre ordinateur, elles seront toujours présentes. Attention toutefois, si vous désinstallez votre application et que la base de données est stockée dans l'espace de stockage de l'application, l'ensemble des données sera supprimé.
4 Récupération de données
Grâce au package NuGet sqlite-net, les données insérées en base peuvent être récupérées en utilisant une syntaxe proche de LINQ. Récupérer une table se fait grâce à la méthode générique Table de la classe SQLiteAsyncConnection. Cette méthode renvoie un objet de type AsyncTableQuery, générique également. Cette classe possède les méthodes suivantes :
Where : permet de filtrer les résultats.
Skip : permet de sauter les n premiers résultats renvoyés.
Take : permet de conserver les n premiers résultats renvoyés.
OrderBy / OrderByDescending : permet d'appliquer un ordre croissant ou décroissant à une propriété des résultats retournés.
ElementAtAsync : permet de récupérer un élément situé à un certain index.
CountAsync : compte le nombre de résultats renvoyés.
First : renvoie le premier résultat retourné. Lève une exception s'il n'existe pas.
FirstOrDefault : renvoie le premier résultat retourné ou une valeur null s'il n'existe pas.
ToListAsync : retourne les résultats en tant que List<T>.
La méthode suivante renvoie ainsi la liste de toutes les recettes stockées en base, sous forme de liste triée par nom.
private async Task RecupererRecettesAsync()
{
var recettes = await _connexion.Table<Recette>()
.OrderBy(r => r.Nom)
.ToListAsync();
}
La méthode suivante, elle, retourne simplement la première recette ou à défaut la valeur null.
private async Task<Recette> RecupererPremiereRecette()
{
return await _connexion.Table<Recette>()
.FirstOrDefaultAsync();
}
Cette syntaxe permet de récupérer facilement toutes les données que vous souhaitez.
5 Mise à jour de données
La mise à jour d'une ligne se fait grâce à la méthode UpdateAsync de la classe SQLiteAsyncConnection. Cette méthode prend en paramètre un objet de type Object. Cet objet doit correspondre à une ligne qui existe déjà en base.
private async Task MiseAJourDonneesAsync()
{
Recette recette = await this.RecupererPremiereRecette();
recette.NombrePersonnes = 6;
await _connexion.UpdateAsync(recette);
}
Lors de l'exécution de la méthode UpdateAsync, la table correspondant à cet objet est récupérée. L'objet est ensuite mis à jour en fonction de sa clé primaire.
6 Suppression de données
La suppression d'une ligne se fait de la même façon que la mise à jour. La méthode DeleteAsync prend un paramètre un objet de type Object. Cet objet doit également correspondre à une ligne existante en base.
private async Task SupprimerDonneesAsync()
{
Recette recette = await this.RecupererPremiereRecette();
await _connexion.DeleteAsync(recette);
}
Lors de l'exécution de la méthode DeleteAsync, la table correspondant à cet objet est récupérée. L'objet est ensuite supprimé en fonction de sa clé primaire.
7 Suppression de tables
Pour certaines raisons, il est possible que vous deviez supprimer une table pour la recréer par la suite. Pour cela, utilisez la méthode DropTableAsync de la classe SQLiteAsyncConnection. Cette méthode est générique. Le type qui lui est passé correspond à la table à supprimer.
private async Task SupprimerTableAsync()
{
await _connexion.DropTableAsync<Recette>();
}
Lors de la suppression d'une table, l'ensemble des données de cette table est également supprimé. Lorsque vous effectuez ce genre d'actions, pensez aux éventuelles clés étrangères présentes dans d'autres tables qui pourraient mettre à mal l'intégrité de vos données.
Cet article est constitué à partir de bonnes feuilles issues de l'ouvrage "C# et XAML sous Windows 8.1" de Nathanaël Marchand et Loïc Rebours publié aux éditions Eni.