Executing MOSS CAML Queries in Batch

Introduction:


In SharePoint we use SPQuery object to get filtered data from a list. We assign a CAML query to Query property of SPQuery object. The problem with SPQuery object is that it does not support all SQL operation like Joins, IN, Between etc. So to get filtered data we need to apply many ‘OR’ or ‘AND’ operation. Another problem is that lengths of string in its Query property have a limit. So an exception can occurs where we are creating query at run time with ‘OR’ or ‘AND’ operation. To overcome from this situation we have two solutions. One, use Data View object and then filter out the rows. Second, retrieve the rows in batches (execute each batch separately) and combine the result. In Batch execution we make a batch which can contain between 100-120 ‘OR’ or ‘And’ operation. In a batch we get data as a data table and merge this table with main table.
Example:-
Simple ‘Or’ or ‘And’:
This example illustrates the implementation of a function that runs a query(with only ‘Or’ operations) in batches and retuns the combined result as a data table.

///Class level variables
private const string C_Query = "<{3}>{2}";
private int _intBatch = 120; // Size of a batch.

/// This method returns a data table after applying passed filter.
/// 

Field name which will be used to get data from passed data table
Field name on which Or filter will be applied
Value types of field on which Or filter will be applied
List name
Data table whose values will be used in query
SPWeb object
///
public DataTable GetFilteredData(string sourceFieldName, string
queryFieldName, string queryFieldValueType, string queryListName, DataTable itemCollectionForCondition,
SPWeb webQuery)
{
SPQuery spQueryBatch;
DataTable dtbResult;
int intCount = 0;
int j;
int l;

// Get total items of DataTable object.
intCount = itemCollectionForCondition.Rows.Count;

if (intCount > 0)
{
// Create a object of a table in which we will
//merge all table.
dtbResult = new DataTable(queryListName);

for (int i = 0; i < intCount; i += _intBatch) { StringBuilder sbdBatchFilter = new StringBuilder(); int intOrCount = 0; sbdBatchFilter.Append("");
//Add all ‘Or’ condition.
for(j = i; j<(i + _intBatch)-1 && j < intCount -1; j++) { intOrCount += 1; sbdBatchFilter.Append("");
sbdBatchFilter.Append(string.Format(C_Query,
queryFieldName, queryFieldValueType,
itemCollectionForCondition.Rows[j]
[sourceFieldName], "Eq"));
}
sbdBatchFilter.Append(string.Format(C_Query,
queryFieldName, queryFieldValueType,

itemCollectionForCondition.Rows[j]
[sourceFieldName], "Eq"));
//Close all added ‘Or’.
for (int k = 0; k < intOrCount; k++) { sbdBatchFilter.Append("");
}

sbdBatchFilter.Append("");

spQueryBatch = new SPQuery();
spQueryBatch.Query = sbdBatchFilter.ToString();
// SPQuery can only get the items in the current
// folder; SPQuery will not get the items in
// subfolders. So we need to set following
// property.
spQueryBatch.ViewAttributes =
"Scope=\"Recursive\"";

if(webQuery.Lists[queryListName].GetItems
(spQueryBatch).Count>0)
// Add new result to data table.
dtbResult.Merge(webQuery.Lists[queryListName].
GetItems(spQueryBatch).GetDataTable());
}
}
else
{
return null;
}

return dtbResult;
}

To perform ‘AND’ operation in above example we can replace ‘’ and ‘’ tag with ‘’ and ‘’ tag.

‘Or’ with ‘And’:This example illustrates the implementation of a function that runs a query(with ‘Or’ and ‘And’ operation) in batches and retuns the combined result as a data table.

///Class level variables
private const string C_Query = "<{3}>{2}";
private int _intBatch = 100; // Size of a batch.

///

/// This method returns a data table after applying passed filter.
///

///
Field name which will be used to get
data from passed data table
///
Field name on which Or filter will be
applied
///
Value types of field on which Or
filter will be applied
///
List name
///
Data table whose values
will be used in query
///
SPWeb object
///
Array of string which contains all
field names on which AND Operation will be applied
///
Corresponding values of passed
fields
///
Corresponding value types of passed
fields
///
public DataTable GetFilteredData( string sourceFieldName,
string queryFieldName,
string queryFieldValueType,
string queryListName,
DataTable itemCollectionForCondition,
SPWeb webQuery,
string[] andFieldNames,
string[] andValues,
string[] andFieldValueType)
{
string[] strAndFieldNames;
string[] strAndValues;
string[] strAndFieldValueType;

SPQuery spQueryBatch;
DataTable dtbResult;
int intCount = 0;
int j;
int l;

strAndFieldNames = andFieldNames;
strAndValues = andValues;
strAndFieldValueType = andFieldValueType;

// Get total items of DataTable object.
intCount = itemCollectionForCondition.Rows.Count;

if (intCount > 0)
{
// Create a object of a table in which we will
//merge all table.
dtbResult = new DataTable(queryListName);

for (int i = 0; i < intCount; i += _intBatch) { StringBuilder sbdBatchFilter = new StringBuilder(); int intOrCount = 0; sbdBatchFilter.Append("");
//Add all ‘Or’ condition.
for (j = i; j < (i + _intBatch) - 1 && j < intCount - 1; j++) { intOrCount += 1; sbdBatchFilter.Append("");
sbdBatchFilter.Append(string.Format(C_Query,
queryFieldName,
queryFieldValueType,
itemCollectionForCondition
.Rows[j][sourceFieldName],
"Eq"));
}
sbdBatchFilter.Append(string.Format(C_Query,
queryFieldName, queryFieldValueType,
itemCollectionForCondition.Rows[j]
[sourceFieldName], "Eq"));
//Close all added ‘Or’.
for (int k = 0; k < intOrCount; k++) { sbdBatchFilter.Append("");
}
// Check array for And operation. If it is not null
// then add all And conditions.
if (strAndFieldNames != null)
{
if (strAndFieldNames.Length > 0)
{
int intAndCount = 0;
sbdBatchFilter.Replace("",
"");

for(l=0; l < strAndFieldNames.Length - 1; l++) { intAndCount += 1; sbdBatchFilter.Append("");
sbdBatchFilter.Append(
string.Format(C_Query,
strAndFieldNames[l],
strAndFieldValueType[l],
strAndValues[l], "Eq"));
}
sbdBatchFilter.Append(string.Format(C_Query,
strAndFieldNames[l],strAndFieldValueType[l],
strAndValues[l], "Eq"));
//Close all added And's.
for (int k = 0; k <= intAndCount; k++) { sbdBatchFilter.Append("");
}
}
}
sbdBatchFilter.Append("");
spQueryBatch = new SPQuery();
spQueryBatch.Query = sbdBatchFilter.ToString();
spQueryBatch.ViewAttributes = Scope=\"Recursive\"";

if(webQuery.Lists[queryListName].GetItems
(spQueryBatch).Count>0)
// Add new result to data table.

dtbResult.Merge(webQuery.Lists[queryListName]
.GetItems(spQueryBatch).GetDataTable());
}
}
else
{
return null;
}

return dtbResult;
}

References: http://msdn2.microsoft.com/en-us/library/ms978519.aspx