Dapper的动态查询生成器

寒冰屋发布时间：2020-09-02 20:35:48 ，浏览量：0

介绍

背景

动态SQL

你好Bobby 表

参数化SQL

回到现代软件

Dapper 例子

不要重复自己

字符串插值内部

DapperQueryBuilder

快速开始

筛选器清单

Fluent API（链式）

介绍

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(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(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(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();

尽管看起来您只是在构建动态SQL（使用值作为内联文字），但实际上您得到的是参数化SQL。

以防万一，query有以下基本声明：

SELECT * FROM [Product] WHERE 1=1 AND Name LIKE @p0 AND ProductSubcategoryId = @p1

并保留以下参数：

@p0 = productName
@p1 = subCategoryId

总而言之，不是使用Dapper的扩展名.Query来扩展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]

关注

打赏

1665926880

查看更多评论

Dapper的动态查询生成器

最近更新

热门博客

[ 申请 ]友情链接：