罗罗编程学习记录
本页内容 展开查看

10.4 阶段 3:本地数据层与文件系统集成

所属:全栈开发面试题库 / 学习路径 / 10 原生桌面开发

本页目录

一、阶段定位

阶段 2 的待办数据还在内存里,一关窗口就没了。本阶段把数据落到本地:SQLite 嵌入式数据库负责结构化数据(待办、笔记、历史记录),文件系统负责导入导出(CSV、JSON、Excel)与用户选择的外部文件。学完本阶段,你应当能让业务工具支持本地数据库持久化与导入导出,多窗口访问同一数据库时不损坏,且能说清本地文件存放在哪。

与前后阶段的衔接:往上承接阶段 2 的依赖注入——本阶段你把 ITodoStorage 接口从 JSON 实现换成 SQLite 实现,ViewModel 一行不改。往下接阶段 4 的系统集成——文件选择对话框、拖拽接收、托盘菜单都会用到本阶段的文件路径 API。不掌握 SQLite 的并发模型与 WAL,阶段 4 做多窗口或托盘后台进程同时写库时就会撞上 database is locked。

原有的三条卡点展开成可判断的现象依据:

  • 把数据库文件写在程序安装目录:现象是普通用户双击运行时报 UnauthorizedAccessException,或者升级安装后用户数据全没了。判断依据:你连接字符串里写的是 Data Source=notes.db(相对当前工作目录,双击启动时可能是 Program Files),或者硬编码了安装路径。
  • 字符串拼接 SQL 留下注入隐患:现象是你写 string sql = "SELECT * FROM Todo WHERE Title = '" + title + "'",用户输入 ' OR '1'='1 就能查出全部数据。判断依据:代码里搜得到 + title + 拼进 SQL 语句。
  • 忽略多进程/多窗口并发写:现象是第二个窗口同时写库时抛 database is locked,或者 WAL 文件(-wal)一直增大不释放。判断依据:你没用事务、没开 WAL、多个连接同时写同一个数据库文件。

二、阶段目标与投入

能力目标(可验证的行为) 验证方式
能用 SQLite 建表、建索引、做参数化查询与事务 用 sqlite3 命令行能查到表结构与数据
能开启 WAL 模式,多窗口并发读写不锁 开两个窗口同时写,无 database is locked
能用文件选择对话框让用户选文件,支持拖拽入窗 拖一个 CSV 进窗口,解析后显示在列表
能把数据导出为 CSV/JSON,再导入恢复 导出后删数据,导入能完整恢复
能说出数据库、配置、缓存分别写在哪个用户目录 路径里无硬编码盘符与用户名
能处理大文件导入的内存问题 导入 10 万行 CSV 不 OOM

建议学时:

  • 全职(每天 4–6 小时):2 周,约 50 小时。
  • 在职(每天 2 小时、周末补 4 小时):5–6 周,约 55 小时。

学时分配建议:SQLite 基础 20%、WAL 与事务 20%、参数化查询与加密 15%、文件选择与拖拽 15%、导入导出格式 15%、路径规范与封装 15%。

三、先修自检

先修项 合格标准 不合格时回到
依赖注入 能写接口并构造函数注入 10.3 阶段 2
用户目录 知道应用数据目录怎么取 10.1 阶段 0
异步语法 能写 async/await 04.6 CSharp 与 .NET
基础 SQL 能写 SELECT/INSERT/UPDATE/DELETE 05.5 NoSQL 与专用存储

经验判断:有后端经验的人会发现 SQLite 与服务端 MySQL 的差别——SQLite 是单文件、进程内库、写锁是库级而不是表级;不要把 MySQL 的连接池思维套过来。

四、知识地图

