应用嵌入式数据库实战：access VS sqlite

1.背景

最近写了个监听文件、上传文件的工具，其实可以使用FileSystemWatcher 类实现，但是FileSystemWatcher 类对于同一时间监听多个文件变化的场景不太友好，

容易丢失监听变化的数据（此问题是FileSystemWatcher 依赖于系统的Buffer缓冲区大小，而这个大小是有限的，大数据量涌进造成泄露）。FileSystemWatcher 内容参考：

<a>https://docs.microsoft.com/zh-cn/dotnet/api/system.io.filesystemwatcher?view=net-6.0</a>

所以我自己通过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不太友好