C#实战：SQLite二进制数据存取与Dapper高效操作

1. 为什么选择SQLite存储二进制数据在开发桌面应用、移动应用或小型服务时我们经常需要处理文件存储问题。传统做法是把文件保存在磁盘上然后在数据库里记录文件路径。但这种方式有几个明显的缺点文件容易丢失、备份困难、权限管理复杂。而SQLite的BLOBBinary Large Object类型恰好能解决这些问题。SQLite作为轻量级数据库特别适合嵌入到应用程序中。我做过一个图片管理工具最初用文件系统存储图片后来改用SQLite存储二进制数据发现几个优势数据一致性更好事务支持、备份简单只需复制一个.db文件、迁移方便。实测在.NET环境下读写10MB以下的文件性能差异可以忽略不计。2. 环境准备与项目搭建2.1 基础环境配置推荐使用Visual Studio 2022或VS Code开发需要安装.NET 5 SDK。通过NuGet安装以下关键包Microsoft.Data.Sqlite官方SQLite驱动Dapper轻量级ORM工具dotnet add package Microsoft.Data.Sqlite dotnet add package Dapper创建控制台项目后建议按这个结构组织代码/Models - TestModel.cs /Services - FileService.cs - DatabaseService.cs Program.cs2.2 数据库初始化先创建带BLOB字段的测试表。我习惯用这个SQL初始化using var connection new SqliteConnection(Data Sourcetest.db); connection.Execute( CREATE TABLE IF NOT EXISTS files ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, content_type TEXT, data BLOB, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ));注意几个细节BLOB类型在不同数据库方言中可能有差异SQLite直接用BLOB关键字建议添加content_type字段记录原始文件类型自动记录创建时间便于后续查询3. 二进制数据存储实战3.1 文本内容存储方案处理文本转二进制存储时最容易踩的坑是编码问题。来看我的改进版代码public static int SaveText(string text) { var encoding Encoding.UTF8; // 明确指定编码 byte[] byteData encoding.GetBytes(text); using var conn new SqliteConnection(GetConnectionString()); var parameters new { name config.json, content_type application/json, data byteData }; return conn.Execute( INSERT INTO files(name, content_type, data) VALUES(name, content_type, data), parameters); }关键改进点显式指定UTF8编码避免乱码使用匿名对象代替DynamicParameters简化代码添加content_type帮助后续处理3.2 文件存储最佳实践存储图片/PDF等文件时我推荐使用分块读取方式处理大文件public async Taskint SaveLargeFile(string filePath) { const int bufferSize 81920; // 80KB缓冲区 byte[] buffer new byte[bufferSize]; using var fileStream File.OpenRead(filePath); using var memoryStream new MemoryStream(); int bytesRead; while ((bytesRead await fileStream.ReadAsync(buffer, 0, buffer.Length)) 0) { await memoryStream.WriteAsync(buffer, 0, bytesRead); } var fileInfo new FileInfo(filePath); var parameters new { name fileInfo.Name, content_type GetMimeType(fileInfo.Extension), data memoryStream.ToArray() }; using var conn new SqliteConnection(GetConnectionString()); return await conn.ExecuteAsync( INSERT INTO files(name, content_type, data) VALUES(name, content_type, data), parameters); }这个方法有三个优势分块读取避免内存溢出异步操作提升性能自动识别文件类型4. 数据读取与还原技巧4.1 基础读取方法从数据库读取二进制数据时Dapper默认会将BLOB字段映射为byte[]。基本读取操作public FileModel GetFile(int id) { using var conn new SqliteConnection(GetConnectionString()); return conn.QueryFirstOrDefaultFileModel( SELECT id, name, content_type, data FROM files WHERE id id, new { id }); }这里定义了一个FileModel类public class FileModel { public int Id { get; set; } public string Name { get; set; } public string ContentType { get; set; } public byte[] Data { get; set; } }4.2 文件还原实战将二进制数据写回文件时要注意文件权限和目录存在性检查public void SaveToDisk(int fileId, string targetPath) { var file GetFile(fileId); if (file?.Data null) return; var directory Path.GetDirectoryName(targetPath); if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } using var fs new FileStream(targetPath, FileMode.Create); fs.Write(file.Data, 0, file.Data.Length); fs.Flush(); // 保持原始文件扩展名 if (!Path.GetExtension(targetPath).Equals( Path.GetExtension(file.Name), StringComparison.OrdinalIgnoreCase)) { var newPath Path.ChangeExtension(targetPath, Path.GetExtension(file.Name)); File.Move(targetPath, newPath); } }这段代码处理了几个关键问题自动创建目标目录确保文件流正确释放保持原始文件扩展名5. 性能优化与常见问题5.1 批量插入优化需要批量插入文件时事务处理能大幅提升性能public int BulkInsert(IEnumerablestring filePaths) { using var conn new SqliteConnection(GetConnectionString()); conn.Open(); using var transaction conn.BeginTransaction(); try { var count 0; foreach (var path in filePaths) { var bytes File.ReadAllBytes(path); conn.Execute( INSERT INTO files(name, data) VALUES(name, data), new { name Path.GetFileName(path), data bytes }, transaction); count; } transaction.Commit(); return count; } catch { transaction.Rollback(); throw; } }实测插入100个1MB文件无事务约12秒带事务约3秒5.2 内存管理技巧处理大文件时要特别注意内存使用使用FileStream直接读取到数据库避免全加载到内存设置合理的SQLite缓存大小// 在连接字符串中添加缓存配置 Data Sourcetest.db;Cache Size5000 // 5MB缓存5.3 常见错误排查database is locked错误检查是否有多线程同时写操作增加busy_timeout参数Data Sourcetest.db;Busy Timeout5000 // 5秒等待BLOB数据损坏验证读取的byte[]长度是否与写入时一致使用校验和验证var checksum BitConverter.ToString(SHA256.HashData(fileData));6. 实际应用案例最近给客户做的文档管理系统就采用了这种方案。需求特点是需要管理10万的PDF/Word文档要求支持全文检索需要定期备份我的实现方案文档内容存BLOB字段文本内容提取后存单独字段供检索用SQLite的备份API实现热备份关键代码片段// 备份数据库 public void BackupDatabase(string backupPath) { using var source new SqliteConnection(Data Sourcemain.db); using var destination new SqliteConnection($Data Source{backupPath}); source.Open(); destination.Open(); source.BackupDatabase(destination); } // 带文本提取的文档存储 public void SaveDocument(string path) { var bytes File.ReadAllBytes(path); var textContent ExtractText(bytes); // 使用Apache Tika等库 using var conn new SqliteConnection(GetConnectionString()); conn.Execute( INSERT INTO documents(path, content, raw_data) VALUES(path, content, rawData), new { path, content textContent, rawData bytes }); }这个方案比传统文件系统存储节省了30%的备份时间且检索速度提升明显。