Avoid enumerating SPListItemCollection object

  • Article

Enumerating a SPListItemCollection object should be avoided for data querying purposes.

for e.g. in the following code is not optimal,

using(SPSite site = new SPSite(siteURL))
{
   using(SPWeb web = site.OpenWeb())
   {
      SPList tasksList = web.Lists["Tasks"];
      foreach(SPListItem item in tasksList.Items)
{
// Code
}
   }
}

This is because each step will invoke a separate call to the DB. Instead the foreach loop should be used on a DataTable object which can derived as mentioned below. This will reduce multiple calls to the content DB with just one.

DataTable dt = tasksList.Items.GetDataTable();
foreach(DataRow dr in dt.Rows)
{
   //code
}

Comments

  • Anonymous
    March 31, 2008
    Nice! Never thought of using this method.. maybe even faster than a CAML query :)

  • Anonymous
    April 01, 2008
    There is a fairly authoratative whitepaper about dealing with large lists in SharePoint.  For/each is of course absolutely the worst method, but just using a DataTable isn't a ton better for the most part. http://office.microsoft.com/download/afile.aspx?AssetID=AM102377231033

  • Anonymous
    April 01, 2008
    Not sure if I get this right, but getting the DT will result in the entire list sent over the wire and into memory. Thus, if you know that you need to get all items and/or have a limited number of items then fine, but if you only need to examine a certain number of items or have a massive list or limited memory, this seems to be a sub-optimal solution. Also, in non-web solutions, responsiveness may be an issue, ie if you want to output intermediate results as they are read, you need to wait for the entire datatable to load before you can output anything. Or am I missing something?