SQLite API Reference
This document provides a comprehensive reference for using SQLite databases within Universal Windows Platform (UWP) applications.
Introduction to SQLite in UWP
SQLite is a powerful, self-contained, serverless, zero-configuration, transactional SQL database engine. It is widely used in embedded systems and mobile applications due to its simplicity and performance. In UWP, you can leverage SQLite to manage structured data for your applications.
Note: UWP applications can use SQLite through various libraries. The most common and recommended approach is using the Microsoft.Data.SQLite NuGet package, which provides a managed .NET API.
Key Concepts and Operations
- Database Connection: Establishing a connection to a SQLite database file.
- Table Creation: Defining the schema for your data with `CREATE TABLE` statements.
- Data Manipulation: Inserting, querying, updating, and deleting data using SQL commands.
- Transactions: Ensuring data integrity by grouping multiple operations into atomic transactions.
- Error Handling: Managing potential errors during database operations.
Commonly Used SQLite Functions (via Microsoft.Data.SQLite)
Opening a Connection
You can open a connection to a database file using a connection string.
using Microsoft.Data.Sqlite;
using System.IO;
// Define database path
string dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "myDatabase.db");
// Connection string
string connectionString = $"Data Source={dbPath}";
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
// Database operations here
// ...
}
Creating a Table
Use SQL `CREATE TABLE` statements to define your table structure.
using (var command = connection.CreateCommand())
{
command.CommandText = @"
CREATE TABLE IF NOT EXISTS Users (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Email TEXT UNIQUE
);
";
command.ExecuteNonQuery();
}
Inserting Data
Use parameterized queries to safely insert data.
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Users (Name, Email) VALUES ($name, $email);";
command.Parameters.AddWithValue("$name", "Alice");
command.Parameters.AddWithValue("$email", "alice@example.com");
command.ExecuteNonQuery();
}
Querying Data
Use SQL `SELECT` statements and process the results.
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT Id, Name, Email FROM Users WHERE Id = $id;";
command.Parameters.AddWithValue("$id", 1);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
// Process the retrieved data
System.Diagnostics.Debug.WriteLine($"User: ID={id}, Name={name}, Email={email}");
}
}
}
Updating and Deleting Data
Similar to insertion, use parameterized queries for updates and deletes.
// Update
using (var command = connection.CreateCommand())
{
command.CommandText = "UPDATE Users SET Email = $newEmail WHERE Id = $id;";
command.Parameters.AddWithValue("$newEmail", "alice.updated@example.com");
command.Parameters.AddWithValue("$id", 1);
command.ExecuteNonQuery();
}
// Delete
using (var command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM Users WHERE Id = $id;";
command.Parameters.AddWithValue("$id", 1);
command.ExecuteNonQuery();
}
Best Practices
- Always use parameterized queries to prevent SQL injection vulnerabilities.
- Wrap database operations in `using` statements to ensure connections and commands are properly disposed of.
- Utilize transactions for operations that require atomicity.
- Consider using an ORM (Object-Relational Mapper) like SQLite-net for more complex scenarios.
Tip: For debugging SQLite databases, consider using tools like DB Browser for SQLite, which can be used to open and inspect your UWP app's database file after copying it from the app's local storage.