SqlBinder库

• 下载 SqlBinder-0.2.0-alpha-Binaries - 46.3 KB
• 下载 SqlBinder-0.2.0-alpha - 801.4 KB

引言

SqlBinder 是一个工具，它以一种我在其他任何地方都未曾见过的方式来处理构建 SQL 查询的挑战——它将纯粹的旧式 SQL 与您可能在 XSLT 中找到的可能性结合起来。由于处理类似主题的工具及其扩展数量众多，也许最好解释一下 SqlBinder 不是什么。

它不是 ORM 或微 ORM 解决方案——相反，它是一个独立于 DBMS、以 SQL 为中心的模板引擎。它所做的只是消除编写生成 SQL 和绑定变量的代码的麻烦。它**不**生成完整的 SQL 本身，它**转换**现有的 SQL 模板。因此，它应该辅助（而不是取代）您可能正在使用的任何 ORM 解决方案。

它不是“SQL 构建器”，因为它具有高度的可组合性，旨在编写更复杂的查询，那些不止一行的查询——使用 SqlBinder，您的动态 SQL 可以由单个模板组成，您不必**构建**它，其理念是能够将其原样存储在某个地方。它也不是瑞士军刀，它可以优雅地与 Dapper、Dapper.Contrib、PetaPoco 等工具附带的其他流行的 SQL 构建助手一起使用，它并非旨在适应**所有**场景。

尽管有许多工具可以帮助您构建 SQL，但通过字符串拼接生成 SQL 查询仍然是一种惊人常见的方法。虽然这些工具在创建查询方面提供了一定程度的帮助，但它们自然有其不足之处。顺便说一句，我个人不喜欢那些生成**整个 SQL** 的工具——对我来说，那只是为了让已经可行的东西现在**与工具一起**工作而添加的另一层编程。然而，使用 SqlBinder，您可以完全表达您的数据库查询需求，而无需持续担心您的 SQL 是否能与 X 工具一起工作，或者您需要多少时间来**使其**工作。

背景

我最初在2009年编写了这个库的第一个版本，是为了让我的生活更轻松。我当时参与的项目依赖于大型且非常复杂的 Oracle 数据库，所有业务逻辑都在其中，所以我使用 SQL 来访问我需要的任何东西。我负责开发前端，其中涉及大量的过滤器和按钮，帮助用户自定义要可视化的数据。获取数千条记录然后在客户端进行过滤是不可行的。因此，在 DBA、PLSQL 开发人员等人的帮助下，我们设法整理出一些性能极高、复杂而巧妙的 SQL。

然而，这导致了一些相当笨拙的 SQL 生成和变量绑定代码，这些代码难以维护、优化和修改。像 NHibernate 这样的工具解决了我们没有的许多问题，但并没有完全解决我们遇到的问题。我当时不知道 Dapper，但它会减轻问题，却仍然无法解决它们（否则我就会转用 Dapper，因为它是一个**非常棒**的库）。正是在这里，SqlBinder 语法派上用场了，所有那些混乱都被转换成类似 string.Format 的代码，我可以编写整个脚本，然后传递变量（或者不传递）。它对我帮助很大，为了让它更容易访问和在多个项目中重用，我将其发布到 GitHub 上。现在，我为其他人也写了这篇文章，无论其价值如何。

使用库

SqlBinder 的源代码附带了一个演示应用程序、控制台示例和许多单元测试。我将在这里演示一些基本用法，以便您入门。正如您稍后将看到的，SqlBinder 的本质和语法实际上非常简单。

快速演示

考虑以下方法签名

IEnumerable<CategorySale> GetCategorySales(
	IDbConnection connection,
	IEnumerable<int> categoryIds = null,
	DateTime? fromShippingDate = null, DateTime? toShippingDate = null,
	DateTime? fromOrderDate = null, DateTime? toOrderDate = null,
	IEnumerable<string> shippingCountries = null);

此方法的实现应返回按类别分组并按以下任意组合条件过滤的销售摘要：类别、发货日期、订单日期和发货国家。

通常，您会通过某种 Fluent API（例如 PetaPoco 的 Sql.Builder）、Dapper.Contrib 出色的 SqlBuilder 或仅仅是 StringBuilder 来构建 SQL 以实现此方法。相反，我将向您展示如何通过 SqlBinder 和常规 Dapper 实现此方法。它会是这样：

