对数据库和O/RM框架的流水式小结

黄杰, 2021-11-08
root[a]linuxsand.info

目录:

场景简介

我的场景是客户端/服务器(C/S)版桌面工控软件(.NET),有两类数据库使用工况:

  1. 软件自身的微量数据和动态配置类数据
  2. 软件运行时产生的大量过程类数据

第一种工况下我使用 SQLite 嵌入式数据库,用 Dapper 微框架作为 O/RM(对象-关系映射)来操作数据库。第二种工况下我使用 SQL Server 2019 Express 或 PostgreSQL 作为数据库,使用 Entity Framework 6(以下简称 EF6)作为 O/RM 框架。

SQLite 和 Dapper

在快速批量部署时,SQLite 无需安装配置的特性是很吸引人的。我不想每台机器部署时,都要拷贝大体积的数据库软件安装包,执行安装、配置等操作,这对于现场实施人员来说是不友好的,也是有错误操作风险的。因此在能够只用 SQLite 的场合,我尽可能会用它。

https://nuget.org 上有一系列相关的软件包:

安装软件包后,是直接通过 ADO.NET provider 来操作数据库,比较冗长。

// columns in db table 'blacklist'
// sku_desc     process      speed_percent      blow_time_in_sec    suck_time_in_sec
public class Blacklist
{
    public static void QueryBlacklist(string skuDesc)
    {
        using (var connection = NewOpenedDatabaseConnection())
        {
            var command = connection.CreateCommand();
            command.CommandText = $"select process, speed_percent, " +
                $"blow_time_in_sec, suck_time_in_sec " +
                $"from blacklist where '{skuDesc}' like sku_desc;";
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    int process = reader.GetInt32(0);
                    int speedOverride = reader.GetInt32(1); // unit: percent %
                    float blowTimeInSeconds = reader.GetFloat(2); // unit: seconds
                    float suckTimeInSeconds = reader.GetFloat(3); // unit: seconds
                    NaiLogger.LogInfo($"{process} | {speedOverride} | " +
                        $"{blowTimeInSeconds} | {suckTimeInSeconds}");
                }
                catch (Exception e) 
                {
                    NaiLogger.LogError(e.ToString());
                    throw; 
                }
                break;
            } // end of while
        } // end of using

    } // end of method
} // end of class

而使用 Dapper https://github.com/DapperLib/Dapper 这个微框架后,代码可以得到简化;Dapper 为 IDbConnection 增加了许多拓展方法,能够自动将查出的数据映射到相应模型实例的属性上。

    public class BlacklistActionModel
    {
        public int Id { get; set; }
        public string SkuDesc { get; set; }

        public int Process { get; set; }
        public int SpeedPercent { get; set; }
        public float BlowTime { get; set; }
        public float SuckTime { get; set; }
    }

    public class Blacklist
    {
        public static void QueryBlacklist(string skuDesc)
        {
            using (var connection = NewOpenedDatabaseConnection())
            {
                var action = connection.Query<BlacklistActionModel>(
                                $"select process Process, speed_percent SpeedPercent, " + 
                                $"blow_time_in_sec BlowTime, suck_time_in_sec SuckTime " +
                                $"from blacklist where '{skuDesc}' like sku_desc;";
                             ).FirstOrDefault();
                if (action == null) return;
                NaiLogger.LogInfo($"{action.Process} | {action.SpeedOverride} | " + 
                                "{action.BlowTimeInSeconds} | {action.SuckTimeInSeconds}");
            } // end of using

        } // end of method  
    } // end of class

Dapper 的性能是很好的,它是由 Stack Overflow 的工程师们发起的开源项目,用于 Stack Overflow 的生产环境中。

SQL Server Express(PostgreSQL) 和 Entity Framework

当遇到需要存储大量数据,并且表结构经常要变化的项目时,我选用 SQL Server 2019 Express 和 Entity Framework 的搭配。先说 Entity Framework https://www.nuget.org/packages/EntityFramework/

EF 解放了手写 SQL、手工管理数据库连接的工作,大大提高了开发效率,程序员可以直接用 Linq 编写查询功能。

public class BlacklistActionModel
{
    public int Id { get; set; }
    public string SkuDesc { get; set; }

    public int Process { get; set; }
    public int SpeedPercent { get; set; }
    public float BlowTime { get; set; }
    public float SuckTime { get; set; }
}

public class ContextBlacklist: DbContext
{
    public ContextBlacklist(): base("connection string") {} // 也可以通过配置文件传入

    public DbSet<BlacklistActionModel> BlacklistActions { get; set; }
}

public class Test
{
    public void TestCURD()
    {
        using (var ctx = new ContextBlacklist())
        {
            // Linq 读取记录(method syntax)
            var rows = ctx.BlacklistActions.Where(q => q.Process == 1).ToList();
            foreach (var row in rows) NaiLogger.LogDebug(row.ToString());

            // Linq 插入记录(method syntax)
            ctx.BlacklistActions.Add(new BlacklistActionModel() { ... });
            // ctx.SaveChanges();

            // Linq 更新记录(method syntax)
            var row = ctx.BlacklistActions.Where(q => q.SkuDesc == "sku name").FirstOrDefault();
            if (row != null) row.SpeedPercent = 42;
            ctx.SaveChanges()
        } // end of using

    } // end of method
} // end of class

很多人说 EF6 性能不行,但是对我来说足够了。几个小贴士可以显著提升性能:

另一个我喜欢的功能是自动迁移(Auto Migration)。当模型发生变化时,例如增加数据表栏位(column)时,在控制台执行 Add-Migration changelog_xxUpdate-Database 即可,或者在程序初始化处使用 MigrateDatabaseToLatestVersion 就能无需执行 Update-Database 自动迁移(因生产环境中可能不便使用控制台)。


说完了 EF,再说回 SQL Server。我对 SQL Server 没有任何抱怨。

不过由于我使用了免费版本(2019 Express),它的限制之一是数据库文件的最大体积为 10GB。而这个限制在需要储存大量数据时就没法用了,不购买标准版或企业版 SQL Server的话,就要更换数据库软件。

我选择的替代是 PostgreSQL https://www.postgresql.org/(以下简称 PG),它是可以免费商用的。PG 的 SQL 语法相比于 SQL Server 的 T-SQL,最大的不同是表名、栏位名需要用引号括起来,而且被引号括起来的内容是大小写敏感的。

-- SQL Server T-SQL
select * from dbo.blacklistactions where process=1;

-- PG SQL
select * from dbo."BlacklistActions" where "Process"=1;

那么,如果你是手工编写 SQL 来操作数据库的,从 SQL Server 迁移到 PG,你得仔细查找并修改代码。

幸好我使用了 O/RM 框架,因此更换数据库几乎没有什么额外工作,安装几个包来连接 PG 即可。

使用 EF 操作 PG 也不是没有坑,https://github.com/npgsql/EntityFramework6.Npgsql/issues/62 描述了一则问题 42P08: could not determine data type of parameter $1 和临时解决办法。


推广:欢迎关注我的微信公号 NaiStudio