高级查询

本文介绍一些复杂查询的使用场景。

IN 查询

XmlCommand定义

<XmlCommand Name="getByIn">
    <Parameters />
    <CommandText><![CDATA[
    select * from Customers where CustomerID in ( {customerIdList} )
    ]]></CommandText>
</XmlCommand>

注意上面使用了一个 占位参数 {customerIdList}

C#调用代码

public async Task<List<Customer>> GetByIn(int[] ids)
{
    var args = new { customerIdList = ids };
    return await dbContext.XmlCommand.Create("getByIn", args).ToListAsync<Customer>();
}

或者用 CPQuery 来实现

public async Task<List<Customer>> GetByIn2(int[] ids)
{
    string sql = "select * from Customers where CustomerID in ( {customerIdList} )";
    var args = new { customerIdList = ids };
    return await dbContext.CPQuery.Create(sql, args).ToListAsync<Customer>();
}

小结

IN查询需要在SQL语句中使用一个占位参数
调用时，参数用数组或者List来赋值
参数类型范围：
- int[], List<int>
- long[], List<long>
- string[], List<string>
- Guid[], List<Guid>

动态条件查询

示例代码，请仔细阅读代码中的注释

public class ProductBLL : BaseBLL
{
    /// <summary>
    /// 演示动态条件查询（多字段的组合查询）
    /// </summary>
    /// <param name="product"></param>
    /// <param name="pagingInfo"></param>
    /// <returns></returns>
    public async Task<List<Product>> Search(ProductSearchParam product, PagingInfo pagingInfo)
    {
        // 这里演示一种【动态查询】，也就是根据一定条件拼接查询

        // 先创建一个CPQuery基础对象，它包含查询哪些表，用于后面追加查询条件
        CPQuery query = dbContext.CPQuery.Create("select * from Products where (1=1)");

        if( product.CategoryID > 0 ) 
            //【数字参数】可以直接相加，会自动变成SQL参数
            query = query + " and CategoryID = " + product.CategoryID; 

        if( product.UnitPrice > 0 )
            // 【数字参数】可以直接相加，会自动变成SQL参数
            query = query + " and UnitPrice > " + product.UnitPrice; 

        if( string.IsNullOrEmpty(product.Unit) == false )
            // 【字符串参数】由于不能自动识别（到底是SQL的一部分，还是参数），所以需要强制类型转换
            query = query + " and Unit = " + (QueryParameter)product.Unit; 

        if( string.IsNullOrEmpty(product.ProductName) == false )
            // 【字符串参数】，也可以调用扩展方法，转成参数对象
            query = query + " and ProductName like " + product.ProductName.AsQueryParameter(); 

        // 添加排序部分
        query = query + " order by ProductId";

        // 执行查询，获取结果
        return await query.ToPageListAsync<Product>(pagingInfo);
    }
}

注意：

CPQuery和各种数据类型相加，这里不是简单的字符串拼接！
CPQuery实现了运算符重载，上面那些 + 会做特殊处理，将后面的数据转成标准SQL参数
string数据需要显式转换或者调用AsQueryParameter() ，之后才能与CPQuery相加
非string类型可以直接与CPQuery相加
IN查询支持的数据类型，与可以和CPQuery相加

嵌套查询

占位参数还有另一种用法是实现【嵌套查询】，这里的嵌套是指 CPQuery和XmlCommand之间的嵌套。

可参考以下示例：

/// <summary>
/// WHERE中包含占位参数。 CPQuery 嵌套 CPQuery
/// </summary>
/// <returns></returns>
public async Task<DataTable> DemoNestQuery()
{
    // 定义一个基础的SQL查询，它包含一个【固定SQL参数】，以及二个【占位符参数】
    // {orderFilter} , {productFilter} 就是二个【占位符参数】，它们的位置将被另一个CPQuery实例来替换
    string sql = @"
SELECT od.*, p.ProductName, p.Unit, p.UnitPrice, c.CustomerName, os.SumMoney, os.OrderDate
FROM   Orders as os inner join OrderDetails as od on os.orderId = od.orderId
inner join Customers as c on os.CustomerID = c.CustomerID
INNER JOIN  Products as p ON od.ProductID = p.ProductID
WHERE {orderFilter} and os.OrderDate > @OrderDate and {productFilter} ";

    // 获取CPQuery的调用参数，包括2个占位参数
    var args = GetArgs();
    
    // 执行最终的查询
    return await dbContext.CPQuery.Create(sql, args).ToDataTableAsync();
}

请认真查看示例代码中的注释。GetArgs()的代码如下：

private object GetArgs()
{
    // 以下参数变量应该从方法外部传入，为了简单，这里直接硬编码
    int oid = 1;
    decimal money = 500;
    string name = "%原装%";
    decimal price = 20;


    // 构造第一个【占位符参数】，也是根据一系列条件，动态生成
    CPQuery orderFilter = dbContext.CPQuery.Create("(1=1)");
    if( oid > 0 )
        orderFilter = orderFilter + " and os.OrderID > " + oid;

    if( money > 0 )
        orderFilter = orderFilter + " and os.SumMoney > " + money;

    // 构造第二个【占位符参数】，也是根据一系列条件，动态生成
    CPQuery productFilter = dbContext.CPQuery.Create("(2=2)");
    if( string.IsNullOrEmpty(name) == false )
        productFilter = productFilter + " and p.ProductName like " + name.AsQueryParameter();
    if( price > 0 )
        productFilter = productFilter + " and p.UnitPrice > " + price;

    return new {
        OrderDate = new DateTime(2010, 1, 30),
        orderFilter,    // 注意这个参数赋值，它用于替换 {orderFilter}
        productFilter   // 注意这个参数赋值，它用于替换 {productFilter}
    };
}

如果使用XmlCommand，C#代码会简洁一些：

/// <summary>
/// WHERE中包含占位参数。 XmlCommand 嵌套 CPQuery
/// </summary>
/// <returns></returns>
public async Task<DataTable> DemoNestQuery2()
{
    // 获取XmlCommand的调用参数，包括2个占位参数
    var args = GetArgs();

    // 与上例相同的结果，只是将SQL分离到 XmlCommand 中
    return await dbContext.XmlCommand.Create("demoNest", args).ToDataTableAsync();
}

Nebula 开发文档

高级查询

IN 查询

动态条件查询

嵌套查询