Thursday, June 27, 2013

SPSiteDataQuery class in SharePoint

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 ViewFieldsproperty. Control data selection and order by setting the Query property.
To execute the query, pass the SPSiteDataQuery object to the GetSiteData(SPSiteDataQuery) method, which returns a DataTable object containing rows of data that represent the result of the query. Each DataRow object in the DataTable.Rows collection represents a single item that satisfies the query. EachDataColumn object in the 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.

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.

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()

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

      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\" Type=\"Text\"/>";
        // Note that setting the Nullable attribute to TRUE
        // causes an empty value to be returned for lists that
        // do not include the FirstName column. The default is 
        // to skip a list that does not include the column.

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

         // 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";

         boundField = new BoundField();
         boundField.HeaderText = "First Name";
         boundField.DataField = "FirstName";

         this.grid.AutoGenerateColumns = false;
         this.grid.DataSource = dv;

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



No comments:

Post a Comment