SPWeb.GetSiteData method

Gets the list items across multiple lists, which can be located in multiple websites in the same website collection.

Namespace:  Microsoft.SharePoint
Assembly:  Microsoft.SharePoint (in Microsoft.SharePoint.dll)

'Declaration
Public Function GetSiteData ( _
    query As SPSiteDataQuery _
) As DataTable
'Usage
Dim instance As SPWeb
Dim query As SPSiteDataQuery
Dim returnValue As DataTable

returnValue = instance.GetSiteData(query)

public DataTable GetSiteData(
    SPSiteDataQuery query
)

• query
  Type: Microsoft.SharePoint.SPSiteDataQuery

  A query to perform, specifying which websites and lists participate, which fields to return, and the Where and OrderBy sections to apply.

Type: System.Data.DataTable
The result of the query.

Each DataRow object in the DataTable.Rows collection represents a single item that satisfies the requirements of the query. Each DataColumn object in the DataTable.Columns collection represents a field that is requested in the query fields, and the column name of the field equals the value of the Name attribute for that field. In addition, DataTable.Columns contains a DataColumn named ListId, which identifies the list that contains each item; a DataColumn named WebId, which identifies the website that contains each item; and a DataColumn named ID, which identifies each item.

The following example is a console application that queries all Tasks lists in a site collection. The query string selects tasks that are assigned to the current user, ordered by date due. After the results are returned, the application prints a report to the console.

Imports System
Imports System.Data
Imports Microsoft.SharePoint

Module ConsoleApp
   Sub Main()
      Using site As SPSite = New SPSite("https://localhost")
         Using web As SPWeb = site.OpenWeb()

            Dim query As SPSiteDataQuery = New SPSiteDataQuery()

            ' Get IDs for all fields used in the query.
            Dim assignedToId As String = SPBuiltInFieldId.AssignedTo.ToString("B")
            Dim taskDueDateId As String = SPBuiltInFieldId.TaskDueDate.ToString("B")
            Dim titleId As String = SPBuiltInFieldId.Title.ToString("B")
            Dim taskStatusId As String = SPBuiltInFieldId.TaskStatus.ToString("B")
            Dim percentCompleteId As String = SPBuiltInFieldId.PercentComplete.ToString("B")

            ' Define the data selection.
            Dim where As String = "<Where><Eq>"
            where += "<FieldRef ID='" + assignedToId + "' />"
            where += "<Value Type='Integer'><UserID/></Value>"
            where += "</Eq></Where>"

            ' Define the sort order.
            Dim orderBy As String = "<OrderBy>"
            orderBy += "<FieldRef ID='" + taskDueDateId + "' />"
            orderBy += "</OrderBy>"

            ' Set the query string.
            query.Query = where + orderBy

            ' Query task lists.
            query.Lists = "<Lists ServerTemplate='107'/>"

            ' Specify the view fields.
            query.ViewFields = "<FieldRef ID='" + titleId + "' />"
            query.ViewFields += "<FieldRef ID='" + taskDueDateId + "' Nullable='TRUE' />"
            query.ViewFields += "<FieldRef ID='" + taskStatusId + "' Nullable='TRUE' />"
            query.ViewFields += "<FieldRef ID='" + percentCompleteId + "' Nullable='TRUE' />"

            ' Query all websites in this site collection.
            query.Webs = "<Webs Scope='SiteCollection'>"

            ' Run the query.
            Dim results As DataTable = web.GetSiteData(query)

            ' Print the results.
            Console.WriteLine("{0, -10} {1, -30} {2, -30} {3}", "Date Due", "Task", "Status", "% Complete")
            Dim row As DataRow
            For Each row In results.Rows
               ' Extract column values from the data table.
               Dim dueDate As String = CType(row(taskDueDateId), String)
               Dim task As String = CType(row(titleId), String)
               Dim status As String = CType(row(taskStatusId), String)
               Dim percentComplete As String = CType(row(percentCompleteId), String)

               ' Convert the due date to a short date.
               Dim dt As DateTime
               Dim hasDate As Boolean = DateTime.TryParse(dueDate, dt)
               If hasDate Then
                  dueDate = dt.ToShortDateString()
               Else
                  dueDate = String.Empty
               End If

               ' Convert the PercentComplete field value to a percentage.
               Dim pct As Decimal
               Dim hasValue As Boolean = Decimal.TryParse(percentComplete, pct)
               If hasValue Then
                  percentComplete = pct.ToString("P0")
               Else
                  percentComplete = "0 %"
               End If

               ' Print a line.
               Console.WriteLine("{0, -10} {1, -30} {2, -30} {3, 10}", dueDate, task, status, percentComplete)
            Next

         End Using
      End Using
      Console.ReadLine()
   End Sub
