目录
介绍
DapperQueryBuilder是Dapper的包装器，主要用于帮助构建动态SQL查询和命令。它基于两个基本原则：
- 字符串插值，而不是使用DynamicParameters手动进行
- 查询和参数并排
看起来很简单，但是您会发现它的功能强大。
背景
我们都喜欢Dapper micro-ORM。
许多开发人员已经意识到，尽管Entity Framework提供了Dapper无法提供的某些功能，但Dapper仍比EF更灵活（且速度要快得多）。许多人已经意识到，为完成正确的工作而使用的正确工具有时涉及将EF用于某些事物而将Dapper用于其他事物。Dapper为王的用途之一是构建动态查询。
动态SQL
老派开发人员（那些从90年代末或2000年代初开始编写代码的人，可能使用VB6，ASP3或PHP）可能会记住以下代码：
string sql = "SELECT * FROM [Product] WHERE 1=1";
if (!string.IsNullOrEmpty(Request["ProductName"]))
sql += " AND Name LIKE '" + Request["ProductName"].Replace("'", "''") + "'"; // this replace!
if (!string.IsNullOrEmpty(Request["SubCategoryId"]))
sql += " AND ProductSubcategoryID = " +
Request["SubCategoryId"].Replace("'", "''"); // this replace!
// here is where you pray that you've correctly sanitized inputs against sql-injection
var products = cn.Query<Product>(sql);基本上，我们都是直接将动态SQL语句发送到我们的数据库。而且由于用户输入可能是恶意的（请参阅下文），因此我们必须手动清理用户输入以避免SQL-Injection攻击。
你好Bobby 表
如果您没有正确清理输入内容，则可能会面临严重的风险。SQL注入可能是最流行的Web黑客技术。如果您忘记清理“id” querystring之类的东西，则可能会打开一个巨大的漏洞，并可能会暴露您的所有数据库，不仅用于读取，而且用于修改。
参数化SQL
即使您是高中生（嘿VB6家伙，我又在看您），您可能都知道，像这样动态构建SQL不仅容易出错（易受SQL注入攻击），而且也不会从SQL中受益服务器缓存执行计划。您可能使用了某种代码生成工具，可以帮助您构建这样的代码（也许是调用存储过程，因为2000年代初期三层架构和“Windows DNA”大肆宣传）：
Dim strSQL As String
Dim cmd As New ADODB.Command
strSQL = "UPDATE MyTable SET " & vbNewLine _
& " NEEDS_ID = @NEEDS_ID, " & vbNewLine _
& " OBJ_ID = @OBJ_ID, " & vbNewLine _
& " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
& " TIME21_ID = @TIME21_ID, " & vbNewLine _
& " WHERE ID = @WHEREID"
With cmd
.ActiveConnection = Cn
.CommandText = strSQL
.Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
.Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
.Parameters.Append .CreateParameter
("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
.Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
.Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 456)
.Execute
End With我希望千禧一代的开发人员不要对上个世纪已经存在参数化SQL感到惊讶。
回到现代软件
时光流逝，Java和.NET出现了（也许被淹没了吗？），不赞成使用反射，字节码，Full-ORM，Micro-ORM，3层，而是使用了数十种现代体系结构，现在数据库访问已很多更容易吧？
现在，我们不必手动将每个参数描述为SQL。我们最喜欢的ORM将为我们做到这一点。
Dapper 例子
var dynamicParams = new DynamicParameters();
string sql = "SELECT * FROM [Product] WHERE 1=1";
if (productName != null)
{
sql += " AND Name LIKE @productName";
dynamicParams.Add("productName", productName);
}
if (subCategoryId != null)
{
sql += " AND ProductSubcategoryID = @subCategoryId";
dynamicParams.Add("subCategoryId", subCategoryId);
}
var products = cn.Query<Product>(sql, dynamicParams);不要重复自己
下面的示例使我有些沮丧，原因有两个：
- 我必须两次传递productName给sql字符串和dynamicParams字典。
他们的名字应该匹配。 - 我必须将这两个变量（SQL语句和参数列表）分开，即使它们彼此之间密切相关。
字符串插值内部
字符串插值于2016年在C＃中引入。
而不是这样的写代码：
string name = "Rick";
int accesses = 10;
string output = string.Format("{0}, you've been here {1:n0} times.", name, accesses);您可以这样：
string name = "Rick";
int accesses = 10;
string output = $"{name}, you've been here {accesses:n0} times.";在内部，当您编写一个插值字符串（以$开头）时，编译器会生成一个FormattableString类，该类既包含模板（好像是“ {0}, you've been here {1:n0} times.”），又包含参数列表（string name和int accesses）。
如果您的方法要求使用常规字符串，则FormattableString将被隐式转换为常规字符串，并且您将获得与将string.format传递给方法相同的行为。但是，如果您的方法需要一个FormattableString类，则可以访问彼此隔离的格式和参数。
例如，如果我们想构建参数化的SQL语句，而又让用户像常规string连接那样构建strings，FormattableString可能会很有用：
QueryBuilder ParseSQL(FormattableString query)
{
QueryBuilder myWrapper = new QueryBuilder();
string dapperSql = query.Format;
// replace {0} by "@p0",
// replace {1} by "@p1", etc..
myWrapper.SQL = dapperSql;
var dapperArgs = new Dapper.DynamicParameters();
var args = query.GetArguments();
// dapperArgs.Add("p0", args[0]);
// dapperArgs.Add("p1", args[1]); ...
myWrapper.Arguments = dapperArgs;
return myWrapper;
// now anyone can use Dapper like
// var pocos = connection.Query<POCO>(myWrapper.SQL, myWrapper.Parameters);
}DapperQueryBuilder
基于上述想法，我创建了DapperQueryBuilder-这是Dapper的简单包装器，它使我们能够使用字符串插值传递SQL参数。
您可以这样编写：
var query = cn.QueryBuilder($"SELECT * FROM [Product] WHERE 1=1");
if (productName != null)
query.Append($"AND Name LIKE {productName}");
if (subCategoryId != null)
query.Append($"AND ProductSubcategoryID = {subCategoryId}");
var products = query.Query<Product>();尽管看起来您只是在构建动态SQL（使用值作为内联文字），但实际上您得到的是参数化SQL。
以防万一，query有以下基本声明：
SELECT * FROM [Product] WHERE 1=1 AND Name LIKE @p0 AND ProductSubcategoryId = @p1并保留以下参数：
@p0 = productName
@p1 = subCategoryId总而言之，不是使用Dapper的扩展名.Query<T>来扩展IDbConnection和接受SQL字符串和参数列表，而是使用QueryBuilder()扩展名来创建一个QueryBuilder可以动态（在单个语句中）添加新参数并添加关联的SQL子句的位置。
快速开始
如果您喜欢它并想立即开始使用它：
- 安装NuGet软件包Dapper-QueryBuilder
- 开始像这样使用：
using DapperQueryBuilder;
// ...
cn = new SqlConnection(connectionString);
// If you have all your parameters at once and
// just want to benefit from string interpolation:
var products = cn.QueryBuilder($@"
SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE [ListPrice] <= {maxPrice}
AND [Weight] <= {maxWeight}
AND [Name] LIKE {search}
ORDER BY ProductId").Query<Product>();或建立像这样的动态条件：
using DapperQueryBuilder;
// ...
cn = new SqlConnection(connectionString);
// If you want to dynamically add conditions
var q = cn.QueryBuilder($@"
SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE 1=1 ");
q.AppendLine("AND [ListPrice] <= {maxPrice}");
q.AppendLine("AND [Weight] <= {maxWeight}");
q.AppendLine("AND [Name] LIKE {search}");
q.AppendLine("ORDER BY ProductId");
var products = q.Query<Product>();筛选器清单
某些人错误地认为WHERE 1=1"会导致性能下降。它不会。因此使用此伪造条件是一个绝妙的技巧，因此您可以像上面的示例中所示附加其他条件（如果有）。
另一个选择是，您可以构建整个查询，但保留过滤器在以后动态定义（和呈现）。DapperQueryBuilder具有此特殊命令Where()，其用于在内部保存过滤器，以后将其替换。
int maxPrice = 1000;
int maxWeight = 15;
string search = "%Mountain%";
var cn = new SqlConnection(connectionString);
// You can build the query manually and
// just use QueryBuilder to replace "where" filters (if any)
var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
/**where**/
ORDER BY ProductId
");
// You just pass the parameters as if it was an interpolated string,
// and QueryBuilder will automatically convert them to Dapper parameters (injection-safe)
q.Where($"[ListPrice] <= {maxPrice}");
q.Where($"[Weight] <= {maxWeight}");
q.Where($"[Name] LIKE {search}");
// Query() will automatically build your query and replace
// your /**where**/ (if any filter was added)
var products = q.Query<Product>();您也可以创建OR过滤器，如下所示：
q.Where(new Filters(Filters.FiltersType.OR)
{
new Filter($"[Weight] <= {maxWeight}"),
new Filter($"[Name] LIKE {search}")
});或者您可以像这样混合或匹配OR / AND：
q.Where(new Filters(Filters.FiltersType.OR)
{
new Filters(Filters.FiltersType.AND)
{
$"[Weight] <= {maxWeight}",
$"[Weight] >= {minWeight}",
}
new Filter($"[Name] LIKE {search}")
});甚至数组也可以用作内插字符串：
var categories = new string[] { "Components", "Clothing", "Acessories" };
q.Append($"WHERE c.[Name] IN {categories}");Fluent API（链式）
对于那些喜欢方法链指南的人（或那些允许最终用户构建自己的查询的人），有一个Fluent API，它允许您逐步模仿动态SQL串联来构建查询。
因此，基本上，不是从完整的查询开始，而只是追加新的过滤器（.Where()），QueryBuilder将为您构建整个查询：
var q = cn.QueryBuilder()
.Select($"ProductId")
.Select($"Name")
.Select($"ListPrice")
.Select($"Weight")
.From($"[Product]")
.Where($"[ListPrice] <= {maxPrice}")
.Where($"[Weight] <= {maxWeight}")
.Where($"[Name] LIKE {search}")
.OrderBy($"ProductId");
var products = q.Query<Product>();您将得到以下查询：
SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE [ListPrice] <= @p0 AND [Weight] <= @p1 AND [Name] LIKE @p2
ORDER BY ProductId希望您和我写这个库时一样喜欢这篇文章！
您可以在此处找到完整的源代码。如果喜欢，请添加星号并将其fork到GitHub中。
扫一扫
1386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
