Do you build criteria by using a where clause?
  v2.0 Posted at 17/11/2016 4:14 AM by Tiago Araujo

It is very common to come up with ways to filter data. 
As an example, you could do it like this.


Figure: Filtering Data

This allows you to easily extract fields and values, but it only works for the fields you hard code. You could get around it by writing complex code to build a SQL query or ignore the ones that don't match.

But this gives exact matches. E.g.:


What if you want to give the ability to allow the user to be able to use a like e.g.

ClientID like '%ssw%'

Well then I could add something like


But why do this when a WHERE clause in SQL can do all this 


Figure: Similar matches

Try this - ClientSearch.aspx?Where=Client.ClientID%20like%20'%ssw%'

The Pros for do this are:

  • Quicker development time.
  • SQL is very powerful - say I want to JOIN another table in the WHERE, I could use an IN statement and do a sub query - no extra code by the developer.
  • Matches HTML syntax (named value pair) and as a developer you can get it easy. e.g.
  • Request.QueryString("Where")


  • It shows the database schema to the users - users maybe should not see the structure of the database.
  • Security - the where clause could show data we don't want users to see.
  • Got to add a little extra code to avoid SQL injection.

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address: