Every kind of Application or System no matter whether they are simple or complex, they needs their data to be stored in someplace. It can be either a Database, a XML file or simply a text file. And all those applications uses CRUD (Create,Read,Update,Delete) operations to maintain their data. So in SharePoint, one of the major way of Reading or Selecting data is using SPQuery Class in Microsoft.SharePoint Namespace.
Today I am going write about this SPQuery Class. The SPQuery Class is used to represent a query in the list view. Assuming you all know about SQL Select queries, to give you some basic idea on what SPQuery do is, It does what Select query in SQL does but syntaxes are totally different. Since I am hoping to write some examples using SPQuery, I think as we go along with this post you will definetely be able to identify the differences.
Then let's start. I will be using a custom list called Customer List. It contains following columns.
- Title - string
- Name - string
- Field1 - Number
- Field2 - Number
First of all, I will write a full simple example. Follwing code will retrieve all the items in the "Customer List" in which Name field equals to "Jaliya Udagedara".
using (SPSite oSPSite = new SPSite("http://ravanaserver/"))
{
using (SPWeb oSPWeb = oSPSite.OpenWeb())
{
oSPWeb.AllowUnsafeUpdates = true;
SPList oSPList = oSPWeb.Lists["Customer List"];
SPQuery oSPQuery = new SPQuery();
// your query writes here
oSPQuery.Query = "<Where><Eq><FieldRef Name='Name'/><Value Type='Text'>Jaliya Udagedara</Value></Eq></Where>";
SPListItemCollection oSPListItemCollection = oSPList.GetItems(oSPQuery);
foreach (SPListItem item in oSPListItemCollection)
{
// your code
}
}
}
Now I will write example for common scenarios. When modifying, modify only the part where I have put comment "// your query writes here".
- Retrieve all Items.
- For this you don't have to write any query. Just modify the above code as follows. This will return the all Items.
SPQuery oSPQuery = new SPQuery();
SPListItemCollection oSPListItemCollection = oSPList.GetItems(oSPQuery);
- Retrieve items that a field matches a given value.
oSPQuery.Query = "<Where><Eq><FieldRef Name='Name'/><Value Type='Text'>Jaliya Udagedara</Value></Eq></Where>";
- AND Operator - Retrieve items that matches the following condition,
(Field1 >= 500) AND (Field2 <= 1000)
oSPQuery.Query = "<Where><And><Geq><FieldRef Name='Field1'/><Value Type='Number'>500</Value></Geq><Leq><FieldRef Name='Field2'/><Value Type='Number'>1000</Value></Leq></And></Where>";
- OR Operator - Retrieve items that matches the following condition,
(Field1 >= 500) OR (Field2 <= 1000)
oSPQuery.Query = "<Where><Or><Geq><FieldRef Name='Field1'/><Value Type='Number'>500</Value></Geq><Leq><FieldRef Name='Field2'/><Value Type='Number'>1000</Value></Leq></Or></Where>";
- BeginsWith, AND, Contains
oSPQuery.Query = "<Where><And><BeginsWith><FieldRef Name='Name'/><Value Type="Text">Jaliya</Value></BeginsWith><Contains><FieldRef Name="Name" /><Value Type="Text">Udagedara</Value></Contains></And></Where>";
- IsNull - Retrieve all items Where Name field is empty,
oSPQuery.Query = "<Where><IsNull><FieldRef Name='Name'></FieldRef></IsNull></Where>";
I think I wrote some examples for most common scenarios. Here is a table of Comparison Operators used in SPQuery Class.
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
|
Things to Note
But when writing these queries there is some thing that you all should note about. That is, if your field name contains any spaces for example like "First Name", in your FieldRef Name if you put as 'First Name', you will get a error like "No Matching Field". The reason behind this is when you are leaving spaces in field names, the actual field name changes. You can get the correct field name by opening the List in InfoPath designer and referring the name in that field. For 'First Name', you might get something like 'First_x0020_Name'.
Feel free to give me your feedback.
Happy Coding.
Regards,
Jaliya
