C# SQLite3增删改查实战：工业级本地数据库开发避坑指南

简介：本资源是一份面向C#初学者与.NET开发者的SQLite3数据库操作实战Demo，聚焦轻量级本地数据库在桌面应用中的增删改查实践。项目完整封装了连接管理、参数化查询、事务处理等核心能力，并通过辅助类（如SqliteDatabase）提供可复用的静态方法，显著降低入门门槛。压缩包共175个文件，含40个关键dll（如System.Data.SQLite）、9个C#源码文件（含SqliteOpera.csproj）、1个SQLite数据库文件（.db）及配套配置、缓存与构建文件，整体32.56MB，结构清晰便于直接集成到VS项目中。已有2255人学习下载，读者可直接获取开箱即用的代码框架、规范的异常处理逻辑、防SQL注入的参数化示例，以及涵盖创建表、批量插入、条件查询、更新删除等全链路操作的完整实现，是快速掌握C#+SQLite3开发模式的高效实践素材。

1. 为什么一个“C# SQLite3增删改查Demo”能卡住90%的上位机和嵌入式配套开发？

你刚接到一个设备配套软件需求：用C#写个本地数据管理模块，存传感器采样时间、温度、电压、校准参数——不连服务器、不装SQL Server、不能依赖外部服务。老板说“就一个小Demo，半天搞定”。结果你翻遍文档，发现System.Data.SQLite.dll版本对不上.NET Framework/.NET Core运行时，插入中文字段乱码，事务没提交却显示成功，批量插入10万条耗时从2秒飙到47秒……最后交期前夜还在查SQLiteConnection.Open()卡死在sqlite3_open_v2底层调用。

这不是Demo太简单，而是SQLite3在C#生态里表面轻量、实则暗坑密布：它不像SQL Server有图形化工具兜底，也不像MySQL有Docker一键拉起；它是嵌入式数据库，所有行为都直面操作系统文件系统、线程安全模型、连接字符串语义、甚至.NET GC对IDisposable资源的回收节奏。这个“C# SQLite3增删改查Demo”，本质是本地持久化能力的最小可信验证单元——它要能跑通，意味着你的上位机软件已具备可靠的数据落地能力，后续加日志、加历史曲线、加导出Excel、加离线同步，才有根基。适合设备驱动开发、工业HMI、便携式检测仪、边缘计算终端等无网络/弱网络/强实时性要求场景的开发者，尤其当你手头只有Visual Studio + 一台Windows工控机，连Docker都不让装的时候。

2. 从零构建可复现的C# SQLite3 CRUD工程：选型、初始化与连接池控制

2.1 为什么必须用System.Data.SQLite而非Microsoft.Data.Sqlite？

虽然.NET 5+官方推荐Microsoft.Data.Sqlite（轻量、跨平台、无本地DLL依赖），但工业现场90%的存量项目仍卡在.NET Framework 4.6.1~4.8，且大量旧设备SDK仅提供x86/x64混合架构支持。Microsoft.Data.Sqlite在.NET Framework下需额外安装SQLitePCLRaw.bundle_e_sqlite3，而System.Data.SQLite（v1.0.115+）直接提供预编译的System.Data.SQLite.dll，支持x86/x64/AnyCPU三态切换，且内置sqlite3.dll资源，无需手动部署.so或.dll——这对需要U盘拷贝即用的现场调试至关重要。

提示：System.Data.SQLite官网已停止维护，但GitHub上system-data-sqlite组织持续发布兼容.NET 6+的v1.0.118+版本，下载地址为https://github.com/system-data-sqlite/system.data.sqlite/releases（注意选择带bundle后缀的安装包，如System.Data.SQLite.Core.Bundle.x64.1.0.118.0.nupkg）

2.2 创建最小可运行工程：NuGet包、连接字符串与连接池配置

新建一个.NET Framework 4.7.2控制台项目（确保目标框架明确，避免AnyCPU导致x86/x64混用崩溃），执行以下命令安装：

Install-Package System.Data.SQLite.Core -Version 1.0.118.0

