Thursday, August 27, 2009

SPSiteDataQuery in SharePoint

If we want to search some record(s) within a SPList, then SPQuery is sufficient.

But consider a scenarion, where we don't know SPList name or we want to fire a query at SPWeb (Site) level, then we have to use SPSiteDataQuery.

Syntax wise SPSiteDataQuery is same as SPQuery.
Following code snippet gives a rough idea about SPSiteDataQuery usage:



SPWeb oWebsite = SPContext.Current.Web;
SPSiteDataQuery oQuery = new SPSiteDataQuery();

string s1 = "avinash";
string s2 = "IsAdmin";

StringBuilder strb = new StringBuilder();
strb.Append("<Where><And>");
strb.AppendFormat("<Eq><FieldRef Name='LoginName' /><Value Type='Text'>{0}</Value></Eq>", s1);
strb.AppendFormat("<Eq><FieldRef Name='{0}' /><Value Type='Boolean'>1</Value></Eq>", s2);
strb.Append("</And></Where>");

oQuery.Query = strb.ToString();
oQuery.Lists = "<Lists ServerTemplate=\"100\" />";
//oQuery.ViewFields = "<FieldRef Name=\"Title\" />";

DataTable dtResults = oWebsite.GetSiteData(oQuery);

if(dtResults.Rows.Count > 0)
{
//There are some records


//dtResults.Columns.Count --- 3 --- data type : int

//dtResults.Columns[0].ColumnName --- "ListId" --- data type : string
//dtResults.Columns[1].ColumnName --- "WebId" --- data type : string
//dtResults.Columns[2].ColumnName --- "ID" --- data type : string


//In case of 1 record found
//dtResults.Rows.Count --- 1 --- data type : int

//dtResults.Rows[0][0] --- "{some GUID value}" --- data type : object {string}
//dtResults.Rows[0][1] --- "{some GUID value}" --- data type : object {string}
//dtResults.Rows[0][2] --- "6" --- data type : object {string}



//To open the SPList that contains our desired record:
string stringGuid
= dtResults.Rows[0][0].ToString().Replace("{", "").Replace("}", "");
Guid guidObject = new Guid(stringGuid);
SPList spList = oWebsite.Lists[guidObject];
}
else
{
//There is no record
}


In the above code I am looking for record that contains LoginName (SPColumn name) as "avinash" and IsAdmin (SPColumn name) is set to true.



Quick re-cap:
1) Open SPWeb
2) Build query
3) Create SPSiteDataQuery object
4) Set following properties of SPSiteDataQuery's object -
a) Query
b) Lists
c) ViewFields
5) Get DataTable by executing query on SPWeb
6) Check number of Rows in DataTable
7) If record(s) found, then get GUID of SPList
8) Get SPList based on GUID
9) Do required operation on SPList



Following are the ServerTemplate IDs for List:



100 Generic list, 101 Document library, 102 Survey, 103 Links list, 104 Announcements list, 105 Contacts list, 106 Events list, 107 Tasks list, 108 Discussion board, 109 Picture library, 110 Data sources, 111 Site template gallery, 112 User Information list, 113 Web Part gallery, 114 List template gallery, 115 XML Form library, 116 Master pages gallery, 117 No-Code Workflows, 118 Custom Workflow Process, 119 Wiki Page library, 120 Custom grid for a list, 130 Data Connection library, 140 Workflow History, 150 Gantt Tasks list, 200 Meeting Series list, 201 Meeting Agenda list, 202 Meeting Attendees list, 204 Meeting Decisions list, 207 Meeting Objectives list, 210 Meeting text box, 211 Meeting Things To Bring list, 212 Meeting Workspace Pages list, 300 Portal Sites list, 301 Blog Posts list, 302 Blog Comments list, 303 Blog Categories list, 850 Page Library, 1100 Issue tracking, 1200 Administrator tasks list, 2002 Personal document library, 2003 Private document library



Reference Links:

Server Template Numbers
SPSiteDataQuery 1
SPSiteDataQuery 1
SPSiteDataQuery 1

1 comment:

Kallan said...

Excellent post. You explain every step very clearly and your topics are allways interesting. Thanks Heaps.

Google