Getting all the webpages of a SPWeb and subwebs that have a field with a value in Sharepoint?

Question

I have a website in Sharepoint 2007. I want to make a query where the field "home" of the webpages is equal to 1 in one specific SPWeb and (this is the important part) its sub-SPwebs. I can make this work with the site in question and not with the subsites. That is to say: It is not recursive but I indicate it in the "webs scope='recursive'" clause.

I include also the List I want to use, that is to say, the pages (not documents, master pages or whatever) this is whay i look for the "850" basetemplate (the one for pages).

The code I am using is this (i have tried with other methods with the same query and the result is the same):

  string campo="home";
  SPSiteDataQuery qry = new SPSiteDataQuery();

                  qry.Query = "<Where><Eq><FieldRef Name='";
                  qry.Query += campo + "'/><Value

Type='Boolean'>1</Value></Eq>";

                  qry.Query += "</Where><OrderBy><FieldRef

Name='Modified' Ascending='false'> />";

                  qry.Webs = "<Webs Scope='Recursive'/>";

                  qry.ViewFields = "<FieldRef Name='Title'/><FieldRef

Name='Modified'/>";

 //this gives me system privileges
                  using (SPSite site = new SPSite(CurrentSite.ID,

GetSystemToken(CurrentSite))) {

                      using (SPWeb web = site.OpenWeb("/News/"))
                      {

                          StringBuilder sb = new StringBuilder();

                          sb.Append("<Lists>");

                          foreach (SPList list in web.Lists)
                          {

                              if (list.BaseTemplate.ToString() ==

"850") {

                                  sb.Append("<List ID=\"" +

list.ID.ToString() + "\"/>");

                              }

                          }

                          sb.Append("</Lists>");
                          qry.Lists = sb.ToString();

                          dt = web.GetSiteData(qry);

                      ..................

So, the only solution I found was to make a loop after the precedent code through the Webs but i don't think this is a very optimized way:

     foreach (SPWeb w2 in web.Webs)
                              {
                                 sb = new StringBuilder();
                                      sb.Append("<Lists>");
                                      foreach (SPList list in w2.Lists)


                           {
                                              if (list.BaseTemplate.ToString() 
== "850")
                                              {

      sb.Append("<List ID=\""
     + list.ID.ToString() + "\"/>");
                                              }
                                          }


                                      sb.Append("</Lists>");
                                              qry.Lists = sb.ToString();

                                          DataTable dttmp = w2.GetSiteData(qry);
                                          if (dttmp != null
 && dttmp.Rows.Count > 0)
                                          {
                                              dt.Merge(dttmp);
                                          }

                                  w2.Dispose();
                              }

Answer 1

You talk about setting the webs scope to recursive, but in your (ill-formatted) example code you leave the webs property blank, meaning the site data query will only search through the specified web.

This specific (omitted) detail of your code is very important, because making even the slightest spelling mistake in the innerXML specification will silently revert the behaviour back to the default, meaning only the current web will be searched. A typical pitfall would be getting the capitalization wrong in either Webs or Scope.

For the record, the correct way of specifying this is

qry.Webs = "<Webs Scope='Recursive' />";

As a quick check, you can try setting the scope to SiteCollection, see if that works.

[Edit] Agreed, now your Webs property shows up it seems fine :-). Have you tried setting the Nullable='TRUE' attribute in the fieldref of the Campo field? If the field isn't present (or corrupted or whatever) in any of the subsites, that might help. [/Edit]

Answer 2

Finally I did the following, i don't know what has worked finally, i have changed the way I ask the Lists and i have included RowLimit:

DataTable dt = null; DataView dv = null;

    SPSiteDataQuery qry = new SPSiteDataQuery();

    qry.Query = "<Where><Eq><FieldRef Name='";
    qry.Query += campo + "'/><Value Type='Boolean'>1</Value></Eq>";
    qry.Query += "</Where><OrderBy><FieldRef Name='Modified' Ascending='false' /></OrderBy>";
    qry.Webs = "<Webs Scope='Recursive'/>";
    qry.Lists = "<Lists ServerTemplate='850' Hidden='FALSE' MaxListsLimit='50'/>";
    qry.RowLimit = 3;

    qry.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='Modified'/><FieldRef Name='FileRef'/>";

    using (SPSite site = new SPSite(CurrentSite.ID, GetSystemToken(CurrentSite)))
    {
        using (SPWeb web = site.OpenWeb(webUrl))
        {   
            dt = web.GetSiteData(qry);

            dv = dt.DefaultView;

        }
    }

    return dv;