关键点：必须安装System.Data.SQLite.Core（含本机库），而非System.Data.SQLite（仅托管层）。安装后检查项目引用中是否包含：

• System.Data.SQLite.dll
• System.Data.SQLite.Linq.dll（可选，Linq to SQLite支持）
• System.Data.SQLite.xml（IntelliSense支持）

连接字符串示例（含连接池控制）：

string connectionString = @"Data Source=.\sensor.db;Version=3;Pooling=true;Max Pool Size=10;Min Pool Size=1;Connection Timeout=30;Journal Mode=WAL;Synchronous=Normal;";

注意：Data Source路径使用.\sensor.db而非绝对路径，便于程序移动部署；若需中文路径，必须确保数据库文件所在目录编码为UTF-8（Windows默认GBK，建议路径不含中文）

2.3 初始化数据库：建表、索引与PRAGMA优化

首次运行需创建表结构。以下代码封装为InitializeDatabase()方法，必须在应用启动时单次执行：

public static void InitializeDatabase(string dbPath) { using (var conn = new SQLiteConnection($"Data Source={dbPath};Version=3;")) { conn.Open(); // 启用WAL模式（必须在建表前执行） using (var cmd = conn.CreateCommand()) { cmd.CommandText = "PRAGMA journal_mode = WAL;"; cmd.ExecuteNonQuery(); } // 创建传感器数据表 string createTableSql = @" CREATE TABLE IF NOT EXISTS SensorData ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Timestamp DATETIME NOT NULL, Temperature REAL, Voltage REAL, DeviceId TEXT NOT NULL, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP );"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = createTableSql; cmd.ExecuteNonQuery(); } // 创建复合索引：按设备ID+时间范围查询高频 string createIndexSql = "CREATE INDEX IF NOT EXISTS idx_device_time ON SensorData(DeviceId, Timestamp);"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = createIndexSql; cmd.ExecuteNonQuery(); } } }

逻辑说明：

• PRAGMA journal_mode = WAL必须在CREATE TABLE前执行，否则无效；
• AUTOINCREMENT仅在INTEGER PRIMARY KEY上生效，避免误用INT IDENTITY（SQLite不支持）；
• CURRENT_TIMESTAMP是SQLite内置函数，无需C#生成时间字符串；
• 索引idx_device_time针对典型查询场景：SELECT * FROM SensorData WHERE DeviceId='DEV001' AND Timestamp BETWEEN '2024-01-01' AND '2024-01-31'。

3. 四步落地CRUD：参数化防注入、事务原子性、批量插入优化与空值安全处理

3.1 插入：用Parameter避免SQL注入与类型转换错误

错误示范（字符串拼接，必踩坑）：

// ❌ 危险！设备ID含单引号会崩溃，温度为null时转字符串报错 string sql = $"INSERT INTO SensorData (Timestamp, Temperature, DeviceId) VALUES ('{dt}', {temp}, '{deviceId}')";

正确做法（参数化+DBNull.Value处理）：

public static void InsertSensorData(DateTime timestamp, double? temperature, string deviceId) { string sql = @"INSERT INTO SensorData (Timestamp, Temperature, DeviceId) VALUES (@timestamp, @temperature, @deviceId);"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(sql, conn)) { // 显式处理null：SQLite中NULL对应DBNull.Value，不能传C# null cmd.Parameters.AddWithValue("@timestamp", timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff")); cmd.Parameters.AddWithValue("@temperature", temperature ?? DBNull.Value); cmd.Parameters.AddWithValue("@deviceId", deviceId ?? string.Empty); cmd.ExecuteNonQuery(); } } }

参数说明：

• @timestamp必须转为SQLite认可的yyyy-MM-dd HH:mm:ss.fff格式，SQLite原生不支持DateTime类型，存为TEXT最稳妥；
• temperature为double?可空类型，?? DBNull.Value确保null值正确写入；
• AddWithValue自动推断类型，但工业现场强烈建议显式指定类型（见3.4节避坑）。

3.2 查询：分页、条件过滤与DataReader高效读取

针对10万+记录场景，避免DataTable.Load()吃光内存：

public static List<SensorData> QueryByDeviceAndTime(string deviceId, DateTime startTime, DateTime endTime, int page = 1, int pageSize = 100) { var results = new List<SensorData>(); string sql = @"SELECT Id, Timestamp, Temperature, Voltage, DeviceId, CreatedAt FROM SensorData WHERE DeviceId = @deviceId AND Timestamp BETWEEN @startTime AND @endTime ORDER BY Timestamp DESC LIMIT @pageSize OFFSET @offset;"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(sql, conn)) { cmd.Parameters.AddWithValue("@deviceId", deviceId); cmd.Parameters.AddWithValue("@startTime", startTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); cmd.Parameters.AddWithValue("@endTime", endTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); cmd.Parameters.AddWithValue("@pageSize", pageSize); cmd.Parameters.AddWithValue("@offset", (page - 1) * pageSize); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { results.Add(new SensorData { Id = Convert.ToInt32(reader["Id"]), Timestamp = DateTime.Parse(reader["Timestamp"].ToString()), Temperature = reader["Temperature"] == DBNull.Value ? null : Convert.ToDouble(reader["Temperature"]), Voltage = reader["Voltage"] == DBNull.Value ? null : Convert.ToDouble(reader["Voltage"]), DeviceId = reader["DeviceId"].ToString(), CreatedAt = DateTime.Parse(reader["CreatedAt"].ToString()) }); } } } } return results; }

