如何使用Dapper.SqlBuilder和OrWhere建立动态SQL查询 [英] How do I build a dynamic sql query with Dapper.SqlBuilder and OrWhere

问题描述

我正在尝试为多个搜索词构建动态Sql查询。我通常了解如何使用构建器，但是不确定在循环中要做什么，因为我实际上每次都需要@term不同（我认为）。不仅在查询中，而且在匿名类型中也要匹配。

I am attempting to build a dynamic Sql query for multiple search terms. I understand in general how to use the builder, but am not sure what to do in the loop since I actually need the @term to be different each time (I think). Not just in the query, but in the anonymous type as well to match.

我可以在查询字符串中使用string.Format，但是不确定如何在匿名类型中进行匹配吗？

I could use a string.Format in the query string, but not sure how to match it in the anonymous type?

public async Task<List<Thing>> Search(params string[] searchTerms)
{
    var builder = new SqlBuilder();
    var template = builder.AddTemplate("SELECT * /**select**/ from ThingTags /**where**/ ");

    for (int i = 0; i < searchTerms.Length; i++)
    {
        builder.OrWhere("value LIKE @term", new { term = "%" + searchTerms[i] + "%" });
    }
...
}

当前形式为术语 abc， def， ghi创建的查询为

in the current form the query that gets created for terms "abc" "def" "ghi" is

CommandType: Text, CommandText: SELECT *  from ThingTags WHERE  ( value LIKE @term OR value LIKE @term OR value LIKE @term ) 

Parameters:
Name: term, Value: %ghi%

推荐答案

这是进行查询构建的一种方法。我没有意识到这些参数最初可能是字典。

Well here is one way to do the query building. I didn't realize that the parameters could be a Dictionary initially.

public async Task<List<Thing>> Search(params string[] searchTerms)
{
var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT * /**select**/ from ThingTags /**where**/ ");

    for (int i = 0; i < searchTerms.Length; i++)
    {
        var args = new Dictionary<string, object>();
        var termId = string.Format("term{0}", i.ToString());
        args.Add(termId, "%" + searchTerms[i] + "%");
        builder.OrWhere("value LIKE @" + termId, args);
    }
...
}

这篇关于如何使用Dapper.SqlBuilder和OrWhere建立动态SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！