There are various ways of accessing the List Items (SPListItem) in a List (SPList).
Accessing list items in the same site
SPList.Items foreach
This SPListItem Collection works directly against the underlying SharePoint List (SPList). Any updates made against List Items are updated on the server.
The below code shows enumerating through all List Items in a List.
using (SPWeb web = siteCollection.AllWebs["webname"])
{
SPListItemCollection items = web.Lists["Document Library"].Items;
foreach (SPListItem item in items)
{
Console.Write(item.Title);
}
}
Iterating on Collection Properties of objects with for loop
Please note that when iterating through a list, to reduce underlying calls to the database, be very careful what property you iterate on. Make sure in this example you do not use:
for(int i; i <= web.Lists["Document Library"].Items.Count; i++)
{
Console.WriteLine(web.Lists["Document Library"].Items[i].Title.ToString());
}
This will cause a new SPListItemCollection object everytime the property is accessed. An example of the performance hit on this would be that if the list had 100 items, you would get 200 hits to the database. By instantiating an SPListItemCollection object outside of the foreach loop reduces this significantly.
SPListItemCollection items = web.Lists["Document Library"].Items;
for(int i; i <= items.Count; i++)
{
Console.WriteLine(items[i].Title.ToString());
}
Source: The wrong way to iterate through SharePoint SPList Items by Andreas Grabner
Accessing List Item instances
The below code shows accessing a List Item by its identifier (Int).
using (SPWeb web = siteCollection.AllWebs["webname"])
{
SPList list = web.Lists["Document Library"];
SPListItem item = list.GetItemById(id);
Console.Write(item.Title);
}
Performance mini Case Study
Gopinath Devadasshad an issue with this code pattern using GetItemById and noticed a 30 second to 3 second difference in performance compared to using SPQuery (see below).
The below code shows accessing a List Item by it's unique identifier (Guid).
using (SPWeb web = siteCollection.AllWebs["webname"])
{
SPList list = web.Lists["Document Library"];
SPListItem item = list.GetItemByUniqueId(guid);
Console.Write(item.Title);
}
MSDN Guidance
Please note that it is advised not to use SPList.Items[System.Guid] and SPList.Items[System.Int32] as mentioned in 'Table 1. Alternatives to SPList.Items on the source referenced below'.
Source: MSDN:Best Practices: Common Coding Issues When Using the SharePoint Object Model
SPList.Items foreach with SPQuery
SPList list = SPContext.Current.Web.Lists["Contracts"];
SPQuery query = new SPQuery();
query.Query = string.Format("<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>{0}</Value></Eq></Where>","407");
SPListItem item = list.GetItems(query)[0];
SPList.Items.GetDataTable()
SPListItemCollection.GetDataTable Method (Microsoft.SharePoint)
Returns a copy of the list items as an ADO.NET DataTable, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
using (SPWeb web = siteCollection.AllWebs["webname"])
{
SPList list = web.Lists["Document Library"];
DataTable table = list.GetItems(list.DefaultView).GetDataTable();
//TODO: enumerate DataTable
}
SPList.Items.GetDataTable() with SPQuery
Returns a copy of the list items as an ADO.NET DataTable, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
PortalSiteMapProvider.GetCachedListItemsByQuery
Requires Microsoft Office SharePoint Server (not WSS) using the Publishing feature. Returns a copy of the list items, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
PortalSiteMapProvider psmp = PortalSiteMapProvider.CurrentNavSiteMapProviderNoEncode;
SPQuery query = new SPQuery
{
ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />",
Query = "", // replace with your CAML query
RowLimit = 10
};
SPListItemCollection listItemNodes = _portalSiteMapProvider.GetCachedListItemsByQuery(
((PortalSiteMapNode)psmp.CurrentNode).WebNode,
"List Title", query, web);
SPWeb.GetListItem(string url)
The SPWeb.GetListItem method allows you to retrieve a list item using its URL:
SPListItem listItem = SPContext.Current.Web.GetListItem(listItemUrl);
Such approach for retrieving list items is extremely useful while working with Publishing Pages with elevated privileges:
SPSecurity.RunWithElevatedPrivileges(delegate() {
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
string listItemUrl = SPContext.Current.ListItemServerRelativeUrl;
SPListItem listItem = web.GetListItem(listItemUrl);
// do something with the list item
}
}
});
Keep in mind
In spite of being called in the context of a site (web.GetListItem) the GetListItem(string) method requires a server-relative URL in order to retrieve a list item. Providing a site-relative URL will result in a COMException (more information available @ http://blog.mastykarz.nl/inconvenient-spweb-getlistitem-exception-hresult-0x80070001/
List Web Service
See Lists SharePoint Web Service
Accessing list items in multiple sites
Waldek Mastykarz: Performance of content aggregation queries on multiple lists
SPSiteDataQuery
Returns a copy of the list items, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
Source: Chakkaradeep Chandran
StringBuilder queryBuilder = new StringBuilder();
SPSiteDataQuery oQuery = new SPSiteDataQuery();
oQuery.Lists = "<Lists BaseType='1'/>";
oQuery.RowLimit = 100;
oQuery.Webs = "<Webs Scope=\"Recursive\" />";
queryBuilder.Append("<Where><Eq><FieldRef Name=\"UniqueId\" />");
queryBuilder.Append("<Value Type=\"Lookup\">");
queryBuilder.Append("e5d483ac-1c4a-4699-bcd1-dd0bb0455a71");
queryBuilder.Append("</Value></Eq></Where>");
oQuery.Query = queryBuilder.ToString();
DataTable dtResult = web.GetSiteData(oQuery);
More on SPSiteDataQuery
SPSiteDataQuery Samples for WSS v3.0
CrossListQueryCache
Uses the same caching functionality as the Content Query Web Part and similarly requires Microsoft Office SharePoint Server 2007 Standard minimum. Returns a copy of the list items, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
CrossListQueryInfo clqInfo = new CrossListQueryInfo();
clqInfo.Webs = "<Webs Scope='SiteCollection'/>";
clqInfo.Lists = "<Lists ServerTemplate='101'/>";
clqInfo.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />";
clqInfo.Query = ""; // replace with your CAML query
clqInfo.RowLimit = 10;
clqInfo.UseCache = true;
CrossListQueryCache clqCache = new CrossListQueryCache(clqInfo);
DataTable resultsTable = clqCache.GetSiteData(SPContext.Current.Site);
Query context
David Crabbe explains that only the current site collection can be queried using CrossListQueryCache.
Caching
Jeff Dalton has found that only GetSiteData methods using SPSite parameters are cached (not SPWeb).
MSDN references
CrossListQueryInfo
CrossListQueryCache
Search
Returns a copy of the list items, any updates to the DataTable do not affect the underlying SharePoint List (SPList).
External Links
White Paper: Working with large lists in Office SharePoint® Server 2007 - Extremely good white paper showing the differences in performance of various methods of accessing lists with graphs to prove it!
Measure 48,000 times; Cut once by Scott Singleton Great post on performance based on method used to access List Items.
Original Post by: Jeremy Thake
