对数据库和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_xx，Update-Database 即可，或者在程序初始化处使用 MigrateDatabaseToLatestVersion 就能无需执行 Update-Database 自动迁移（因生产环境中可能不便使用控制台）；当然也可以导出 SQL 脚本来进行数据库迁移。

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

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

我选择的替代是 PostgreSQL https://www.postgresql.org/（以下简称 PG），它是可以免费商用的。PG 的 SQL 语法是大小写敏感的，这不同于 SQL Server 的 T-SQL。没被双引号包括的大写字符会被自动转为小写字符，当使用 Npqsql 和 EF6 / EFCore 时，默认会将你的 .NET 类、属性名套上双引号。—— 那么， 当你用 O/RM 建表后再有写 SQL 的需要时，得自己给表名、栏位名加上双引号，见 这里 和 这里 。

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

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

.NET 下使用 PG 往往用到如下这几个包。

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