Several people have asked for a sample query that is more advanced in nature, here is such an example.
- Code: Select all
public void ThePowerQuery()
{
// Before we start, the default conjuction for all dynamic queries is \"AND\",
// You can override this when you call Load(Where) or GenerateSQL().
// The query we are going to generate doesn' really make sense, it's the
// AND's, OR's, and grouping that is important here.
//=========================================================================
// SELECT * FROM [Employees]
// WHERE [LastName] LIKE 'A%' AND [LastName] IS NOT NULL
// OR ( [City] LIKE 'Ind%' OR [HireDate] BETWEEN '1/1/95' AND '4/4/04' )
//=========================================================================
//=========================================================================
// SELECT * FROM [Employees]
//=========================================================================
// The default is all fields or *, however, this can be overridden by
// using emps.Query.AddResultColumn()
Employees emps = new Employees();
//=========================================================================
// WHERE
//=========================================================================
// As soon as you access a WhereParameter via the traditional way
// or via a TearOff you've initiated the creation of a WHERE clause
//=========================================================================
// [LastName] LIKE 'A%' AND [LastName] IS NOT NULL
//=========================================================================
emps.Where.LastName.Value = \"A%\";
emps.Where.LastName.Operator = WhereParameter.Operand.Like;
// Here we need to use LastName again, but we already used are intrinsic
// WhereParameter, let's create a tear off, notice how we access it a
// little differently, each time you access a TeafOff it is created and
// add to the WhereParameter collection
WhereParameter wp = emps.Where.TearOff.LastName;
wp.Operator = WhereParameter.Operand.IsNotNull;
//=========================================================================
// OR (
//=========================================================================
emps.Query.AddConjunction(WhereParameter.Conj.Or);
emps.Query.OpenParenthesis();
//=========================================================================
// [City] LIKE 'Ind%'
//=========================================================================
emps.Where.City.Conjuction = WhereParameter.Conj.And;
emps.Where.City.Value = \"Ind%\";
emps.Where.City.Operator = WhereParameter.Operand.Like;
//=========================================================================
// OR [HireDate] BETWEEN '1/1/95' AND '4/4/04'
//=========================================================================
// Notice how we override the Query level default conjuction here, we set
// the HireData to \"Or\", when you override the conjuction the it precedes
// the WhereParameter it is attached to.
emps.Where.HireDate.Conjuction = WhereParameter.Conj.Or;
emps.Where.HireDate.Operator = WhereParameter.Operand.Between;
emps.Where.HireDate.BetweenBeginValue = \"1/1/95\";
emps.Where.HireDate.BetweenEndValue = \"4/4/04\";
//=========================================================================
// )
//=========================================================================
emps.Query.CloseParenthesis();
// Let's look at the SQL without executing it
string query = emps.Query.GenerateSQL();
// We could have executed it this way
// emps.Query.Load();
//=========================================================================
// In reality, your query will look like this as all data is passed in via
// Paramters
//=========================================================================
// SELECT * FROM [Employees]
// WHERE [LastName] LIKE @LastName1 AND [LastName] IS NOT NULL
// OR ( [City] LIKE @City3 OR [HireDate] BETWEEN @HireDate4 AND @HireDate5 )
//=========================================================================
}
