Search This Blog

Monday, April 4, 2011

Issues with Columns Name used for quering using SPSiteDataQuery

Issues with Columns Name used in SPSiteDataQuery

One of the major issues I found working with SPSiteDataQuery was that of different field’s internal names as site columns and List columns. In one of my projects, I need to build a dynamic query based on the site columns specific to the selected Content Types. I was hanged with the issue of not getting any results and while digging down the code, I found that the issue lies in column internal names which are different when the column is attached to list/library.

 In order to explain this scenario, let us consider an example in which we will create a content type and add a Site Column named “Test-Column” in it and then we will add that content type to a list. Now if you look at the internal names of the column in List and in content types, you will find that both are different.


Column Name
Internal Name in Site Column
Internal Name in List Field
Test-Column
Test-Column
Test_x002d_Column
~!@#$%^&*()_-

_x007E__x0021__x0040__x0023
__x0024__x0025__x005E
__x0026__x002A__x0028__x0029__-
_x007E__x0021__x0040__x0023
__x0024__x0025__x005E
__x0026__x002A__x0028__x0029___x002d_


I was looking at some generic static value that we can use as field names while searching with SPSiteDataQuery. I found the solution in the GUID field which remains static for the field irrespective of the location where field is added. But the question arises, does CAML supports GUID as FieldRef. The answer is YES. So I changed the query and added GUID instead of field static or internal names and the issue was resolved. J

public DataTable GetResults()
        {
            SPSiteDataQuery objSPSiteDataQuery = null;
            SPWeb objSPWeb = null;
            DataTable objDataTable = null;
            String strSearchQuery = String.Empty;
            try
            {

              SPQuery query = new SPQuery();
              objSPWeb = SPContext.Current.Web;
              objSPSiteDataQuery = new SPSiteDataQuery();

              strSearchQuery = "<Query><Where><Eq><FieldRef Name=\"Test_x002d_Column\" /><Value Type=\"Text\">Test</Value></Eq></Where></Query>";

//Instead use Guid as FieldRefName
              strSearchQuery = "<Query><Where><Eq><FieldRef Name=\"73ef14b1-13a9-416b-a9b5-ececa2b0604c\" /><Value Type=\"Text\">Test</Value></Eq></Where></Query>";


              objSPSiteDataQuery.ViewFields = "<FieldRef Name=\"LinkFilename\"/>" +
                                                 "<FieldRef Name=\"Created\" />" +
                                                 "<FieldRef Name=\"Modified\"/>" +
                                                 "<FieldRef Name=\"Editor\"/>";

               objSPSiteDataQuery.Lists = "<Lists ServerTemplate=\"101\" Hidden=\"FALSE\" MaxListsLimit=\"0\"/>";//it will query only document libraries

              objSPSiteDataQuery.RowLimit = 2000;
              objSPSiteDataQuery.Webs = "<Webs Scope=\"Recursive\"/>";              
              objSPSiteDataQuery.Query = strSearchQuery;
              objDataTable = objSPWeb.GetSiteData(objSPSiteDataQuery);
            }
            catch (Exception Ex)
            {
                //throw with custom exception
            }
            return objDataTable;
        }

No comments:

Post a Comment