Design Pattern for Creating Sql Queries

Question

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?

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

Answer 1

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

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.

Answer 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.