SPSiteDataQuery Class (Microsoft.SharePoint)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (7)

Click here to expand

Collapse All

Language Filter

SPSiteDataQuery Class (Microsoft.SharePoint)

Represents a query that can be performed across multiple lists in multiple Web sites in the same Web site collection.

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

Syntax

Visual Basic (Declaration)

<SharePointPermissionAttribute(SecurityAction.InheritanceDemand, ObjectModel:=True)> _
<SharePointPermissionAttribute(SecurityAction.LinkDemand, ObjectModel:=True)> _
Public NotInheritable Class SPSiteDataQuery

Visual Basic (Usage)

Dim instance As SPSiteDataQuery

[SharePointPermissionAttribute(SecurityAction.InheritanceDemand, ObjectModel=true)] 
[SharePointPermissionAttribute(SecurityAction.LinkDemand, ObjectModel=true)] 
public sealed class SPSiteDataQuery

Remarks

You can use an instance of this class to retrieve data from selected lists or from all lists in the current site collection. Specify the scope of the query by setting the Webs property. Specify the lists to participate in the query by setting the Lists property and the fields to return by setting the ViewFields property. Control data selection and order by setting the Query property.

To execute the query, pass the SPSiteDataQuery object to the GetSiteData method, which returns a DataTable object containing rows of data that represent the result of the query. Each DataRow object in the System.Data.DataTable.Rows collection represents a single item that satisfies the query. Each DataColumn object in the System.Data.DataTable.Columns collection represents a field that is requested in the ViewFields property, and the column name equals the value of the Name attribute for that field. In addition, the data table contains a column named WebId, which identifies the Web site that contains each item, a column named ListId, which identifies the list that contains each item, and a column named ID, which identifies each item.

Example

The following example is a Web Part that queries all lists that were created with the Contacts list template anywhere in the site collection, retrieves the first and last names of every contact, and displays that information in a GridView control.

Note that if you compile the example code, you can deploy the Web Part simply by copying the compiled assembly to the bin directory of the Web application. If you choose that method of deployment, be sure your project includes a reference to Microsoft.SharePoint.Security.dll. Then add the Web Part to the SafeControls list in the web.config file and elevate the Web application's trust level to WSS_Medium. For more information, see Deploying Web Parts in Windows SharePoint Services and Securing Web Parts in Windows SharePoint Services.

Visual Basic

Imports System
Imports System.Data
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports Microsoft.SharePoint

Public Class ContactViewer
   Inherits WebPart

   Private grid As GridView

   Protected Overrides Sub CreateChildControls()
      MyBase.CreateChildControls()

      ' Add an instance of the grid control.
      Me.grid = New GridView()
      Controls.Add(Me.grid)

   End Sub

   Protected Overrides Sub RenderContents(ByVal writer As System.Web.UI.HtmlTextWriter)

      Dim web As SPWeb = SPContext.Current.Web
      Dim query As SPSiteDataQuery = New SPSiteDataQuery()

      ' Ask for all lists created from the contacts template.
      query.Lists = "<Lists ServerTemplate='105' />"

      ' Get the Title (Last Name) and FirstName fields.
      query.ViewFields = "<FieldRef Name='Title' />" + _
                         "<FieldRef Name='FirstName' Nullable='TRUE'/>"

      ' Set the sort order.
      query.Query = "<OrderBy>" + _
                        "<FieldRef Name='Title' />" + _
                    "</OrderBy>"

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

      Dim dt As DataTable = web.GetSiteData(query)
      Dim dv As DataView = New DataView(dt)

      ' Set up the field bindings.
      Dim boundField As BoundField = New BoundField()
      boundField.HeaderText = "Last Name"
      boundField.DataField = "Title"
      Me.grid.Columns.Add(boundField)

      boundField = New BoundField()
      boundField.HeaderText = "First Name"
      boundField.DataField = "FirstName"
      Me.grid.Columns.Add(boundField)

      Me.grid.AutoGenerateColumns = False
      Me.grid.DataSource = dv
      Me.grid.DataBind()

      Me.grid.AllowSorting = True
      Me.grid.HeaderStyle.Font.Bold = True

      Me.grid.RenderControl(writer)

   End Sub

End Class

using System;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;

namespace SampleWebParts
{
   public class ContactViewer : WebPart
   {
      private GridView grid;