模块 知识点 掌握程度 对应题库章节
SQLite 基础 建表、索引、查询、CRUD 会用 05.5 NoSQL 与专用存储
事务与参数化 BEGIN/COMMIT、@param、防注入 能讲清原理 05.5 NoSQL 与专用存储
WAL 与并发 WAL 模式、读不阻塞写、database is locked 能讲清原理 05.5 NoSQL 与专用存储
加密存储 SQLCipher、敏感字段加密 了解 11.4 安全与合规
文件选择与拖拽 OpenFileDialog、DragDrop 事件 会用 07.4 桌面开发
导入导出 CSV/JSON 解析与生成、编码处理 会用 05.5 NoSQL 与专用存储
路径规范 应用数据目录、临时目录、清理策略 会用 01.1 操作系统
数据访问封装 Repository 模式、轻量 ORM 了解 06 后端框架与工程实践

进度管理建议:本阶段以 C# Microsoft.Data.Sqlite 为例,其他语言(Python sqlite3、Rust rusqlite、Swift SQLite.swift)概念一致。学完每个模块在笔记里把掌握程度往下调一档;全部到位再进 阶段 4 系统集成。

五、分模块精讲

5.1 SQLite 建表、索引与参数化查询

为什么需要它:桌面应用没有数据库服务器,SQLite 是一个单文件、零配置、进程内的嵌入式数据库,把整个数据库存在一个 .db 文件里。不掌握参数化查询,就会写出可注入的 SQL;不建索引,数据量上来后查询变慢。

核心机制:

  • SQLite 数据库就是一个文件,连接字符串 Data Source=notes.db;推荐用绝对路径指向应用数据目录。
  • 建表:CREATE TABLE IF NOT EXISTS Todo (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL)。
  • 索引:CREATE INDEX IF NOT EXISTS idx_todo_done ON Todo(done);WHERE done = ? 的查询走索引。
  • 参数化查询:用 @param 占位符,参数通过 SqliteParameter 传入,绝不字符串拼接。
  • 类型:SQLite 是动态类型(TEXT/INTEGER/REAL/BLOB),不强校验列类型。

最小可运行示例(建表与 CRUD):

using Microsoft.Data.Sqlite;

public class TodoDatabase
{
    private readonly string _connString;

    public TodoDatabase(string dbPath)
    {
        // 路径指向应用数据目录,不写在安装目录
        _connString = new SqliteConnectionStringBuilder { DataSource = dbPath }.ToString();
        Initialize();
    }

    private void Initialize()
    {
        using var conn = new SqliteConnection(_connString);
        conn.Open();
        // 建表
        using var cmd = conn.CreateCommand();
        cmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Todo (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                done INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_todo_done ON Todo(done);
        ";
        cmd.ExecuteNonQuery();
    }

    // 参数化插入:绝不拼接 title
    public void AddTodo(string title)
    {
        using var conn = new SqliteConnection(_connString);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "INSERT INTO Todo(title, done, created_at) VALUES(@title, 0, @ts)";
        cmd.Parameters.AddWithValue("@title", title);
        cmd.Parameters.AddWithValue("@ts", DateTime.UtcNow.ToString("o"));
        cmd.ExecuteNonQuery();
    }

    public List<(int id, string title, bool done)> QueryDone(bool done)
    {
        var list = new List<(int, string, bool)>();
        using var conn = new SqliteConnection(_connString);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT id, title, done FROM Todo WHERE done = @done";
        cmd.Parameters.AddWithValue("@done", done ? 1 : 0);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add((reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2) == 1));
        }
        return list;
    }
}

常见坑:

现象 原因 修法
SQLite Error 1: 'no such table: Todo' 建表没执行就查询 启动时先 Initialize()
用户输入 ' OR '1'='1 查出全表 字符串拼接 SQL 改参数化查询
数据量上万后查询慢 没建索引 CREATE INDEX 覆盖 WHERE 列
Data Source=notes.db 找不到文件 相对路径,工作目录不确定 用应用数据目录的绝对路径

面试考法:常问"SQLite 与 MySQL 的区别""为什么要参数化查询"。要答到"SQLite 是进程内单文件库、无服务端、写锁库级;参数化查询把 SQL 与数据分离,防注入且可复用执行计划"。

