| Feature |
WhereStatement class |
The WhereStatement now allows clauses to be added to level 0 of a query, which
makes it possible to add a clause which will be AND'ed to all
the clauses on other levels (which are all OR'ed together). This allows
you to create more powerful and flexible queries, easier. The default level is
still 1, so existing code will still work.
Basically, this feature is something I've always missed in the querybuilder of
SQL Server Enterprise Manager. It's simply the 1 column which would define the
criteria that must be met for all criteria mentioned in the other columns.
And this means: No more adding those recurring criteria to every single
'Or..' column!
Let me give you an example. Previously, if you wanted to create a query that
would check if records in the Orders table had CustomerID=123 and had placed
their order before '2000/01/01' or after '2006/01/01', you would have to write
the following code:
query.AddWhere( Customer.TableColumns.CustomerID,
Comparison.Equals,
123); // Add to default level 1
query.AddWhere( Customer.TableColumns.OrderDate,
Comparison.LessThan,
new DateTime(2000,1,1)); // Add to default level 1
query.AddWhere( Customer.TableColumns.CustomerID,
Comparison.Equals,
123,
2); // Manually add to level 2
query.AddWhere( Customer.TableColumns.OrderDate,
Comparison.GreaterThan,
new DateTime(2006,1,1),
2); // Manually add to level 2
Calling query.BuildQuery() would result in this query: .. WHERE
(CustomerID=123 AND OrderDate<'2000/01/01') OR (CustomerID=123 AND
OrderDate>'2006/01/01') Now, you can simply add the CustomerID
check to level 0 as follows:
query.AddWhere( Customer.TableColumns.CustomerID,
Comparison.Equals,
123,
0); // Add to level 0
query.AddWhere( Customer.TableColumns.OrderDate,
Comparison.LessThan,
new DateTime(2000,1,1)); // Add to default level 1
query.AddWhere( Customer.TableColumns.OrderDate,
Comparison.GreaterThan,
new DateTime(2006,1,1)); // Add to default level 1
Calling query.BuildQuery() would now result in this query: ..
WHERE (CustomerID=123) AND (OrderDate<'2000/01/01' OR
OrderDate>'2006/01/01'). As you can see, a real improvement for
easy query building! See my article on the QueryBuilder at CodeProject (http://www.codeproject.com/cs/database/SelectQueryBuilder.asp)
for more information about using levels. Please note that at the time the
CodeProject article was written, this feature wasn't implemented yet.
|
