Querying SharePoint lists
SharePoint lists are the most common toolset we use in our everyday SharePoint project. You can save documents or contact information; you can create lists for some control settings or even use some data to use in workflow activities. In this post i will introduce some query methods and options to get information out of SharePoint list.
Preface
Before we start querying our first list you must understand SharePoint list structure. Every list in SharePoint contains some built-in fields what are created out-of-box and other fields you create your own purpose. Every field in this list has its own data type and may have been associated with special SharePoint field class (e.g SPFieldLookup, SPFieldUser), which you can use to get values or specify field’s metadata. Every item returned from the query is in object type and must be casted into right field type before usage. Every query you make to the list returns SPListItemCollection object where you can use indexer to get specific SPListItem or you can iterate the item collection using foreach.
Every field in a item’s field collection contains two type of naming to programmatically use. There is field’s internal name – this name will not be changed after creation, and is the best way to query specific field; and display name or title – what you can change in the UI. Display name is not the best way to query fields in code, because when somebody changes it you will get an exception which is not very useful to debug and may take a lot of your time to find this bug. When you create a field in the UI, the new field will have the same name for the internal name and display name, when you modify a field in UI you only change a display name. To programmatically access this field you must use the field name you assigned first time.
Query options
To begin with querying list you have two options to get results out of list. You can use SPList object methods and properties to ask items from the collection or you can use CAML (Collaborative Application Markup Language) to specify fields and filters.
NB! You should not use SPList.Items property with the large list, because it will return all the items in the list with all fields. Query results are not cached and it will be a performance hit to your server because every time you query some items, SharePoint will create a new collection for your items.
So to start with our first example I will show to you how you can get the first item’s field value from the Items collection using field’s display name “MyField”.
SPSite site = SPContext.Current.Site;
SPWeb web = null;
try {
web = site.OpenWeb();
if (web == null)
//do something
return;
SPList list = web.Lists["MyList"];
SPListItemCollection items = list.Items;
if (items.Count != 0)
{
Response.Write(items[0]["MyField"]);
}
} catch (SPException ex)
{
//to somethingwith this exception
}
finally
{
if (web != null)
web.Dispose();
}
To use internal name in this example we will create SPField object which will allows us to use all the field metadata. We will use SPField Id property to get item field value.
SPField field = items[0].Fields.GetFieldByInternalName("MyField");
Response.Write(items[field.Id].ToString());
To get the best performance out of querying lists you should use CAML queries. It will allow you to choose fields you want to show, result items count and filters.
Basic CAML query syntax is:
<Where>
<And>
<Eq>
<FieldRef Name="MyField"/>
<Value Type="Text">MyValue</Value>
</Eq>
</And>
<Or>
<Lt>
<FieldRef Name="MyField2" />
<Value Type="Number">3</Value>
</Lt>
</Or>
</Where>
<OrderBy>
<FieldRef Name="MyField"/>
</OrderBy>
<GroupBy>
<FieldRef Name="MyField"/>
</GroupBy>
To use CAML in our queries we have to create SPQuery object. SPQuery object allows us to specify other parameters of query such as: ViewFields, RowLimit.
In our next example I will create CAML query to get list items where “Status” field value is “Archived”
SPSite site = SPContext.Current.Site;
SPWeb web = null;
try {
web = site.OpenWeb();
if (web == null)
//do something
return;
SPList list = web.Lists["MyList"];
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name=’Status’/><Value Type=’Text’>Archived</Value></Eq></Where>";
SPListItemCollection items = list.GetItems(query);
if (items.Count != 0)
{
//Some code to show result
}
} catch (SPException ex)
{
//to somethingwith this exception
}
finally
{
if (web != null)
web.Dispose();
}
To specify how many items to return you should add line:
query.RowLimit = 5;
To show only fields “Title” and “Status” you should add line:
query.ViewFields = "<FieldRef Name=’Title’/><FieldRef Name=’Status’/>";
Exceptions
You must choose a the type of query always carefully, because if there is some mistake in field’s display name or internal name SharePoint gives you the most generic message e.g “Value does not fall within the expected range” or “One or more field types are not installed properly. Go to the list settings page to delete these field” To avoid these exceptions you should always use internal name because it can’t be changed after creation.
To avoid field name mistakes you should always create list instance based on list template or list definition. It avoids mistakes that people can do when installing your solution and allows you to specify internal name you like.
In Conclusion
Lists are important tool in our everyday programming and it is important to know how to query items from lists. Using Items collection will help you quickly to get all the items in the list, but it will be a performance hit when you use it on a large list. To specify how many items to show, which fields to use, you should use CAML queries.
References
Related posts: