Category: SPQuery

SPQuery for my tasks

Developing solution with multiple languages (or a language which is not English) sometimes can be a bit painful. To configure a Webpart to display only my tasks, I would filter for [Me] or [Ich].

image

To achieve the same via code / CAML, you can filter by UserID and not the string “Me”.

  1: <Where>
  2:   <Eq>
  3:     <FieldRef Name="AssignedTo" />
  4:     <Value Type="Integer">
  5:       <UserID />
  6:     </Value>
  7:   </Eq>
  8: </Where>
  9: <OrderBy>
 10:   <FieldRef Name="Status" />
 11:   <FieldRef Name="Priority" />
 12: </OrderBy>

This is just a reminder for me, so I can find the information more quickly. But maybe this is useful for some of you as well 🙂

Empty Admin Recycle Bin items

What is it?

Usually the size of the recycle bin is not relevant. But on development machines, you don’t want lots of files in there, which make your databases grow without actually used data.

What do you do? Go to the recycle bin, click on “Site Collection Recycle Bin”. The two stages of the recycle bin can be managed independently.

image

The two views on the left let you switch between the first- and second stage. All items from the first stage can be moved to the second stage with one click (+ 1 confirmation). But if the items are in the second stage, you can only delete 200 items at a time by selecting all and delete them in batches. An option to delete all items is missing (in the GUI).

image

My solutions adds this option for the second stage recycle bin. A link for deleting all items at once is displayed in the toolbar. If you click it, a modal dialog – I love SharePoint 2010 🙂 – is displayed, and you can delete all items at once.

image

 

How does it work?

A feature (farm-scoped) activates a delegate control. It references jQuery/JavaScript code to add the button “Empty Recycle Bin”, as shown in the second picture, and to open a modal dialog.

This modal dialog is a custom application page, which will query the recycle bin for statistics (as shown in the second picture) and delete items in batches via SPRecycleBinQuery which is similar to the SPQuery.

New link in the toolbar

jQuery was my choice to add an additional button the the toolbar of the second stage recycle bin. The developer tools from the Internet Explorer helped me to find the right table by its ID. The content of it is modified, so the button is injected via JavaScript. That way there is not server code required to add the button directly to the page.

jQuery(document).ready(function () {
   // the Toolbar2 is used for the second stage recycle bin
   var row = jQuery("#ctl00_PlaceHolderMain_Toolbar2 > tbody > tr");
   
   // find last cell
   var lastTD = $(row).find('td:last');

   // add seperator
   lastTD.before('<TD class=ms-separator>|</TD>');

   // change content of the last cell
   var content =
   '<TABLE border=0 cellSpacing=0 cellPadding=1>' +
      '<TBODY>' +
         '<TR>' +
            '<TD class=ms-toolbar noWrap><A class=ms-toolbar title=' + emptyRecycleBin + ' href="javascript:openEmptyAdminRecycleBinDialog()">...</A></TD>' +
            '<TD class=ms-toolbar noWrap><A class=ms-toolbar title=' + emptyRecycleBin + ' href="javascript:openEmptyAdminRecycleBinDialog()">' + emptyRecycleBin + '</A>...</TD>' +
         '</TR>' +
      '</TBODY>' +
   '</TABLE>';
   lastTD.html(content);
});

Modal Dialog

The modal dialog is opened with JavaScript:

function openEmptyAdminRecycleBinDialog() {
   var options = {
      url: "../../_layouts/RH.EmptyAdminRecycleBin/EmptyAdminRecycleBin.aspx",
      width: 400,
      height: 130,
      title: "Empty Admin RecycleBin",
      dialogReturnValueCallback: onDialogClose
   };
   SP.UI.ModalDialog.showModalDialog(options);
}

function onDialogClose(dialogResult, returnValue) {
   if (dialogResult == SP.UI.DialogResult.OK) {
      // refresh the page to reflect changes
      SP.UI.ModalDialog.RefreshPage(SP.UI.DialogResult.OK)
   }
   if (dialogResult == SP.UI.DialogResult.cancel && returnValue != null) {
      alert(returnValue);
   }
}

