Windows UWP Data Access: SQLite

Introduction to SQLite in UWP

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is an excellent choice for local data storage in UWP applications due to its small footprint, ease of integration, and robust feature set.

This document provides a comprehensive reference for using SQLite with the Windows Universal Platform (UWP), covering installation, fundamental operations, and advanced techniques.

Setting Up SQLite

To use SQLite in your UWP project, you'll need to add the appropriate NuGet package. The most common and recommended package is Microsoft.Data.Sqlite.

Installing the NuGet Package

1. Open your UWP project in Visual Studio.
2. Right-click on the project in the Solution Explorer and select "Manage NuGet Packages...".
3. Navigate to the "Browse" tab.
4. Search for Microsoft.Data.Sqlite.
5. Select the package and click "Install".

The Microsoft.Data.Sqlite package provides ADO.NET compatible interfaces for interacting with SQLite databases, optimized for .NET Core and UWP.

Permissions

Ensure your application has the necessary capabilities enabled in the Package.appxmanifest file to access local storage. For most local database scenarios, the default capabilities are usually sufficient.

Basic SQLite Operations

Here's how to perform common database operations using Microsoft.Data.Sqlite.

Connecting to a Database

You can create a new database file or connect to an existing one. The database file is typically stored in the application's local folder.

using Microsoft.Data.Sqlite;
using System;
using System.IO;
using Windows.Storage;

public class DatabaseHelper
{
    public static SqliteConnection DbConnection { get; private set; }

    public static void InitializeDatabase()
    {
        var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "my_database.db");

        // Check if the database file exists. If not, create it.
        // Note: In a real app, you might want to manage schema creation more robustly.
        if (!File.Exists(dbPath))
        {
            // Creating the file here is implicit when opening a new connection.
            // However, explicit creation might be desired for initial setup.
        }

        DbConnection = new SqliteConnection($"Data Source={dbPath}");
        DbConnection.Open();

        // Example: Create a table if it doesn't exist
        using (var command = DbConnection.CreateCommand())
        {
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Items (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Description TEXT
                );";
            command.ExecuteNonQuery();
        }
    }

    public static void CloseDatabase()
    {
        if (DbConnection != null && DbConnection.State == System.Data.ConnectionState.Open)
        {
            DbConnection.Close();
            DbConnection.Dispose();
            DbConnection = null;
        }
    }
}
                

Inserting Data

Use INSERT statements to add new records.

using Microsoft.Data.Sqlite;

public static void InsertItem(string name, string description)
{
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "INSERT INTO Items (Name, Description) VALUES (@Name, @Description);";
        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Description", description ?? (object)DBNull.Value);
        command.ExecuteNonQuery();
    }
}
                

Querying Data

Use SELECT statements to retrieve data. You can iterate through the results using a SqliteDataReader.

using Microsoft.Data.Sqlite;
using System.Collections.Generic;

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public static List<Item> GetItems()
{
    var items = new List<Item>();
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "SELECT Id, Name, Description FROM Items;";
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                items.Add(new Item
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Description = reader.IsDBNull(2) ? null : reader.GetString(2)
                });
            }
        }
    }
    return items;
}
                

Updating Data

Use UPDATE statements to modify existing records.

using Microsoft.Data.Sqlite;

public static void UpdateItemDescription(int id, string newDescription)
{
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "UPDATE Items SET Description = @Description WHERE Id = @Id;";
        command.Parameters.AddWithValue("@Description", newDescription ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Id", id);
        command.ExecuteNonQuery();
    }
}
                

Deleting Data

Use DELETE statements to remove records.

using Microsoft.Data.Sqlite;

public static void DeleteItem(int id)
{
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "DELETE FROM Items WHERE Id = @Id;";
        command.Parameters.AddWithValue("@Id", id);
        command.ExecuteNonQuery();
    }
}
                

Advanced SQLite Concepts

Explore more complex scenarios and optimizations.

Transactions

Transactions are crucial for maintaining data integrity, especially when performing multiple related database operations.

using Microsoft.Data.Sqlite;

public static void AddItemsTransactionally(List<Item> newItems)
{
    using (var transaction = DatabaseHelper.DbConnection.BeginTransaction())
    {
        try
        {
            foreach (var item in newItems)
            {
                using (var command = DatabaseHelper.DbConnection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO Items (Name, Description) VALUES (@Name, @Description);";
                    command.Parameters.AddWithValue("@Name", item.Name);
                    command.Parameters.AddWithValue("@Description", item.Description ?? (object)DBNull.Value);
                    command.ExecuteNonQuery();
                }
            }
            transaction.Commit(); // Commit the transaction if all operations succeed
        }
        catch (Exception ex)
        {
            transaction.Rollback(); // Rollback if any error occurs
            // Handle exception appropriately
            System.Diagnostics.Debug.WriteLine($"Transaction failed: {ex.Message}");
        }
    }
}
                

Database Migrations

For managing schema changes over time, consider using a migration library like EntityFrameworkCore.SQLite (though this adds ORM capabilities) or implementing a custom migration strategy to track and apply schema versions.

Indexes

Create indexes on columns frequently used in WHERE clauses or JOIN conditions to improve query performance.

-- Example: Creating an index on the 'Name' column
CREATE INDEX IF NOT EXISTS idx_items_name ON Items (Name);
                

You can execute this SQL statement using the same command execution pattern as above.

Working with Blobs

SQLite supports storing binary data (BLOBs). You can use byte arrays to insert and retrieve BLOB data.

// Example: Inserting a blob
public static void InsertBlob(byte[] data)
{
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "INSERT INTO DataBlobs (BinaryData) VALUES (@Data);";
        command.Parameters.AddWithValue("@Data", data); // Microsoft.Data.Sqlite handles byte[] conversion
        command.ExecuteNonQuery();
    }
}

// Example: Reading a blob
public static byte[] ReadBlob(int id)
{
    using (var command = DatabaseHelper.DbConnection.CreateCommand())
    {
        command.CommandText = "SELECT BinaryData FROM DataBlobs WHERE Id = @Id;";
        command.Parameters.AddWithValue("@Id", id);
        using (var reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                return reader.IsDBNull(0) ? null : (byte[])reader.GetValue(0); // GetValue is more flexible for BLOBs
            }
        }
    }
    return null;
}
                

Best Practices

• Use Parameterized Queries: Always use parameters (@ParamName) to prevent SQL injection vulnerabilities.
• Close Connections and Disposables: Ensure that `SqliteConnection`, `SqliteCommand`, and `SqliteDataReader` objects are properly disposed of, typically by using using statements.
• Handle Errors Gracefully: Implement robust error handling for database operations, including connection issues and query failures.
• Manage Schema Changes: Plan how you will handle database schema updates as your application evolves.
• Optimize Queries: Use indexes effectively and analyze your query performance, especially for large datasets.
• Avoid Frequent Database Opens/Closes: Keep the database connection open for the duration of your application's data access needs to minimize overhead.
• Consider Data Types: Be mindful of SQLite's dynamic typing and how your .NET types map to them.