使用 Dapper 简化动态 SQL 查询

引言

Dapper 是一个开源的、轻量级的对象关系映射 (ORM) 库，适用于 .NET。Dapper 简化了 SQL 查询的构建和执行，提供了一套丰富的工具，可与数据库无缝集成。

在本文中，我们将通过实际示例深入探讨使用 Dapper 与 SQLite 数据库进行交互，重点介绍其优势。此外，我们还将介绍一个有价值的配套库 Dapper.SimpleSqlBuilder，它旨在通过简化动态 SQL 查询的构建过程来增强 Dapper 的体验。

Dapper 入门

让我们从一个常见的场景开始。假设你在 SQLite 数据库中有一个名为 Users 的表，并且你想检索 UserTypeId 为 4 且 Role 为 Admin 的用户。以下 Dapper 代码可以实现此目的：

using Dapper;
using Microsoft.Data.Sqlite;

var userTypeId = 4;
var role = "Admin";

var sql = @"
SELECT * FROM Users
WHERE UserTypeId = @userTypeId
AND Role = @role";

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(sql, new { userTypeId, role });

这段简洁的代码展示了 Dapper 在执行 SQL 查询和从数据库检索数据方面的强大功能和简洁性。

引入 Dapper.SimpleSqlBuilder

什么是 Dapper.SimpleSqlBuilder？

Dapper.SimpleSqlBuilder 是一个库，它通过提供一种简单、高效且流畅的方式来构建静态和动态 SQL 查询，从而增强了 Dapper 的体验。该库利用 字符串插值 和流畅 API，使开发人员能够轻松地构建安全且参数化的 SQL 查询。

这并不是一个新颖的想法，因为已经有关于此的文章，也有其他库可以做类似的事情。尽管如此，我还是想构建一个简单、易于使用、内存高效、快速、安全并且能够创建参数化 SQL 查询的东西。

主要特点

• 提供一种简单自然的方式来使用字符串插值编写 SQL 查询
• 可链式调用的方法和流畅的 API，用于构建 SQL 查询
• 支持查询中的参数重用
• 依赖注入支持
• 用于构建动态 SQL 查询的条件方法
• 高性能且内存高效。与 Dapper 的 SqlBuilder 相比，性能相似甚至更好

该库提供了两个构建器来构建 SQL 查询

• Builder - 用于构建静态、动态和复杂的 SQL 查询
• Fluent Builder - 用于使用流畅 API 构建动态 SQL 查询

Builder

让我们再次回顾之前的场景，但这次使用 Dapper.SimpleSqlBuilder 的 Builder。

using Dapper;
using Dapper.SimpleSqlBuilder;
using Microsoft.Data.Sqlite;

var userTypeId = 4;
var role = "Admin";