With the callback function, a page refresh is triggered. Otherwise the deleted items would still be visible on the recycle bin page.

Recycle Bin

Querying recycle bin items is achieved with a dedicated object. The SPRecycleBinQuery. It takes parameters like the RowLimit. If you don’t specify the RowLimit, only 50 items will be returned. The ItemState defines if items from the first, or second stage will be returned. The query is executed multiple times, to get all items.

private void GetRecycleBinStorageInfo(out int itemCount, out long overalSize)
{
   itemCount = 0;
   overalSize = 0;
   SPRecycleBinItemCollectionPosition itemcollectionPosition = null;
   do
   {
      SPRecycleBinQuery query = CreateQuery(itemcollectionPosition);
      SPRecycleBinItemCollection recycleBinItems = Site.GetRecycleBinItems(query);
      itemcollectionPosition = recycleBinItems.ItemCollectionPosition;
      // get itemCount
      itemCount += recycleBinItems.Count;
      // get overalSize
      overalSize += recycleBinItems.Cast<SPRecycleBinItem>().Sum(item2 => item2.Size);
   } while (itemcollectionPosition != null);
}

private static SPRecycleBinQuery CreateQuery(SPRecycleBinItemCollectionPosition page)
{
   var query = new SPRecycleBinQuery
                  {
                     RowLimit = 200,
                     ItemState = SPRecycleBinItemState.SecondStageRecycleBin,
                     OrderBy = SPRecycleBinOrderBy.Default,
                     ItemCollectionPosition = page ?? SPRecycleBinItemCollectionPosition.FirstPage
                  };
   return query;
}

The method returns the itemcount and size of all items in the second stage recycle bin.

To delete all items, I’ve used this code:

using (var operation = new SPLongOperation(this))
{
   operation.Begin();

   try
   {
      // delete all items in pages of 200 items
      SPRecycleBinItemCollectionPosition itemcollectionPosition = null;
      do
      {
         SPRecycleBinQuery query = CreateQuery(itemcollectionPosition);
         SPRecycleBinItemCollection recycleBinItems = Site.GetRecycleBinItems(query);
         itemcollectionPosition = recycleBinItems.ItemCollectionPosition;
         for (int i = 0; i < recycleBinItems.Count; i++)
         {
            recycleBinItems[i].Delete();
         }
      } while (itemcollectionPosition != null);

      operation.End("/_layouts/RH.EmptyAdminRecycleBin/CloseModalDialog.html", SPRedirectFlags.Default, Context, null);
   }
   catch (Exception ex)
   {
      UlsLogging.Write(TraceSeverity.Unexpected, ex.ToString());
      SPUtility.TransferToErrorPage(ex.Message);
      return false;
   }
   return true;
}

The SPLongOperation shows the nice animation during processing the code. Just make sure you end the operation to hide the animation.

Exception Handling

Usually my classes have a method HandleException, as the WSPBuilder does :-). This class will write the exception to the SharePoint ULS log.

private void HandleException(Exception ex)
{
   try
   {
      UlsLogging.Write(TraceSeverity.Unexpected, ex.ToString());
      Controls.AddAt(Controls.Count, new Label {CssClass = "ms-error", Text = ex.Message});
   }
   catch (Exception e)
   {
      Trace.Write(e.ToString());
   }
}

internal class UlsLogging
{
   internal static void Write(TraceSeverity traceSeverity, string message)
   {
      var uls = SPDiagnosticsService.Local;
      if (uls != null)
      {
         SPDiagnosticsCategory cat = uls.Areas["SharePoint Foundation"].Categories["Web Controls"];
         uls.WriteTrace(1, cat, traceSeverity,message, uls.TypeName);
      }
   }
}

If you want to use an area or category which is not a default one, you’ll need administration permissions to create it. The use of RunWithElevatedPrivilegues is not enough!

