How to query across multiple lists in multiple Web sites in in SharePoint

In this article we will be seeing about SPSiteDataQuery class which is used to query across multiple lists in multiple Web sites in the same Web site collection

I have created a site collection (SPSiteDataQuery) along with that I have created two sub sites (Subsite1 and Subsite2) within the same site collection.



I have created a custom list definition with TemplateType="10000". Using the Custom List Definition I am creating "Custom List" in all the sites (SPSiteDataQuery, Subsite1 and Subsite2).

Custom List Definition:



SPSiteDataQuery: Custom List




Subsite1: Custom List



Subsite2: Custom List



SPSiteDataQueryWP web part:

I have created a visual web part which looks like the following.



SPSiteDataQuery:

SPSiteDataQuery class is used to query across multiple lists in multiple Web sites in the same Web site collection.

• You can specify the scope of the query by "Webs" property.
• You can specify the lists to participate in the query by "Lists" property.
• You can specify the fields to return by "ViewFields" property.
• You can specify the order and data selection by "Query" property.

Here we will be seeing how to query the multiple list which has the TemplateType=10000 ("Custom list" that I have created using "Custom List Definition" which has the TemplateType=10000 from SPSiteDataQuery, Subsite1 and Subsite2 sites.)

We will be creating a web part which contains Label, Textbox, Search Button and a Grid view.

In the textbox we will be entering the EmployeeID and on Search button click we will be retrieving the result from any of the list that we are querying.

Steps Involved:

• Open Visual Studio 2010.
• Select Visual webpart template from the installed templates and click on Ok.
• My entire solution looks like the following.
  
  
   
• Add the following code snippet in the .ascx file.
  
  <style type="text/css">
      .style1
      {
          width: 100%;
      }
  </style>
  <table class="style1">
      <tr>
          <td>
              <asp:Label ID="lblEmpID" runat="server" Text="Enter the Emp ID:"></asp:Label>
  &nbsp;&nbsp;
              <asp:TextBox ID="txtEmpId" runat="server"></asp:TextBox>
  &nbsp;
              <asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click"
                  Text="Search" />
              <br />
              <br />
          </td>
      </tr>
      <tr>
          <td>
  <asp:GridView ID="gvResult" runat="server" AutoGenerateColumns="False"
                  EnableModelValidation="True">
      <Columns>
          <asp:BoundField DataField="Title" HeaderText="Name" />
      </Columns>
  </asp:GridView>

          </td>
      </tr>
  </table>
  

• Add the following code snippet in the .ascx.cs file.
  
  protected void Page_Load(object sender, EventArgs e)
          {
              gvResult.Visible = false;          
          }

          protected void btnSearch_Click(object sender, EventArgs e)
          {
              using (SPSite site = new SPSite("http://serverName:1111/sites/SPSiteDataQuery/"))
              {
                  using (SPWeb web = site.RootWeb)
                  {
                      SPSiteDataQuery dataQuery = new SPSiteDataQuery();
                      dataQuery.Webs = "<Webs Scope=\"SiteCollection\">";
                      dataQuery.Lists = "<Lists ServerTemplate=\"10000\" />";
                      dataQuery.ViewFields = "<FieldRef Name=\"Title\" />";
                      string where = "<Where><Eq>";
                      where += "<FieldRef Name=\"EmployeeID\"/>";
                      where += "<Value Type='Integer'>" + txtEmpId.Text + "</Value>";
                      where += "</Eq></Where>";

                      dataQuery.Query = where;
                      DataTable dt = web.GetSiteData(dataQuery);
                      DataView dv = new DataView(dt);
                      gvResult.DataSource = dv;
                      gvResult.DataBind();
                      gvResult.Visible = true;
                  }

              }
          } 
  

• Build the solution.
• Deploy the solution.
• Go to the SharePoint site and add the custom web part.
• In the textbox enter the EmployeeID value and click on search.
• The result looks like the following.