IEnumerable<CategorySale> GetCategorySales(
	IDbConnection connection,
	IEnumerable<int> categoryIds = null,
	DateTime? fromShippingDate = null, DateTime? toShippingDate = null,
	DateTime? fromOrderDate = null, DateTime? toOrderDate = null,
	IEnumerable<string> shippingCountries = null)
{
	var query = new Query(GetEmbeddedResource("CategorySales.sql")); // SqlBinder!

	query.SetCondition("categoryIds", categoryIds);
	query.SetConditionRange("shippingDates", fromShippingDate, toShippingDate);
	query.SetConditionRange("orderDates", fromOrderDate, toOrderDate);
	query.SetCondition("shippingCountries", shippingCountries);

	return connection.Query<CategorySale>(query.GetSql(), query.SqlParameters);
}

但是 SQL 在哪里呢？这个 CategorySales.sql 文件里有什么？现在是精彩的部分了，您可以安全地将 SQL 存储在其他地方，并且它可能包含多个 WHERE 子句、多个 ORDER BY 子句以及任意数量的子查询——所有这些都由 SqlBinder 的模板原生支持，由于其高度可组合性，几乎没有理由将模板存储在您的方法中，除非它们是单行且非常小的。

如果我们把它们放到 CategorySales.sql 中，有多种可能的 SQL 脚本都可以与上述方法一起工作。

例如，这个带有快捷别名和可选子查询的脚本

SELECT
	CAT.CategoryID, 
	CAT.CategoryName, 
	SUM(CCUR(OD.UnitPrice * OD.Quantity * (1 - OD.Discount) / 100) * 100) AS TotalSales
FROM ((Categories AS CAT		
	INNER JOIN Products AS PRD ON PRD.CategoryID = CAT.CategoryID)
	INNER JOIN OrderDetails AS OD ON OD.ProductID = PRD.ProductID)
{WHERE 	
	{OD.OrderID IN (SELECT OrderID FROM Orders AS ORD WHERE 
			{ORD.ShippedDate :shippingDates} 
			{ORD.OrderDate :orderDates}
			{ORD.ShipCountry :shippingCountries})} 
	{CAT.CategoryID :categoryIds}}
GROUP BY 
	CAT.CategoryID, CAT.CategoryName

这个**可选**的子查询是什么？嗯，由于我们的 `OD.OrderID IN` 条件被 ` { } ` 括号包围，这意味着如果它**不需要**，它就不会被使用——换句话说，如果不需要，那么输出的 SQL 将不包含它及其子查询 `SELECT OrderID FROM Orders`。同样，如果其条件未使用，特别是如果 ` :shippingDates `、` :orderDates ` 或 ` :shippingCountries ` 都未使用，则 ` { } ` 中包含的整个部分将被删除。

:categoryIds 条件与此分离，属于父查询，SqlBinder 会自动将其（**如果**使用）与上述条件通过 AND 运算符连接。

下一个脚本使用不同的别名，但效果相同

SELECT
	Categories.CategoryID, 
	Categories.CategoryName, 
	SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * 
		(1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories		
	INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
	INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
{WHERE 	
	{OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE 
			{Orders.ShippedDate :shippingDates} 
			{Orders.OrderDate :orderDates}
			{Orders.ShipCountry :shippingCountries})} 
	{Categories.CategoryID :categoryIds}}
GROUP BY 
	Categories.CategoryID, Categories.CategoryName

这和之前的一样，只是使用了不同的别名——请注意，您不需要修改您的 GetCategorySales 方法即可使此模板工作，只要参数名称相同，它就能工作。

下一个模板使用完全不同的连接，并且没有子查询，它可能效率稍低，但效果相同

