.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> SharePoint >> Post New Resource Bookmark and Share   

 Subscribe to Articles

SPSiteDataQuery with Example

Posted By:Jean Paul       Posted Date: February 19, 2013    Points: 200    Category: SharePoint    URL: http://www.dotnetspark.com  

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


In this article we can have a closer look on the SPSiteDataQuery using the 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 2 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 2 Grid Views

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



SPDataQuery in Action Getting Data from multiple lists of same type

Here we are creating 2 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

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx
http://msdn.microsoft.com/en-us/library/ee539975.aspx

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.


 Subscribe to Articles

     

Further Readings:

    Responses

    No response found. Be the first to respond this post

    Post Comment

    You must Sign In To post reply
    Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend