应用嵌入式数据库实战：access VS sqlite
1.背景
最近写了个监听文件、上传文件的工具，其实可以使用FileSystemWatcher 类实现，但是FileSystemWatcher 类对于同一时间监听多个文件变化的场景不太友好，
容易丢失监听变化的数据（此问题是FileSystemWatcher 依赖于系统的Buffer缓冲区大小，而这个大小是有限的，大数据量涌进造成泄露）。FileSystemWatcher 内容参考：
https://docs.microsoft.com/zh-cn/dotnet/api/system.io.filesystemwatcher?view=net-6.0
所以我自己通过hangfire的延时任务实现了定时监听上传的工具
var jobId = BackgroundJob.Schedule(
() => Console.WriteLine("Delayed!"),
TimeSpan.FromDays(7));
核心处理逻辑则是通过当前扫描的文件信息与上次结果做对比，只有文件大小不再发生变化，或者在上次上传后又再次修改的文件才符合上传的规则，而文件的信息需要保存。
2.文件存储技术选型
拍脑子一想，有这么几个选型：
1）内存
2）文件（json、txt）
3）嵌入式数据库
做一下分析：内存不靠谱，容易丢数据；文件操作太麻烦，不利于检索写入；那就只剩下嵌入式数据库了，一搜嵌入式数据库也有很多，也就是咱们这次分享的主角：access 与 sqlite
3.access数据库
通过依赖nuget：System.Data.OleDb; 然后写一个Repo方法即可：
public static class FileInfoRepository
{
private static OleDbConnection conn = new OleDbConnection
($"Provider= Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password=;Data Source={AppDomain.CurrentDomain.BaseDirectory}Db\\mydb.accdb;");
/// <summary>
/// 根据sql查询文件信息，返回实体
/// </summary>
/// <param name="querySql"></param>
/// <returns></returns>
public static MonitorFileInfoModel GetByFileId(string querySql)
{
OleDbDataAdapter inst = new OleDbDataAdapter(); //实例化
SelectSQL(querySql, ref inst); //执行查询方法
DataTable dt = new DataTable(); //创建DataTable
inst.Fill(dt);
conn.Close();
IList<MonitorFileInfoModel> res = DataTableHelper.ConvertTo<MonitorFileInfoModel>(dt);
return res.FirstOrDefault();
}
public static bool WriteEntity(string writeSql)
{
var res = WriteSQL(writeSql);
conn.Close();
return res;
}
/// <summary>
/// 执行查找语句
/// </summary>
/// <param name="sql">要执行的语句</param>
/// <returns></returns>
private static void SelectSQL(string sql, ref OleDbDataAdapter inst)
{
try
{
Open();
inst = new OleDbDataAdapter(sql, conn);
}
catch (Exception ex)
{
LogHelper.Error($"SelectSQL 发生错误:{ex.Message}");
}
}
/// <summary>
/// 执行添加、删除、更新语句，判断是否成功
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <returns>成功则返回True</returns>
public static bool WriteSQL(string sql)
{
try
{
Open(); //调用连接
OleDbCommand cmd = new OleDbCommand();
cmd = new OleDbCommand(sql, conn);
if (cmd.ExecuteNonQuery() > 0) //判断是否执行
{
cmd.Parameters.Clear(); //清空sql语句
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
LogHelper.Error($"WriteSQL 发生错误:{ex.Message}");
return false;
}
}
/// <summary>
/// 是否连接数据库，连接成功则返回True
/// </summary>
private static bool Open()
{
try
{
conn.Open();
return conn.State == System.Data.ConnectionState.Open; //判断是否打开
}
catch (Exception ex)
{
LogHelper.Error($"Open 发生错误:{ex.Message}");
return false;
}
}
}
这种方式操作数据没有问题，但是有个不好的地方，需要在电脑上安装access database，还要依赖office（如上我写的driver，需要安装office2007+）
参考：https://docs.microsoft.com/en-us/previous-versions/troubleshoot/winautomation/support-tips/databases/ace-oledb-12-0-provider-not-registered-on-local-machine
4.sqlite数据库
1）通过下载https://sqlite.org/download.html
在E盘software文件夹下解压，然后将dll解压后的文件放在tools下，进入文件夹cmd，执行命令sqlite3 my.db
然后通过Navicat连接这个db文件，然后设计表与字段。
2）操作sqlite代码
增加依赖nuget： System.Data.SQLite
public class FileInfoRepository
{
private object lockThis = new object();
private static string connectStr = $"Data Source = {AppDomain.CurrentDomain.BaseDirectory}Db\\file_monitor.db; Version=3;Pooling=true;FailIfMissing=false;Journal Mode=WAL";
/// <summary>
/// 根据fileId查询文件信息，返回实体
/// </summary>
/// <param name="fileId"></param>
/// <returns></returns>
public MonitorFileInfoDb GetByFileId(string fileId)
{
string sql = $"select * from file_info where fileId = '{fileId}'";
try
{
lock (lockThis)
{
using (SQLiteConnection connection = new SQLiteConnection(connectStr))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long size = reader["size"] == null ? 0 : (long)reader["size"];
string uploadTime = reader["uploadTime"] == null ? DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss") : reader["uploadTime"].ToString();
MonitorFileInfoDb result = new MonitorFileInfoDb
{
fileId = fileId,
size = size,
uploadTime = uploadTime
};
return result;
}
reader.Close();
}
}
}
}
catch (Exception ex)
{
LogHelper.Error($"---执行GetByFileId异常:{JsonConvert.SerializeObject(ex)}");
}
return null;
}
/// <summary>
/// 新增文件信息
/// </summary>
/// <param name="fileId"></param>
/// <param name="size"></param>
/// <returns></returns>
public bool AddEntity(string fileId,long size)
{
string uploadTime = DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss");
string sql = $"insert into file_info (fileId, size, uploadTime) values ('{fileId}',{size},'{uploadTime}')";
try
{
return ExecuteNonQuery(sql);
}
catch (Exception ex)
{
LogHelper.Error($"---执行AddEntity异常:{JsonConvert.SerializeObject(ex)}");
}
return false;
}
/// <summary>
/// 修改实体Size值
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool UpdateEntitySize(string fileId, long size)
{
string sql = $"update file_info set size = {size} where fileId='{fileId}'";
try
{
return ExecuteNonQuery(sql);
}
catch (Exception ex)
{
LogHelper.Error($"---执行UpdateEntitySize异常:{JsonConvert.SerializeObject(ex)}");
}
return false;
}
/// <summary>
/// 修改实体UploadTime值
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool UpdateEntityUploadTime(string fileId, DateTime uploadTime)
{
string sql = $"update file_info set uploadTime = '{uploadTime.ToString("yyyy-MM-dd HH:mm:ss")}' where fileId='{fileId}'";
try
{
return ExecuteNonQuery(sql);
}
catch (Exception ex)
{
LogHelper.Error($"---执行UpdateEntityUploadTime异常:{JsonConvert.SerializeObject(ex)}");
}
return false;
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private bool ExecuteNonQuery(string sql)
{
try
{
lock (lockThis)
{
using (SQLiteConnection connection = new SQLiteConnection(connectStr))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
int effectCount = command.ExecuteNonQuery();
if (effectCount > 0)
{
return true;
}
}
}
}
}
catch (Exception ex)
{
LogHelper.Error($"---执行ExecuteNonQuery异常:{JsonConvert.SerializeObject(ex)}");
}
return false;
}
}
使用lock的原因是避免并行的时候，会发生sqlite3 database is locked问题，代码中对connection使用开启，用完关闭，保证资源释放；没有使用efcore是因为efcore操作sqlite不太友好
