How to simulate the SQL WHERE IN clause in a SPQuery

Simulate a WHERE IN clause in a SPQuery

If you ever have used something like

SELECT * FROM table WHERE field IN ('value1','value2','value3','value4')


in T-SQL, and wanted to mimic this functionality in the SharePoint SPQuery class, you probably have found out there is no IN statement in the CAML Query language.
So what do do? Here’s a way to simulate WHERE IN clause in a SPQuery statement.

One solution is to use a LinQ query that looks something like this:

var values = new List {"value1", "value2", "value3"};
var listItems = from SPListItem tItem in list.Items
orderby tItem.Title ascending
where values.Contains(tItem["Title"].ToString())
select tItem;
foreach (var spListItem in listItems)

However, this has a serious performance problem. Because the .AllItems property will do a full T-SQL select all items behind the scenes, it can get very slow if you have thousands and thousand of items in your list.
It do look elegant, however.

Another way is to build up a CAML query that does an OR statement for every item that you have in your list of values.
Essentially, you will have to build up something that looks similar to this CAML query:





Notice that the CAML syntax for multiple OR statements is a little bit odd. You would at first glance think that is would look like this:




But this will throw an error. You will need to have nest the OR statements within each other.

For this purpose I have written a small helper method that will build the CAML query for a given list of values in a List of strings.
You call it like this:

SPQuery query = new SPQuery();
query.RowLimit = 2000;
query.ViewFields = "";
var whereClause = GetWhereClause("Title", "Eq", new List
"value1", "value2", "value3"
query.Query = string.Format(@"{0}", whereClause);
// Get the list items
SPListItemCollection items = list.GetItems(query);

The first parameter is the field that you want to comapre on, the second parameter is the type (“Eq” makes most sense in this scenario), and the third parameter is of List of strings.
Here’s the code for the helper method:

private static string GetWhereClause(string fieldName, string whereElement, IEnumerable list)
var sb = new StringBuilder();
var makeFirstEntry = true;
foreach (var entry in list)
var savedQuery = sb.ToString();
sb = new StringBuilder();
if (!makeFirstEntry)
sb.Append(MakeStatement(whereElement, fieldName, entry));
makeFirstEntry = false;
sb.Append(MakeStatement(whereElement, fieldName, entry));
return sb.ToString();
private static string MakeStatement(string whereElement, string fieldName, string entry)
var sb = new StringBuilder();
sb.Append(string.Format("<{0}>", whereElement));
sb.Append(string.Format("{1}", fieldName, entry));
sb.Append(string.Format("", whereElement));
return sb.ToString();

Microsoft SharePoint 2007 June 2010 CU published

Today Microsoft published the June 2010 CU for SharePoint 2007. This CU also addresses the important security bulletin MS10-039. To read more about this CU, please follow these links:

MOSS 2007 KB983310

WSS 3.0 KB983311

This CU requires you to have the Service Pack 2 installed. And remember to either run the Configuratio Wizard or run this command: psconfig –cmd upgrade –inplace b2b -wait on all of your servers in the farm.


Here is a list of known updates for SharePoint 2010.

How to use the SPGridView filter together with a SPDataSource

Normally you will no problems finding examples on how to use the SPGridView together with an ObjectDataSource and use the filtering mechanism. But what about using the SPDataSource object?

This can also be done fairly simple. You do the basic things as you do using an ObjectDataSource object, however, there are two things that you will have to change.

1. The ObjectDataSource has a FitlerExpression property that set the filter for the data source. That isn’t available on the SPDataSource object. Instead we have the SelectCommand property. The format string for this property is the same as in a standard CAML Query where clause.

2. We will need to set the SPGridView DataSourceID property instead of the more normally used DataSource property.

3. We have to add the SPDataSource control to the Controls colelction of the Web Part.

So let’s look at some code samples.

First, we have our usual suspects, one SPGridView and one SPDataSource:

private SPGridView gridView;
private SPDataSource dataSource;
this.dataSource = new SPDataSource();
this.dataSource.ID = "myDataSource";


In our CreateChildControls() method ( if it’s in a Web Part), we can do much of the work. I have commented the code, so I will not say so much about it more.

this.gridView = new SPGridView();
// Get our currnet Web object
SPWeb web = SPContext.Current.Web;
// And the SPList
SPList taskList = web.Lists["Tasks"];
// Assign the SPlist to our SPDataSource object
dataSource.List = taskList;
// Add our SPDataSource object to the Controls collection so that our SPGridView can find it.
// Disable auto generation of columns
this.gridView.AutoGenerateColumns = false;
this.gridView.ID = "gridView1";
// Add the Title column.
BoundField colTitle = new BoundField()
DataField = "Title",
HeaderText = "Title",
SortExpression = "Title"
// Add the Status column
BoundField colProgress = new BoundField()
DataField = "Status",
HeaderText = "Status",
SortExpression = "Status"
// Enable filtering
this.gridView.AllowFiltering = true;
// We will not filter on the Title, so start with a comma
this.gridView.FilterDataFields = ",Status";
// Set the propertyname of the data source object
this.gridView.FilteredDataSourcePropertyName = "SelectCommand";
// Set the filter format. {1} will contain the column and {0} will contain the value.
this.gridView.FilteredDataSourcePropertyFormat = "<View><Query><Where><Eq><FieldRef Name='{1}'/><Value Type='Text'>{0}</Value></Eq></Where></Query></View>";
// Set the DataSource ID to our SPDataSource.ID
this.gridView.DataSourceID = "myDataSource";
// Add the GridView to our Controls collection
// Now data bind.

SharePoint: How to apply ViewStyle in schema.xml

When you are customizing a SharePoint list with a custom schema.xml file, you may also want to set the ViewStyle for your View.

You do that by adding a <ViewStyle ID="xx"/> element within the <View> element. For exampl ID 17 corresponds to the "Shaded" view style.

Here’s a table with the common ViewStyles available, as defined in the <12 HIVE>\TEMPLATE\GLOBAL\XML\VWStyles.xml

0 Basic Table
12 Boxed, no labels
13 Boxed
15 Newsletter
16 Newsletter, no lines
17 Shaded
20 Preview Pane
0 Default