设计用于创建Sql查询的模式

Nearly every project have search panel and custom filters in my company. It is hard to create when project has too much filter.

我公司几乎每个项目都有搜索面板和自定义过滤器。当项目有太多的过滤器时，很难创建。

Is there any good design pattern for creating custom sql queries for using with filters?

是否有好的设计模式来创建自定义sql查询，以便与过滤器一起使用?

I always write something like this:

我总是这样写:

commandText = "SELECT * FROM XXX "

innerJoinCommand = ""
whereCommand = ""

if (??.length > 0)
  whereCommand += "AND ??? "

if (??.Count > 0)
  innerJoinCommand += "??? "

//...

if (innerJoinCommand.length > 0)
  commandText += innerJoinCommand

if (whereCommand.length > 0)
  commandText += "WHERE " + whereCommand

3 个解决方案

#1

This sort of thing is frequently done by using the Builder Pattern.

这种事情通常是通过使用Builder模式来完成的。

If you want to support very complex queries, it might be a rather complex builder, and other patterns might come into play, but this is where I would start.

如果您希望支持非常复杂的查询，那么它可能是一个相当复杂的构建器，其他模式可能会发挥作用，但我将从这里开始。

#2

I used the following design :

我采用了以下设计:

Is it a oop good design?

这是一个好的设计吗?

The little trick is to put a "WHERE 1 = 1" so you don't have to handle if ti's a AND or a WHERE you have to add.

这里有个小技巧，就是把"WHERE 1 = 1"这样你就不需要处理ti是a还是a了。

#3

this is how I do: (srp is an object that contains all the possible parameters)

我是这样做的:(srp是一个包含所有可能参数的对象)

   string query = "select * from TABLE";

        if (srp != null)
        {
            query += " Where ";
            bool firstParameter = true;

            if (srp.ID!= 0)
            {
                if (!firstParameter)
                {
                    query += " and ";
                }
                query += " ID= " + srp.ID.ToString();
                firstParameter = false;
            }
        }

then you can replicate the inner if as much as you need.

然后你可以复制内部，如果你需要的话。

Of course, this will only work with AND parameters, still didn't have to create a more complex logic to use or

当然，这只适用于和参数，仍然不需要创建更复杂的逻辑来使用or

#1