dapper 代码生成器_Dapper的动态查询生成器

dapper 代码生成器

介绍 (Introduction)

DapperQueryBuilder is a wrapper around Dapper mostly for helping building dynamic SQL queries and commands. It's based on two fundamentals:

DapperQueryBuilder是Dapper的包装，主要用于帮助构建动态SQL查询和命令。 它基于两个基本原则：

1. String Interpolation instead of manually using DynamicParameters
   字符串插值，而不是使用DynamicParameters手动进行
2. Query and Parameters walk side-by-side
   查询和参数并排

It seems so simple, but you'll see how powerful it is.

看起来很简单，但是您会发现它的功能强大。

背景 (Background)

We all love Dapper micro-ORM.

我们都喜欢Dapper micro-ORM。

Many developers have realized that although Entity Framework offers some features that Dapper doesn't, Dapper is still more flexible (and much much faster) than EF. And many have realized that the right tool for the right job sometimes involving using both EF for some things and Dapper for others. And one of the usages where Dapper is the king is for building dynamic queries.

许多开发人员已经意识到，尽管Entity Framework提供了Dapper无法提供的某些功能，但Dapper仍比EF更灵活(且速度要快得多)。 许多人已经意识到，为完成正确的工作而使用的正确工具有时涉及将EF用于某些事物而将Dapper用于其他事物。 Dapper为王的用途之一是构建动态查询。

动态SQL (Dynamic SQL)

Old-school developers (those who have been coding since the late 90s or early 2000s, probably in VB6, ASP3, or PHP) will probably remember code like this:

老派开发人员(那些从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);

Basically, we were all sending dynamic SQL statements directly to our databases. And since user-input can be malicious (see below), we had to manually sanitize user-input to avoid SQL-Injection attacks.

基本上，我们都是直接将动态SQL语句发送到我们的数据库。 而且由于用户输入可能是恶意的(请参阅下文)，因此我们必须手动清理用户输入以避免SQL-Injection攻击 。

你好鲍比表 (Hello Bobby Tables)

If you weren't sanitizing your input correctly, you were at serious risk. SQL-injection is probably the most popular web-hacking technique. If you forgot to sanitize something like an "id" querystring, you could be opening a huge vulnerability and possibly exposing all your database, not only for reading but also for modifications.

如果您没有正确清理输入内容，则可能会面临严重的风险。 SQL注入可能是最流行的Web黑客技术。 如果您忘记清理“ id ” querystring类的东西，则可能会打开一个巨大的漏洞，并可能会暴露所有数据库，不仅用于读取，而且用于修改。

参数化SQL (Parametrized SQL)

Even if you're old school (hey VB6 guy, I'