关键点：

• LIMIT/OFFSET实现真分页，避免SELECT *全表加载；
• reader["Column"]比reader.GetOrdinal("Column")更易读，但列名拼写错误会在运行时报IndexOutOfRangeException，建议配合单元测试；
• DateTime.Parse()需确保Timestamp字段存储格式统一（见3.1节格式约定）。

3.3 更新与删除：WHERE条件必须含主键或唯一约束

更新操作务必带上Id（主键）或DeviceId+Timestamp（业务唯一键），避免误更新全表：

public static int UpdateTemperature(int id, double newTemp) { string sql = "UPDATE SensorData SET Temperature = @temp, UpdatedAt = CURRENT_TIMESTAMP WHERE Id = @id;"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(sql, conn)) { cmd.Parameters.AddWithValue("@temp", newTemp); cmd.Parameters.AddWithValue("@id", id); return cmd.ExecuteNonQuery(); // 返回影响行数，用于判断是否更新成功 } } } public static int DeleteOldData(DateTime cutoffTime) { string sql = "DELETE FROM SensorData WHERE Timestamp < @cutoff;"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(sql, conn)) { cmd.Parameters.AddWithValue("@cutoff", cutoffTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); return cmd.ExecuteNonQuery(); } } }

注意：ExecuteNonQuery()返回值必须检查——返回0表示无匹配记录，不是失败，而是业务逻辑预期（如删除过期数据时恰无数据可删）。

3.4 批量插入：10万条从47秒降到1.8秒的核心技巧

单条INSERT循环10万次≈47秒；开启事务+参数化批量插入≈1.8秒。关键不在BEGIN TRANSACTION，而在复用同一SQLiteCommand对象：

public static void BulkInsertSensorData(List<SensorData> dataList) { string sql = @"INSERT INTO SensorData (Timestamp, Temperature, Voltage, DeviceId) VALUES (@timestamp, @temperature, @voltage, @deviceId);"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); using (var transaction = conn.BeginTransaction()) // 开启事务 using (var cmd = new SQLiteCommand(sql, conn, transaction)) { // 预编译参数，避免每次循环重新解析SQL var pTimestamp = cmd.Parameters.Add("@timestamp", DbType.String); var pTemperature = cmd.Parameters.Add("@temperature", DbType.Double); var pVoltage = cmd.Parameters.Add("@voltage", DbType.Double); var pDeviceId = cmd.Parameters.Add("@deviceId", DbType.String); foreach (var item in dataList) { // 复用参数对象，只更新Value属性 pTimestamp.Value = item.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff"); pTemperature.Value = item.Temperature ?? DBNull.Value; pVoltage.Value = item.Voltage ?? DBNull.Value; pDeviceId.Value = item.DeviceId ?? string.Empty; cmd.ExecuteNonQuery(); } transaction.Commit(); // 必须显式Commit } } }

性能原理：

• cmd.Parameters.Add()返回SQLiteParameter对象，pXXX.Value = ...比cmd.Parameters.AddWithValue()快3倍以上（避免类型推断开销）；
• transaction.Commit()前所有SQL在内存中执行，磁盘I/O仅发生一次；
• 若中途异常，transaction.Rollback()自动回滚，保证原子性。

4. 避坑指南：5个让工程师凌晨三点还在重启电脑的真实问题

4.1 现象：SQLiteConnection.Open()卡死，CPU占用100%，进程无法响应

原因：连接字符串中Data Source指向被其他进程（如Windows资源管理器、另一实例的Visual Studio调试器）独占打开的.db文件。SQLite的WAL模式虽支持并发读写，但文件句柄被占用时，Open()会无限等待。
解决：

• 检查任务管理器中是否有explorer.exe或devenv.exe进程持有该文件（用Process Explorer搜索文件名）；
• 在连接字符串末尾添加;FailIfMissing=False;，并捕获SQLiteException，重试前Thread.Sleep(100)；
• 血泪经验：开发阶段禁用Windows资源管理器预览窗格（注册表HKEY_CURRENT_USER\Software\Classes\Local Settings\Software\Microsoft\Windows\Shell\BagMRU清空）。

4.2 现象：插入中文字段后查询显示乱码（如“温度”变“涓害”）

原因：SQLite数据库文件本身无字符集声明，但System.Data.SQLite默认按Encoding.Default（Windows-936）读写，而C#字符串内部为UTF-16。当数据库用UTF-8创建（如DB Browser for SQLite默认），C#写入时未转码，导致字节错位。
解决：

• 统一用UTF-8创建数据库：用DB Browser for SQLite新建库时，选择Encoding: UTF-8；
• C#写入前强制转UTF-8字节数组再转字符串（不推荐）；
• 终极方案：连接字符串加;UTF8Encoding=True;（v1.0.115+支持），或升级到v1.0.118+并确保安装包含utf8标识。

4.3 现象：多线程同时插入，部分数据丢失，ExecuteNonQuery()返回1但数据库无记录

原因：未启用连接池或连接池大小不足，导致多个线程共用同一SQLiteConnection对象（非线程安全）。SQLite的connection对象不是线程安全的，即使WAL模式开启。
解决：

• 连接字符串必须设Pooling=true;Max Pool Size=10;；
• 每次CRUD操作必须创建新连接（using (var conn = new SQLiteConnection(...))），由连接池复用底层句柄；
• 绝对禁止将SQLiteConnection作为静态字段或单例注入。

4.4 现象：DateTime字段存入后查询出来时间偏移8小时（如存2024-01-01 12:00:00，查出2024-01-01 04:00:00）

原因：SQLite无DATETIME类型，存为TEXT时若用DateTime.Now.ToString()，会受系统时区影响；CURRENT_TIMESTAMP返回UTC时间，而C#DateTime.Now是本地时间。
解决：

• 统一用UTC时间存储：DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")；
• 查询时DateTime.Parse(...).ToUniversalTime()转UTC，业务层再转本地时间；
• 或在建表时用TEXT存ISO8601格式："2024-01-01T12:00:00.000Z"，避免时区歧义。

4.5 现象：程序退出后.db-journal和.db-wal文件残留，下次启动报database is locked

原因：未正确关闭连接或事务未提交，WAL文件未被SQLite自动清理。
解决：

• 强制关闭连接池：应用退出前调用SQLiteConnection.ClearAllPools()；
• 在finally块或using外显式调用conn.Close()（虽using会调用Dispose()，但某些旧版驱动Dispose不等于Close）；
• 启动时检查并删除残留WAL/JOURNAL文件（仅开发环境，生产环境应杜绝此操作）：

  if (File.Exists(dbPath + "-wal")) File.Delete(dbPath + "-wal"); if (File.Exists(dbPath + "-journal")) File.Delete(dbPath + "-journal");

5. 生产级加固：连接泄漏监控、Schema版本迁移与离线日志回传策略

5.1 监控连接泄漏：用WeakReference跟踪未释放的Connection

SQLite连接泄漏会导致文件句柄耗尽，最终Too many open files崩溃。以下代码在Debug模式下注入监控：

public static class SQLiteConnectionMonitor { private static readonly List<WeakReference> _activeConnections = new List<WeakReference>(); public static void TrackConnection(SQLiteConnection conn) { if (Debugger.IsAttached) { _activeConnections.Add(new WeakReference(conn)); } } public static void CheckLeaks() { if (!Debugger.IsAttached) return; var leaked = _activeConnections.Where(wr => wr.IsAlive).ToList(); if (leaked.Count > 5) // 超5个未释放连接触发警告 { Debug.WriteLine($"[SQLite Leak Alert] {leaked.Count} connections not disposed!"); // 可在此处触发Dump或发送告警 } } }

在SQLiteConnection构造后调用SQLiteConnectionMonitor.TrackConnection(conn)，在Dispose()前调用CheckLeaks()。这是我在三个工业项目中保住KPI的关键习惯——连接泄漏往往在压力测试时才暴露，提前埋点能省去三天抓包时间。

5.2 Schema版本迁移：用PRAGMA user_version实现无损升级

设备软件升级时，数据库结构常需变更（如加Humidity字段）。硬编码ALTER TABLE易出错，推荐用SQLite内置user_version：

public static void MigrateSchema() { using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); int currentVersion = GetSchemaVersion(conn); switch (currentVersion) { case 0: // 初始化 ExecuteSql(conn, "CREATE TABLE ..."); SetSchemaVersion(conn, 1); goto case 1; case 1: // v1 → v2：加湿度字段 ExecuteSql(conn, "ALTER TABLE SensorData ADD COLUMN Humidity REAL;"); SetSchemaVersion(conn, 2); break; default: throw new InvalidOperationException($"Unknown schema version: {currentVersion}"); } } } private static int GetSchemaVersion(SQLiteConnection conn) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = "PRAGMA user_version;"; return Convert.ToInt32(cmd.ExecuteScalar()); } } private static void SetSchemaVersion(SQLiteConnection conn, int version) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = $"PRAGMA user_version = {version};"; cmd.ExecuteNonQuery(); } }

优势：PRAGMA user_version是SQLite原子操作，不会因断电中断导致半升级状态；版本号存于数据库文件头，比维护单独version.txt更可靠。

5.3 离线日志回传：用WAL文件增量同步替代全量上传

设备常处于离线状态，需将本地SQLite日志在联网后同步至中心服务器。直接传.db文件效率低，且可能损坏。正确做法是提取WAL文件中的增量操作：

public static List<string> ExtractWALChanges(string dbPath) { var walPath = dbPath + "-wal"; if (!File.Exists(walPath)) return new List<string>(); var changes = new List<string>(); // WAL文件格式复杂，生产环境建议用sqlite3_wal_hook或第三方库sqlite-wal-parser // 此处简化：记录WAL文件最后修改时间，作为增量同步时间戳 changes.Add($"WAL_SYNC_TIME:{File.GetLastWriteTimeUtc(walPath):o}"); return changes; }

真实落地策略：

• 设备端每小时生成WAL快照（复制-wal文件为sync_20240101_120000.wal）；
• 联网后，中心服务器用sqlite3命令行工具回放：sqlite3 sensor.db ".read sync_20240101_120000.wal"；
• 回放成功后，设备端删除该WAL文件。

这比“上传整个DB再Merge”节省95%带宽，且避免冲突合并逻辑。

我带过的三个工业项目，上线后最常被客户问的问题不是“功能有没有”，而是“断网8小时后数据会不会丢”。把WAL增量同步做稳，比堆炫酷UI更能建立客户信任。希望帮到你。

本文还有配套的精品资源，点击获取