CODEWITHSUNDEEP
sqlt-sqldesign-patternsdatabase-design
ogun
ogun

Reputation: 1334

Design Pattern for Creating Sql Queries

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

Upvotes: 5

Views: 8254

Answers (3)

remi bourgarel
remi bourgarel

Reputation: 9389

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.

Upvotes: 1

Don Roby
Don Roby

Reputation: 41137

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.

Upvotes: 10

Diego
Diego

Reputation: 36146

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

   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

Upvotes: 0

Related Questions