var builder = SimpleBuilder.Create($@"
SELECT * FROM Users
WHERE UserTypeId = {userTypeId}
AND Role = {role}");

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(builder.Sql, builder.Parameters);

我知道你们中的一些人已经开始想了：这不安全，你使用了字符串插值，这可能导致 SQL 注入。

你的担忧是有效的；然而，该库通过捕获传递到插值字符串的值并创建参数化 SQL 语句来缓解这个问题，如下所示。

传递到插值字符串的所有值都被放入 Dapper 的 DynamicParameters 集合中。

SELECT * FROM Users
WHERE UserTypeId = @p0
AND Role = @p1

使用相同的场景，让我们让查询变得动态。

using Dapper;
using Dapper.SimpleSqlBuilder;
using Microsoft.Data.Sqlite;

var users = GetUsers(null, "Admin");

IEnumerable<User> GetUsers(int? userTypeId = null, string role = null)
{

    var builder = SimpleBuilder.Create($"SELECT * FROM Users")
      .AppendNewLine(userTypeId.HasValue || 
                     !string.IsNullOrWhiteSpace(role), $"WHERE 1 = 1")
      .Append(userTypeId.HasValue, $"AND UserTypeId = {userTypeId}")
      .Append(!string.IsNullOrWhiteSpace(role), $"AND Role = {role}");

    using var connection = new SqliteConnection("Data Source=database.db");
    return users = connection.Query<User>(builder.Sql, builder.Parameters);
}

生成的 SQL 将是：

SELECT * FROM Users
WHERE 1 = 1 AND Role = @p0

你可以在 文档 中查看 Builder 的完整功能集。

让我们快速谈谈 SQL 注入

我们都意识到 SQL 注入的危险，但是，如果你不了解，我建议你在此 处 阅读。

那么该库是如何防止这种情况发生的呢？

该库通过强制你使用字符串插值来编写所有 SQL 查询来缓解这个问题，这是为了确保传递到插值字符串的值被捕获和参数化。由于这个限制，下面的代码将无法编译。

// Scenario 1: Won't compile
var builder = SimpleBuilder.Create("SELECT * FROM User");

// Scenario 2: Won't compile
var sql = "SELECT * FROM User";
builder = SimpleBuilder.Create(sql);

// Scenario 3: Won't compile
builder = SimpleBuilder.Create(sql + " WHERE ROLE IS NOT NULL");

// Scenario 4: Won't compile
sql = $"SELECT * FROM User WHERE UserTypeId = {userTypeId}";
builder = SimpleBuilder.Create(sql);

// Scenario 5: Won't compile
builder = SimpleBuilder.Create(sql + $" AND Role = {role}");

Fluent Builder

Fluent Builder 提供了一种更具表现力的方式来构建动态 SQL 查询，它使用流畅的 API，并支持 SELECT、INSERT、UPDATE 和 DELETE 操作。

为了让本文保持简短易懂，我们将只关注 Select 操作。但是，你可以在 文档 中了解更多关于 Fluent Builder 的其他操作和功能。

Select Builder

使用前面提到的相同场景，我们将使用 Fluent Builder 来构建我们的 SQL 查询。

var userTypeId = 4;
var role = "Admin";

var builder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"Users")
    .Where($"UserTypeId = {userTypeId}")
    .Where($"Role = {role}");

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(builder.Sql, builder.Parameters);

生成的 SQL 将是：

SELECT *
FROM Users
WHERE UserTypeId = @p0 AND Role = @p1

让我们看另一个例子，但这次我们将让 SQL 查询变得动态。

var filter = new Filter { UserTypeId = null, 
             Roles = new [] { "Admin", "User" }, IncludeUsersWithoutRole = true };
var users = GetUsers(filter);

IEnumerable<User> GetUsers(Filter? filter = null)
{
    var builder = SimpleBuilder.CreateFluent()
        .Select($"*")
        .From($"User")
        .Where(filter?.UserTypeId.HasValue == true, $"UserTypeId = {filter.UserTypeId}")
        .OrWhere(filter?.Roles?.Length > 0, $"Role IN {filter.Roles}")
        .OrWhere(filter?.IncludeUsersWithoutRole == true, $"Role IS NULL");

    using var connection = new SqliteConnection("Data Source=database.db");
    return connection.Query<User>(builder.Sql, builder.Parameters);
}

生成的 SQL 将是：

SELECT *
FROM Users
WHERE Role IN @p0 OR Role IS NULL

Select Builder 也支持

• Distinct、Joins、OrderBy、Having 和 GroupBy 子句
• 分页：Limit、Offset 和 Fetch 子句
• Where 过滤器（复杂过滤器语句）

与 Dapper 相比如何？

下面的代码显示了该库与 Dapper 和 Dapper 的 SqlBuilder 的对比。

using var connection = new SqliteConnection("Data Source=database.db");

// Building and executing SQL query with Dapper

var sql = @"
SELECT * FROM Users
WHERE UserTypeId = @userTypeId 
AND Role = @role";

var users = connection.Query<User>(sql, new { userTypeId, role })

// Building and executing SQL query with Dapper's SqlBuilder

var sqlBuilder = new SqlBuilder()
    .Where("UserTypeId = @userTypeId", new { userTypeId })
    .Where("Role = @role", new { role });

var template = sqlBuilder.AddTemplate("SELECT * FROM Users /**where**/");
users = connection.Query<User>(template.RawSql, template.Parameters);

