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

"Design pattern"? You keep using this term. I do not think it means what you think it means. : D Sorry, had to. — Nick Vaccaro
– Nick Vaccaro, Commented Feb 7, 2012 at 13:43
I was joking. Within the object-oriented world, the term "design pattern" has evolved into something that fits, somewhat ironically, into a subset of coding archetypes. The big patterns don't really cover database interaction, which is why I said that. — Nick Vaccaro
– Nick Vaccaro, Commented Feb 7, 2012 at 14:23
Design patterns are not limited to object-oriented world. Any domain can have it. — Nazar Merza
– Nazar Merza, Commented Nov 30, 2016 at 18:33

Don Roby · Accepted Answer · 2012-02-07 23:22:43Z

11

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.

answered Feb 7, 2012 at 23:22

Don Roby

41.2k6 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ogun Over a year ago

thanks... and i found it with your help: codeproject.com/Articles/13419/…

Mladen B. Over a year ago

Here is one such implementation: github.com/mladenb/sql-query-builder

Community · Accepted Answer · 2017-05-23 12:08:44Z

1

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.

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Feb 8, 2012 at 9:57

remi bourgarel

9,4494 gold badges46 silver badges76 bronze badges

1 Comment

nirmesh khandelwal Over a year ago

Small , but really useful trick. Thanks for pointing that out.

Diego · Accepted Answer · 2012-02-07 13:41:41Z

0

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

answered Feb 7, 2012 at 13:41

Diego

36.5k21 gold badges94 silver badges138 bronze badges

1 Comment

ogun Over a year ago

thanks for your answer but i think it is confusing too and hard to implement... it should be easier than i think :)

Collectives™ on Stack Overflow

Design Pattern for Creating Sql Queries

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related