C#与SQLite嵌入式数据库开发实战指南

1. 嵌入式数据库与C#的天然契合

第一次接触嵌入式数据库是在2013年做工业控制项目时，当时需要在不依赖外部数据库服务的情况下实现设备运行数据的本地存储。传统数据库要么体积庞大，要么需要独立服务进程，而SQLite这种嵌入式方案完美解决了我的痛点——它只需要一个.dll文件就能提供完整的数据库功能，这种"零配置"特性与C#的便捷性简直是天作之合。

嵌入式数据库与传统数据库最大的区别在于它的"无服务器"架构。以SQLite为例，它的整个数据库就是一个独立的磁盘文件，不需要像MySQL那样先安装数据库服务、配置连接字符串。在C#中引用System.Data.SQLite库后，用下面三行代码就能完成数据库的创建和连接：

csharp复制SQLiteConnection.CreateFile("MyDatabase.sqlite");
var connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
connection.Open();

这种开箱即用的特性特别适合以下场景：

• 单机版应用程序（如桌面工具、移动应用）
• 需要离线工作的系统（工业现场数据采集）
• 快速原型开发（避免搭建复杂数据库环境）
• 应用程序配置存储（替代传统的ini/xml文件）

提示：虽然SQLite支持多线程读取，但写入操作是全局锁定的。在高并发写入场景下，考虑使用Microsoft.Data.Sqlite的WAL模式提升性能。

2. 开发环境搭建与核心组件选型

2.1 工具链配置实战

当前最稳定的开发组合是Visual Studio 2022 + System.Data.SQLite 1.0.117。安装时要注意：

1. 在NuGet中搜索时选择"System.Data.SQLite.Core"（纯托管版本）
2. 如果项目要支持x86/x64多平台，需要额外安装"SQLite.Interop.dll"
3. 对于.NET Core/5+项目，Microsoft.Data.Sqlite是官方推荐选择

我习惯使用DB Browser for SQLite作为辅助工具，这个开源工具可以：

• 可视化查看数据库结构
• 直接执行SQL语句调试
• 导出/导入CSV数据
• 检查数据库完整性

2.2 数据库设计最佳实践

虽然SQLite支持标准SQL语法，但有些特殊约束需要注意：

sql复制CREATE TABLE Employees (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 必须显式声明自增
    Name TEXT NOT NULL COLLATE NOCASE,     -- 支持大小写不敏感排序
    Salary REAL CHECK(Salary > 0),         -- 字段级约束
    DepartmentId INTEGER,
    FOREIGN KEY(DepartmentId) REFERENCES Departments(Id) ON DELETE SET NULL
) WITHOUT ROWID;  -- 对于键值访问频繁的表可提升性能

实测对比发现，合理使用以下特性可以显著提升性能：

• 对索引字段声明NOT NULL约束（提升约15%查询速度）
• 使用COLLATE NOCASE的字段进行LIKE查询时快3倍
• 事务批量插入比单条插入快50倍以上

3. C#操作SQLite的进阶技巧

3.1 参数化查询的陷阱与突破

新手常犯的错误是直接拼接SQL字符串，这不仅有SQL注入风险，还会导致数据库反复编译SQL语句。正确做法是：

csharp复制// 错误示范
string sql = $"SELECT * FROM Users WHERE Name='{userInput}'";

// 正确做法
using var cmd = new SQLiteCommand("SELECT * FROM Users WHERE Name=@name", connection);
cmd.Parameters.AddWithValue("@name", userInput);

但参数化查询有个隐藏坑点：SQLite的参数类型是动态推断的。我曾遇到一个诡异问题——查询DateTime字段时结果为空，最终发现需要显式指定参数类型：

csharp复制var param = cmd.Parameters.Add("@date", DbType.DateTime);
param.Value = DateTime.Now;

3.2 事务处理的艺术

在一次数据迁移任务中，我因为没有妥善处理事务导致部分数据丢失。现在我的事务模板必定包含回滚机制：

csharp复制using var transaction = connection.BeginTransaction();
try
{
    // 批量操作代码...
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();
    // 记录日志或通知用户
    throw new Exception("操作失败，已回滚", ex);
}

对于需要高性能的场景，可以调整SQLite的同步模式（非必要不建议修改）：

csharp复制// 在连接字符串中添加
"PRAGMA synchronous=OFF; PRAGMA journal_mode=WAL;"

4. 实战：构建人事管理系统核心模块

4.1 数据库初始化脚本

创建完整的HR系统需要以下表结构：

sql复制-- 部门表
CREATE TABLE Departments (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT UNIQUE NOT NULL,
    ManagerId INTEGER
);

-- 员工表（包含薪资信息）
CREATE TABLE Employees (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Gender TEXT CHECK(Gender IN ('M','F','O')),
    BirthDate TEXT,  -- SQLite没有原生Date类型
    Position TEXT,
    Salary REAL CHECK(Salary > 0),
    DepartmentId INTEGER REFERENCES Departments(Id),
    EntryDate TEXT DEFAULT (datetime('now'))
);

-- 考勤记录
CREATE TABLE Attendance (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    EmployeeId INTEGER NOT NULL REFERENCES Employees(Id),
    CheckInTime TEXT NOT NULL,
    CheckOutTime TEXT,
    Status TEXT DEFAULT 'Normal'
);

4.2 C#数据访问层实现

采用Repository模式封装数据库操作：

