Recently I used a really useful library that was posted on codeproject called SelectQueryBuilder I found it a really useful library that helped to build up SQL queries without having to resort to nasty string concatenation, this post should not be seen as me putting down the SelectQueryBuilder in anyway I think it’s a great library and fulfills everything most people will need, the purpose of this post is to show how we can utilise design patterns to refactor a real world application for further changing requirements.
Current Where Clause Usage
WhereClause whereClause1 = new WhereClause(); whereClause1.ComparisonOperator = Comparison.Equals; whereClause1.FieldName = "col1"; whereClause1.Value = "test"; queryBuilder.AddWhere(whereClause1, 1);
Notice the “1” argument this is used to specify the level that the clause should appear at, for example, if we added another where clause at the same level then these clauses would be AND’ed together however if we added another clause at level 2 then they would be OR’ed together:
WHERE ((col1 = 'test') AND (col2 = 'test2')) -- same level WHERE (col1 = 'test') OR (col2 = 'test2') -- different level
This poses a few of issues the first being that the client has now got to be control of a local level variable and also the builder needs to have protection in place to make sure that a bad index cannot be used.
The second being related to generating SQL how you would like to be written, say I want to have a WHERE statement like this:
WHERE (col1 = 'test') AND ((col1 = 'test2') OR (col2 = 'test3'))
We can’t get the SQL to produce the above because as-is clauses in different levels are OR’ed and same levels are AND’ed together which is the opposite of the above, now we can get the builder to produce a query that will provide the same logic:
WHERE ((col1 = 'test2') AND (col1 = 'test')) OR ((col2 = 'test3') AND (col1 = 'test'))
However we have now had to repeat the AND clauses for both OR clauses and it is not a readable as the SQL we were after.The third issue is extensibility, the where clause uses the following attributes:
- Comparison Operator
- Field Name
This is somewhat restrictive one of the questions posted on the codeproject page is about having the builder support functions:
WHERE DATEDIFF(dd,col1,'20080101') >= 0
Given the current Where clause there’s no way we could add this in.
Next I want to get into how we can architect the syntax tree that will resolve the issues above, and reduce the code in the builder for building the WHERE clauses to one line 🙂