Dapper高级查询：SqlBuilder动态SQL构建详解

Dapper高级查询：SqlBuilder动态SQL构建详解

【免费下载链接】Dapper 项目地址: https://gitcode.com/gh_mirrors/dappe/dapper-dot-net

在日常开发中，你是否还在为拼接复杂SQL语句而烦恼？是否遇到过因条件变化导致SQL语句冗长难维护的问题？本文将详细介绍Dapper中的SqlBuilder组件，通过简单直观的API帮助你轻松构建动态SQL，解决条件查询、多表关联等常见场景的痛点。读完本文后，你将能够掌握SqlBuilder的核心用法，编写更简洁、灵活的数据库访问代码。

什么是SqlBuilder

SqlBuilder是Dapper提供的一个强大工具，位于Dapper.SqlBuilder/SqlBuilder.cs文件中，它允许开发者通过面向对象的方式动态构建SQL语句，避免了手动字符串拼接带来的安全风险和维护难题。SqlBuilder的核心思想是将SQL语句分解为多个可复用的片段，根据不同条件动态组合，最终生成完整的SQL语句。

SqlBuilder的核心优势

• 类型安全：避免手动拼接SQL带来的语法错误和SQL注入风险
• 可维护性：将SQL片段模块化，便于修改和扩展
• 灵活性：根据不同条件动态生成SQL，适应复杂查询场景
• 简洁性：通过流畅的API设计，使代码更易读、易写

核心组件与工作原理

主要类结构

SqlBuilder主要由以下几个核心类组成：

• SqlBuilder：主类，提供添加SQL片段的方法
• Template：模板类，用于定义SQL模板和解析参数
• Clause：SQL片段类，存储单个SQL片段和参数
• Clauses：SQL片段集合类，负责组合多个SQL片段

工作流程图

基本使用方法

安装与引用

要使用SqlBuilder，首先需要在项目中引用Dapper.SqlBuilder组件。在项目文件中添加以下引用：

<ProjectReference Include="Dapper.SqlBuilder/Dapper.SqlBuilder.csproj" />

基本查询示例

下面是一个简单的查询示例，展示如何使用SqlBuilder构建动态查询：

var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT /**select**/ FROM Products /**where**/");

builder.Select("Id, Name, Price");
builder.Where("Price > @MinPrice", new { MinPrice = 10 });
builder.Where("Category = @Category", new { Category = "Electronics" });

using (var connection = new SqlConnection(connectionString))
{
    var products = connection.Query<Product>(template.RawSql, template.Parameters);
}

在这个示例中，我们创建了一个SqlBuilder实例，定义了一个包含/**select**/和/**where**/占位符的SQL模板。然后通过Select和Where方法添加SQL片段，最后使用Query方法执行查询。

高级功能详解

条件查询

SqlBuilder提供了Where和OrWhere方法来构建条件查询：

var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT Id, Name FROM Users /**where**/");

builder.Where("Status = @Status", new { Status = 1 });

if (!string.IsNullOrEmpty(searchKeyword))
{
    builder.OrWhere("Name LIKE @Keyword", new { Keyword = $"%{searchKeyword}%" });
    builder.OrWhere("Email LIKE @Keyword", new { Keyword = $"%{searchKeyword}%" });
}

// 生成的SQL:
// SELECT Id, Name FROM Users WHERE Status = @Status OR (Name LIKE @Keyword OR Email LIKE @Keyword)

多表关联

使用InnerJoin、LeftJoin等方法可以轻松实现多表关联查询：

var builder = new SqlBuilder();
var template = builder.AddTemplate(@"
    SELECT /**select**/ 
    FROM Orders /**leftjoin**/
    /**where**/
    /**orderby**/
");

builder.Select("o.Id, o.OrderDate, c.Name as CustomerName");
builder.LeftJoin("Customers c ON o.CustomerId = c.Id");
builder.Where("o.OrderDate >= @StartDate", new { StartDate = new DateTime(2023, 1, 1) });
builder.OrderBy("o.OrderDate DESC");

// 生成的SQL:
// SELECT o.Id, o.OrderDate, c.Name as CustomerName 
// FROM Orders 
// LEFT JOIN Customers c ON o.CustomerId = c.Id
// WHERE o.OrderDate >= @StartDate
// ORDER BY o.OrderDate DESC

分页查询

结合OrderBy和Where方法，可以实现灵活的分页查询：

var builder = new SqlBuilder();
var template = builder.AddTemplate(@"
    SELECT /**select**/ 
    FROM Products /**where**/
    /**orderby**/
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
");

builder.Select("Id, Name, Price");
builder.Where("Category = @Category", new { Category = "Books" });
builder.OrderBy("Price ASC");

var parameters = new { 
    Category = "Books", 
    Offset = (pageNumber - 1) * pageSize, 
    PageSize = pageSize 
};
builder.AddParameters(parameters);

// 生成的SQL包含分页逻辑

实际应用场景

动态筛选

在后台管理系统中，经常需要根据用户选择的多个条件进行筛选，使用SqlBuilder可以轻松实现这一功能：

var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT * FROM Orders /**where**/");

if (filter.CustomerId.HasValue)
{
    builder.Where("CustomerId = @CustomerId", new { filter.CustomerId });
}

if (filter.StartDate.HasValue)
{
    builder.Where("OrderDate >= @StartDate", new { filter.StartDate });
}

if (filter.EndDate.HasValue)
{
    builder.Where("OrderDate <= @EndDate", new { filter.EndDate });
}

if (!string.IsNullOrEmpty(filter.Status))
{
    builder.Where("Status = @Status", new { filter.Status });
}

批量操作

SqlBuilder不仅可以用于查询，还可以用于构建INSERT、UPDATE、DELETE等语句：

var builder = new SqlBuilder();
var template = builder.AddTemplate("UPDATE Products /**set**/ /**where**/");

builder.Set("Price = @NewPrice", new { NewPrice = product.Price });
builder.Set("Stock = @Stock", new { Stock = product.Stock });
builder.Where("Id = @Id", new { product.Id });

// 生成的SQL: UPDATE Products SET Price = @NewPrice , Stock = @Stock WHERE Id = @Id

最佳实践与注意事项

参数化查询

始终使用参数化查询，避免SQL注入攻击。SqlBuilder会自动处理参数，确保查询安全：

// 正确做法
builder.Where("Name LIKE @Keyword", new { Keyword = $"%{search}%" });

// 错误做法 (存在SQL注入风险)
builder.Where($"Name LIKE '%{search}%'");

代码组织

对于复杂查询，建议将SQL片段的构建逻辑封装到专门的方法或类中：

public class ProductQueryBuilder
{
    private readonly SqlBuilder _builder;
    
    public ProductQueryBuilder()
    {
        _builder = new SqlBuilder();
    }
    
    public ProductQueryBuilder WithCategory(string category)
    {
        _builder.Where("Category = @Category", new { Category = category });
        return this;
    }
    
    public ProductQueryBuilder WithPriceRange(decimal? minPrice, decimal? maxPrice)
    {
        if (minPrice.HasValue)
            _builder.Where("Price >= @MinPrice", new { MinPrice = minPrice });
            
        if (maxPrice.HasValue)
            _builder.Where("Price <= @MaxPrice", new { MaxPrice = maxPrice });
            
        return this;
    }
    
    // 其他条件方法...
    
    public Template Build()
    {
        return _builder.AddTemplate("SELECT * FROM Products /**where**/");
    }
}

性能考量

• 缓存模板：对于频繁执行的查询，可以缓存Template对象，避免重复解析
• 减少不必要的条件：只添加必要的查询条件，避免生成过于复杂的SQL
• 使用索引：动态生成的SQL也需要注意索引的使用，确保查询性能

总结与展望

SqlBuilder作为Dapper生态中的重要组件，为动态SQL构建提供了简洁而强大的解决方案。通过本文的介绍，相信你已经掌握了SqlBuilder的基本用法和高级技巧。无论是简单的条件查询还是复杂的多表关联，SqlBuilder都能帮助你编写出更安全、更易维护的数据库访问代码。

官方文档：docs/index.md 示例代码：tests/Dapper.Tests/SqlBuilderTests.cs API参考：Dapper.SqlBuilder/SqlBuilder.cs

随着Dapper的不断发展，SqlBuilder也将持续优化和完善。未来可能会加入更多高级功能，如子查询支持、更复杂的条件表达式等。建议保持关注项目的更新，及时了解新特性和最佳实践。

如果你觉得本文对你有帮助，请点赞、收藏并关注，后续将带来更多Dapper使用技巧和最佳实践分享。

【免费下载链接】Dapper 项目地址: https://gitcode.com/gh_mirrors/dappe/dapper-dot-net