csharp复制public class EmployeeRepository : IDisposable
{
    private readonly SQLiteConnection _connection;
    
    public EmployeeRepository(string dbPath)
    {
        _connection = new SQLiteConnection($"Data Source={dbPath};Version=3;");
        _connection.Open();
    }
    
    public List<Employee> GetByDepartment(int departmentId)
    {
        const string sql = @"SELECT e.*, d.Name as DepartmentName 
                            FROM Employees e 
                            LEFT JOIN Departments d ON e.DepartmentId = d.Id 
                            WHERE e.DepartmentId = @deptId";
        
        using var cmd = new SQLiteCommand(sql, _connection);
        cmd.Parameters.AddWithValue("@deptId", departmentId);
        
        var result = new List<Employee>();
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            result.Add(new Employee {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                // 其他字段映射...
                DepartmentName = reader.IsDBNull(8) ? null : reader.GetString(8)
            });
        }
        return result;
    }
    
    public void Dispose() => _connection?.Dispose();
}

4.3 报表生成优化技巧

生成月度考勤报表时，直接使用SQL的聚合函数比在内存中计算快10倍以上：

csharp复制public DataTable GenerateMonthlyReport(int year, int month)
{
    string sql = @"
        SELECT 
            e.Name,
            COUNT(a.Id) as WorkDays,
            SUM(CASE WHEN a.Status != 'Normal' THEN 1 ELSE 0 END) as AbnormalDays,
            GROUP_CONCAT(DISTINCT a.Status) as Statuses
        FROM Attendance a
        JOIN Employees e ON a.EmployeeId = e.Id
        WHERE strftime('%Y', a.CheckInTime) = @year 
          AND strftime('%m', a.CheckInTime) = @month
        GROUP BY e.Id";
    
    using var cmd = new SQLiteCommand(sql, _connection);
    cmd.Parameters.AddWithValue("@year", year.ToString());
    cmd.Parameters.AddWithValue("@month", month.ToString("D2"));
    
    var dt = new DataTable();
    using var adapter = new SQLiteDataAdapter(cmd);
    adapter.Fill(dt);
    return dt;
}

5. 性能调优与疑难排解

5.1 索引优化实战

在包含10万条员工记录的表中，没有索引的查询耗时约1200ms，添加合适索引后降至8ms：

sql复制-- 常用查询字段创建复合索引
CREATE INDEX IX_Employees_DepartmentPosition 
ON Employees(DepartmentId, Position);

-- 模糊查询字段使用COLLATE NOCASE
CREATE INDEX IX_Employees_Name ON Employees(Name COLLATE NOCASE);

但索引不是越多越好，我曾遇到插入性能下降的问题，最终通过以下命令分析解决：

sql复制-- 查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM Employees WHERE Name LIKE '张%';

-- 重建所有索引
ANALYZE;

5.2 连接泄漏检测

在长时间运行的应用程序中，未关闭的连接会导致数据库文件锁定。我的诊断方案：

csharp复制// 在应用程序启动时注册诊断事件
AppDomain.CurrentDomain.ProcessExit += (s, e) => {
    var count = SQLiteConnection.ConnectionCount;
    if (count > 0)
        Logger.Warning($"有{count}个数据库连接未正常关闭");
};

5.3 跨平台兼容性问题

当把数据库文件从Windows迁移到Linux时，遇到大小写敏感问题。解决方案：

1. 所有SQL语句统一使用小写表名和字段名
2. 连接字符串添加"CaseSensitiveLike=Off"参数
3. 使用PRAGMA设置兼容性模式：

csharp复制using var cmd = new SQLiteCommand("PRAGMA case_sensitive_like=OFF;", connection);
cmd.ExecuteNonQuery();

6. 扩展应用：与上位机系统的深度集成

在工业自动化领域，SQLite常作为本地缓存数据库。以下是与PLC通信的典型架构：

code复制[PLC设备] ←(Modbus)→ [C#上位机] ←→ [SQLite本地缓存] ←→ [云端数据库]

关键实现代码片段：

csharp复制// PLC数据变化事件处理
private void OnPlcDataChanged(object sender, PlcDataEventArgs e)
{
    // 使用事务批量更新
    using var transaction = _connection.BeginTransaction();
    try
    {
        foreach (var tag in e.ChangedTags)
        {
            var cmd = new SQLiteCommand(
                "INSERT INTO PlcHistory(TagName, Value, Timestamp) VALUES (?,?,?)",
                _connection, transaction);
            
            cmd.Parameters.AddWithValue(null, tag.Name);
            cmd.Parameters.AddWithValue(null, tag.Value);
            cmd.Parameters.AddWithValue(null, DateTime.UtcNow);
            cmd.ExecuteNonQuery();
        }
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

对于高频数据采集（如每秒1000点），需要特殊优化：

1. 启用WAL日志模式
2. 设置合适的cache_size（通常为-2000到-10000）
3. 使用预编译语句
4. 批量提交间隔设置为1-5秒

csharp复制// 高性能写入配置
var sb = new SQLiteConnectionStringBuilder
{
    DataSource = "hidata.db",
    JournalMode = SQLiteJournalModeEnum.Wal,
    SyncMode = SynchronizationModes.Off,
    CacheSize = -5000,
    DefaultTimeout = 30
};

在最近的一个风电监控项目中，这套方案成功实现了在树莓派上每秒处理1200条传感器记录并持久化，CPU占用率保持在15%以下。关键技巧是将实时数据先写入内存表，然后由后台线程每5秒同步到磁盘数据库。