      protected override void CreateChildControls()
      {
         base.CreateChildControls();

         // Add an instance of the grid control.
         this.grid = new GridView();
         this.Controls.Add(this.grid);
      }

      protected override void RenderContents(HtmlTextWriter writer)
      {
         SPWeb web = SPContext.Current.Web;
         SPSiteDataQuery query = new SPSiteDataQuery();

         //Ask for all lists created from the contacts template.
         query.Lists = "<Lists ServerTemplate=\"105\" />";

         // Get the Title (Last Name) and FirstName fields.
         query.ViewFields = "<FieldRef Name=\"Title\" />" +
                            "<FieldRef Name=\"FirstName\" Nullable=\"TRUE\"/>";

         // Set the sort order.
         query.Query = "<OrderBy>" + 
                           "<FieldRef Name=\"Title\" />" + 
                       "</OrderBy>";

         // Query all Web sites in this site collection.
         query.Webs = "<Webs Scope=\"SiteCollection\" />";

         DataTable dt = web.GetSiteData(query);
         DataView dv = new DataView(dt);

         // Set up the field bindings.
         BoundField boundField = new BoundField();
         boundField.HeaderText = "Last Name";
         boundField.DataField = "Title";
         this.grid.Columns.Add(boundField);

         boundField = new BoundField();
         boundField.HeaderText = "First Name";
         boundField.DataField = "FirstName";
         this.grid.Columns.Add(boundField);

         this.grid.AutoGenerateColumns = false;
         this.grid.DataSource = dv;
         this.grid.DataBind();

         this.grid.AllowSorting = true;
         this.grid.HeaderStyle.Font.Bold = true;

         this.grid.RenderControl(writer);
      }
   }
}

Inheritance Hierarchy

System.Object
Microsoft.SharePoint.SPSiteDataQuery

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Reference

SPSiteDataQuery Members
Microsoft.SharePoint Namespace

Tags

: Add a tag

Community Content

Add new content

Annotations

.Net SqlClient Data Provider exception when adding EventDate into ViewFields with Nullable = true.

JeffLin | Edit | Show History

If you are getting the following exception when running SPSiteDataQuery, you may want to check if you have any of the following three fields types in your <ViewFields> element.

Lookup field
Person or Group field
a field called EventDate

The root cause for the above three fields is that the back-end query into SQL, that needs to return one than one columns for any of the three site fields(columns), is constructed to return a few more other columns from DB. However, the Nullable setting only mark one of the many fields into allowing NULL values. There is nothing we can do on # 1 or 2, but for #3, we will have to add a few more fields to avoid the problem.

Tags

: Add a tag

Flag as ContentBug

Limitations of the SPSiteDataQuery

Steve.Curran | Edit | Show History

Please see the following Knowledgebase article on the limitations of the SPSiteDataQuery.

http://support.microsoft.com/kb/946484

Tags

: Add a tag

Flag as ContentBug

Samples...

Vince Rothwell | Edit | Show History

Here are some links to SPSiteDataQuery samples

http://blog.thekid.me.uk/archive/2007/02/27/spsitedataquery-samples-for-wss-v3.aspx

http://blog.thekid.me.uk/archive/2007/02/25/xml-results-using-spsitedataquery-in-sharepoint.aspx

Tags

: Add a tag

Flag as ContentBug

EndDate also needs additional fields

SP.Dev | Edit | Show History

If you want to return the built-in EndDate field (not to be confused with "_EndDate") you will also need to return the group of fields listed above. Otherwise, SP will throw a COM exception which doesn't point to the source of the problem. I.e.,

<ViewFields>
<FieldRef Name="EventDate" Nullable="TRUE" />
<FieldRef Name="fAllDayEvent" Nullable="TRUE" />
<FieldRef Name="EndDate" Nullable="TRUE" />
<FieldRef Name="TimeZone" Nullable="TRUE" />
<FieldRef Name="XMLTZone" Nullable="TRUE" />
</ViewFields>

Interestingly, if you use "_EndDate" instead of "EndDate", your query will return rows instead of blowing up but the EndDate will always return an empty string.

See the post "SP Field Dependencies" at the bottom of http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spbuiltinfieldid.enddate.aspx for why all 5 fields are required.

Tags

: Add a tag

Flag as ContentBug