Click here to Skip to main content
Click here to Skip to main content

SharePoint 2010 SPSiteDataQuery with Example

, 15 May 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
A closer look at SPSiteDataQuery.
Introduction

In this article we can have a closer look at SPSiteDataQuery using scenarios to combine or join multiple lists. Please note that these classes are Server Object Models.

Here I would like to address the following scenario: Union Data from multiple lists of same list template

Following are the core aspects involved:

  • SPSiteDataQuery: Server Object Model type to represent a CAML query. This class can be used to get data from selected lists or from all lists in the current site collection.
  • GetSiteData() is the method to be invoked passing the above object

Scenario

You have a site collection where n number of Contact List exists. You need to generate a list of items with all the contacts.

Solution: For the Contacts lists the template ID is 105. Using SPSiteDataQuery we can select multiple list items of same list type.

Before proceeding with we need to create the two Lists and data to work with. In the next part I am setting up the code to create Lists and Data dynamically.

Setup Data

Please note that to run the above examples we need the following lists with data:

  1. Contact List 1
  2. Contact List 2

The attached source code contains the Windows Form Application which creates the data.

While creating Windows Application please note to set the:

  • Properties > Target Framework > .Net 3.5
  • Build > Platform Target > Any CPU

Following is the screen shot of the application:

You can use the first button to generate the lists and associated data in the site specified.

The activities performed for setting up data are:

  1. Create Lists
  2. Create Data
  3. Fetch Data into two GridViews

Following is the code to perform the activities:

using (SPSite site = new SPSite(ServerText.Text))
{
    using (SPWeb web = site.OpenWeb())
    {
        // Create Lists
        try
        {
            var c1 = web.Lists["Contact List 1"];
        }
        catch
        {
            web.Lists.Add("Contact List 1", "A contact list", SPListTemplateType.Contacts);
        }

        try
        {
            var c2 = web.Lists["Contact List 2"];
        }
        catch
        {
            web.Lists.Add("Contact List 2", "A contact list", SPListTemplateType.Contacts);
        }

        // Create Data

        AddItem(web.Lists["Contact List 1"], "1");
        AddItem(web.Lists["Contact List 1"], "2");
        AddItem(web.Lists["Contact List 2"], "3");
        AddItem(web.Lists["Contact List 2"], "4");

        // Fetch and Show Data Lists to user
        dataGridView1.DataSource = web.Lists["Contact List 1"].Items.GetDataTable();
        dataGridView2.DataSource = web.Lists["Contact List 2"].Items.GetDataTable();                    
    }
}

// AddItem Method
private void AddItem(SPList list, string index)
{
    string key = "Last Name " + index;
    var any = list.Items.Cast<SPListItem>().Where(i => i["Last Name"].ToString() == key).Any();

    if (!any) 
    {
        SPListItem item = list.Items.Add();
        item["First Name"] = "First Name " + index.ToString();
        item["Last Name"] = key;

        item.Update();
    }
}

// AddItem Method
private void AddItem(SPList list, string index)
{
    string key = "Last Name " + index;
    var any = list.Items.Cast<SPListItem>().Where(i => i["Last Name"].ToString() == key).Any();

    if (!any) 
    {
        SPListItem item = list.Items.Add();
        item["First Name"] = "First Name " + index.ToString();
        item["Last Name"] = key;

        item.Update();
    }
}

SPDataQuery in Action Getting data from multiple lists of same type

Here we are creating two lists of template Contacts. Then using SPSiteDataQuery we can get the union of both the lists.

Following is the code to achieve that:

using (SPSite site = new SPSite(ServerText.Text))
{
    using (SPWeb web = site.OpenWeb())
    {
        // Fetch using SPSiteDataQuery
        SPSiteDataQuery query = new SPSiteDataQuery();
        query.Lists = "<Lists ServerTemplate=\"105\" />";
        query.ViewFields = "<FieldRef Name=\"Title\" />" +  /* Title is LastName column */
                "<FieldRef Name=\"FirstName\" Nullable=\"TRUE\" Type=\"Text\"/>";
        query.Webs = "<Webs Scope=\"SiteCollection\" />";

        DataTable dataTable = web.GetSiteData(query);
        dataGridView3.DataSource = dataTable;
    }
}

Following are the facts involved in above code:

  1. The query.Lists statement says to get all list with template ID as 105
  2. The query.ViewFields states to return the fields Title and FirstName
  3. The Title field represents LastName field

Following is the result on executing the above code:

This summarizes the article on SPDataQuery.

References

Summary

In this article we have seen the purpose of SPDataQuery and how to use it. In real world scenarios such complex queries are required to combine multiple lists. The attachment contains the source code of the article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jean Paul V.A
Architect
United States United States
Jean Paul is a Microsoft MVP and Architect with 12+ years of experience. He is very much passionate in programming and his core skills are SharePoint, ASP.NET & C#.
 
In the academic side he do hold a BS in Computer Science & MBA. In the certification side he holds MCPD & MCTS spanning from .Net Fundamentals to SQL Server.
 
Most of the free time he will be doing technical activities like researching solutions, writing articles, resolving forum problems etc. He believes quality & satisfaction goes hand in hand.
 
You can find some of his work over here. He blogs at http://jeanpaulva.com
Follow on   Twitter

Comments and Discussions

 
QuestionHow to combine the list and document library? Pinmemberkkamalakannanmca11-Jun-13 2:23 
QuestionNeed your guidance in sharepoint2010 Pinmemberkkamalakannanmca11-Jun-13 1:38 
QuestionFetch data from multiple lists on basis of Lookup column. Pinmembermr.akhilgupta22-May-13 3:14 
QuestionSource Code Download Pinmembernalin nair15-May-13 11:05 
AnswerRe: Source Code Download PinmvpJean Paul V.A15-May-13 11:16 
GeneralRe: Source Code Download Pinmembernalin nair15-May-13 13:26 
GeneralRe: Source Code Download PinmvpJean Paul V.A15-May-13 13:53 
GeneralRe: Source Code Download Pinmembernalin nair16-May-13 1:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 15 May 2013
Article Copyright 2012 by Jean Paul V.A
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid