Bitwarden Server 中实现 Dapper 查询的完整指南：从存储过程、迁移脚本到仓储方法的实战规范
【免费下载链接】serverBitwarden infrastructure/backend (API, database, Docker, etc).项目地址: https://gitcode.com/GitHub_Trending/ser/server
导读
本文以 Bitwarden Server 开源仓库（server）中 Claude Skill 文档《implementing-dapper-queries》为核心，系统讲解在该项目中创建与修改 Dapper 仓储方法、MSSQL 存储过程及数据库迁移脚本时必须遵循的规范与陷阱。读完本文，你将掌握"SSDT 源文件 + 迁移脚本"双份存储过程维护流程、CREATE OR ALTER与CREATE PROCEDURE的适用场景、安全添加 NOT NULL 列与存储过程参数的方法，以及配套集成测试的编写方式，可直接用于实际开发与 Code Review。
仓库中的 Repository Pattern 全景
在 Bitwarden Server 中，所有 Dapper 仓储实现都位于 src/Infrastructure.Dapper/Repositories/，每个仓储类实现一个来自src/Core/的接口，并使用存储过程完成全部数据库操作。仓储方法被刻意保持"薄"——只做两件事：
- 把 C# 参数映射为 SQL 参数；
- 把存储过程返回的结果集映射回领域对象。
以 Repository.cs 这个抽象基类为例，可以看到这个模式的标准形态：GetByIdAsync通过new { Id = id }匿名对象传参，以CommandType.StoredProcedure调用[{Schema}].[{Table}_ReadById]；CreateAsync使用DynamicParameters并注册Id为InputOutput方向以取回数据库生成的主键；DeleteAsync则调用{Table}_DeleteById。
// src/Infrastructure.Dapper/Repositories/Repository.cs（节选） public virtual async Task<T?> GetByIdAsync(TId id) { using (var connection = new SqlConnection(ConnectionString)) { var results = await connection.QueryAsync<T>( $"[{Schema}].[{Table}_ReadById]", new { Id = id }, commandType: CommandType.StoredProcedure); return results.SingleOrDefault(); } }注意Schema默认值为"dbo"，Table默认取实体类型名，这意味着只要存储过程遵守{Entity}_{Action}命名规范，基类就能自动拼出过程名，无需逐方法硬编码。
具体仓储的例子见 UserRepository.cs：它继承Repository<User, Guid>，在构造函数中通过globalSettings.SqlServer.ConnectionString传入连接串，并用IDataProtectionProvider创建保护器，在读取结果后调用UnprotectData解密数据库字段。方法体内同样是"打开连接 →QueryAsync→ 映射"的薄封装：
// src/Infrastructure.Dapper/Repositories/UserRepository.cs（节选） public async Task<User?> GetByGatewayCustomerIdAsync(string gatewayCustomerId) { using (var connection = new SqlConnection(ConnectionString)) { var results = await connection.QueryAsync<User>( "[dbo].[User_ReadByGatewayCustomerId]", new { GatewayCustomerId = gatewayCustomerId }, commandType: CommandType.StoredProcedure); UnprotectData(results); return results.FirstOrDefault(); } }存储过程优先，内联 SQL 是例外
默认模式是"所有 Dapper 数据库操作都走存储过程"。少数内联 SQL 例外不是由各仓储方法临时手写的，而是由仓储基类和父类模式自动提供（例如基类中基于表名的通用 CRUD 封装），新增代码时不应打破这一约定。
仓储的依赖注入注册
所有 Dapper 仓储通过 DapperServiceCollectionExtensions.cs 中的AddDapperRepositories(this IServiceCollection services, bool selfHosted)统一注册为单例，例如services.AddSingleton<IUserRepository, UserRepository>()。注意IEventRepository仅在selfHosted为 true 时才注册——这正是集成测试中DatabaseDataAttribute传入SelfHosted参数所控制的差异点之一。
标准工作流：四步实现一个 Dapper 查询
文档给出了明确的实现顺序，四步缺一不可：
- 在 SSDT 源目录定义/更新存储过程：src/Sql/dbo/Stored Procedures/ —— 使用普通
CREATE PROCEDURE（SSDT 语法）。 - 创建部署它的迁移脚本：util/Migrator/DbScripts/ —— 使用幂等的
CREATE OR ALTER PROCEDURE。 - 实现仓储方法：src/Infrastructure.Dapper/Repositories/ —— 通过 Dapper 调用该过程。
- 编写集成测试：使用
[DatabaseData]特性（见下文"集成测试"一节）。
存储过程是 MSSQL 查询行为的唯一事实来源（source of truth），Dapper 仓储方法只做参数与结果映射。这一原则保证了查询逻辑集中在数据库层，C# 侧保持简单可读。
存储过程命名规范
过程统一遵循{Entity}_{Action}模式：User_Create、Cipher_ReadManyByUserId、Organization_DeleteById等。工具链和代码生成依赖这一约定把仓储方法映射到对应过程——例如Repository基类就是靠{Table}_ReadById/{Table}_Create/{Table}_Update/{Table}_DeleteById推导过程名的。一个存储过程一个文件，文件名为{Entity}_{Action}.sql。
最容易让 AI 助手翻车的两个文件位置
Bitwarden 在两个不同上下文中各维护一份存储过程，且工具链约束完全不同：
| 上下文 | 位置 | 必需语法 |
|---|---|---|
| SSDT schema 源 | src/Sql/dbo/Stored Procedures/ | CREATE PROCEDURE（普通） |
| 迁移脚本 | util/Migrator/DbScripts/ | CREATE OR ALTER PROCEDURE |
为什么不同：
- SSDT 项目不支持
CREATE OR ALTER——使用会产生构建错误。SSDT 通过自己的部署模型管理对象生命周期，因此每个源文件必须使用裸CREATE PROCEDURE。 - 迁移脚本必须幂等，因为可能被重复执行。
CREATE OR ALTER无论过程是否存在都能成功，而迁移脚本中绝不能用裸CREATE PROCEDURE。
仓库实证：SSDT 源 User_ReadById.sql 正是以CREATE PROCEDURE [dbo].[User_ReadById]开头；而迁移脚本侧，例如 2022-09-20_00_AvatarColor.sql 中的User_Create则使用CREATE OR ALTER PROCEDURE [dbo].[User_Create]。两类文件的语法差异在仓库中清晰可查。
SSDT 表文件需要GO批处理分隔符
在 src/Sql/dbo/Tables/ 中，SSDT 要求在CREATE TABLE与后续任何CREATE INDEX/CREATE NONCLUSTERED INDEX语句之间放置GO批处理分隔符：
-- CORRECT — GO 分隔 DDL 语句以满足 SSDT 要求 CREATE TABLE [dbo].[Example] ( [Id] UNIQUEIDENTIFIER NOT NULL, [Name] NVARCHAR(256) NOT NULL, CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ([Id] ASC) ) GO CREATE NONCLUSTERED INDEX [IX_Example_Name] ON [dbo].[Example] ([Name] ASC) GO存储过程与表结构的迁移铁律
新参数必须可空且带默认值
给既有存储过程加参数时，永远使用@NewParam DATATYPE = NULL。既有调用方不会传这个新参数——没有默认值，它们会立即报错。
-- CORRECT — 既有调用方不受影响 CREATE OR ALTER PROCEDURE [dbo].[Cipher_Create] @Id UNIQUEIDENTIFIER, @NewField NVARCHAR(MAX) = NULL -- 默认值保护既有调用方 -- WRONG — 所有既有调用方立即报错 CREATE OR ALTER PROCEDURE [dbo].[Cipher_Create] @Id UNIQUEIDENTIFIER, @NewField NVARCHAR(MAX) -- 没有默认值 = 必填参数NOT NULL 列：用内联默认值，不要 ALTER-UPDATE-ALTER
给大表加 NOT NULL 列时，如果先加可空列、再全表 UPDATE、最后 ALTER 为 NOT NULL，会触发全表扫描。正确做法是ADD [Column] INT NOT NULL CONSTRAINT DF_Table_Column DEFAULT 0——这在 SQL Server 中是仅元数据操作（metadata-only），瞬时完成。这是 AI 助手在 Bitwarden 迁移中犯的最常见错误。
-- CORRECT — 仅元数据操作，无全表扫描 ALTER TABLE [dbo].[Organization] ADD [UseCustomPermissions] BIT NOT NULL CONSTRAINT DF_Organization_UseCustomPermissions DEFAULT 0 -- WRONG — 大表上引发全表扫描 ALTER TABLE [dbo].[Organization] ADD [UseCustomPermissions] BIT NULL UPDATE [dbo].[Organization] SET [UseCustomPermissions] = 0 ALTER TABLE [dbo].[Organization] ALTER COLUMN [UseCustomPermissions] BIT NOT NULL绝不在迁移脚本里给大表建索引
在dbo.Cipher、dbo.OrganizationUser等大表上于迁移脚本中建索引可能导致生产事故。绝不指定ONLINE = ON——生产环境会自动处理，且该选项在不受支持的 SQL Server 版本上会直接失败。大型索引操作应放入DbScripts_manual。
默认值只用于数值类型
默认值只用于BIT、TINYINT、INT、BIGINT。绝不要在VARCHAR、NVARCHAR或 MAX 类型上使用默认值——SQL Server 对字符串的处理方式不同，字符串上的默认值会在 EF Core 迁移时产生意外行为。
视图修改后必须刷新元数据
修改表之后，任何引用它的视图都会持有过期元数据：要对受影响的视图调用sp_refreshview；修改视图后，要对依赖它的过程调用sp_refreshsqlmodule。这是最容易被遗漏的一步。仓库中真实案例见 2020-03-26_00_CipherSoftDelete.sql，该迁移依次执行了EXECUTE sp_refreshsqlmodule N'[dbo].[CipherView]'、N'[dbo].[UserCipherDetails]'、N'[dbo].[Cipher_Create]'、N'[dbo].[Cipher_Move]'等刷新调用。
GUID 列必须用UNIQUEIDENTIFIER
所有实体 ID 都是UNIQUEIDENTIFIER，由应用层代码CoreHelpers.GenerateComb()生成（保证 GUID 有序性以优化索引），不是由 SQL Server 生成。存储过程中绝不要用NEWID()或NEWSEQUENTIALID()。这与 Repository.cs 中CreateAsync先调obj.SetNewId()再由Id以InputOutput方向回传的实现相互印证。
EF Parity 要求：EF 实现必须等价复刻
每一条存储过程的行为都必须在 EF Core 实现中被精确复刻。编写新存储过程时，要提前想好 EF 实现将如何复现相同的过滤（filtering）、排序（ordering）与副作用（side effects）。如果存储过程做了复杂操作（如条件更新、多表操作），必须把预期行为清楚地写成文档，让 EF 实现可以对齐。这一点在测试层也有呼应：DatabaseDataAttribute会同时为 SqlServer（Dapper）与 EF 路径构造不同的服务提供者来运行同一批测试。
Critical Rules：最常被违反的硬性约定
以下是文档内联的、最常被违反的规范（这些约定已内联在 Skill 中，不依赖运行时联网获取）：
- 每个存储过程开头必须有
SET NOCOUNT ON（消除行计数消息，减少网络流量并避免 Dapper 结果映射干扰）； - 参数命名：
@ParamName使用 PascalCase，与 C# 属性名一致——这样 Dapper 的匿名对象/DynamicParameters才能按名字匹配； - 迁移脚本必须幂等：
util/Migrator/DbScripts/中用CREATE OR ALTER；SSDT 源（src/Sql/dbo/）中用普通CREATE PROCEDURE； - 约束命名：
PK_TableName、FK_Child_Parent、IX_Table_Column、DF_Table_Column； - 存储过程文件命名：一个过程一个文件，命名为
{Entity}_{Action}.sql。
完整示例：同一个存储过程的两种写法
下面是文档给出的对照示例，展示了同一过程在 SSDT 源与迁移脚本中的不同写法。该示例与仓库中真实文件 User_ReadById.sql 的形态一致（真实文件从UserView视图中取数，并同样以SET NOCOUNT ON开头）：
-- SSDT 源文件：src/Sql/dbo/Stored Procedures/User_ReadById.sql -- 使用普通 CREATE PROCEDURE（SSDT 不支持 CREATE OR ALTER） CREATE PROCEDURE [dbo].[User_ReadById] @Id UNIQUEIDENTIFIER AS BEGIN SET NOCOUNT ON SELECT * FROM [dbo].[User] WHERE [Id] = @Id END-- 迁移脚本：util/Migrator/DbScripts/YYYY-MM-DD_00_AddUser_ReadById.sql -- 使用 CREATE OR ALTER 保证幂等 CREATE OR ALTER PROCEDURE [dbo].[User_ReadById] @Id UNIQUEIDENTIFIER AS BEGIN SET NOCOUNT ON SELECT * FROM [dbo].[User] WHERE [Id] = @Id END集成测试：用[DatabaseData]验证仓储
编写 Dapper 仓储后，集成测试位于 test/Infrastructure.IntegrationTest/，使用[DatabaseData]特性。DatabaseDataAttribute.cs 的实现揭示了它的运行机制：
- 从用户机密（
AddUserSecrets）、BW_TEST_前缀的环境变量和命令行参数中读取数据库配置； - 对 MySql、Postgres、Sqlite、SqlServer 四种支持的数据库枚举生成理论数据（theory）——未配置的数据库自动以
Unconfigured跳过，配置但未启用的以Not-Enabled跳过； - 当数据库为 SqlServer 且
UseEf == false时走 Dapper 路径（AddDapperRepositories+AddDistributedSqlServerCache），否则走 EF 路径（SetupEntityFramework+AddPasswordManagerEFRepositories）——这正是 EF Parity 要求在测试层的落地； - 通过
ServiceBasedTheoryDataRow把测试方法的参数从服务提供者中按类型解析注入。
// test/Infrastructure.IntegrationTest/AdminConsole/Repositories/...（用法示意） [Theory] [DatabaseData] public async Task CreateAsync_Works(IUserRepository userRepository) { // 直接注入仓储接口，测试存储过程行为 }仓库中已有大量此类测试，例如 UserRepositoryTests.cs、CipherRepositoryTests.cs，可作为编写新集成测试的模板。
总结
在 Bitwarden Server 中实现 Dapper 查询，本质是一条"SSDT 源（CREATE PROCEDURE）→ 迁移脚本（CREATE OR ALTER）→ 薄仓储方法 →[DatabaseData]集成测试"的流水线。把握住几个关键纪律——存储过程为唯一事实来源、命名约定驱动代码生成、迁移脚本必须幂等且只做元数据级操作、EF 实现必须等价复刻——就能既快又稳地交付符合仓库规范的 MSSQL 数据访问代码。
进一步阅读仓库内相关资料：SQL code style 与迁移规范的仓库内说明、迁移脚本目录 util/Migrator/DbScripts/、SSDT schema 源 src/Sql/dbo/，以及 迁移器实现。
【免费下载链接】serverBitwarden infrastructure/backend (API, database, Docker, etc).项目地址: https://gitcode.com/GitHub_Trending/ser/server
创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
