locked
Fetch list items in iterations RRS feed

  • Question

  • I have a list with 15 000 items in it. In my code, I need to do some work over ALL items, but I need to do it in iterations, let's say 100 items at the time, because I don't want to create memory problems (it is a large data). What is the best way to do this?

    When I fetch first 100 items, how do I know which item was last and which is following in the next iteration (items are dispersed in many folders)?

     SPList tList = SPWeb.Lists[ListName];

     SPQuery _query = new SPQuery();
     _query.Query = "<Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";
     _query.ViewAttributes = "Scope=\"Recursive\"";

    This code gets all items. I can add

    _query.RowLimit = 100;

    But, how can I then get all 15 000 items?

    Friday, April 12, 2013 2:49 PM

Answers

  • Microsoft gives you simple solution on this page:

    http://msdn.microsoft.com/en-us/library/bb687949.aspx

    This is what you need (just change RowLimit to 100):

    SPWeb oWebsite = SPContext.Current.Web;
    SPList oList = oWebsite.Lists["Announcements"];
    
    SPQuery oQuery = new SPQuery();
    oQuery.RowLimit = 10;
    int intIndex = 1;
    
    do
    {
        Response.Write("<BR>Page: " + intIndex + "<BR>");
        SPListItemCollection collListItems = oList.GetItems(oQuery);
    
        foreach (SPListItem oListItem in collListItems)
        {
            Response.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) +"<BR>");
        }
    
        oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition;
        intIndex++;
    } while (oQuery.ListItemCollectionPosition != null);

    


    • Edited by Mario Zagreb Monday, April 15, 2013 9:12 AM
    • Marked as answer by SPManiac Monday, April 15, 2013 10:09 PM
    Monday, April 15, 2013 9:11 AM

All replies

  • I'd probably do it like so (psuedo code)

    do a while loop, set a variable with the ID of the current list item, and perform a check if any list items are returned from the query, something like:

    lastHighestID=0

    while (!TheEnd)

    your query above, but add a where clause for ID > lastHighestID

    While you are iterating through your 100 items, set lastHighestID=the ID of the current Item

    if you get 0 list items back (ie none of them are greater ID than the last one) then set theEnd=true

    Friday, April 12, 2013 4:34 PM
  • Thn you for your answer, I will try it.
    Friday, April 12, 2013 9:56 PM
  • I would use the new ContentIterator that is specifically created for accessing over 5,000 records in a SharePoint list.

    using the SPQuery with a filter/order may cause your query to hit the throttle limit and failing.

    Information about Content Iterator: http://msdn.microsoft.com/en-us/library/ff798376.aspx


    Dennis Alzoubi

    Friday, April 12, 2013 10:15 PM
  • Almost you are going good

    I want to some more point like

    1)Use foreach lo0p instead of simple for loop

    2)Access Column by column

    3)Use geitems(Query ) method

    you can also use Content Iterator..

    Thanks

    Sunday, April 14, 2013 2:18 PM
  • Microsoft gives you simple solution on this page:

    http://msdn.microsoft.com/en-us/library/bb687949.aspx

    This is what you need (just change RowLimit to 100):

    SPWeb oWebsite = SPContext.Current.Web;
    SPList oList = oWebsite.Lists["Announcements"];
    
    SPQuery oQuery = new SPQuery();
    oQuery.RowLimit = 10;
    int intIndex = 1;
    
    do
    {
        Response.Write("<BR>Page: " + intIndex + "<BR>");
        SPListItemCollection collListItems = oList.GetItems(oQuery);
    
        foreach (SPListItem oListItem in collListItems)
        {
            Response.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) +"<BR>");
        }
    
        oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition;
        intIndex++;
    } while (oQuery.ListItemCollectionPosition != null);

    


    • Edited by Mario Zagreb Monday, April 15, 2013 9:12 AM
    • Marked as answer by SPManiac Monday, April 15, 2013 10:09 PM
    Monday, April 15, 2013 9:11 AM