Wednesday, July 29, 2009

Accessing multiple lists across multiple sites - SPSiteDataQuery

Sometimes, we may require to access multiple lists in multiple websites using a single query. Sharepoint provides two ways to achieve this-
1. joined subview using sharepoint designer ( click here to see)
2. Using SPSiteDataQuery class
SPSiteDataQuery class can be used to run CAML query across multiple lists in multiple websites within a site collection. It can be defined as-
SPSiteDataQuery query = new SPSiteDataQuery();
SPSiteDataQuery class returns a DataTable Class which can be used to access the data.
SPSiteDataQuery class has few properties which need to be set in order to use it. These properties are-

a. Lists- Can be used to specify list type, lists which has to be used in query through ListID, Maximum number of lists can be used in query etc. List Type is specified by BaseType attribute. The value of BaseType attribute will be
0 for a generic list
1 for a document library
3 for a discussion forum
4 for a survey
5 for a issues list.
The Lists property can be set as
query.Lists="<Lists BaseType="'0'/">";
If we want to query some specific lists only then we must specify their list ID as below-
<List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/>
to get the list ID go to list setting page take the value of 'List' querystring from page URL. It will be like-
%7BE8A71D9E%2DDDB6%2D4A2A%2D985C%2D976FCFBBDD23%7D

replace %7B with '{', %7D with '}' and %2D with a '-' and we will have the list ID as
{E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23}.
The maximum number of lists that the query can use will be specified by MaxListsLimit attribute as below-
<Lists BaseType='0' MaxListsLimit='10'>

b. Webs Property- This will specify the scope of the query. It may have any of the three values-

1. SiteCollection- query will run through the whole site collection.
query.Webs = "<Webs Scope="'SiteCollection'/">";

2. Recursive- query will run within website and its child site.
query.Webs = "<Webs Scope="Recursive">";

3. Blank- query will run through current web only.
query.Webs = "<Webs Scope="'">";

c. ViewFields property- It can be used to specify the column we are going to display. It can be used a follow-
query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' />"
If we set the value Nullable='TRUE' then the query will fetch the data even if the column has null value in the corresponding row.

d. Query property- It is used to specify the CAML query as below-
query.Query="<Where><Eq><FieldRef Name="'ID'"><Value Type=" 'Counter'"> 1</Value></Eq></Where>";

e. RowLimit- This property will limit the number of rows which has to be fetched.
It can be used as-
query.RowLimit=5; ( To limit the number of rows to 5)

Below is the sample code to use SPSiteDataQuery-

SPWeb web = SPControl.GetContextWeb(Context);

SPSiteDataQuery q = new SPSiteDataQuery();


query.Query = "<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Eq></Where>";


query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' /><FieldRef Name='DepartmentLink' Nullable='TRUE' />";


query.Lists = "<Lists BaseType='0'><List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/><List ID=\"B59FC80A-B586-4F31-A376-2FE20896641D\"/><List ID=\"3482EBF1-A2A7-4420%2DA920-09EA2635C557\"/></Lists>";

query.Webs = "<Webs Scope='SiteCollection'/>";

query.RowLimit = 10;

DataTable t = web.GetSiteData(query);
The DataTable will be populated by the data fetched from the list. It can be used to display the contents on the web page.

5 comments:

  1. Could SPSiteDataQuery be used to get data from lists with different fields? if yes how? could any one help? is there any method to join two lists?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Answer for your first two question. http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.webs.aspx.

      Answer for your Third Question:

      SPSiteDataQuery query = new SPSiteDataQuery();
      query.Lists = "";
      query.Webs = "";
      string strView =
      "" +
      "";
      query.ViewFields = strView;
      System.Data.DataTable table = webSite.GetSiteData(query);

      Similarly you can get data from the second list using the SPSiteDataQuery and then merge the tables (DataTable) and show them in SPGridView.

      Delete
  2. Gives you perfect understanding on SPSiteDataQuery. Thanks for sharing your experience SPSiteDataQuery through this blog . Keep up the good work! You understand, a lot of people like me are searching round for this info, you could help us greatly.

    ReplyDelete