How to: Using CAML query from SharePoint List with folders content types

Hi all,

While developing the custom document library which has folders as content types, I had to query the custom document library for a particular folder using the U2U query builder.

This is my business requirement. I have provisioned a custom document library called "IT System Checklists". This is full structure:

IT System Checklists (Main document library)
---------Desktop Support (sub-folder)
----------------------Daily (sub-sub-folders)
----------------------Weekly
----------------------Monthly
----------------------Yearly
---------VMS (sub-folder)
----------------------(Same structure Daily, Weekly, Monthly, Yearly)

What I have to search for each folder and do some kind of manipulations. If we use U2U builder,
>>Choose the Site URL: http://testserver/apac/Tools
>>Choose "Connect via Object Model"








>> Right click on the "IT System Checklist". We have just option "Query". If we query this list "ID" and order by, there will be just 2 rows i.e. "Desktop Support" and "VMS". There is no folders like daily, weekly folders etc.










But, we can choose second option: "Connect via SharePoint Web Services"








>> Right click on "IT System Checklists", select "GetListItems" and choose "Query Options" tab.
Here we can select any sub folder.












A very detailed and extremely useful article by Karine Bosch (from U2U).
Here is the sample code:

public override void Execute(Guid contentDbId)

{

// get a reference to the current web application
SPWebApplication webApplication = this.Parent as SPWebApplication;
// get a reference to the current site collection's content database
SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];
weekend = isWeekend(DateTime.Today);
foreach(SPSite site in contentDb.Sites)
{
Console.WriteLine(site);
if(site.ToString().Contains("apac"))
{
siteName = site.ServerRelativeUrl;
break;
}

}
// get a reference to the Site Collection in the content database
using (SPSite spSite = contentDb.Sites[siteName])
{
// get a reference to the Web
using (SPWeb spWebRoot = spSite.OpenWeb("Tools"))
{
// get a reference to the "System Checklists" list
SPList sysCheckList = spWebRoot.Lists["System Checklists"];
// this will give us a list of all objects (docs/files and folders) for a particular subfolder level
SPListItemCollection sysCheckColl = sysCheckList.Items;
// loop for iterating all folders, subfolders and documents.
foreach (SPFolder subFolder in sysCheckList.RootFolder.SubFolders)
{
// get the query object
SPQuery queryFolder = new SPQuery();
queryFolder.Query = "<OrderBy><FieldRef Name='Title'/></OrderBy>";
queryFolder.Folder = subFolder;
queryFolder.ViewAttributes = "Scope=\"RecursiveAll\"";
// get sub-folders such as Daily, Weekly
foreach (SPListItem folder in sysCheckList.GetItems(queryFolder))
{
Console.WriteLine(queryFolder.Folder);
#region Daily
if (folder.Name == "Daily")
{
string str = queryFolder.Folder.ToString();
// get the SharePoint group names for each category (Daily)
if (str.Contains("VMS"))
{
GroupName = "VMS - Daily";
GroupName = SharePointGroups.DesktopSupportDaily;
}
if (str.Contains("Desktop Support"))
{
GroupName = "Desktop Support - Daily";
}
// get the group name for "Daily" checklists

SPGroup dailyGrp = spWebRoot.Groups[GroupName];
SPUserCollection userCollection = dailyGrp.Users;

foreach (SPUser user in userCollection)
{
FolderVar = queryFolder.Folder;
FolderVarSub = FolderVar.SubFolders["Daily"];

// get the query object for user alert
SPQuery queryUserAlert = new SPQuery();
queryUserAlert.Query = "<Where>" +
"<And>" +
"<Eq>" +
"<FieldRef Name='Author' />" +
"<Value Type='User'>" + user + "</Value>" +
"</Eq>" +
"<Eq>" +
"<FieldRef Name='Modified' IncludeTimeValue='FALSE'/>" +
"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today) + "</Value>" +"</Eq>" +
"</And>" +
"</Where>";
queryUserAlert.Folder = FolderVarSub;
SPListItemCollection noOfItems = sysCheckList.GetItems(queryUserAlert);
// No Daily Checklists for today then send email
if (noOfItems.Count == 0)
{
string daily = "Daily Checklists: " + str;
SendEmailReminder(spWebRoot, user.Email, user.Name, daily);
}
}
}
#endregion

#region Weekly
if (folder.Name == "Weekly")
{
string str = queryFolder.Folder.ToString();

// get the SharePoint group names for each category (Daily)
if (str.Contains("VMS"))
{
GroupName = "VMS - Weekly";
}
if (str.Contains("Desktop Support"))
{
GroupName = "Desktop Support - Weekly";
}
// get the group name for "Daily" checklists
SPGroup dailyGrp = spWebRoot.Groups[GroupName];
SPUserCollection userCollection = dailyGrp.Users;
foreach (SPUser user in userCollection)
{
FolderVar = queryFolder.Folder;
FolderVarSub = FolderVar.SubFolders["Weekly"];
// get the query object for user alert
SPQuery queryUserAlert = new SPQuery();
queryUserAlert.Query = "<Where>" +
"<And>" +
"<Eq>" +
"<FieldRef Name='Author' />" +
"<Value Type='User'>" + user + "</Value>" +
"</Eq>" +
"<Eq>" +
"<FieldRef Name='Modified' IncludeTimeValue='FALSE'/>" +
"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today) + "</Value>" +
"</Eq>" +
"</And>" +
"</Where>";
queryUserAlert.Folder = FolderVarSub;
SPListItemCollection noOfItems = sysCheckList.GetItems(queryUserAlert);
if (noOfItems.Count == 0)
{
string weekly = "Weekly Checklists: " + str;
SendEmailReminder(spWebRoot, user.Email, user.Name, weekly);
}
}
}
#endregion

}
}
}
}
}