End Module

using System;
using System.Data;
using Microsoft.SharePoint;

namespace Test
{
   class ConsoleApp
   {
      static void Main(string[] args)
      {
         using (SPSite site = new SPSite("https://localhost"))
         {
            using (SPWeb web = site.OpenWeb())
            {
               SPSiteDataQuery query = new SPSiteDataQuery();

               // Get IDs for all fields used in the query.
               string assignedToId = SPBuiltInFieldId.AssignedTo.ToString("B");
               string taskDueDateId = SPBuiltInFieldId.TaskDueDate.ToString("B");
               string titleId = SPBuiltInFieldId.Title.ToString("B");
               string taskStatusId = SPBuiltInFieldId.TaskStatus.ToString("B");
               string percentCompleteId = SPBuiltInFieldId.PercentComplete.ToString("B");

               // Define the data selection.
               string where = "<Where><Eq>";
               where += "<FieldRef ID='" + assignedToId + "' />";
               where += "<Value Type='Integer'><UserID/></Value>";
               where += "</Eq></Where>";

               // Define the sort order.
               string orderBy = "<OrderBy>";
               orderBy += "<FieldRef ID='" + taskDueDateId + "' />";
               orderBy += "</OrderBy>";

               // Set the query string.
               query.Query = where + orderBy;

               // Query task lists.
               query.Lists = "<Lists ServerTemplate='107'/>";

               // Specify the view fields.
               query.ViewFields = "<FieldRef ID='" + titleId + "' />";
               query.ViewFields += "<FieldRef ID='" + taskDueDateId + "' Nullable='TRUE' />";
               query.ViewFields += "<FieldRef ID='" + taskStatusId + "' Nullable='TRUE' />";
               query.ViewFields += "<FieldRef ID='" + percentCompleteId + "' Nullable='TRUE' />";

               // Query all websites in this site collection.
               query.Webs = "<Webs Scope='SiteCollection'>";

               // Run the query.
               DataTable results = web.GetSiteData(query);

               // Print the results.
               Console.WriteLine("{0, -10} {1, -30} {2, -30} {3}", "Date Due", "Task", "Status", "% Complete");
               foreach (DataRow row in results.Rows)
               {
                  // Extract column values from the data table.
                  string dueDate = (string)row[taskDueDateId];
                  string task = (string)row[titleId];
                  string status = (string)row[taskStatusId];
                  string percentComplete = (string)row[percentCompleteId];

                  // Convert the due date to a short date string.
                  DateTime dt;
                  bool hasDate = DateTime.TryParse(dueDate, out dt);
                  if (hasDate)
                     dueDate = dt.ToShortDateString();
                  else
                     dueDate = String.Empty;

                  // Convert the PercentComplete field value to a percentage.
                  decimal pct;
                  bool hasValue = decimal.TryParse(percentComplete, out pct);
                  if (hasValue)
                     percentComplete = pct.ToString("P0");
                  else
                     percentComplete = "0 %";

                  // Print a line.
                  Console.WriteLine("{0, -10} {1, -30} {2, -30} {3, 10}", dueDate, task, status, percentComplete);
               }
            }
         }
         Console.ReadLine();
      }
   }
}

SPWeb class

SPWeb members

Microsoft.SharePoint namespace