SQLite、事务与数据访问

SQLite、事务与数据访问

SQLite 让本地工具获得事务、约束、索引和查询能力，同时不需要独立数据库服务。它适合单机应用和测试，但“数据库是一个文件”不代表可以忽略参数化查询、事务边界、锁竞争和备份。

---

一、学习目标

• 使用 Microsoft.Data.Sqlite 10.0.10 建立连接
• 设计带约束的任务表
• 始终使用参数化 SQL
• 把数据库行显式映射为领域对象
• 使用事务保证多步更新原子性
• 理解 SQLite 并发、WAL 和异步边界

---

二、安装与连接字符串

dotnet add src/StudyTasks.Infrastructure \
  package Microsoft.Data.Sqlite --version 10.0.10

使用连接字符串构建器：

var builder = new SqliteConnectionStringBuilder
{
    DataSource = databasePath,
    Mode = SqliteOpenMode.ReadWriteCreate,
    Cache = SqliteCacheMode.Default,
    ForeignKeys = true,
    DefaultTimeout = 5
};

using var connection = new SqliteConnection(
    builder.ToString());
connection.Open();

数据库路径来自已验证配置，不接受任意用户文本。连接字符串可能包含路径或密码信息，不要直接写日志。

每次用例短暂打开连接通常比长期共享一个连接更安全。SqliteConnection 不是可任意跨线程并发使用的全局单例。

---

三、建立表结构

const string schema = """
    CREATE TABLE IF NOT EXISTS task (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        title        TEXT NOT NULL
                     CHECK (length(trim(title)) BETWEEN 1 AND 120),
        status       TEXT NOT NULL
                     CHECK (status IN ('todo', 'done')),
        created_at   TEXT NOT NULL,
        completed_at TEXT NULL,
        CHECK (
            (status = 'todo' AND completed_at IS NULL)
            OR
            (status = 'done' AND completed_at IS NOT NULL)
        )
    );

    CREATE INDEX IF NOT EXISTS task_status_id_idx
        ON task(status, id);
    """;

using SqliteCommand command = connection.CreateCommand();
command.CommandText = schema;
command.ExecuteNonQuery();

应用校验改善错误体验，数据库约束保护所有写入路径，两者不能互相替代。

SQLite 只有 INTEGER、REAL、TEXT、BLOB 四种基础存储类型。这里把 DateTimeOffset 以 "O" 格式文本保存，读取时用不变文化显式解析。

正式应用应使用版本化迁移表，不要仅靠一段不断变化的 CREATE TABLE IF NOT EXISTS。

---

四、参数化写入

using SqliteCommand command = connection.CreateCommand();
command.CommandText = """
    INSERT INTO task(title, status, created_at, completed_at)
    VALUES ($title, $status, $createdAt, NULL);

    SELECT last_insert_rowid();
    """;

command.Parameters.AddWithValue("$title", task.Title);
command.Parameters.AddWithValue("$status", "todo");
command.Parameters.AddWithValue(
    "$createdAt",
    task.CreatedAt.ToString("O", CultureInfo.InvariantCulture));

long id = (long)(command.ExecuteScalar()
    ?? throw new InvalidOperationException("数据库未返回 ID"));

错误示例：

// 错误：SQL 注入、引号和类型问题
command.CommandText =
    $"INSERT INTO task(title) VALUES ('{task.Title}')";

参数只用于值，不能参数化表名、列名和排序方向。动态 SQL 结构必须来自程序白名单。

---

五、读取与映射

using SqliteCommand command = connection.CreateCommand();
command.CommandText = """
    SELECT id, title, status, created_at, completed_at
    FROM task
    WHERE ($status IS NULL OR status = $status)
    ORDER BY id
    LIMIT $limit;
    """;

command.Parameters.AddWithValue(
    "$status",
    status is null ? DBNull.Value : ToStorage(status.Value));
command.Parameters.AddWithValue("$limit", limit);

using SqliteDataReader reader = command.ExecuteReader();
var tasks = new List<StudyTask>();
while (reader.Read())
{
    tasks.Add(MapTask(reader));
}

return tasks;

映射函数应按列名或稳定顺序读取，并验证：

• ID 为正
• 状态文本已知
• 时间能按指定格式解析
• 完成状态和完成时间一致

数据库里出现非法数据应被视为数据完整性故障，不要静默替换为默认值。

---

六、仓储边界

public sealed class SqliteTaskRepository(
    string connectionString) : ITaskRepository
{
    public StudyTask Add(StudyTask task)
    {
        using SqliteConnection connection = OpenConnection();
        return Insert(connection, transaction: null, task);
    }

    private SqliteConnection OpenConnection()
    {
        var connection = new SqliteConnection(connectionString);
        connection.Open();
        return connection;
    }
}

仓储负责 SQL、连接、事务和映射；服务负责用例和业务规则。仓储不打印用户消息，CLI 不知道表结构。

接口不要为了数据库实现而暴露 SqliteConnection。需要批量原子操作时，在仓储增加业务含义明确的方法，或引入受控 Unit of Work，而不是让外层手工控制内部连接。

---

七、事务

完成任务并写审计记录必须一起成功：

using SqliteConnection connection = OpenConnection();
using SqliteTransaction transaction =
    connection.BeginTransaction();

try
{
    UpdateTask(connection, transaction, completed);
    InsertAudit(connection, transaction, completed.Id, "completed");
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

所有命令必须显式关联同一事务：

command.Connection = connection;
command.Transaction = transaction;

事务中不要等待 HTTP。先完成网络读取和数据验证，再开启尽量短的事务写入；否则会长时间持锁。

批量导入应先完整解析和验证，再在一个事务执行。部分成功若是产品要求，应返回逐项结果并有明确恢复策略。

---

八、Microsoft.Data.Sqlite 的异步边界

SQLite 不提供真正的异步文件 I/O，Microsoft.Data.Sqlite 的异步 ADO.NET 方法通常同步执行。不要为了方法名带 Async 就假设它不会占用调用线程。

本地 CLI 可保持短事务同步访问。如果一次操作会长时间扫描大量数据，先优化查询和索引；必要时在架构边界调度工作，但不要把每条 SQL 随意包装进 Task.Run。

异步网络同步与同步 SQLite 写入可以分阶段：先异步获取并验证远端数据，再短暂同步事务落库。

---

九、常见错误

字符串拼接 SQL

用户值必须参数化。动态结构只允许经过白名单。

依赖应用校验却不建约束

其他导入路径或缺陷可写入非法状态。关键不变量在数据库重复保护。

在事务中等待网络

会延长锁时间并增加失败组合。网络和数据库阶段分离。

把一个连接注册为全局 Singleton

跨线程使用、事务泄漏和生命周期会互相干扰。按用例打开和释放。

---

十、练习与自测

练习：

1. 建表并实现添加、列表、完成和删除。
2. 用约束拒绝 done 但没有完成时间的行。
3. 模拟审计插入失败，确认任务更新回滚。
4. 对状态列表查询运行 EXPLAIN QUERY PLAN。

自测：

• 参数化查询能防止什么，不能参数化什么？
• 为什么应用校验和数据库约束都需要？
• WAL 改善了什么，又没有改变什么？
• 内存数据库测试为何必须保持连接打开？

---

十一、官方资料

• Microsoft.Data.Sqlite 概览
• SQLite 参数
• SQLite 事务
• SQLite WAL
• SQLite 查询计划

上一篇：命令行、配置、依赖注入与日志 ｜ 下一篇：HTTP、JSON 与 API 客户端