Sep 29, 2014 at 2:53 PM
Edited Sep 29, 2014 at 3:32 PM
First, thank you for a great open source library for dynamic SQL generation. Great work!
I am using the library to generate some complex SQL statements on the fly within a Winforms app, and am trying to understand how to group statements together (using parenthesis) in the WHERE clause.
I have gone through the documentation page, but I'm not finding any in depth documentation anywhere. Most of what I've done so far has been trial and error. Is there a reference list of all of the methods of DynaSQL somewhere? I have tried looking at some of
the tests, but most of them seem to be relatively simple and what I'm doing is much more complex. What am I missing?
Let me post an example of what I am trying to do, to clarify.
I have several IF statements that are not mutually exclusive, and I want to group all of them together:
(Note that it is in VB.NET, but I am fluent in C# as well, so answers are welcome in either language)
Dim sel As DBSelectQuery
sel = DBQuery.Select()
'... add columns, etc ...
'... snip ...
'The 1=1 trick, because I couldn't find another way to guarantee the
'Initial WHERE statement was generated
sel.Where(DBConst.Int32(1), Compare.Equals, DBConst.Int32(1))
'*** I want to group all of the conditions below together in one statement,
' because these must be independent of the SQL that follows them.
If condition1 = True Then
sel.AndWhere("Field1", Compare.Equals, DBConst.String("Something"))
If condition2 = True Then
sel.AndWhere("Field2", Compare.Equals, DBConst.String("Something"))
If condition3 = True Then
sel.AndWhere("Field3", Compare.Equals, DBConst.String("Something"))
If condition4 = True Then
sel.AndWhere("Field4", Compare.Equals, DBConst.String("Something"))
'... Additional SQL statements ...
How do I make sure that all of the statements generated in condition1, 2, 3, and 4 remain grouped together?
Say that condition 1, 3 and 4 were true, I want the WHERE clause to look like:
Field1 = 'Something'
AND Field3 = 'Something'
AND Field4 = 'Something'
... additional SQL with unrelated constraints ...
I am simplifying what I'm doing with my example, but the point is to group certain statements together.