5.2 WAL 模式与事务

为什么需要它:默认的 rollback journal 模式下,写操作会锁整个数据库,读也会被阻塞。多窗口或后台进程同时读写时就会撞上 database is locked。WAL(Write-Ahead Logging)把写先写日志,读不阻塞写。事务保证一组操作要么全成功要么全回滚。

核心机制:

  • 开启 WAL:PRAGMA journal_mode=WAL;,执行后数据库旁会生成 notes.db-wal 日志文件。
  • WAL 下:读写可并发(多个读 + 一个写);写仍单写,但不阻塞读。
  • 事务:BEGIN; ...; COMMIT; 中间出错 ROLLBACK;;用 using var tx = conn.BeginTransaction()。
  • 批量插入:必须包在一个事务里,否则每条 INSERT 都触发一次 fsync,慢 100 倍。
  • WAL 文件:正常情况下 checkpoint 时自动合并回主库;进程崩溃后重启自动恢复。

最小可运行示例(开启 WAL + 批量插入事务):

private void Initialize()
{
    using var conn = new SqliteConnection(_connString);
    conn.Open();
    // 开启 WAL
    using (var pragma = conn.CreateCommand())
    {
        pragma.CommandText = "PRAGMA journal_mode=WAL;";
        pragma.ExecuteNonQuery();
    }
    // 建表...
}

public void BulkAdd(IEnumerable<string> titles)
{
    using var conn = new SqliteConnection(_connString);
    conn.Open();
    using var tx = conn.BeginTransaction();
    try
    {
        foreach (var title in titles)
        {
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText = "INSERT INTO Todo(title, done, created_at) VALUES(@t, 0, @ts)";
            cmd.Parameters.AddWithValue("@t", title);
            cmd.Parameters.AddWithValue("@ts", DateTime.UtcNow.ToString("o"));
            cmd.ExecuteNonQuery();
        }
        tx.Commit();   // 全部成功才提交
    }
    catch
    {
        tx.Rollback(); // 出错全回滚
        throw;
    }
}

常见坑:

现象 原因 修法
database is locked 没开 WAL,或多个写连接并发 开 WAL;写操作串行化;设置 busy_timeout
批量插入 1 万条要 30 秒 每条单独事务 包在一个事务里
notes.db-wal 文件持续增大 没触发 checkpoint PRAGMA wal_checkpoint(TRUNCATE);
事务回滚后数据还是变了 没调 Rollback 或提前 Commit try-catch 里显式 Rollback

面试考法:常问"WAL 模式的原理与优势""SQLite 为什么写要加事务"。要答到"WAL 把写先追加到日志而非直接改库,读不阻塞写;每条 INSERT 默认隐式事务,fsync 开销大,批量必须显式事务"。

5.3 加密与敏感数据

为什么需要它:笔记、密码、令牌等敏感数据不能明文写在 .db 文件里——用户磁盘丢了、被拷走就能直接读。SQLCipher 提供透明加密的 SQLite,整个数据库文件加密。

核心机制:

  • SQLCipher:在 SQLite 上加一层 AES-256 加密,连接时传 PRAGMA key = 'secret';文件内容密文,没有密钥打不开。
  • 密钥管理:密钥不能硬编码在代码里(反编译可见);桌面应用用操作系统的凭据存储(Windows Credential Manager、macOS Keychain)。
  • 字段级加密:非敏感字段不加密,只加密敏感字段;整库加密会损失查询性能(不能走索引搜密文)。
  • 注意:Microsoft.Data.Sqlite 默认不带加密,要换成 SQLitePCLRaw + SQLCipher 包。

最小可运行示例(连接时设置密钥,伪代码示意):

// 注意:密钥从凭据存储读,不要硬编码
string key = await GetKeyFromCredentialManagerAsync();
var cs = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Password = key          // SQLCipher 识别的密钥参数
}.ToString();
// 后续操作与普通 SQLite 一致,整个文件密文

