0

I have the following code in my SPData class:

public static DataTable GetDataTableFromMultipleSpLists(List<string> lists, List<string> fields, string camlQuery)
    {
        var web = SPContext.Current.Site.RootWeb;
        DataTable listItemsTable1 = null;

        var sbListsQuery = new StringBuilder();
        if (lists != null)
        {
            sbListsQuery.Append("<Lists MaxListLimit=\"0\">");
            foreach (var list in lists)
            {
                var spList = web.Lists[list];
                if (spList != null)
                {
                    sbListsQuery.Append("<List ID=\"" + spList.ID + "\" />");
                }
            }
            sbListsQuery.Append("</Lists>");
        }

        var sbFields = new StringBuilder();
        if (fields != null)
        {
            foreach (var field in fields)
            {
                sbFields.Append(string.Format("<FieldRef Name=\"{0}\" />", field));
            }
        }

        var clqi = new CrossListQueryInfo
        {
            Lists = sbListsQuery.ToString(),
            Webs = "<Webs Scope=\"\" />",
            ViewFields = sbFields.ToString(),
            UseCache = true,
            RowLimit = 1000,
            Query = camlQuery
        };

        var clqc = new CrossListQueryCache(clqi);
        listItemsTable1 = clqc.GetSiteData(SPContext.Current.Site, CrossListQueryCache.ContextUrl());

        return listItemsTable1;
    }

Then I call it with the following:

var data = SPData.GetDataTableFromMultipleSpLists(new List<string>
        {
            "List1",
            "List2",
            "List3"
        }, 
        new List<string>
        {
            "Field1",
            "Field2",
            "Field3",
            "Field4",
            "Field5"
        }, 
        GetCamlQuery());

        rprPopularSearches.DataSource = Resource.ToList(data);
        rprPopularSearches.DataBind();

GetCamlQuery() is as follows:

private string GetCamlQuery()
    {
        var oSb = new StringBuilder();

        oSb.Append("<Where>");
        oSb.Append("     <And>");
        oSb.Append("          <Eq>");
        oSb.Append("               <FieldRef Name=\"Active\" />");
        oSb.Append("               <Value Type=\"Boolean\">1</Value>");
        oSb.Append("          </Eq>");
        oSb.Append("          <Eq>");
        oSb.Append("               <FieldRef Name=\"Category\" />");
        oSb.Append("               <Value Type=\"Text\">MyCategory</Value>");
        oSb.Append("          </Eq>");
        oSb.Append("     </And>");
        oSb.Append("</Where>");

        return oSb.ToString();
    }

Within Resource.ToList() is the following:

public static List<Resource> ToList(DataTable collection)
    {
        var result = new List<Resource>();

        if (collection != null)
        {

            foreach (DataRow item in collection.Rows)
            {
                var resource = new Resource();

                if (item.Table.Columns.Contains("Field1") && item["Field1"] != DBNull.Value)
                {
                    resource.Field2 = new SPFieldUrlValue(item["Field2"].ToString()).Url;
                    resource.Field1 = new SPFieldUrlValue(item["Field2"].ToString()).Description;
                }

                if (resource.Field2 != null && resource.Field1.Contains("http") && item.Table.Columns.Contains("Field1") && item["Field1"] != DBNull.Value)
                {
                    var field1 = item["Field1"].ToString();
                    if (!string.IsNullOrEmpty(field1))
                    {
                        resource.Field1 = item["Field1"].ToString();
                    }
                }

                if (item.Table.Columns.Contains("Field3") && item["Field3"] != DBNull.Value)
                {
                    resource.Field3 = item["Field3"].ToString();
                }
if (item.Table.Columns.Contains("Field4") && item["Field4"] != DBNull.Value)
                {
                    resource.Field4 = DateTime.Parse((item["Field4"].ToString()));
                }

                if (item.Table.Columns.Contains("Field5") && item["Field5"] != DBNull.Value)
                {
                    resource.Field5 = DateTime.Parse((item["Field5"].ToString()));
                }

                result.Add(resource);
            }

        }
        else
        {
            // Logging, etc.
        }

        return result;
    }

List1 and List2 data is retrieved fine. List3 has items with MyCategory assigned just as the items in the other list.

List1 and List2 both utilize the same "Resource" content type for most of the fields. List3 uses a different content type but contains the same named fields as the other two lists, so I should be able to join them.

The only difference other than that, I could find, was List3 does not contain an "Active" field which is being used in the CAML query; however, I removed that from the CAML and still no items from List3.

Let me know if there's any other code missed that I need to share. I've tried combing through for typos and other gotchas, scoured Google for answers and cannot even find proper documentation on these classes for querying multiple lists. There are no exceptions or errors thrown as well.

  • FYI: I am using SharePoint 2010 – Michael Sep 12 '19 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.