SharePoint

Performing Cross-List Queries in SharePoint 2010 using Visual Studio and Cross Site look up

Try creating tasks in several sites within the site collection, assign them to your own account then run this project.

This SPSiteDataQuery searches for all entries in all Tasks folders that are assigned to the current

Steps
  • Open Visual Studio in your system.
  • Select Console Application template and give as name.
  • Add a Microsoft.Client Assembly reference file in right side reference tab in visual studio.
  • Replace Program.cs with the source code.
  1. using System;   
  2. using System.Collections.Generic;   
  3. using System.Data;   
  4. using System.Linq;   
  5. using System.Text;   
  6. using Microsoft.SharePoint;   
  7.    
  8. namespace GENERAL_CrossListQuery   
  9. {   
  10.     class Program   
  11.     {   
  12.           
  13.         static void Main(string[] args)   
  14.         {   
  15.             //Start by formulating the query in CAML   
  16.   
  17.             SPSiteDataQuery query = new SPSiteDataQuery();   
  18.    
  19.             //Get IDs for the SharePoint built-in fields we want to use   
  20.             string assignedToID = SPBuiltInFieldId.AssignedTo.ToString("B");   
  21.             string taskDueDateID = SPBuiltInFieldId.TaskDueDate.ToString("B");   
  22.             string titleID = SPBuiltInFieldId.Title.ToString("B");   
  23.             string taskStatusID = SPBuiltInFieldId.TaskStatus.ToString("B");   
  24.             string percentCompleteID = SPBuiltInFieldId.PercentComplete.ToString("B");   
  25.    
  26.             //This is the selection creterion   
  27.             string whereClause = "<Where><Eq><FieldRef ID='" + assignedToID + "' />"   
  28.                 + "<Value Type='Integer'><UserID/></Value>"   
  29.                 + "</Eq></Where>";   
  30.    
  31.             //This is the sort order   
  32.             string orderByClause = "<OrderBy><FieldRef ID='" + taskDueDateID + "' /></OrderBy>";   
  33.    
  34.             //Set the query CAML   
  35.             query.Query = whereClause + orderByClause;   
  36.    
  37.             //We will query all the Tasks lists   
  38.             query.Lists = "<Lists ServerTemplate='107' />";   
  39.    
  40.             //Define the view fields in the result set   
  41.             string viewFields = "<FieldRef ID='" + titleID + "' />"   
  42.                 + "<FieldRef ID='" + taskDueDateID + "' Nullable='TRUE' />"   
  43.                 + "<FieldRef ID='" + taskStatusID + "' Nullable='TRUE' />"   
  44.                 + "<FieldRef ID='" + percentCompleteID + "' Nullable='TRUE' />";   
  45.             query.ViewFields = viewFields;   
  46.    
  47.             //Query all the SPWebs in this SPSite   
  48.             query.Webs = "<Webs Scope='SiteCollection'>";   
  49.    
  50.             //Get the SPSite and SPWeb, ensuring correct disposal   
  51.             //Replace the URL with your own site collection   
  52.             using (SPSite site = new SPSite("http://intranet.contoso.com/"))   
  53.             {   
  54.    
  55.                 using (SPWeb web = site.OpenWeb())   
  56.                 {   
  57.    
  58.                      //Run the query   
  59.                     DataTable resultsTable = web.GetSiteData(query);   
  60.    
  61.                     //Print a heading line   
  62.                     Console.WriteLine("{0, -10} {1, -30} {2, -20} {3}", "Date Due", "Task", "Status", "% Complete");   
  63.    
  64.                     //Loop through the results and print them   
  65.                     foreach (DataRow currentRow in resultsTable.Rows)   
  66.                     {   
  67.                         //Extract various values   
  68.                         string dueDate = (string)currentRow[taskDueDateID];   
  69.                         string task = (string)currentRow[titleID];   
  70.                         string status = (string)currentRow[taskStatusID];   
  71.                         string percentComplete = (string)currentRow[percentCompleteID];   
  72.    
  73.                         //Format the due date   
  74.                         DateTime dueDateTime;   
  75.                         bool hasDate = DateTime.TryParse(dueDate, out dueDateTime);   
  76.                         if (hasDate)   
  77.                         {   
  78.                             dueDate = dueDateTime.ToShortDateString();   
  79.                         }   
  80.                         else   
  81.                         {   
  82.                             dueDate = String.Empty;   
  83.                         }   
  84.    
  85.                         //Format the percent complete string   
  86.                         decimal pctDec;   
  87.                         bool hasValue = decimal.TryParse(percentComplete, out pctDec);   
  88.                         if (hasValue)   
  89.                         {   
  90.                             percentComplete = pctDec.ToString("P0");   
  91.                         }   
  92.                         else   
  93.                         {   
  94.                             percentComplete = "0%";   
  95.                         }   
  96.    
  97.                         //Print a line for this row   
  98.                         Console.WriteLine("{0, -10} {1, -30} {2, -20} {3, 10}", dueDate, task, status, percentComplete);   
  99.    
  100.                     }   
  101.    
  102.                 }   
  103.    
  104.             }   
  105.             //Wait for the user to press a key before closing   
  106.             Console.ReadKey();   
  107.    
  108.         }   
  109.     }   
  110. }  
Ebook Download
View all
Learn
View all
Membership not found