常见坑:

现象 原因 修法
密钥写在源码里被反编译拿到 硬编码 用操作系统凭据存储
忘了密钥数据库打不开 密钥丢失 密钥备份机制;首次启动生成并提示用户备份
整库加密后查询变慢 密文无法走索引 只加密敏感列,或对要查询的列做哈希

面试考法:常问"桌面应用的敏感数据怎么存"。要答到"优先用操作系统凭据存储;需要结构化数据用 SQLCipher 整库加密;密钥不硬编码;最小化敏感数据落盘"。

5.4 文件选择对话框与拖拽接收

为什么需要它:桌面应用要让用户打开 CSV、保存导出文件、接收拖拽文件。这三个动作是文件系统集成的入口。

核心机制:

  • 打开文件对话框:Microsoft.Win32.OpenFileDialog,设 Filter 限定扩展名,ShowDialog() 返回 bool?。
  • 保存文件对话框:SaveFileDialog,FileName 取目标路径。
  • 拖拽接收:窗口设 AllowDrop="True",处理 Drop 事件,e.Data.GetData(DataFormats.FileDrop) 取文件路径数组。
  • 路径校验:打开前 File.Exists,保存时提示覆盖。

最小可运行示例(打开 CSV + 拖拽接收):

// 点按钮打开文件
private void OpenCsv_Click(object sender, RoutedEventArgs e)
{
    var dlg = new Microsoft.Win32.OpenFileDialog
    {
        Filter = "CSV 文件|*.csv|所有文件|*.*",
        Title = "选择要导入的 CSV"
    };
    if (dlg.ShowDialog() == true)
    {
        string path = dlg.FileName;
        ImportCsv(path);
    }
}

// 窗口 XAML:AllowDrop="True" DragEnter="Window_DragEnter" Drop="Window_Drop"
private void Window_DragEnter(object sender, DragEventArgs e)
{
    // 只接受文件拖拽
    e.Effects = e.Data.GetDataPresent(DataFormats.FileDrop)
        ? DragDropEffects.Copy : DragDropEffects.None;
}

private void Window_Drop(object sender, DragEventArgs e)
{
    if (e.Data.GetData(DataFormats.FileDrop) is string[] files)
    {
        foreach (var f in files)
        {
            if (f.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
            {
                ImportCsv(f);
            }
        }
    }
}

常见坑:

现象 原因 修法
拖文件进来没反应 窗口没设 AllowDrop="True" XAML 里加上
对话框选了文件但读不到 路径含空格没引号 直接用 dlg.FileName,不要手拼
导入 CSV 中文乱码 用了默认 UTF-8 但文件是 GBK 检测编码或让用户选编码

面试考法:常问"拖拽接收要处理哪几个事件"。要答到"DragEnter 决定是否接受、Drop 取数据;AllowDrop 必须开启"。

5.5 导入导出格式与大文件内存处理

为什么需要它:导出 CSV/JSON 是用户数据的可移植性保障;大文件导入不能一次性读进内存。

核心机制:

  • CSV:逗号分隔,字段含逗号或引号要转义(双引号包裹,内部双引号转 "");首行表头。
  • JSON:System.Text.Json 序列化;大集合用 JsonSerializer.SerializeAsync 流式写。
  • 大文件读取:用 StreamReader 逐行读,不要 File.ReadAllText;10 万行 CSV 流式处理。
  • 编码:Windows 上 Excel 打开 CSV 默认按 GBK,导出带 BOM 的 UTF-8 才能被 Excel 正确识别。

最小可运行示例(流式导入 CSV):

public async Task ImportCsvStreamingAsync(string path)
{
    using var reader = new StreamReader(path);   // 不一次性读入
    string? line = await reader.ReadLineAsync(); // 跳过表头
    while ((line = await reader.ReadLineAsync()) != null)
    {
        if (string.IsNullOrWhiteSpace(line)) continue;
        var parts = line.Split(',');
        if (parts.Length >= 1 && !string.IsNullOrWhiteSpace(parts[0]))
        {
            AddTodo(parts[0].Trim());
        }
    }
}

public async Task ExportCsvAsync(string path, IEnumerable<(string title, bool done)> items)
{
    using var writer = new StreamWriter(path, false, new UTF8Encoding(true)); // 带 BOM
    await writer.WriteLineAsync("标题,完成");
    foreach (var (title, done) in items)
    {
        // 简单转义:含逗号则加引号
        var t = title.Contains(",") ? $"\"{title.Replace("\"", "\"\"")}\"" : title;
        await writer.WriteLineAsync($"{t},{(done ? "是" : "否")}");
    }
}

