iterate a sharepoint list

Question

In code how can I access a list e.g "MyList" in sharepoint, then iterate through this list items and get the value of a particular column on that list e.g the "URL" column?

Answer 1

From this blog post:

The correct way to do it is to store the Items property return value in a SPListItemCollection variable. With this the database is only queried once and we will then iterate over the result set that is stored within the collection object. Here is the changed sample code:

SPListItemCollection items = SPContext.Current.List.Items;
for(int i=0;i<100 && i<items.Count;i++) {
  SPListItem listItem = items[i];
  htmlWriter.Write(listItem["Title"]);
}

Answer 2

You can also iterate the items directly, and if you're using a URL field you probably want to use the SPFieldUrlValue class, so you don't have to deal with the way SharePoint stores URLs:

foreach(SPListItem item in spList.Items){
  SPFieldUrlValue data = item["Url"] as SPFieldUrlValue;
  // now you have data.Description, data.Url
}

There are many such SPField* helper classes, and they are very useful, specially when you have multiple values.

---

Edit:

For some reason some people believe this way is slower, based on the evidence in the blog post of on Greg's post (even got down voted). This, however, has nothing to do with my answer: a foreach loop creates an Iterator, so it shouldn't access the database 99 more times (on the post they used a for loop to access the first 100 items).

Answer 3

if you are in a feature, the feature is activated at a specific scope, (e.g. Site, Web, WebApplication or Farm).

When you want to access a list from the feature, use the SPFeatureReceiver class to bind an event receiver to your feature. Then, in that class, there are overrides for when the feature activated event is triggered. that override receives a parameter of type SPFeatureReceiverProperties.

from that parameter, you can use get into a site:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
  using(SPSite site = properties.Feature.Parent as SPSite) //this depends on scope of feature
  {
    SPList myList = site.RootWeb.Lists["MyList"];
  }
}

for how the iterate that list, see the orther answers

Answer 4

As others have said, you shouldn´t iterate the Items collection directly (especially in large collections). Here´s an alternaltive:

// if you need the whole collection. Otherwise use SPQuery on the list

DataTable dt = list.Items.GetDataTable();

foreach (DataRow row in dt.Rows)
{
 ...

Then you can do numerous things. If you need to do a check to get only some of the items, ex:

   if (row["ContentType"].ToString().Equals("Your contenttype id"))
   {
   SPListItem item = list.GetItemById((int)row["ID"]);

or use the SpQuery to get your column in the query, like:

SPQuery oQuery = new SPQuery();
oQuery.ViewFields = "<FieldRef Name='UrlColumn'/>";

list.Items.GetItems(oQuery).GetDataTable();

...foreach code...
row["UrlColumn"]

Answer 5

If you are in a x86 environment I've recently discovered an awesomely read-only way to get at the data with MSSQL/OLEDB...

SELECT * FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http://sharepoint.lsi.local/ops/;LIST={3DCAF100-44A1-4331-8328-748AA98E36AB};',
    'SELECT * FROM list'
)

http://www.connectionstrings.com/sharepoint

Answer 6

BTW When using OPENROWSET...

IMEX=2 is for Read/Write. IMEX=1 is ReadOnly.

List = [Name] works for me instead of needing to use the list={GUID}.

Answer 7

To retrieve all items from a list and iterate through each one, the best solution would be as follows (assuming that this code is run as part of a feature):

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    using(SPSite site = properties.Feature.Parent as SPSite)
    {
        SPList list = site.RootWeb.Lists["ListName"];
        SPListItemCollection items = list.Items;

        foreach (SPListItem listItem in items)
        {
            Response.Write(SPEncode.HtmlEncode(listItem["Url"].ToString()) +"<BR>");
        }
    }
}

But if the list is very large, it would be better to paginate through the list items:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    using(SPSite site = properties.Feature.Parent as SPSite)
    {
        SPList list = site.RootWeb.Lists["ListName"];

        if(items.ItemCount > 100)
        {        
            SPQuery query = new SPQuery();
            query.RowLimit = 100;
            int index = 1;

            do
            {
                SPListItemCollection items = list.GetItems(query);

                foreach (SPListItem listItem in items)
                {
                    Response.Write(SPEncode.HtmlEncode(listItem["Url"].ToString()) +"<BR>");
                }

                query.ListItemCollectionPosition = items.ListItemCollectionPosition;
                index++;

            } while (query.ListItemCollectionPosition != null);
        }
        else
        {
            SPListItemCollection items = list.Items;

            foreach (SPListItem listItem in items)
            {
                Response.Write(SPEncode.HtmlEncode(listItem["Url"].ToString()) +"<BR>");
            }
        }
    }
}

This is based on the Microsoft's Best Practices for SharePoint.