Do not reuse SPQuery!
Tuesday, May 20th, 2008Last week I was refactoring some of my SharePoint code. I stumbled on a loop that created a new SPQuery instance for each iteration. The code was something like this:
1: SPList list = GetCommentsList();
2: const string VIEWFIELDS = "";
3:
4: foreach (string param in params)
5: {
6: // Create fresh new SPQuery instance...
7: SPQuery query = new SPQuery();
8: query.ViewFields = VIEWFIELDS;
9: query.Query = BuildQuery(param);
10:
11: // And use it...
12: SPListItemCollection items = list.GetItems(query);
13: ProcessItems(items);
14: }
As the value of the ViewFields property remained the same for each iteration I decided to create just one SPQuery instance and reuse it, like this:
1: SPList list = GetCommentsList();
2: const string VIEWFIELDS = "";
3:
4: // Create just one SPQuery instance...
5: SPQuery query = new SPQuery();
6: query.ViewFields = VIEWFIELDS;
7:
8: foreach (string param in params)
9: {
10: // And reuse it...
11: query.Query = BuildQuery(param);
12: SPListItemCollection items = list.GetItems(query);
13: ProcessItems(items);
14: }
To my surprise this code didn’t work! The first time the SPQuery instance was used it worked just fine. However, during the next iterations of the foreach loop it didn’t seem to get updated.
So, what have we learned today? Never reuse SPQuery instances!
Well… That’s not entirely true. You can reuse SPQuery instances for a very valid reason. If you use the RowLimit property you can limit the number of items returned in the query, which is useful for paging as seen in this sample code (taken from MSDN):
1: using (SPWeb oWebsiteRoot = SPContext.Current.Site.RootWeb)
2: {
3: SPList oList = oWebsiteRoot.Lists["Announcements"];
4: SPQuery oQuery = new SPQuery();
5: oQuery.RowLimit = 10;
6: int intIndex = 1;
7:
8: do
9: {
10: Console.WriteLine("Page: " + intIndex);
11: SPListItemCollection collListItems = oList.GetItems(oQuery);
12:
13: foreach(SPListItem oListItem in collListItems)
14: {
15: Console.WriteLine(oListItem["Title"]);
16: }
17: oQuery.ListItemCollectionPosition =
18: collListItems.ListItemCollectionPosition;
19: intIndex++;
20: } while(oQuery.ListItemCollectionPosition != null);
21: }
So, only reuse SPQuery instances if you use paging. If you change the actual CAML query you should create a new SPQuery instance for it.