常见坑:

现象 原因 修法
导入 10 万行 CSV 后内存爆 File.ReadAllText 一次读入 用 StreamReader.ReadLineAsync 流式
Excel 打开导出的 CSV 中文乱码 没带 BOM new UTF8Encoding(true)
含逗号的字段被拆错列 没转义 字段含逗号时双引号包裹

面试考法:常问"大文件导入怎么避免 OOM"。要答到"流式逐行读、逐行写,不一次性加载;批量事务插入数据库;进度条反馈"。

5.6 路径规范与数据访问封装

为什么需要它:数据库写在哪、缓存写在哪、临时文件写在哪,必须统一管理;数据访问层要封装成接口,方便阶段 6 替换测试。

核心机制:

  • 路径规范:
    • 数据库:%APPDATA%/MyApp/data.db
    • 配置:%APPDATA%/MyApp/config.json
    • 缓存:%LOCALAPPDATA%/MyApp/cache/
    • 临时:%TEMP%/MyApp/
  • 封装:AppPaths 静态类统一提供路径;ITodoStorage 接口隔离数据访问。
  • 清理策略:缓存目录定期清理;临时文件退出时删;数据库不自动删(用户数据)。

最小可运行示例(AppPaths 统一路径):

public static class AppPaths
{
    private static readonly string AppData =
        Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
    private static readonly string LocalAppData =
        Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);

    public static string DataDir => Path.Combine(AppData, "MyApp");
    public static string DbPath => Path.Combine(DataDir, "data.db");
    public static string ConfigPath => Path.Combine(DataDir, "config.json");
    public static string CacheDir => Path.Combine(LocalAppData, "MyApp", "cache");
    public static string TempDir => Path.Combine(Path.GetTempPath(), "MyApp");

    static AppPaths()
    {
        Directory.CreateDirectory(DataDir);
        Directory.CreateDirectory(CacheDir);
        Directory.CreateDirectory(TempDir);
    }
}

常见坑:

现象 原因 修法
数据库写在安装目录 连接字符串用相对路径 用 AppPaths.DbPath 绝对路径
缓存无限增大 没清理策略 启动时清缓存目录或设上限
临时文件残留 退出时没删 退出时 Directory.Delete(TempDir, true)

面试考法:常问"数据库、配置、缓存分别写在哪"。要答到"数据库与配置写漫游 AppData(随用户漫游),缓存与临时写 LocalAppData 或 TEMP,安装目录只读"。

六、分层练习

6.1 复现型(照着做,目标是跑通 + 能解释)

练习 要求 验收标准 预计耗时
SQLite 建表 CRUD 用 Microsoft.Data.Sqlite 建 Todo 表,实现增删查 sqlite3 命令行能查到数据 3 小时
开启 WAL 执行 PRAGMA journal_mode=WAL,观察 -wal 文件 开两个窗口同时写不锁 2 小时
参数化查询 把字符串拼接改成 @param 输入 ' OR '1'='1 不再注入 1 小时
文件选择对话框 打开 CSV 导入、导出 CSV 导出文件能被 Excel 打开 2 小时
拖拽接收 窗口接受 CSV 拖拽 拖文件进窗口触发导入 1.5 小时

6.2 改造型(给出现成代码,改出正确行为)