You can download the sourcecode here: RH.EmptyAdminRecycleBin(Sourcecode).zip

The compiled solution as WSP file can be downloaded here: RH.EmptyAdminRecycleBin.wsp

And before you ask: The solution is for SP 2010! SharePoint Services 3 lacks the modal dialog. But you can take the source code, and modify it to fulfill your needs.

PortalSiteMapProvider.GetCachedListItemsByQuery

With MOSS 2007 or SharePoint Server 2010 you can use the PoraltSiteMapProvider of the Microsoft.SharePoint.Publishing.dll assembly to retrieve cached listitems.

   1: PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider;
   2: var pNode = ps.FindSiteMapNode(web.ServerRelativeUrl) as PortalWebSiteMapNode;
   3: var query = new SPQuery
   4:                {
   5:                   Query = "<Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where>"
   6:                };
   7: SiteMapNodeCollection quoteItems = ps.GetCachedListItemsByQuery(pNode, "Top Seiten", query, web);

In my case, I didn’t need any special where clause. I wanted to retrieve all items, so I left the Query property  empty. And because I needed only three columns, I specified the ViewFields property of the SPQuery object.

Bad idea. The query failed hard and fast 🙂

Conclusion:

If you use the GetCachedListItemsByQuery method, do not specify the ViewFields property of the SPQuery and configure a query. Even if it returns all items of the list!

SPQuery with lookup columns returns no data

An SPQuery for lookup columns should be easy. Start the famous U2U Caml Editor, configure your query, and query the list.

image

This query will get a result if the lookup column stores something like “1;#1”. But if its value is e.g. “1;#Title”, the query will not return an item.

So what can we do about it?

Add a LookupId=’TRUE’ to your query, so it will look like this:

Remember to omit the Query tags from the U2U Caml Editor when pasting the query into your Visual Studio source code!

Summary: Common Coding Issues

The Best Practice MSDN page has some interesting hints to generate better code. In this post I write about some point of that article, provide samples and fix bugs which are in the MSDN article.

Caching

You should only cache thread safe objects. What’s that?

This means, that you should only cache objects, which can not be changed from the outside of your code. An itemCollection (as list.Items) is changed, if another user adds an item. But if you cache a DataTable which you can get from an itemCollection with list.Items.GetDataTable() it will not be changed later on, and can be cached.

A lock should be put around reading the items and caching them. This will ensure that for the reading process, the items are not changed from the outside.

   1:  private static object _Lock = new object();
   2:   
   3:  public void CacheData(SPList list)
   4:  {
   5:      SPListItemCollection listItems;
   6:      lock (_Lock)
   7:      {
   8:          // Cache is your caching mechanism
   9:          DataTable dataTable = (DataTable) Cache["ListItemCacheName"];
  10:          if (dataTable == null)
  11:          {
  12:              listItems = list.GetItems(new SPQuery {RowLimit = 0});
  13:              dataTable = listItems.GetDataTable();
  14:              Cache.Add("ListItemCacheName", dataTable)
  15:          }
  16:      }
  17:  }

Event Receivers

Use the objects provided by the SPItemEventProperties parameter of your event method.

SPWeb web = properties.OpenWeb();

instead of

using (SPSite site = new SPSite(properties.WebUrl))
using (SPWeb web = site.OpenWeb())

Do not use SPList.Items

The Items property will return every list item. And it will read all items for every call to it. So every time your code has an .Items, a SQL query is generated, which reads all items. They have to be fetched, and stored in memory.

  • SPList.Items.Add()

Instead use SPList.AddItem we can not use SPList.AddItem, since it does not exist 🙁
So we query the list for 0 rows, and add a new list item to the itemCollection with 0 elements.

   1:  SPQuery query = new SPQuery {RowLimit = 0};
   2:  SPListItem newItem = list.GetItems(query).Add();
   3:  newItem[SPBuiltInFieldId.Title] = DateTime.Now.ToString();
   4:  newItem.Update();