// Building and executing SQL query with the Builder (Dapper.SimpleSqlBuilder)

var builder = SimpleBuilder.Create($@"
SELECT * FROM Users
WHERE UserTypeId = {userTypeId}
AND Role = {role}");

users = connection.Query<User>(builder.Sql, builder.Parameters);

// Building and executing SQL query with the Fluent Builder (Dapper.SimpleSqlBuilder)

var fluentBuilder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"Users")
    .Where($"UserTypeId = {userTypeId}")
    .Where($"Role = {role}");

users = connection.Query<User>(fluentBuilder.Sql, fluentBuilder.Parameters);

正如你所见，该库减轻了使用 Dapper 时的一些仪式性工作，并提供了一种简单自然的方式来编写 SQL 查询。

性能

性能总是相对的，并且取决于场景和其他因素（例如，硬件、操作系统等）。然而，下面的结果很好地表明了该库的性能。

基准测试显示了 Builder 和 Fluent Builder 与 Dapper 的 SqlBuilder 在仅构建查询方面的性能（这不包含 SQL 执行的基准测试）。

BenchmarkDotNet=v0.13.5, OS=Windows 11 (10.0.22621.1778)
Intel Core i7-8750H CPU 2.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores
.NET SDK=7.0.302
  [Host]     : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2
  Job-UDVULW : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2
  Job-ZBHUIE : .NET Framework 4.8.1 (4.8.9139.0), X64 RyuJIT VectorSize=256

方法	运行时	分类	平均	已分配
SqlBuilder (Dapper)	.NET 7.0	简单查询	1.865 μs	2.92 KB
Builder	.NET 7.0	简单查询	1.531 μs	4.43 KB
FluentBuilder	.NET 7.0	简单查询	2.001 μs	4.5 KB
Builder (重用参数)	.NET 7.0	简单查询	2.195 μs	4.7 KB
FluentBuilder (重用参数)	.NET 7.0	简单查询	2.755 μs	4.77 KB
SqlBuilder (Dapper)	.NET Framework 4.6.1	简单查询	3.237 μs	3.43 KB
Builder	.NET Framework 4.6.1	简单查询	3.821 μs	4.7 KB
FluentBuilder	.NET Framework 4.6.1	简单查询	4.493 μs	5.2 KB
Builder (重用参数)	.NET Framework 4.6.1	简单查询	4.607 μs	5.27 KB
FluentBuilder (重用参数)	.NET Framework 4.6.1	简单查询	5.260 μs	5.77 KB
SqlBuilder (Dapper)	.NET 7.0	大型查询	28.193 μs	42.19 KB
Builder	.NET 7.0	大型查询	21.475 μs	48.79 KB
FluentBuilder	.NET 7.0	大型查询	26.700 μs	48.62 KB
Builder (重用参数)	.NET 7.0	大型查询	14.929 μs	29.34 KB
FluentBuilder (重用参数)	.NET 7.0	大型查询	20.039 μs	29.18 KB
SqlBuilder (Dapper)	.NET Framework 4.6.1	大型查询	43.275 μs	53.1 KB
Builder	.NET Framework 4.6.1	大型查询	52.571 μs	62.15 KB
FluentBuilder	.NET Framework 4.6.1	大型查询	63.775 μs	68.61 KB
Builder (重用参数)	.NET Framework 4.6.1	大型查询	39.589 μs	37.42 KB
FluentBuilder (重用参数)	.NET Framework 4.6.1	大型查询	50.712 μs	43.87 KB

基准测试结果在撰写本文时是有效的。要查看最新的基准测试结果，请参阅 基准测试页面 以获取更多详细信息。

结论

我希望您喜欢阅读这篇文章并学到了一些新东西。Dapper.SimpleSqlBuilder 是一个很棒的库（我希望它是😄），它满足了特定需求，我希望您发现它很有用。

如果您喜欢这个库，请使用它、分享它，并在 GitHub 上给它一个 ⭐️。如有任何问题、评论或反馈，请随时在 GitHub 上与我联系。

历史

• 2023年12月11日：初始版本