练习 要求 验收标准 预计耗时
批量插入加事务 给定循环单条插入,改成一个事务 1 万条插入从 30 秒降到 1 秒 1.5 小时
修注入 给定拼接 SQL,改成参数化 注入字符串失效 1 小时
流式读 CSV 给定 ReadAllText,改成逐行读 10 万行导入内存稳定 1.5 小时
CSV 加 BOM 导出 CSV 不带 BOM,Excel 乱码 Excel 打开中文正常 0.5 小时
数据库迁移到用户目录 给定写在安装目录,改 AppPaths 普通用户双击可写 1 小时

6.3 设计型(自己定方案,目标是能交付、能讲清取舍)

练习 要求 验收标准 预计耗时
带搜索的笔记库 SQLite 建笔记表,支持标题索引搜索 1 万条笔记搜索 < 100ms 6 小时
导入导出全链路 支持 JSON 整库备份与恢复 导出后删库,导入完整恢复 5 小时
加密笔记 用 SQLCipher 加密敏感笔记,密钥从凭据存储读 直接打开 db 文件是密文 6 小时

七、排障表

现象 可能原因 排查步骤 解决办法
database is locked 没开 WAL,多连接并发写 查 PRAGMA journal_mode; 开 WAL;写串行;设 busy_timeout
SQLite Error 1: 'no such table' 建表没执行 启动时是否调 Initialize 启动时建表
普通用户双击报 UnauthorizedAccessException 写在安装目录 打印实际 db 路径 改写到 %APPDATA%
用户输入 ' OR '1'='1 查出全表 字符串拼接 SQL 搜代码里 SQL 拼接 改参数化查询
批量插入 1 万条要 30 秒 每条单独事务 看是否有 BeginTransaction 包在一个事务
导入 10 万行 CSV 内存爆 ReadAllText 一次读入 看读取代码 改 StreamReader 逐行
Excel 打开导出 CSV 中文乱码 没带 BOM 用十六进制编辑器看文件头 导出带 BOM 的 UTF-8
拖文件进窗口没反应 没设 AllowDrop 看 XAML 是否 AllowDrop="True" 加上并挂 Drop 事件

八、出口条件

全部勾选后再进入 阶段 4:系统集成与桌面独有能力。

  • [ ] 能用 SQLite 建表、建索引、做参数化查询与事务
  • [ ] 能开启 WAL,多窗口并发读写不锁
  • [ ] 能用文件选择对话框打开 CSV,支持拖拽入窗
  • [ ] 能导出 CSV(带 BOM)并导入恢复,大文件流式处理不 OOM
  • [ ] 能说出数据库、配置、缓存分别写在哪个用户目录
  • [ ] 能把数据访问封装成接口,ViewModel 不依赖具体实现
  • [ ] 排障表里随机抽三个现象,能在 5 分钟内给出排查路径

自测题(先自己想,再看要点):

  1. SQLite 为什么用参数化查询而不是字符串拼接?
    • 要点:防注入;参数与 SQL 分离,可复用执行计划。
  2. WAL 模式解决了什么问题?
    • 要点:写先写日志,读不阻塞写;多窗口并发读写不锁。
  3. 为什么批量插入必须包在事务里?
    • 要点:每条 INSERT 默认隐式事务,fsync 开销大;一个事务批量提交快 100 倍。
  4. 数据库文件应该写在哪个目录?为什么不能写安装目录?
    • 要点:%APPDATA%/MyApp/;安装目录只读且随卸载删除。
  5. 导出 CSV 给 Excel 用,为什么要带 BOM?
    • 要点:Excel 默认按系统编码(GBK)识别;带 BOM 的 UTF-8 才能正确解析中文。
  6. 大文件导入怎么避免 OOM?
    • 要点:流式逐行读、逐行写;不一次性加载;批量事务插入。
  7. 敏感数据为什么不能明文写在 SQLite?怎么加密?
    • 要点:磁盘丢失即泄露;用 SQLCipher 整库加密,密钥从 OS 凭据存储读。