目录
介绍
Dapper是一个用于.NET的开源、轻量级对象关系映射（ORM）库。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查询的简单自然方法
- 用于构建SQL查询的可链接方法和流畅的API
- 支持在查询中重用参数
- 依赖注入支持
- 用于生成动态SQL查询的条件方法
- 高性能和内存效率高。与Dapper的SqlBuilder相比，性能相似或更好
该库提供了两个用于构建SQL查询的生成器：
- 构建器——用于构建静态、动态和复杂的SQL查询
- Fluent Builder——用于使用Fluent API构建动态SQL查询
构建器
让我们重新审视前面的场景，但这次使用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 API的动态 SQL 查询，并支持SELECT、INSERT、UPDATE和DELETE操作。
我们只会看Select操作，以保持本文的简短和甜蜜。但是，您可以在文档中了解有关Fluent 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 NULLSelect 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微秒 | 2.92 KB |
| Builder | .NET 7.0 | 简单查询 | 1.531微秒 | 4.43 KB |
| FluentBuilder的 | .NET 7.0 | 简单查询 | 2.001微秒 | 4.5 KB |
| Builder（重用参数） | .NET 7.0 | 简单查询 | 2.195微秒 | 4.7 KB |
| FluentBuilder（重用参数） | .NET 7.0 | 简单查询 | 2.755微秒 | 4.77 KB |
| SqlBuilder （Dapper） | .NET Framework 4.6.1 | 简单查询 | 3.237微秒 | 3.43 KB |
| Builder | .NET Framework 4.6.1 | 简单查询 | 3.821微秒 | 4.7 KB |
| FluentBuilder的 | .NET Framework 4.6.1 | 简单查询 | 4.493微秒 | 5.2 KB |
| Builder（重用参数） | .NET Framework 4.6.1 | 简单查询 | 4.607微秒 | 5.27 KB |
| FluentBuilder（重用参数） | .NET Framework 4.6.1 | 简单查询 | 5.260微秒 | 5.77 KB |
| SqlBuilder （Dapper） | .NET 7.0 | 大型查询 | 28.193微秒 | 42.19 KB |
| Builder | .NET 7.0 | 大型查询 | 21.475微秒 | 48.79 KB |
| FluentBuilder的 | .NET 7.0 | 大型查询 | 26.700微秒 | 48.62 KB |
| Builder（重用参数） | .NET 7.0 | 大型查询 | 14.929微秒 | 29.34 KB |
| FluentBuilder（重用参数） | .NET 7.0 | 大型查询 | 20.039微秒 | 29.18 KB |
| SqlBuilder（Dapper） | .NET Framework 4.6.1 | 大型查询 | 43.275微秒 | 53.1 KB |
| Builder | .NET Framework 4.6.1 | 大型查询 | 52.571微秒 | 62.15 KB |
| FluentBuilder | .NET Framework 4.6.1 | 大型查询 | 63.775微秒 | 68.61 KB |
| Builder（重用参数） | .NET Framework 4.6.1 | 大型查询 | 39.589微秒 | 37.42 KB |
| FluentBuilder（重用参数） | .NET Framework 4.6.1 | 大型查询 | 50.712微秒 | 43.87 KB |
基准测试结果在撰写本文时有效。要查看最新的基准测试结果，请参阅基准测试页面了解更多详细信息。
结论
我希望您喜欢阅读这篇文章并学到一些新东西。Dapper.SimpleSqlBuilder是一个很酷的库（我希望它是😄），可以满足特定的需求，我希望你觉得它有用。
如果您喜欢该库，请使用它，共享它，并在GitHub上给它⭐️一个。如有任何问题、意见或反馈，请随时在GitHub上与我联系。
https://www.codeproject.com/Articles/5373705/Simplifying-Dynamic-SQL-Queries-with-Dapper
扫一扫
1019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