SELECT
	Categories.CategoryID, 
	Categories.CategoryName, 
	SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * 
		(1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM (((Categories		
	INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
	INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
	INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID)
{WHERE
	{Orders.ShippedDate :shippingDates} 
	{Orders.OrderDate :orderDates}
	{Orders.ShipCountry :shippingCountries} 
	{Categories.CategoryID :categoryIds}}
GROUP BY 
	Categories.CategoryID, Categories.CategoryName

或者，如果您想要完全不同的东西，这里还有一个模板，它有两个 WHERE 子句，使用不同的语法进行连接，并且没有 GROUP BY —— 同样，它可以开箱即用，并且会生成相同的数据

SELECT 
	Categories.CategoryID, 
	Categories.CategoryName, 
	(SELECT SUM(CCUR(UnitPrice * Quantity * (1 - Discount) / 100) * 100) 
	FROM OrderDetails WHERE ProductID IN 
		(SELECT ProductID FROM Products WHERE Products.CategoryID = Categories.CategoryID)
		{AND OrderID IN (SELECT OrderID FROM Orders WHERE 
			{Orders.ShippedDate :shippingDates} 
			{Orders.OrderDate :orderDates}
			{Orders.ShipCountry :shippingCountries})}) AS TotalSales
FROM Categories {WHERE {Categories.CategoryID :categoryIds}}

上述任何一个脚本都可以放入 CategorySales.sql 文件中，无需修改 C# 代码即可使用。使用 SqlBinder，您的 SQL 脚本可以**真正**独立于所有其他内容。

SqlBinder 的作用是将 SqlBinder.Condition 对象绑定到其模板脚本，返回一个有效的 SQL，然后您可以将其传递给您的 ORM。

顺便说一下，如果您不熟悉 Dapper，您可能会对这篇优秀的 'Dapper.NET 简介' 文章感兴趣。它是一个 ORM 解决方案，也提供了相对基本的辅助功能，用于将绑定变量传递给 SQL。

一些教程

教程可以通过源代码中提供的 *ConsoleTutorial.sln* 解决方案进行测试。演示数据库 (*Northwind Traders.mdb*) 也在那里。

现在让我们开始教程，以便您更好地理解这个东西的全部内容。

教程1：查询员工

让我们连接到 Northwind 演示数据库

var connection = new OleDbConnection
   ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind Traders.mdb");

然后编写一个简单的 OleDB SQL 查询，它将检索 employees 列表。

var query = new DbQuery(connection, @"SELECT * FROM Employees {WHERE EmployeeID :employeeId}");

如您所见，这不是典型的 SQL，其中包含一些**格式化**语法，这些语法稍后由 SqlBinder 处理。这是一个**SQL 模板**，将用于创建实际的 SQL。

我们实际上可以立即从这个模板创建一个命令

IDbCommand cmd = query.CreateCommand();

Console.WriteLine(cmd.CommandText); // Output the passed SQL

输出

SELECT * FROM Employees

请注意，输出 SQL 中不包含最初被 {...} 标签包围的 SQL。

现在我们**按 ID 筛选出一位员工**

query.SetCondition("employeeId", 1);

cmd = query.CreateCommand();

Console.WriteLine(cmd.CommandText); // Output the passed SQL

这是输出

SELECT * FROM Employees WHERE EmployeeID = :pemployeeId_1

我们使用相同的查询创建了两个完全不同的命令，它们具有不同的 SQL。这次，`{WHERE EmployeeID :employeeId}` 部分没有被消除。

让我们进一步，检索**ID 为 1 和 2 的员工**。同样，我们使用相同的查询，但为关键的 SetCondition 方法提供了不同的参数。

query.SetCondition("employeeId", new[] { 1, 2 });

cmd = query.CreateCommand();

Console.WriteLine(cmd.CommandText); // Output the passed SQL

输出

SELECT * FROM Employees WHERE EmployeeID IN (:pemployeeId_1, :pemployeeId_2)

那么发生了什么？让我们先回到我们的 SQL 模板

SELECT * FROM Employees {WHERE EmployeeID :employeeId}

在第一次测试中，query 对象没有提供任何条件，所以它移除了所有以 { 开始和以 } 结束的魔法语法，因为它没有用处。

在第二次测试中，我们调用了 SetCondition("employeeId", 1);，所以现在魔法语法开始发挥作用了。

所以，这个模板

... {WHERE EmployeeID :employeeId} ...

加上这个方法

SetCondition("employeeId", 1);

生成了这个 SQL

... WHERE EmployeeID = :pemployeeId_1 ...

:employeeId 占位符被简单地替换为 = :pemployeeId_1。SqlBinder 还会自动处理将传递给 IDbCommand 的命令参数（绑定变量）。

在第三次测试中，我们调用了 SetCondition("employeeId", new[] { 1, 2 });，这意味着这次我们想要两个员工。

这导致 SqlBinder 查询模板

... {WHERE EmployeeID :employeeId} ...

被转换为这个 SQL

... WHERE EmployeeID IN (:pemployeeId_1, :pemployeeId_2) ...

:employeeId 可以转换为很多东西，但目前，我们只介绍基本概念。

教程2：再查询一些员工

这次我们进行不同的查询

SELECT * FROM Employees {WHERE {City :city} {HireDate :hireDate} {YEAR(HireDate) :hireDateYear}}

这次，我们有嵌套作用域 {...{...}...}。首先，请注意这种语法可以放在 SQL 中的任何位置，并且 WHERE 子句对 SqlBinder 没有任何意义，它只是纯文本，如果其父作用域被移除，它也会被移除。

请记住：作用域仅在其所有子作用域都被移除或其子占位符（即 :param、@param 或 ?param）被移除时才被移除，而子占位符又会在未找到匹配条件时被移除。

例如，如果我们根本不传递任何**条件**，所有神奇的东西都会被移除，最终得到

SELECT * FROM Employees

但是如果我们确实传递了一些条件，例如，**让我们尝试获取1993年雇佣的员工**

query.SetCondition("hireDateYear", 1993);

这将生成以下 SQL

SELECT * FROM Employees WHERE YEAR(HireDate) = :phireDateYear_1

顺便说一句，不用担心命令参数值，它们已经传递给命令了。

如您所见，由于 SqlBinder 没有找到它们的任何匹配条件，所以作用域 {City :city} 和 {HireDate :hireDate} 被消除了。

现在我们尝试获取 1993 年 7 月之后雇佣的员工

query.Conditions.Clear(); // Remove any previous conditions
query.SetCondition("hireDate", from: new DateTime(1993, 6, 1));

这次，我们清除了条件集合，因为我们不想要 hireDateYear，我们现在只想要 hireDate —— 如果你再次查看 SQL 模板，你会发现它们是不同的占位符。

生成的 SQL 将是

SELECT * FROM Employees WHERE HireDate >= :phireDate_1

那么来自伦敦，在1993年至1994年间雇佣的员工呢？

query.Conditions.Clear();
query.SetCondition("hireDateYear", 1993, 1994);
query.SetCondition("city", "London");

现在我们有两个条件，它们将在输出 SQL 中自动通过 AND 运算符连接。所有连续的（即由空格分隔的）作用域将自动通过运算符（例如 AND, OR）连接。

生成的 SQL

SELECT * FROM Employees WHERE City = :pcity_1 AND YEAR(HireDate) _
         BETWEEN :phireDateYear_1 AND :phireDateYear_2

真棒！

演示应用

这个库附带了一个非常漂亮的交互式 WPF 演示应用程序，它作为一个更复杂的 SqlBinder 功能示例。它实际上仍然相当基础（毕竟它只是一个 MDB），但提供了对核心功能的更深入了解，并作为一个真实的示例。

演示应用程序展示了一个真实世界的使用模式。它将 SqlBinder 查询存储在 *.sql* 文件中，这些文件实际上是编译到二进制文件中的嵌入式资源。应用程序中的每个屏幕都由其自己的 *.sql* 脚本支持。

通过查看这些文件，即使是未接触过 SqlBinder 的人也能理解它应该查询什么类型的数据——它只需要理解 SQL 语法。查询简洁、可读、易于扩展和修改。应用程序中每个复杂的搜索屏幕都由一个对应的 SqlBinder 模板定义——后台没有 string 拼接或复杂的 Linq/Lambda C# 代码来生成 SQL。另请注意，SqlBinder 速度非常快，模板只解析一次然后缓存。

让我们浏览一下演示屏幕，看看它们背后隐藏着什么。

产品屏幕

如您所见，可以根据一个或多个类别或供应商、产品名称或单价（大于、小于、等于或介于之间）、停产状态（我写对了吗？）或价格是否高于平均值进行筛选。

用户可以使用上述任何或所有过滤器进行过滤。现在，这是此屏幕及其所有选项背后的单个 SqlBinder 脚本

-- Products.sql

SELECT P.*,
    (SELECT CategoryName FROM Categories WHERE CategoryID = P.CategoryID) AS CategoryName,
    (SELECT CompanyName FROM Suppliers WHERE SupplierID = P.SupplierID) AS SupplierCompany
FROM Products P
{WHERE
{ProductID :productId}
{ProductName :productName}
{SupplierID :supplierIds}
{CategoryID :categoryIds}
{UnitPrice :unitPrice}
{UnitPrice :priceGreaterThanAvg}
{Discontinued :isDiscontinued}}

所以所有的选项和仅仅一个 SQL？是的。

这是此屏幕背后的 C# 方法

public IEnumerable<Product> GetProducts(decimal? productId = null,
    string productName = null,
    int[] supplierIds = null,
    int[] categoryIds = null,
    decimal? unitPriceFrom = null,
    decimal? unitPriceTo = null,
    bool? isDiscontinued = null,
    bool priceGreaterThanAvg = false)
{
    var query = new DbQuery(_connection, GetSqlBinderScript("Products.sql"));
            
    if (productId != null)
        query.SetCondition("productId", productId);
    else
    {
        query.SetCondition("productName", productName, StringOperator.Contains);
        query.SetCondition("supplierIds", supplierIds);
        query.SetCondition("categoryIds", categoryIds);
        query.SetConditionRange("unitPrice", unitPriceFrom, unitPriceTo);
        query.SetCondition("isDiscontinued", isDiscontinued, ignoreIfNull: true);
        if (priceGreaterThanAvg)
            query.DefineVariable("priceGreaterThanAvg", "> (SELECT AVG(UnitPrice) From Products)");
    }

    using (var r = query.CreateCommand().ExecuteReader())
        while (r.Read())
            yield return OledbOrm.CreateProduct(r);
}

我在这里通过非常粗糙的 ORM (即 OledbOrm.CreateProduct(r)) 手动填充 POCOs，但您可以为该任务使用几乎任何您想要的 ORM，我只是不想为了可读性而增加额外的依赖。请注意这一切多么直截了当？您在这里没有**构建**任何东西，您只是在使用一个模板并向其应用条件。SqlBinder 处理其余部分。

下载源代码，启动演示，然后看看您操作过滤器选项时它会生成什么样的 SQL。

订单屏幕

这个屏幕甚至更复杂。您可以按客户、产品、员工和发货人进行过滤。然后，您可以选择各种日期、运费、发货国家以及最终的发货城市。

这是用于此屏幕的 SqlBinder 查询

-- Orders.sql

SELECT O.*,
    (SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
    (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
    (SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
{WHERE
{OrderID :orderId}
{CustomerID :customerIds}
{EmployeeID :employeeIds}
{ShipVia :shipperIds}
{OrderDate :orderDate}
{RequiredDate :reqDate}
{ShippedDate :shipDate}
{Freight :freight}
{ShipCity :shipCity}
{ShipCountry :shipCountry}
{OrderID IN (SELECT OrderID FROM OrderDetails WHERE {ProductID :productIds})}}

如您所见，它只比前一个屏幕复杂一点点，但同样，任何对 SQL 有基本了解的人都能理解它的作用。这里真的没有陡峭的学习曲线。

让我们来看看 SqlBinder 脚本可以生成的一些 SQL 查询。

未应用任何过滤器

SELECT O.*,
    (SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
    (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
    (SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O

从产品筛选器选项卡中选择的单个产品

SELECT O.*,
	(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
	(SELECT FirstName + ' ' + LastName FROM Employees _
                                           WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
	(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID = :pproductIds_1)

同上，加上从发货人选项卡选择的两个发货人

SELECT O.*,
	(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
	(SELECT FirstName + ' ' + LastName FROM Employees _
                                           WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
	(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShipVia IN (:pshipperIds_1, :pshipperIds_2)
AND OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID = :pproductIds_1)

选择多个产品和发货日期（仅指定“至”值）

SELECT O.*,
	(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
	(SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) _
                                           AS EmployeeName,
	(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShippedDate <= :pshipDate_1
AND OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID IN (:pproductIds_1, :pproductIds_2))

仅发货日期，同时指定“从”和“到”值

SELECT O.*,
	(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
	(SELECT FirstName + ' ' + LastName FROM Employees _
                                           WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
	(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShippedDate BETWEEN :pshipDate_1 AND :pshipDate_2

以此类推...

我们将在此处停止——SqlBinder 可以生成的内容种类繁多。

这是用于此屏幕的 C# 方法

public IEnumerable<Order> GetOrders(int? orderId = null,
    int[] productIds = null,
    string[] customerIds = null,
    int[] employeeIds = null,
    int[] shipperIds = null,
    DateTime? orderDateFrom = null, DateTime? orderDateTo = null,
    DateTime? reqDateFrom = null, DateTime? reqDateTo = null,
    DateTime? shipDateFrom = null, DateTime? shipDateTo = null,
    decimal? freightFrom = null, decimal? freightTo = null,
    string shipCity = null,
    string shipCountry = null)
{
    var query = new DbQuery(_connection, GetSqlBinderScript("Orders.sql"));

    if (orderId.HasValue)
        query.SetCondition("orderId", orderId);
    else
    {
        query.SetCondition("productIds", productIds);
        query.SetCondition("customerIds", customerIds);
        query.SetCondition("employeeIds", employeeIds);
        query.SetCondition("shipperIds", shipperIds);
        query.SetConditionRange("freight", freightFrom, freightTo);
        query.SetConditionRange("orderDate", orderDateFrom, orderDateTo);
        query.SetConditionRange("reqDate", reqDateFrom, reqDateTo);
        query.SetConditionRange("shipDate", shipDateFrom, shipDateTo);
        query.SetCondition("shipCity", shipCity, ignoreIfNull: true);
        query.SetCondition("shipCountry", shipCountry, ignoreIfNull: true);
    }

    using (var r = query.CreateCommand().ExecuteReader())
        while (r.Read())
            yield return OledbOrm.CreateOrder(r);
}

请注意，您可以立即指定 orderId，如果这样做，该方法只会将其传递给 SqlBinder 的查询。通常，您会为此编写一个单独的方法，带有单独的 SQL，但使用 SqlBinder，您不必这样做，您可以使用相同的模板，并且其他选项的存在不会增加性能开销。

类别销售屏幕

“类别销售”屏幕看起来很简单，也确实如此，但其内部发生了一些有趣的事情。看看它的 SqlBinder 脚本

-- CategorySales.sql

SELECT
    Categories.CategoryID, 
    Categories.CategoryName, 
    SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories        
    INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
    INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
{WHERE     
    {OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE 
            {Orders.ShippedDate :shippingDates} 
            {Orders.OrderDate :orderDates}
            {Orders.ShipCountry :shipCountry})} 
    {Categories.CategoryID :categoryIds}}
GROUP BY 
    Categories.CategoryID, Categories.CategoryName

正如你所看到的，通过检查花括号的结构，很明显，如果 :shippingDates、:orderDates 和 :shipCountry 都被省略，SqlBinder 将移除整个子查询。这是 SqlBinder 的另一个技巧——你可以编写复杂的子查询，这些子查询会给服务器带来显著的性能损失，但你可以指示 SqlBinder 在它们冗余时移除这些查询——只需用 { ... } 将它们包围起来。因此，我们这里有两个 WHERE 子句，每个都有自己的条件。

在这个例子中，要按发货日期过滤，我们必须查询 Orders 表。我们没有在任何地方显示这些日期，我们只需要按它们过滤，所以我们要么执行某种 JOIN 操作，要么执行子查询。通常，开发人员选择第一个选项——连接此表以获取额外的列进行过滤。使用 SqlBinder，您不必一次连接所有三个表，如果想按另一个表中的一个或多个列进行过滤，子查询将保留，如果不是，它将完全删除——这得益于 SqlBinder 由花括号定义的作用域。

所以，如果您没有指定来自 Orders 表的任何过滤条件，SQL 会是这样

-- Category Sales Output Sql 
SELECT
    Categories.CategoryID, 
    Categories.CategoryName, 
    SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories        
    INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
    INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
GROUP BY 
    Categories.CategoryID, Categories.CategoryName

但如果您指定了，SqlBinder 将在其模板脚本中包含您提供的子查询

-- Category Sales Output Sql
SELECT
    Categories.CategoryID, 
    Categories.CategoryName, 
    SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories        
    INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
    INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
WHERE 
    OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE 
            Orders.ShippedDate BETWEEN :pshippingDates_1 AND :pshippingDates_2)
GROUP BY 
    Categories.CategoryID, Categories.CategoryName

真棒！

这是此屏幕背后的 C# 代码

public IEnumerable<CategorySale> GetCategorySales(int[] categoryIds = null, DateTime? fromDate = null, DateTime? toDate = null)
{
    var query = new DbQuery(_connection, GetSqlBinderScript("CategorySales.sql"));

    query.SetCondition("categoryIds", categoryIds);
    query.SetConditionRange("shippingDates", fromDate, toDate);

    using (var r = query.CreateCommand().ExecuteReader())
        while (r.Read())
            yield return OledbOrm.CreateCategorySale(r);
}

性能

SqlBinder 速度**非常**快，但我没有任何可比较的对象。相反，您可以将其与 Dapper 等微 ORM 解决方案结合使用，并测量潜在的开销。我以 Dapper 作为参考，因为它是目前我所知的最快的微 ORM。

考虑以下表格，一个在 LocalDB 上测试，另一个在 Access 上测试。在每个表格的左栏中，您将看到 Dapper 单独的性能；在右栏中，您将看到 Dapper 执行完全相同的操作，但增加了 SqlBinder 施展其魔力的额外开销。

        Dapper +SqlBinder
    ---------------------
         52.88      53.46
         57.31      59.55
         56.22      68.07
         55.97      56.16
         66.52      55.59
         54.82      52.96
         50.98      61.97
         59.06      57.53
         50.38      53.97
        AVG 56     AVG 58

     ^ Dapper = Just Dapper.
     ^ +SqlBinder = Dapper with SqlBinder.
             

        Dapper +SqlBinder
    ---------------------
        335.42     336.38
        317.99     318.89
        342.56     324.85
        317.20     320.84
        327.91     324.56
        320.29     326.86
        334.42     338.73
        344.43     326.33
        315.32     322.48
       AVG 328    AVG 327

     ^ Dapper = Just Dapper.
     ^ +SqlBinder = Dapper with SqlBinder.
             

如您所见，在 SqlServer 上，我们额外增加了 2ms 的开销，这是 SqlBinder 根据不同条件构建查询所需的时间。在 OleDb Access 测试中，这个差异微不足道，完全消失在偏差中（很可能是在与 FS/DB 交互时）。

测试结果中的每一行都是以下查询执行 500 次的结果

SELECT * FROM POSTS WHERE ID IN @id

并且

SELECT * FROM POSTS {WHERE {ID @id}}

后者用于 Dapper+SqlBinder 组合。

值得注意的是，SqlBinder 能够重用已编译的模板，因为它完全分离了解析和模板化关注点。您可以一次创建 SqlBinder 查询模板，然后从相同的预解析模板构建所有后续 SQL 查询。SqlBinder 的关键功能之一是它不会**每次**都解析或生成整个 SQL。此外，它依赖于经过优化的手动编写的解析器。

源代码中提供了简单的性能测试，您可以在其中自行对 SqlBinder 进行基准测试。

它是如何工作的？

将 SqlBinder 模板转换为 ADO.NET 命令的过程主要由四个公共类完成，如下图所示

我将尝试更详细地解释工作流程

• Parser 接收 SqlBinder 脚本，对其进行词法分析并返回一个解析树。这很重要，因为各种 SQL 字面量可能会干扰您的 {...} 作用域和绑定参数。此解析器完全支持 Oracle 替代引用、PostgreSQL $$literals$$、MySql 字面量等。
• SqlBinderProcessor（简称 Processor）将解析树作为输入并枚举它，验证令牌并触发参数占位符事件，请求将 SQL 放入其位置——未收到任何反馈的参数将连同其父作用域一起被移除。因此，不包含任何有效参数占位符或其任何子作用域中不包含任何有效参数占位符的作用域将被移除。在此过程中，Processor 类构建一个输出 SQL string。
• Query 是核心类，它将 Parser 和 Processor 的功能合并为一体。它接收 SqlBinder 脚本和 Condition 对象列表作为输入，从 Parser 获取解析树，可选地缓存其结果，然后将其发送到 Processor 类，同时订阅其事件。Processor 为解析树中的每个参数占位符触发一个事件，Query 类订阅该事件并使用先前提供的 Condition 对象列表为这些占位符生成单独的 SQL（如果匹配），例如“:employeeId”变为“= 123”。根据 Query 类的此反馈，Processor 类将返回一个完整的 SQL。
• DbQuery 类只是覆盖了 Query 类，除了提供基类功能（SQL 和绑定变量的 KeyValue 对）之外，它还根据提供的 IDbConnection 创建一个有效的 IDbCommand 实例。您可以自行覆盖 Query 类以实现任何其他自定义实现——这只是一个开箱即用的 ADO.NET 实现。所有其他类在某种程度上是与数据库无关的，因为它们与 System.Data.* 没有任何关系。

为什么要有一个解析器？

起初，SqlBinder 依赖于非常快速和可靠的 .NET 编译递归正则表达式，但各种 SQL 字面量、注释、转义码等被证明对正则表达式来说太多了，它开始变得丑陋（当你对正则表达式过于乐观时通常会这样）。所以，我为它写了一个解析器，现在速度快了两倍。

然而请注意，这不是**SQL**解析器，它是SqlBinder解析器。它所寻找的**SQL**方面只有字符串字面量和注释——当您将一些神奇的格式化语法注入到别人的语法中（此示例中的SQL可能是Oracle的、MySql的、PostgreSql的等等），您需要特别注意在处理注释和字面量时遵守其规则，因为您确实不想改变它们。

如果您查看代码，您会注意到解析器并不是特别面向对象，这是有意为之的。避免 StringBuilder、string 方法、对象实例化和销毁等都是有意为之的。我们特别注意不调用 GC，这也是为什么会有一些不安全代码的原因。即便如此，我仍然能够将每个标记分离到其对应的类中，因此添加任何新标记都非常容易。我熟悉 Gold 或 ANTLR 等解析器生成器，但我认为使用它们会过度——如果我正在解析整个 SQL 语法，它们才有意义。将词法分析器与解析器分开也没有太大意义，因为，同样，我在这里处理的标记不多——只有注释、字面量和 SqlBinder 极其简单的语法。

额外的好处

当您尝试使用 SqlBinder 时，您会注意到 SetCondition 方法有大量的重载。其中许多重载只是对一些开箱即用的 ConditionValue 实现（如：BoolValue、NumberValue、DateValue 和 StringValue）的快捷封装。abstract 类 ConditionValue 为您提供了将任何类型的 SQL 注入 SqlBinder 参数占位符的途径，并可选择使用绑定变量进行备份。

例如，NumberValue 类提供了各种功能，尽管听起来非常简单。它可以接受一个数字作为输入，两个数字，一个数字列表，或 null。它还验证输入，确保没有垃圾进入 SQL。例如

• BETWEEN 1 AND 1 不会发生，它会输出 = 1。
• NOT BETWEEN 1 AND 1 类似处理。
• IN (1) 或 NOT IN (1) 不会发生，它会输出 = 1 或 <> 1。
• IN (1, 1, 1) 同样，不会发生。
• 可以自动处理 `null` 值，即，将 `null` 传递给条件将导致插入 `IS NULL` 或 `IS NOT NULL`，而不是传递变量。同样，`IN (NULL)` 不会发生。
• 提供强制执行策略的手段，例如在 <> X 和 != X 之间进行选择。

这一切都大大有助于减轻 DBA 的头痛，他们最终将不得不调整您的软件生成的 SQL。在更高层次上，您有办法强制执行某些规则、策略并实施自动化，从而从技术上防止您或您的开发人员创建潜在有害的 SQL，尤其是在输入来自最终用户时。

这是从 NumberValue 类中截取的一段代码，以便您更好地了解其工作原理

protected override string OnGetSql(int sqlOperator)
{
    switch (sqlOperator)
    {
        case (int)Operator.Is:
            return _values.Length == 0 ? "IS NULL" : ValidateParams("= {0}", 1);
        case (int)Operator.IsNot:
            return _values.Length == 0 ? "IS NOT NULL" : ValidateParams("<> {0}", 1);
        case (int)Operator.IsLessThan: return ValidateParams("< {0}", 1);
        case (int)Operator.IsLessThanOrEqualTo: return ValidateParams("<= {0}", 1);
        case (int)Operator.IsGreaterThan: return ValidateParams("> {0}", 1);
        case (int)Operator.IsGreaterThanOrEqualTo: return ValidateParams(">= {0}", 1);
        case (int)Operator.IsBetween:
            switch (_values.Length)
            {
                case 2: return ValidateParams("BETWEEN {0} AND {1}", 2);
                case 1: return ValidateParams("= {0}", 1);
                default: throw new InvalidOperationException
                         (Exceptions.PlaceholdersAndActualParamsDontMatch);
            }
        case (int)Operator.IsNotBetween:
            switch (_values.Length)
            {
                case 2: return ValidateParams("NOT BETWEEN {0} AND {1}", 2);
                case 1: return ValidateParams("<> {0}", 1);
                default: throw new InvalidOperationException
                         (Exceptions.PlaceholdersAndActualParamsDontMatch);
            }
        case (int)Operator.IsAnyOf:
            if (!IsValueList())
                return ValidateParams("= {0}", 1);
            return ValidateParams("IN ({0})", 1, true);
        case (int)Operator.IsNotAnyOf:
            if (!IsValueList())
                return ValidateParams("<> {0}", 1);
            return ValidateParams("NOT IN ({0})", 1, true);
        default: throw new InvalidConditionException
                 (this, (Operator)sqlOperator, Exceptions.IllegalComboOfValueAndOperator);
    }
}

语法规范

我为 SqlBinder 使用的语法编写了一份“规范”，并将其放在其 GitHub 页面上——它提供了比本文描述的更多选项。我目前的计划是将所有示例保留在此处，并将确切的规范保留在 GitHub 页面上，但我们将拭目以待，看看进展如何，或者是否有人对此感兴趣。

关注点

无论您是在另一个库、应用程序、网站还是服务中使用 SQL，这个库都可以帮助您。无论您使用 Dapper、PetaPoco 还是其他工具来辅助 SQL/ORM，SqlBinder 都非常小巧、速度极快，但却为您的 SQL 组合带来了全新的水平。

我最初完全是为了自己而写，以帮助我以一种更好、尽管非传统的方式做事。我不得不说我真的很喜欢它，并且很久以来都想发布它，但一直没有时间。

希望您也喜欢它，如果喜欢，请给文章评分，并随意提出功能建议、测试、报告或修复错误！

下载/安装

您可以通过文章顶部的链接下载源代码和/或编译后的二进制文件，这些文件与文章一样最新——这很方便，因为您拥有的代码与您正在阅读的内容匹配。源代码包含所有示例。

• 获取最新源代码和所有版本，请访问 https://github.com/bawkee/SqlBinder
• 如需方便安装的 NuGet 包，请访问 https://nuget.net.cn/packages/SqlBinder/

您可以通过 NuGet 在 Visual Studio 中安装它，即

Install-Package SqlBinder -Version 0.2.0

文章历史

• 2018年6月13日 - 增加了更好的介绍和快速演示。尝试更好地强调 SqlBinder 可以与微 ORM 配合使用的概念。代码/二进制文件已更新至 v0.2.0，并更新了文章和相关代码以匹配新版本。
• 2018年6月5日 - 初始版本