How to Implement SPQuery?
using (SPWeb web = SPContext.Current.Site.RootWeb)
{ SPList mylist = web.Lists["Tasks"];
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name='Status'/>" +
"<Value Type='Text'>Completed</Value></Eq></Where>";
SPListItemCollection items = mylist.GetItems(query);
foreach (SPListItem item in items)
{ Response.Write(SPEncode.HtmlEncode(item["Title"].ToString()) + "<BR>");
}
}
Operators:
Comparison Operators, Logical Joins and Order/ Group Operators, plays an important role to make this syntax.
Comparison Operators
|
Comparison Operators |
General Meaning |
|
Eq |
= |
|
Gt |
> |
|
Lt |
< |
|
Geq |
>= |
|
Leq |
<= |
|
Neq |
<> |
|
Contains |
Like |
|
IsNull |
Null |
|
IsNotNull |
NotNull |
|
BeginsWith |
Beginning with word |
|
DateRangesOverlap |
compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap |
Now, to use Or between Geq and Leq conditions, we put it inside <Or> tag.
I.e. condition: where (Field1 >= 1500) or (field2 <= 500) can be written like this:
<Where>
<Or>
<Geq>
<FieldRef Name='Field1'/>
<Value Type='Number'>1500</Value>
</Geq>
<Leq>
<FieldRef Name='Field2'/><Value Type='Number'>500</Value>
</Leq>
</Or>
</Where>
The full Code snippets will look like:
<View>
<Query>
<OrderBy>
<FieldRef Name='ID'/>
</OrderBy>
<Where>
<Or>
<Geq>
<FieldRef Name='Field1'/>
<Value Type='Number'>1500</Value>
</Geq>
<Leq>
<FieldRef Name='Field2'/><Value Type='Number'>500</Value>
</Leq>
</Or>
</Where>
</Query>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='Name'/>
</ViewFields>
<RowLimit>10</RowLimit>
</View>
Example: Contains, And, BeginsWith
The following example uses the Contains element that is assigned to the Query property to return the titles of items where the Conference column value begins with "Morning" and contains "discussion session".
<Where>
<And>
<BeginsWith>
<FieldRef Name="Conference"/>
<Value Type="Note">Morning</Value>
</BeginsWith>
<Contains>
<FieldRef Name="Conference" />
<Value Type="Note">discussion session</Value>
</Contains>
</And>
</Where>
Example: DateRangesOverlap
<Where>
<DateRangesOverlap>
<FieldRef Name="EventDate"></FieldRef>
<FieldRef Name="EndDate"></FieldRef>
<FieldRef Name="RecurrenceID"></FieldRef>
<Value Type="DateTime">
<Now/>
</Value>
</DateRangesOverlap>
</Where>
Logical Joins:
|
Logical Joins |
Comments |
|
And |
Used within the ‘Where’ element to group filters in a query for a view |
|
Or |
Used within the ‘Where’ element to group filters in a query for a view |
Example: And
<Where>
<And>
<Neq>
<FieldRef Name="Status"></FieldRef>
<Value Type="Text">Completed</Value>
</Neq>
<IsNull>
<FieldRef Name="Sent"></FieldRef>
</IsNull>
</And>
</Where>
Order/Group Operators:
|
Order/Group Operators |
Comments |
|
OrderBy |
Determines the sort order for a query. The OrderBy element contains a group of FieldRef elements
I.e. <OrderBy><FieldRef Name="Title" Ascending="TRUE">
</FieldRef></OrderBy>
|
|
GroupBy |
Contains a Group By section for grouping the data returned through a query in a list view |
Example: OrderBy
<OrderBy>
<FieldRef Name="Modified" Ascending="FALSE"></FieldRef>
</OrderBy>
<Where>
<Or>
<Neq>
<FieldRef Name="Status"></FieldRef>
<Value Type="Text">Completed</Value>
</Neq>
<IsNull>
<FieldRef Name="Status"></FieldRef>
</IsNull>
</Or>
</Where>
Example: GroupBy
<GroupBy>
<FieldRef Name="Modified"/>
</GroupBy>
<Where>
<Or>
<Neq>
<FieldRef Name="Status"></FieldRef>
<Value Type="Text">Completed</Value>
</Neq>
<IsNull>
<FieldRef Name="Status"></FieldRef>
</IsNull>
</Or>
</Where>
Important:
This is very important thing to keep in mind, because SPQuery will be assigned as a string so it wont show compile time error though it may have tag syntax error, not in order, escape sequence error, etc.
Escape sequence:
The ScriptEncode method escapes characters that would otherwise conflict with script.
|
Original |
Replacement |
|
" |
\" |
|
\ |
\\ |
|
+ |
\u002b |
|
> |
\u003e |
|
< |
\u003c |
|
' |
\u0027 |