Compared with list.Items.Add() the code runs significant faster. In my case I tested against a list with about 1100 items. A trace with the SQL Server Profiler showed, that the code took 19983 microseconds. Adding an item to SPList.Items took 288996 microseconds. This is 14 times more!

  • Get Items by Identifier

If you have an ID, get your list item through list.GetItemsByID(id). Otherwise use a SPQuery instead of iterating through all list items (see above).

  • Use paged Queries

The MSDN article provides some samples on how to get all list items. Not all at once, but 2000 at a time.

  • Use list.ItemCount instead of list.Items.Count
  • Use the PortalSiteMapProvider class (MOSS only!)

The PortalSiteMapProvider class uses caching by default. So if you need to query the same list, and it does not change frequently, you can increase the performance easily.

You can use the PortalSiteMapProvider for WSS sites, if you have MOSS installed. There is no need to activate MOSS features on the site, where you want to use the caching.

Also think about security trimming when you query for items, as you might only get a subset of all items in the initial load, if the user may not access all items!

   1:  SPWeb web = SPContext.Current.Web;
   2:  SPList list = web.Lists.Cast<SPList>().SingleOrDefault(
   3:      l => l.Title == "ListName");
   4:  if (list == null) return;
   5:   
   6:  PortalSiteMapProvider provider = PortalSiteMapProvider.WebSiteMapProvider;
   7:  PortalSiteMapNode webNode = 
   8:      (PortalSiteMapNode)provider.FindSiteMapNode(web.ServerRelativeUrl);
   9:  if (webNode == null || webNode.Type != NodeTypes.Area) return;
  10:   
  11:  Stopwatch watch = new Stopwatch();
  12:  watch.Start();
  13:  SPQuery query = new SPQuery { Query = list.DefaultView.Query };
  14:  SiteMapNodeCollection items = provider.GetCachedListItemsByQuery(
  15:      (PortalWebSiteMapNode)webNode, list.Title, query, web);
  16:   
  17:  foreach (PortalListItemSiteMapNode item in items)
  18:  {
  19:      string title = (string)item[SPBuiltInFieldId.Title];
  20:  }
  21:  watch.Stop();
  22:  Controls.Add(new LiteralControl(
  23:      "Items found: " + items.Count + " in " + watch.ElapsedMilliseconds + "ms"));

In the example, the stopwatch got about 2000 ms for the initial load. For each access to the items after they have been cached, it took 15-20 ms. So it is roughly 100 times faster!
You might have to adjust the query to your needs, since it uses the default view to get the items.

Conclusion

Review your code for memory leaks (SharePoint Diagnostics Tool (SPDiag)) and follow common coding issues to generate clean, safe and fast SharePoint code.

SPQuery, ViewFields and empty fields

If you want your query to return empty columns, you have to add Nullable=’TRUE’ to the viewfields.

<FieldRef Name=’Field1′ Nullable=’TRUE’/>

If you do not add the Nullable attribute, accessing the results of the query like this:

oListItemAvailable["Field1"] will give an Exception.

I’ve made posted a comment about this on the page in the MSDN http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.viewfields.aspx

Get a listitem by ID

Fetching a listitem by ID will generate an error, if the a listitem with the ID does not exist. To avoid this exception, you can get a listitem by id by searching for it:

private SPListItem GetListItem(SPList List, int ListItemID)
{
    try
    {
        string defaultView = List.DefaultView.Title;
        SPQuery query = new SPQuery(List.Views[defaultView]);

        string caml =
String.Format("<Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>{0}</Value></Eq></Where>",
ListItemID); query.Query = caml; SPListItemCollection results = List.GetItems(query); if (results.Count == 1) { return results[0]; } } catch (Exception ex) { _ErrorMessage +=
String.Format("List \"{0}\" does not contain an item with the id \"{1}\".<br/>{2}",
List.Title, ListItemID, ex.Message); } return null; }

If there is no listitem with the specified ListItemID, you will get a null instead of an exception.