.NET 本地Db数据库-技术方案选型
<p>公司现有项目使用了LiteDB作为本地数据存储,但每次开机有较高的概率读取阻塞。</p><p>因为死锁或者损坏导致的阻塞问题,目前只能设置超时。在db读取超时后,部分情况可以删除文件、重建db解决,也有无法删除db文件的情况。</p>
<p>导致的技术债务造成了非常多的冗余维护工作量,需要基于常用的数据库及使用方式,重新做个技术选型确认</p>
<p>LiteDB,是一类NoSql的文档数据库,引用Nuget包LiteDB对接开发,社区litedb-org/LiteDB: LiteDB - A .NET NoSQL Document Store in a single data file</p>
<p>在Windows本地数据存储场景中主要有Sqlite、LiteDB、LocalDB几个主要选项</p>
<h1>Windows本地数据库选型</h1>
<p>.NET Windows 本地数据库中 SQLite、LiteDB、LocalDB 的对比,CodeX生成如下:</p>
<table>
<thead>
<tr><th>维度</th><th>SQLite</th><th>LiteDB</th><th>LocalDB (SQL Server Express LocalDB)</th></tr>
</thead>
<tbody>
<tr>
<td>数据模型</td>
<td>关系型(SQL)</td>
<td>文档型(BSON)</td>
<td>关系型(SQL Server 子集)</td>
</tr>
<tr>
<td>语言/协议</td>
<td>SQL</td>
<td>类 NoSQL API / LINQ</td>
<td>T‑SQL(完整 SQL Server 语法)</td>
</tr>
<tr>
<td>部署</td>
<td>单文件,零安装</td>
<td>单文件,零安装</td>
<td>需安装 LocalDB runtime</td>
</tr>
<tr>
<td>依赖</td>
<td>SQLite 引擎</td>
<td>纯 .NET(无需 native)</td>
<td>SQL Server LocalDB 组件</td>
</tr>
<tr>
<td>体积/性能</td>
<td>极小、快</td>
<td>极小、快(适合小规模)</td>
<td>较大、重</td>
</tr>
<tr>
<td>并发能力</td>
<td>多读单写</td>
<td>多读单写</td>
<td>多用户/多连接更强</td>
</tr>
<tr>
<td>事务</td>
<td>支持</td>
<td>支持</td>
<td>支持(完整)</td>
</tr>
<tr>
<td>ORM 支持</td>
<td>很成熟(EF Core)</td>
<td>限制(非 EF)</td>
<td>极好(EF Core)</td>
</tr>
<tr>
<td>跨平台</td>
<td>完全跨平台</td>
<td>完全跨平台</td>
<td>仅 Windows</td>
</tr>
<tr>
<td>典型使用场景</td>
<td>轻量关系型本地库</td>
<td>轻量文档型嵌入库</td>
<td>需要 SQL Server 兼容性的本地库</td>
</tr>
</tbody>
</table>
<h2>1. SQLite</h2>
<p>特点:</p>
<ul>
<li>单文件存储(关系型数据库),零安装</li>
<li>SQL 语法,支持事务、索引、视图(有限)</li>
<li>EF Core 支持成熟</li>
<li>高度跨平台(Windows、Linux、Mac、Mobile)</li>
</ul>
<p>适合:</p>
<ul>
<li>轻量关系型数据</li>
<li>需要 SQL / ORM 的桌面应用</li>
<li>高兼容+小体积优先</li>
</ul>
<p>劣势:</p>
<ul>
<li>并发写能力有限(多读单写)</li>
<li>缺少部分高级 SQL Server 特性</li>
</ul>
<h2>2. LiteDB</h2>
<p>特点:</p>
<ul>
<li>纯 .NET 嵌入式文档数据库(BSON)</li>
<li>不依赖 native DLL</li>
<li>类 MongoDB 的使用体验</li>
<li>单文件存储</li>
</ul>
<p>适合:</p>
<ul>
<li>非结构化/半结构化数据</li>
<li>简单应用配置、缓存、日志、轻量数据持久化</li>
<li>不想写 SQL</li>
</ul>
<p>劣势:</p>
<ul>
<li>不支持 EF Core</li>
<li>社区生态小于 SQLite</li>
<li>并发/事务能力相对弱一些</li>
</ul>
<h2>3. LocalDB(SQL Server LocalDB)</h2>
<p>**特点: SQL Server Express 的轻量模式</p>
<ul>
<li>完整 T‑SQL 语法</li>
<li>与 SQL Server 高度一致,便于迁移</li>
<li>支持丰富特性(存储过程、视图、触发器等)</li>
</ul>
<p>适合:</p>
<ul>
<li>开发/测试环境需要模拟 SQL Server</li>
<li>需要复杂 SQL、视图、存储过程</li>
<li>将来要迁移到 SQL Server 的桌面应用</li>
</ul>
<p>劣势:</p>
<ul>
<li>仅 Windows</li>
<li>需要安装 LocalDB 组件</li>
<li>体积大、启动相对慢</li>
</ul>
<h2>数据库选型建议</h2>
<h3>1. 死锁损坏问题</h3>
<p>按上面收集的情况,litedb存在频繁的db死锁损坏问题</p>
<p>SQLite 是否也会卡死?对比分析</p>
<div>
<p><img src="https://img2024.cnblogs.com/blog/685541/202603/685541-20260303150919300-1020574872.png" alt="image" loading="lazy"></p>
</div>
<p>SQLite这类"卡死"及损坏问题概率会相对较少。 原因如下:</p>
<p>1. SQLite 有内置的 busy_timeout 机制,写锁冲突时会自动等待+重试,超时后返回错误,不会无限阻塞</p>
<p>2. WAL 模式下读写不互相阻塞,只有写-写冲突</p>
<p>3. 多个连接实例访问同一文件是 SQLite 的正常用法,而 LiteDB 在这种模式下就容易死锁</p>
<p>4. SQLite 的锁机制经过 20+ 年生产环境验证</p>
<p>根据已知的社区反馈,liteDb在并发读写这块有较多问题。LiteDB 的锁机制在高并发场景下天然脆弱,而 SQLite 的 WAL 模式能更好地支持并发读写,且生态更成熟、调试工具更丰富。</p>
<h3>2.社区成熟度</h3>
<p>考虑到社区成熟度的情况。LiteDb Github仓库已知大量死锁问题,Nuget引用量37.8M不算高;而Sqlite是windows客户端本地标准成熟的方案了</p>
<p><img src="https://img2024.cnblogs.com/blog/685541/202602/685541-20260226163458029-563770887.png" alt="image" loading="lazy"></p>
<h3><strong>3.性能对比</strong></h3>
<p>拆成 5 个指标看:</p>
<ul>
<li>冷启动延迟:SQLite/LiteDB 常更快;LocalDB 首次唤醒可能慢。</li>
<li>单条写入:SQLite/LiteDB 都可以很快;是否开事务影响巨大。</li>
<li>批量写入:SQLite 在“单事务 + 预编译语句”下通常非常强。</li>
<li>复杂查询:SQLite/LocalDB 通常明显优于 LiteDB。</li>
<li>并发读写:LocalDB 多并发能力更完整;SQLite 读并发强、写锁模型需设计;LiteDB 在高并发场更容易到瓶颈。</li>
</ul>
<p>纯读写吞吐(尤其批量写):通常 SQLite ≥ LocalDB > LiteDB(具体取决于索引、事务、同步模式、数据模型)</p>
<p><strong>所以大部分情况选用Sqlite</strong>。如果是其它小场景的需求,对象存储可以选文档型数据库LiteDB, 要兼容 SQL Server可以选LocalDB</p>
<h1>Sqlite使用方式选型</h1>
<p>.NET sqlte数据库支持包:</p>
<ul>
<li>Microsoft.EntityFrameworkCore.Sqlite</li>
<li>Microsoft.Data.Sqlite</li>
<li>System.Data.SQLite.Core</li>
</ul>
<p>转换数据类有以下几种方式:</p>
<ul>
<li>Microsoft.EntityFrameworkCore</li>
<li>Dapper</li>
<li>SqlSugar</li>
</ul>
<p>所以.NET读写数据库有几下方案:</p>
<table>
<thead>
<tr><th>方案</th><th>必需依赖(NuGet)</th><th>使用方式概述</th><th>性能/开销</th></tr>
</thead>
<tbody>
<tr>
<td>EF Core + EFCore.Sqlite</td>
<td>
<p>Microsoft.EntityFrameworkCore</p>
<p>Microsoft.EntityFrameworkCore.Sqlite</p>
</td>
<td>DbContext + LINQ + Migrations</td>
<td>中(有跟踪/映射开销)</td>
</tr>
<tr>
<td>EF Core + Microsoft.Data.Sqlite(手写迁移SQL)</td>
<td>
<p>Microsoft.EntityFrameworkCore</p>
<p>Microsoft.EntityFrameworkCore.Sqlite</p>
<p>Microsoft.Data.Sqlite</p>
</td>
<td>DbContext + 手写SQL迁移/修表</td>
<td>中(可控性更高)</td>
</tr>
<tr>
<td>Dapper + Microsoft.Data.Sqlite</td>
<td>Dapper<br>Microsoft.Data.Sqlite</td>
<td>手写SQL + 轻量映射</td>
<td>高(最轻薄)</td>
</tr>
<tr>
<td>SqlSugar + Microsoft.Data.Sqlite</td>
<td>SqlSugarCore<br>Microsoft.Data.Sqlite</td>
<td>ORM + CodeFirst/DbFirst</td>
<td>中~高(配置得当)</td>
</tr>
</tbody>
</table>
<p>以下分别给出4种方案,完成.NET的数据库读写以及表迁移</p>
<p>数据库表迁移目标(V1 -> V2)</p>
<ul>
<li>V1 表:<code>Users(Id, Name, Email)</code></li>
<li>V2 表:<code>Users(Id, Name, Email, Age)</code></li>
<li>迁移数据规则:给历史数据 <code>Age</code> 设为 <code>18</code></li>
</ul>
<h3>EF Core + EFCore.Sqlite</h3>
<p>EF Core,适合快速开发、团队熟悉 .NET 官方生态。但映射存在一定的性能开销</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)"> 1</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Microsoft.EntityFrameworkCore;
</span><span style="color: rgba(0, 128, 128, 1)"> 2</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Microsoft.EntityFrameworkCore.Migrations;
</span><span style="color: rgba(0, 128, 128, 1)"> 3</span>
<span style="color: rgba(0, 128, 128, 1)"> 4</span> <span style="color: rgba(0, 0, 255, 1)">var</span> db = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> AppDbContext();
</span><span style="color: rgba(0, 128, 128, 1)"> 5</span> db.Database.Migrate(); <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 纯代码触发迁移
</span><span style="color: rgba(0, 128, 128, 1)"> 6</span>
<span style="color: rgba(0, 128, 128, 1)"> 7</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写</span>
<span style="color: rgba(0, 128, 128, 1)"> 8</span> db.Users.Add(<span style="color: rgba(0, 0, 255, 1)">new</span> User { Name = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Alice</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Email = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">alice@test.com</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Age = <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)"> });
</span><span style="color: rgba(0, 128, 128, 1)"> 9</span> <span style="color: rgba(0, 0, 0, 1)">db.SaveChanges();
</span><span style="color: rgba(0, 128, 128, 1)">10</span>
<span style="color: rgba(0, 128, 128, 1)">11</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 读</span>
<span style="color: rgba(0, 128, 128, 1)">12</span> <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> u <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> db.Users.AsNoTracking())
</span><span style="color: rgba(0, 128, 128, 1)">13</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">14</span> Console.WriteLine($<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{u.Id} {u.Name} {u.Email} Age={u.Age}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">15</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">16</span>
<span style="color: rgba(0, 128, 128, 1)">17</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> AppDbContext : DbContext
</span><span style="color: rgba(0, 128, 128, 1)">18</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">19</span> <span style="color: rgba(0, 0, 255, 1)">public</span> DbSet<User> Users => Set<User><span style="color: rgba(0, 0, 0, 1)">();
</span><span style="color: rgba(0, 128, 128, 1)">20</span>
<span style="color: rgba(0, 128, 128, 1)">21</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> OnConfiguring(DbContextOptionsBuilder options)
</span><span style="color: rgba(0, 128, 128, 1)">22</span> => options.UseSqlite(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Data Source=efcore_sqlite_demo.db</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">23</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">24</span>
<span style="color: rgba(0, 128, 128, 1)">25</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)">26</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">27</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> Id { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">28</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Name { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">29</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Email { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">30</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span>? Age { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">31</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">32</span>
<span style="color: rgba(0, 128, 128, 1)">33</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> ====== 迁移1:Init ======</span>
<span style="color: rgba(0, 128, 128, 1)">34</span>
</span><span style="color: rgba(0, 128, 128, 1)">35</span>
</span><span style="color: rgba(0, 128, 128, 1)">36</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> Init : Migration
</span><span style="color: rgba(0, 128, 128, 1)">37</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">38</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Up(MigrationBuilder migrationBuilder)
</span><span style="color: rgba(0, 128, 128, 1)">39</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">40</span> <span style="color: rgba(0, 0, 0, 1)"> migrationBuilder.CreateTable(
</span><span style="color: rgba(0, 128, 128, 1)">41</span> name: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Users</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">42</span> columns: table => <span style="color: rgba(0, 0, 255, 1)">new</span>
<span style="color: rgba(0, 128, 128, 1)">43</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">44</span> Id = table.Column<<span style="color: rgba(0, 0, 255, 1)">int</span>>(nullable: <span style="color: rgba(0, 0, 255, 1)">false</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 128, 1)">45</span> .Annotation(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Sqlite:Autoincrement</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">),
</span><span style="color: rgba(0, 128, 128, 1)">46</span> Name = table.Column<<span style="color: rgba(0, 0, 255, 1)">string</span>>(nullable: <span style="color: rgba(0, 0, 255, 1)">false</span><span style="color: rgba(0, 0, 0, 1)">),
</span><span style="color: rgba(0, 128, 128, 1)">47</span> Email = table.Column<<span style="color: rgba(0, 0, 255, 1)">string</span>>(nullable: <span style="color: rgba(0, 0, 255, 1)">false</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 128, 1)">48</span> <span style="color: rgba(0, 0, 0, 1)"> },
</span><span style="color: rgba(0, 128, 128, 1)">49</span> constraints: table => table.PrimaryKey(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">PK_Users</span><span style="color: rgba(128, 0, 0, 1)">"</span>, x =><span style="color: rgba(0, 0, 0, 1)"> x.Id));
</span><span style="color: rgba(0, 128, 128, 1)">50</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">51</span>
<span style="color: rgba(0, 128, 128, 1)">52</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Down(MigrationBuilder migrationBuilder)
</span><span style="color: rgba(0, 128, 128, 1)">53</span> => migrationBuilder.DropTable(name: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Users</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">54</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">55</span>
<span style="color: rgba(0, 128, 128, 1)">56</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> ====== 迁移2:AddAgeAndBackfill ======</span>
<span style="color: rgba(0, 128, 128, 1)">57</span>
</span><span style="color: rgba(0, 128, 128, 1)">58</span>
</span><span style="color: rgba(0, 128, 128, 1)">59</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> AddAgeAndBackfill : Migration
</span><span style="color: rgba(0, 128, 128, 1)">60</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">61</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Up(MigrationBuilder migrationBuilder)
</span><span style="color: rgba(0, 128, 128, 1)">62</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">63</span> migrationBuilder.AddColumn<<span style="color: rgba(0, 0, 255, 1)">int</span>><span style="color: rgba(0, 0, 0, 1)">(
</span><span style="color: rgba(0, 128, 128, 1)">64</span> name: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Age</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">65</span> table: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Users</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">66</span> nullable: <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">67</span>
<span style="color: rgba(0, 128, 128, 1)">68</span> migrationBuilder.Sql(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">UPDATE Users SET Age = 18 WHERE Age IS NULL;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">69</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">70</span>
<span style="color: rgba(0, 128, 128, 1)">71</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Down(MigrationBuilder migrationBuilder)
</span><span style="color: rgba(0, 128, 128, 1)">72</span> => migrationBuilder.DropColumn(name: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Age</span><span style="color: rgba(128, 0, 0, 1)">"</span>, table: <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Users</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">73</span> }</pre>
</div>
<h3>EF Core + 补充手写sql</h3>
<p>如果既想用 EF Core,又希望对数据库变更“强可控”,则可以使用EF Core + Microsoft.Data.Sqlite</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Microsoft.Data.Sqlite;
</span><span style="color: rgba(0, 128, 128, 1)">2</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Microsoft.EntityFrameworkCore;
</span><span style="color: rgba(0, 128, 128, 1)">3</span>
<span style="color: rgba(0, 128, 128, 1)">4</span> <span style="color: rgba(0, 0, 255, 1)">var</span> connStr = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Data Source=efcore_manual_demo.db</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">5</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> MigrationRunner.MigrateAsync(connStr);
</span><span style="color: rgba(0, 128, 128, 1)">6</span>
<span style="color: rgba(0, 128, 128, 1)">7</span> <span style="color: rgba(0, 0, 255, 1)">using</span> <span style="color: rgba(0, 0, 255, 1)">var</span> db = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> AppDbContext(connStr);
</span><span style="color: rgba(0, 128, 128, 1)">8</span>
<span style="color: rgba(0, 128, 128, 1)">9</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写</span>
<span style="color: rgba(0, 128, 128, 1)"> 10</span> db.Users.Add(<span style="color: rgba(0, 0, 255, 1)">new</span> User { Name = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Alice</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Email = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">alice@test.com</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Age = <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)"> });
</span><span style="color: rgba(0, 128, 128, 1)"> 11</span> <span style="color: rgba(0, 0, 0, 1)">db.SaveChanges();
</span><span style="color: rgba(0, 128, 128, 1)"> 12</span>
<span style="color: rgba(0, 128, 128, 1)"> 13</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 读</span>
<span style="color: rgba(0, 128, 128, 1)"> 14</span> <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> u <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> db.Users.AsNoTracking())
</span><span style="color: rgba(0, 128, 128, 1)"> 15</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 16</span> Console.WriteLine($<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{u.Id} {u.Name} {u.Email} Age={u.Age}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)"> 17</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)"> 18</span>
<span style="color: rgba(0, 128, 128, 1)"> 19</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> MigrationRunner
</span><span style="color: rgba(0, 128, 128, 1)"> 20</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 21</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">async</span> Task MigrateAsync(<span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)"> connStr)
</span><span style="color: rgba(0, 128, 128, 1)"> 22</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)"> 23</span> <span style="color: rgba(0, 0, 255, 1)">await</span> <span style="color: rgba(0, 0, 255, 1)">using</span> <span style="color: rgba(0, 0, 255, 1)">var</span> conn = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> SqliteConnection(connStr);
</span><span style="color: rgba(0, 128, 128, 1)"> 24</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> conn.OpenAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 25</span>
<span style="color: rgba(0, 128, 128, 1)"> 26</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 版本表</span>
<span style="color: rgba(0, 128, 128, 1)"> 27</span> <span style="color: rgba(0, 0, 255, 1)">var</span> createVersion =<span style="color: rgba(0, 0, 0, 1)"> conn.CreateCommand();
</span><span style="color: rgba(0, 128, 128, 1)"> 28</span> createVersion.CommandText = <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)"> 29</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS __schema_migrations (
</span><span style="color: rgba(0, 128, 128, 1)"> 30</span> <span style="color: rgba(0, 0, 0, 1)"> version TEXT NOT NULL PRIMARY KEY,
</span><span style="color: rgba(0, 128, 128, 1)"> 31</span> <span style="color: rgba(0, 0, 0, 1)"> applied_at TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)"> 32</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)"> 33</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">;</span>
<span style="color: rgba(0, 128, 128, 1)"> 34</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> createVersion.ExecuteNonQueryAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 35</span>
<span style="color: rgba(0, 128, 128, 1)"> 36</span> <span style="color: rgba(0, 0, 255, 1)">await</span> ApplyIfNotExists(conn, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260001_Init</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)"> 37</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS Users (
</span><span style="color: rgba(0, 128, 128, 1)"> 38</span> <span style="color: rgba(0, 0, 0, 1)"> Id INTEGER PRIMARY KEY AUTOINCREMENT,
</span><span style="color: rgba(0, 128, 128, 1)"> 39</span> <span style="color: rgba(0, 0, 0, 1)"> Name TEXT NOT NULL,
</span><span style="color: rgba(0, 128, 128, 1)"> 40</span> <span style="color: rgba(0, 0, 0, 1)"> Email TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)"> 41</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)"> 42</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)"> 43</span>
<span style="color: rgba(0, 128, 128, 1)"> 44</span> <span style="color: rgba(0, 0, 255, 1)">await</span> ApplyIfNotExists(conn, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260002_AddAgeAndBackfill</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)"> 45</span> <span style="color: rgba(0, 0, 0, 1)"> ALTER TABLE Users ADD COLUMN Age INTEGER NULL;
</span><span style="color: rgba(0, 128, 128, 1)"> 46</span> UPDATE Users SET Age = <span style="color: rgba(128, 0, 128, 1)">18</span><span style="color: rgba(0, 0, 0, 1)"> WHERE Age IS NULL;
</span><span style="color: rgba(0, 128, 128, 1)"> 47</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)"> 48</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)"> 49</span>
<span style="color: rgba(0, 128, 128, 1)"> 50</span> <span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">async</span> Task ApplyIfNotExists(SqliteConnection conn, <span style="color: rgba(0, 0, 255, 1)">string</span> version, <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)"> sql)
</span><span style="color: rgba(0, 128, 128, 1)"> 51</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)"> 52</span> <span style="color: rgba(0, 0, 255, 1)">var</span> check =<span style="color: rgba(0, 0, 0, 1)"> conn.CreateCommand();
</span><span style="color: rgba(0, 128, 128, 1)"> 53</span> check.CommandText = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SELECT COUNT(1) FROM __schema_migrations WHERE version = $v</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 54</span> check.Parameters.AddWithValue(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">$v</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, version);
</span><span style="color: rgba(0, 128, 128, 1)"> 55</span> <span style="color: rgba(0, 0, 255, 1)">var</span> exists = Convert.ToInt32(<span style="color: rgba(0, 0, 255, 1)">await</span> check.ExecuteScalarAsync()) > <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 56</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (exists) <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 57</span>
<span style="color: rgba(0, 128, 128, 1)"> 58</span> <span style="color: rgba(0, 0, 255, 1)">await</span> <span style="color: rgba(0, 0, 255, 1)">using</span> <span style="color: rgba(0, 0, 255, 1)">var</span> tx = <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> conn.BeginTransactionAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 59</span> <span style="color: rgba(0, 0, 255, 1)">try</span>
<span style="color: rgba(0, 128, 128, 1)"> 60</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)"> 61</span> <span style="color: rgba(0, 0, 255, 1)">var</span> cmd =<span style="color: rgba(0, 0, 0, 1)"> conn.CreateCommand();
</span><span style="color: rgba(0, 128, 128, 1)"> 62</span> cmd.Transaction =<span style="color: rgba(0, 0, 0, 1)"> tx;
</span><span style="color: rgba(0, 128, 128, 1)"> 63</span> cmd.CommandText =<span style="color: rgba(0, 0, 0, 1)"> sql;
</span><span style="color: rgba(0, 128, 128, 1)"> 64</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> cmd.ExecuteNonQueryAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 65</span>
<span style="color: rgba(0, 128, 128, 1)"> 66</span> <span style="color: rgba(0, 0, 255, 1)">var</span> ins =<span style="color: rgba(0, 0, 0, 1)"> conn.CreateCommand();
</span><span style="color: rgba(0, 128, 128, 1)"> 67</span> ins.Transaction =<span style="color: rgba(0, 0, 0, 1)"> tx;
</span><span style="color: rgba(0, 128, 128, 1)"> 68</span> ins.CommandText = <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)"> 69</span> <span style="color: rgba(0, 0, 0, 1)"> INSERT INTO __schema_migrations(version, applied_at)
</span><span style="color: rgba(0, 128, 128, 1)"> 70</span> <span style="color: rgba(0, 0, 0, 1)"> VALUES($v, $t);
</span><span style="color: rgba(0, 128, 128, 1)"> 71</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">;</span>
<span style="color: rgba(0, 128, 128, 1)"> 72</span> ins.Parameters.AddWithValue(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">$v</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, version);
</span><span style="color: rgba(0, 128, 128, 1)"> 73</span> ins.Parameters.AddWithValue(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">$t</span><span style="color: rgba(128, 0, 0, 1)">"</span>, DateTime.UtcNow.ToString(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">O</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">));
</span><span style="color: rgba(0, 128, 128, 1)"> 74</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> ins.ExecuteNonQueryAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 75</span>
<span style="color: rgba(0, 128, 128, 1)"> 76</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> tx.CommitAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 77</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)"> 78</span> <span style="color: rgba(0, 0, 255, 1)">catch</span> (SqliteException ex) when (ex.Message.Contains(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">duplicate column name</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">))
</span><span style="color: rgba(0, 128, 128, 1)"> 79</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)"> 80</span> <span style="color: rgba(0, 0, 255, 1)">await</span><span style="color: rgba(0, 0, 0, 1)"> tx.RollbackAsync();
</span><span style="color: rgba(0, 128, 128, 1)"> 81</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)"> 82</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)"> 83</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)"> 84</span>
<span style="color: rgba(0, 128, 128, 1)"> 85</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> AppDbContext : DbContext
</span><span style="color: rgba(0, 128, 128, 1)"> 86</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 87</span> <span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">readonly</span> <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)"> _connStr;
</span><span style="color: rgba(0, 128, 128, 1)"> 88</span> <span style="color: rgba(0, 0, 255, 1)">public</span> AppDbContext(<span style="color: rgba(0, 0, 255, 1)">string</span> connStr) => _connStr =<span style="color: rgba(0, 0, 0, 1)"> connStr;
</span><span style="color: rgba(0, 128, 128, 1)"> 89</span> <span style="color: rgba(0, 0, 255, 1)">public</span> DbSet<User> Users => Set<User><span style="color: rgba(0, 0, 0, 1)">();
</span><span style="color: rgba(0, 128, 128, 1)"> 90</span> <span style="color: rgba(0, 0, 255, 1)">protected</span> <span style="color: rgba(0, 0, 255, 1)">override</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> OnConfiguring(DbContextOptionsBuilder options)
</span><span style="color: rgba(0, 128, 128, 1)"> 91</span> =><span style="color: rgba(0, 0, 0, 1)"> options.UseSqlite(_connStr);
</span><span style="color: rgba(0, 128, 128, 1)"> 92</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)"> 93</span>
<span style="color: rgba(0, 128, 128, 1)"> 94</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)"> 95</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 96</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> Id { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)"> 97</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Name { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 98</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Email { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 99</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span>? Age { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">100</span> }</pre>
</div>
<h3>Dapper + Microsoft.Data.Sqlite</h3>
<p>适合性能优先、SQL 可控优先、追求轻量。这类开销低、速度快、透明 SQL;适合高频读写和明确数据模型。但缺点很明显,sql量太多了</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)"> 1</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Dapper;
</span><span style="color: rgba(0, 128, 128, 1)"> 2</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> Microsoft.Data.Sqlite;
</span><span style="color: rgba(0, 128, 128, 1)"> 3</span>
<span style="color: rgba(0, 128, 128, 1)"> 4</span> <span style="color: rgba(0, 0, 255, 1)">var</span> connStr = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Data Source=dapper_demo.db</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)"> 5</span> <span style="color: rgba(0, 0, 255, 1)">using</span> <span style="color: rgba(0, 0, 255, 1)">var</span> conn = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> SqliteConnection(connStr);
</span><span style="color: rgba(0, 128, 128, 1)"> 6</span> <span style="color: rgba(0, 0, 0, 1)">conn.Open();
</span><span style="color: rgba(0, 128, 128, 1)"> 7</span>
<span style="color: rgba(0, 128, 128, 1)"> 8</span> <span style="color: rgba(0, 0, 0, 1)">Migrate(conn);
</span><span style="color: rgba(0, 128, 128, 1)"> 9</span>
<span style="color: rgba(0, 128, 128, 1)">10</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写</span>
<span style="color: rgba(0, 128, 128, 1)">11</span> <span style="color: rgba(0, 0, 0, 1)">conn.Execute(
</span><span style="color: rgba(0, 128, 128, 1)">12</span> <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">INSERT INTO Users(Name, Email, Age) VALUES (@Name, @Email, @Age);</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">13</span> <span style="color: rgba(0, 0, 255, 1)">new</span> { Name = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Alice</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Email = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">alice@test.com</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Age = <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)"> });
</span><span style="color: rgba(0, 128, 128, 1)">14</span>
<span style="color: rgba(0, 128, 128, 1)">15</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 读</span>
<span style="color: rgba(0, 128, 128, 1)">16</span> <span style="color: rgba(0, 0, 255, 1)">var</span> users = conn.Query<User>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SELECT Id, Name, Email, Age FROM Users ORDER BY Id;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">).ToList();
</span><span style="color: rgba(0, 128, 128, 1)">17</span> <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> u <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> users)
</span><span style="color: rgba(0, 128, 128, 1)">18</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">19</span> Console.WriteLine($<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{u.Id} {u.Name} {u.Email} Age={u.Age}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">20</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">21</span>
<span style="color: rgba(0, 128, 128, 1)">22</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Migrate(SqliteConnection conn)
</span><span style="color: rgba(0, 128, 128, 1)">23</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">24</span> conn.Execute(<span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">25</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS __schema_migrations (
</span><span style="color: rgba(0, 128, 128, 1)">26</span> <span style="color: rgba(0, 0, 0, 1)"> version TEXT NOT NULL PRIMARY KEY,
</span><span style="color: rgba(0, 128, 128, 1)">27</span> <span style="color: rgba(0, 0, 0, 1)"> applied_at TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)">28</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)">29</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">30</span>
<span style="color: rgba(0, 128, 128, 1)">31</span> Apply(conn, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260001_Init</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">32</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS Users (
</span><span style="color: rgba(0, 128, 128, 1)">33</span> <span style="color: rgba(0, 0, 0, 1)"> Id INTEGER PRIMARY KEY AUTOINCREMENT,
</span><span style="color: rgba(0, 128, 128, 1)">34</span> <span style="color: rgba(0, 0, 0, 1)"> Name TEXT NOT NULL,
</span><span style="color: rgba(0, 128, 128, 1)">35</span> <span style="color: rgba(0, 0, 0, 1)"> Email TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)">36</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)">37</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">38</span>
<span style="color: rgba(0, 128, 128, 1)">39</span> Apply(conn, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260002_AddAgeAndBackfill</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">40</span> <span style="color: rgba(0, 0, 0, 1)"> ALTER TABLE Users ADD COLUMN Age INTEGER NULL;
</span><span style="color: rgba(0, 128, 128, 1)">41</span> UPDATE Users SET Age = <span style="color: rgba(128, 0, 128, 1)">18</span><span style="color: rgba(0, 0, 0, 1)"> WHERE Age IS NULL;
</span><span style="color: rgba(0, 128, 128, 1)">42</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">43</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">44</span>
<span style="color: rgba(0, 128, 128, 1)">45</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> Apply(SqliteConnection conn, <span style="color: rgba(0, 0, 255, 1)">string</span> version, <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)"> sql)
</span><span style="color: rgba(0, 128, 128, 1)">46</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">47</span> <span style="color: rgba(0, 0, 255, 1)">var</span> exists = conn.ExecuteScalar<<span style="color: rgba(0, 0, 255, 1)">long</span>><span style="color: rgba(0, 0, 0, 1)">(
</span><span style="color: rgba(0, 128, 128, 1)">48</span> <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SELECT COUNT(1) FROM __schema_migrations WHERE version=@v</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(0, 0, 255, 1)">new</span> { v = version }) > <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">49</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (exists) <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">50</span>
<span style="color: rgba(0, 128, 128, 1)">51</span> <span style="color: rgba(0, 0, 255, 1)">using</span> <span style="color: rgba(0, 0, 255, 1)">var</span> tx =<span style="color: rgba(0, 0, 0, 1)"> conn.BeginTransaction();
</span><span style="color: rgba(0, 128, 128, 1)">52</span> <span style="color: rgba(0, 0, 255, 1)">try</span>
<span style="color: rgba(0, 128, 128, 1)">53</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">54</span> <span style="color: rgba(0, 0, 0, 1)"> conn.Execute(sql, transaction: tx);
</span><span style="color: rgba(0, 128, 128, 1)">55</span> conn.Execute(<span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">56</span> <span style="color: rgba(0, 0, 0, 1)"> INSERT INTO __schema_migrations(version, applied_at)
</span><span style="color: rgba(0, 128, 128, 1)">57</span> <span style="color: rgba(0, 0, 0, 1)"> VALUES(@v, @t)
</span><span style="color: rgba(0, 128, 128, 1)">58</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">, new { v = version, t = DateTime.UtcNow.ToString(</span><span style="color: rgba(128, 0, 0, 1)">"</span>O<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">) }, tx);</span>
<span style="color: rgba(0, 128, 128, 1)">59</span>
<span style="color: rgba(0, 128, 128, 1)">60</span> <span style="color: rgba(0, 0, 0, 1)"> tx.Commit();
</span><span style="color: rgba(0, 128, 128, 1)">61</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">62</span> <span style="color: rgba(0, 0, 255, 1)">catch</span> (SqliteException ex) when (ex.Message.Contains(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">duplicate column name</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">))
</span><span style="color: rgba(0, 128, 128, 1)">63</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">64</span> <span style="color: rgba(0, 0, 0, 1)"> tx.Rollback();
</span><span style="color: rgba(0, 128, 128, 1)">65</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">66</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">67</span>
<span style="color: rgba(0, 128, 128, 1)">68</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)">69</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">70</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">long</span> Id { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">71</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Name { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">72</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Email { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">73</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span>? Age { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">74</span> }</pre>
</div>
<h3>SqlSugar + System.Data.SQLite.Core</h3>
<p>上手快,功能集成度高(CodeFirst/DbFirst 等)。引用Nuget包:</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> <PackageReference Include=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SqlSugar</span><span style="color: rgba(128, 0, 0, 1)">"</span> Version=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">5.1.4.189</span><span style="color: rgba(128, 0, 0, 1)">"</span> />
<span style="color: rgba(0, 128, 128, 1)">2</span> <PackageReference Include=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">System.Data.SQLite.Core</span><span style="color: rgba(128, 0, 0, 1)">"</span> Version=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">1.0.118</span><span style="color: rgba(128, 0, 0, 1)">"</span> /></pre>
</div>
<p>如果是数据库表结构经常变动,建议使用这个方案,CodeFrist开发非常便捷</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)"> 1</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> SqlSugar;
</span><span style="color: rgba(0, 128, 128, 1)"> 2</span>
<span style="color: rgba(0, 128, 128, 1)"> 3</span> <span style="color: rgba(0, 0, 255, 1)">var</span> db = <span style="color: rgba(0, 0, 255, 1)">new</span> SqlSugarClient(<span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionConfig
</span><span style="color: rgba(0, 128, 128, 1)"> 4</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 5</span> ConnectionString = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Data Source=sqlsugar_demo.db</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)"> 6</span> DbType =<span style="color: rgba(0, 0, 0, 1)"> DbType.Sqlite,
</span><span style="color: rgba(0, 128, 128, 1)"> 7</span> IsAutoCloseConnection = <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)"> 8</span> InitKeyType =<span style="color: rgba(0, 0, 0, 1)"> InitKeyType.Attribute
</span><span style="color: rgba(0, 128, 128, 1)"> 9</span> <span style="color: rgba(0, 0, 0, 1)">});
</span><span style="color: rgba(0, 128, 128, 1)">10</span>
<span style="color: rgba(0, 128, 128, 1)">11</span> <span style="color: rgba(0, 0, 0, 1)">Migrate(db);
</span><span style="color: rgba(0, 128, 128, 1)">12</span>
<span style="color: rgba(0, 128, 128, 1)">13</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写</span>
<span style="color: rgba(0, 128, 128, 1)">14</span> db.Insertable(<span style="color: rgba(0, 0, 255, 1)">new</span> User { Name = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Alice</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Email = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">alice@test.com</span><span style="color: rgba(128, 0, 0, 1)">"</span>, Age = <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)"> }).ExecuteCommand();
</span><span style="color: rgba(0, 128, 128, 1)">15</span>
<span style="color: rgba(0, 128, 128, 1)">16</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 读</span>
<span style="color: rgba(0, 128, 128, 1)">17</span> <span style="color: rgba(0, 0, 255, 1)">var</span> list = db.Queryable<User>().OrderBy(x =><span style="color: rgba(0, 0, 0, 1)"> x.Id).ToList();
</span><span style="color: rgba(0, 128, 128, 1)">18</span> <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> u <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> list)
</span><span style="color: rgba(0, 128, 128, 1)">19</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">20</span> Console.WriteLine($<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{u.Id} {u.Name} {u.Email} Age={u.Age}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">21</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">22</span>
<span style="color: rgba(0, 128, 128, 1)">23</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> Migrate(SqlSugarClient db)
</span><span style="color: rgba(0, 128, 128, 1)">24</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">25</span> db.Ado.ExecuteCommand(<span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">26</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS __schema_migrations (
</span><span style="color: rgba(0, 128, 128, 1)">27</span> <span style="color: rgba(0, 0, 0, 1)"> version TEXT NOT NULL PRIMARY KEY,
</span><span style="color: rgba(0, 128, 128, 1)">28</span> <span style="color: rgba(0, 0, 0, 1)"> applied_at TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)">29</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)">30</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">31</span>
<span style="color: rgba(0, 128, 128, 1)">32</span> Apply(db, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260001_Init</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">33</span> <span style="color: rgba(0, 0, 0, 1)"> CREATE TABLE IF NOT EXISTS Users (
</span><span style="color: rgba(0, 128, 128, 1)">34</span> <span style="color: rgba(0, 0, 0, 1)"> Id INTEGER PRIMARY KEY AUTOINCREMENT,
</span><span style="color: rgba(0, 128, 128, 1)">35</span> <span style="color: rgba(0, 0, 0, 1)"> Name TEXT NOT NULL,
</span><span style="color: rgba(0, 128, 128, 1)">36</span> <span style="color: rgba(0, 0, 0, 1)"> Email TEXT NOT NULL
</span><span style="color: rgba(0, 128, 128, 1)">37</span> <span style="color: rgba(0, 0, 0, 1)"> );
</span><span style="color: rgba(0, 128, 128, 1)">38</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">39</span>
<span style="color: rgba(0, 128, 128, 1)">40</span> Apply(db, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">202602260002_AddAgeAndBackfill</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">41</span> <span style="color: rgba(0, 0, 0, 1)"> ALTER TABLE Users ADD COLUMN Age INTEGER NULL;
</span><span style="color: rgba(0, 128, 128, 1)">42</span> UPDATE Users SET Age = <span style="color: rgba(128, 0, 128, 1)">18</span><span style="color: rgba(0, 0, 0, 1)"> WHERE Age IS NULL;
</span><span style="color: rgba(0, 128, 128, 1)">43</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">);</span>
<span style="color: rgba(0, 128, 128, 1)">44</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">45</span>
<span style="color: rgba(0, 128, 128, 1)">46</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> Apply(SqlSugarClient db, <span style="color: rgba(0, 0, 255, 1)">string</span> version, <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)"> sql)
</span><span style="color: rgba(0, 128, 128, 1)">47</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">48</span> <span style="color: rgba(0, 0, 255, 1)">var</span> exists = db.Ado.GetInt(<span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">49</span> SELECT COUNT(<span style="color: rgba(128, 0, 128, 1)">1</span>) FROM __schema_migrations WHERE version=<span style="color: rgba(0, 0, 0, 1)">@v
</span><span style="color: rgba(0, 128, 128, 1)">50</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">, new { v = version }) > 0;</span>
<span style="color: rgba(0, 128, 128, 1)">51</span>
<span style="color: rgba(0, 128, 128, 1)">52</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (exists) <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">53</span>
<span style="color: rgba(0, 128, 128, 1)">54</span> <span style="color: rgba(0, 0, 0, 1)"> db.Ado.BeginTran();
</span><span style="color: rgba(0, 128, 128, 1)">55</span> <span style="color: rgba(0, 0, 255, 1)">try</span>
<span style="color: rgba(0, 128, 128, 1)">56</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">57</span> <span style="color: rgba(0, 0, 0, 1)"> db.Ado.ExecuteCommand(sql);
</span><span style="color: rgba(0, 128, 128, 1)">58</span> db.Ado.ExecuteCommand(<span style="color: rgba(128, 0, 0, 1)">"""
</span><span style="color: rgba(0, 128, 128, 1)">59</span> <span style="color: rgba(0, 0, 0, 1)"> INSERT INTO __schema_migrations(version, applied_at)
</span><span style="color: rgba(0, 128, 128, 1)">60</span> <span style="color: rgba(0, 0, 0, 1)"> VALUES(@v, @t)
</span><span style="color: rgba(0, 128, 128, 1)">61</span> <span style="color: rgba(128, 0, 0, 1)">"""</span><span style="color: rgba(128, 0, 0, 1)">, new { v = version, t = DateTime.UtcNow.ToString(</span><span style="color: rgba(128, 0, 0, 1)">"</span>O<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">) });</span>
<span style="color: rgba(0, 128, 128, 1)">62</span>
<span style="color: rgba(0, 128, 128, 1)">63</span> <span style="color: rgba(0, 0, 0, 1)"> db.Ado.CommitTran();
</span><span style="color: rgba(0, 128, 128, 1)">64</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">65</span> <span style="color: rgba(0, 0, 255, 1)">catch</span>
<span style="color: rgba(0, 128, 128, 1)">66</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">67</span> <span style="color: rgba(0, 0, 0, 1)"> db.Ado.RollbackTran();
</span><span style="color: rgba(0, 128, 128, 1)">68</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">69</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">70</span>
<span style="color: rgba(0, 128, 128, 1)">71</span>
</span><span style="color: rgba(0, 128, 128, 1)">72</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)">73</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">74</span>
</span><span style="color: rgba(0, 128, 128, 1)">75</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> Id { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">76</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Name { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">77</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Email { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">78</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span>? Age { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">79</span> }</pre>
</div>
<p>如果使用SqlSugar的已封装CodeFrist方案,迁移数据表会更简单:</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)"> 1</span> <span style="color: rgba(0, 0, 255, 1)">using</span><span style="color: rgba(0, 0, 0, 1)"> SqlSugar;
</span><span style="color: rgba(0, 128, 128, 1)"> 2</span>
<span style="color: rgba(0, 128, 128, 1)"> 3</span> <span style="color: rgba(0, 0, 255, 1)">var</span> db = <span style="color: rgba(0, 0, 255, 1)">new</span> SqlSugarClient(<span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionConfig
</span><span style="color: rgba(0, 128, 128, 1)"> 4</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)"> 5</span> ConnectionString = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Data Source=app.db</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)"> 6</span> DbType =<span style="color: rgba(0, 0, 0, 1)"> DbType.Sqlite,
</span><span style="color: rgba(0, 128, 128, 1)"> 7</span> IsAutoCloseConnection = <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)"> 8</span> InitKeyType =<span style="color: rgba(0, 0, 0, 1)"> InitKeyType.Attribute,
</span><span style="color: rgba(0, 128, 128, 1)"> 9</span> ConfigureExternalServices = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ConfigureExternalServices
</span><span style="color: rgba(0, 128, 128, 1)">10</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">11</span> EntityService = (prop, col) =>
<span style="color: rgba(0, 128, 128, 1)">12</span> <span style="color: rgba(0, 0, 0, 1)"> {
</span><span style="color: rgba(0, 128, 128, 1)">13</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 可选:统一处理字符串长度等</span>
<span style="color: rgba(0, 128, 128, 1)">14</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (prop.PropertyType == <span style="color: rgba(0, 0, 255, 1)">typeof</span>(<span style="color: rgba(0, 0, 255, 1)">string</span>) && col.Length == <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 128, 1)">15</span> col.Length = <span style="color: rgba(128, 0, 128, 1)">200</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 128, 1)">16</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">17</span> <span style="color: rgba(0, 0, 0, 1)"> }
</span><span style="color: rgba(0, 128, 128, 1)">18</span> <span style="color: rgba(0, 0, 0, 1)">});
</span><span style="color: rgba(0, 128, 128, 1)">19</span>
<span style="color: rgba(0, 128, 128, 1)">20</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 1) CodeFirst 建表/补字段</span>
<span style="color: rgba(0, 128, 128, 1)">21</span> db.CodeFirst.InitTables<User><span style="color: rgba(0, 0, 0, 1)">();
</span><span style="color: rgba(0, 128, 128, 1)">22</span>
<span style="color: rgba(0, 128, 128, 1)">23</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 2) 如需“迁移数据”(例如给新字段Age回填),用.NET代码执行SQL</span>
<span style="color: rgba(0, 128, 128, 1)">24</span> db.Ado.ExecuteCommand(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">UPDATE Users SET Age = 18 WHERE Age IS NULL;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">25</span>
<span style="color: rgba(0, 128, 128, 1)">26</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 3) 写入</span>
<span style="color: rgba(0, 128, 128, 1)">27</span> db.Insertable(<span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)">28</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">29</span> Name = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Alice</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">30</span> Email = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">alice@test.com</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(0, 128, 128, 1)">31</span> Age = <span style="color: rgba(128, 0, 128, 1)">20</span>
<span style="color: rgba(0, 128, 128, 1)">32</span> <span style="color: rgba(0, 0, 0, 1)">}).ExecuteCommand();
</span><span style="color: rgba(0, 128, 128, 1)">33</span>
<span style="color: rgba(0, 128, 128, 1)">34</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 4) 读取</span>
<span style="color: rgba(0, 128, 128, 1)">35</span> <span style="color: rgba(0, 0, 255, 1)">var</span> users = db.Queryable<User>().OrderBy(x =><span style="color: rgba(0, 0, 0, 1)"> x.Id).ToList();
</span><span style="color: rgba(0, 128, 128, 1)">36</span> <span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> u <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> users)
</span><span style="color: rgba(0, 128, 128, 1)">37</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">38</span> Console.WriteLine($<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{u.Id} {u.Name} {u.Email} Age={u.Age}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 128, 1)">39</span> <span style="color: rgba(0, 0, 0, 1)">}
</span><span style="color: rgba(0, 128, 128, 1)">40</span>
<span style="color: rgba(0, 128, 128, 1)">41</span>
</span><span style="color: rgba(0, 128, 128, 1)">42</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User
</span><span style="color: rgba(0, 128, 128, 1)">43</span> <span style="color: rgba(0, 0, 0, 1)">{
</span><span style="color: rgba(0, 128, 128, 1)">44</span>
</span><span style="color: rgba(0, 128, 128, 1)">45</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> Id { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">46</span>
<span style="color: rgba(0, 128, 128, 1)">47</span>
</span><span style="color: rgba(0, 128, 128, 1)">48</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Name { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
</span><span style="color: rgba(0, 128, 128, 1)">49</span>
<span style="color: rgba(0, 128, 128, 1)">50</span>
</span><span style="color: rgba(0, 128, 128, 1)">51</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">string</span> Email { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span>; } = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
</span><span style="color: rgba(0, 128, 128, 1)">52</span>
<span style="color: rgba(0, 128, 128, 1)">53</span> <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 新增字段:CodeFirst会尝试补列</span>
<span style="color: rgba(0, 128, 128, 1)">54</span>
</span><span style="color: rgba(0, 128, 128, 1)">55</span> <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span>? Age { <span style="color: rgba(0, 0, 255, 1)">get</span>; <span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">; }
</span><span style="color: rgba(0, 128, 128, 1)">56</span> }</pre>
</div>
<p>但要注意:<br>InitTables<T>() 主要用于建表/补字段,复杂变更(改列类型、重命名列、删列、数据搬迁)通常仍需你手动 SQL 或版本脚本。</p>
<p>Sugar的几种操作方式,</p>
<p>CodeFirst:db.CodeFirst.InitTables<User>(),啥叫CodeFirst?就是代码优先,由代码驱动数据库。不用自己先在数据库里设计好表、字段、索引,然后用工具生成 C# 实体(这种叫DbFirst)</p>
<p>ORM 的 CRUD/表达式 API:Insertable / Updateable / Queryable</p>
<p>原生 SQL 执行:db.Ado.ExecuteCommand("UPDATE ...")</p>
<p>所以,<strong>个人建议使用SqlSugar方案</strong>,CodeFirst数据表字段补全真的非常适合表结构变动,ORM链式操作提供了便捷的读写操作。</p>
<p>如果需要提升读写性能,也可以通过纯sql语句来替换Insertable、Updateable、Queryable操作。当然,这个差距很多时候尤其是1万级以内、小中型CRUD是不明显的,只有在10万级以上才有细微体现。同时一般瓶颈在并发IO/锁,而不是 ORM 转换</p>
<p>下面是10万条数据.NET Framework472,EFCore与SqlSugar 加载、读取、写入操作的Benchmark性能分析情况:</p>
<p><img src="https://img2024.cnblogs.com/blog/685541/202602/685541-20260227114152443-1242016081.png" alt="image" loading="lazy"></p>
<p>如果是.NET版本比如10性能会更高,所以一般情况下可以不用考虑读写性能 kybs00/SqliteBenchmarkTestDemo</p>
</div>
<div id="MySignature" role="contentinfo">
<div>作者:唐宋元明清2188</div>
<div>出处:http://www.cnblogs.com/kybs0/</div>
<div>让学习成为习惯,假设明天就有重大机遇等着你,你准备好了么</div>
<div>本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须在文章页面给出原文连接,否则保留追究法律责任的权利。 </div><br><br>
来源:https://www.cnblogs.com/kybs0/p/19638136
頁:
[1]