How to Fetch all results from SharePoint Search using Dot Net managed CSOM


As always, I was doing some POC on the above mentioned topic in SharePoint 2013 Azure environment and wanted to share the results with you all.

Problem: Use SharePoint Search to retrieve all results for a given query through C# Dot net managed Client side object model (CSOM) and load a DataTable with the results for further use.

Solution: There are many solutions to this problem but for my case I only have access to a machine which is in the same domain as my SharePoint server and I am not allowed to run stuff on the server, so Server Side Object Model won’t work.

In this POC I have created one sample console application and before doing any other stuff added the following dlls:

  1. Microsoft.SharePoint.Client
  2. Microsoft.SharePoint.Client.Runtime
  3. Microsoft.SharePoint.Client.Search

Last one you have to pull from the ISAPI folder (C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI) in your SharePoint installation or you can borrow from some one.

Once you have added those required dlls, then you can start your project.

Note: For basic introductions you can check the following posts:

Using the SharePoint 2013 search Query APIs

SharePoint 2013: Query Search with the Managed Client Object Model

By default search will return only 50 records and you can beef it up to 500, but to fetch all records you need to do some additional coding. Further more, I wanted to load a System.Data.DataTable object with all records which requires some extra stuff as well.

First I have created this utility method to Get the Search results:

private static ResultTable GetSearchResults(int startIndex,
ClientContext clientContext)
{
ClientResult<ResultTableCollection> results = null;
try
{
KeywordQuery keywordQuery = new KeywordQuery(clientContext);

SearchExecutor searchExecutor = new SearchExecutor(clientContext);

keywordQuery.StartRow = startIndex;//gets or sets the first row of information from the search results

//Specif the ext you want to search
keywordQuery.QueryText = “SharePoint”;

//Specify the number of rows to return, 500 is MAX
keywordQuery.RowLimit = 500;
//Specify the number of rows to return in a page, 500 is MAX
keywordQuery.RowsPerPage = 500;
//Whether to remove duplicate results or not
keywordQuery.TrimDuplicates = false;
//Specify the timeout
keywordQuery.Timeout = 10000; //10 minutes

// execute the query and load the results into a collection
results = searchExecutor.ExecuteQuery(keywordQuery);
clientContext.ExecuteQuery();

return results.Value.FirstOrDefault(v => v.TableType.Equals(KnownTableTypes.RelevantResults));
}
catch (Exception)
{
throw;
}
}

Then I use the main method to get the results like below:

static void Main(string[] args)
{
using (ClientContext clientContext = new ClientContext(“<<YOUR SERVER URL>>”))
{
#region Build Data Dable
DataTable resultDataTable = new DataTable();

DataColumn titleCol = new DataColumn(“Title”);
DataColumn pathCol = new DataColumn(“Path”);

resultDataTable.Columns.Add(titleCol);
resultDataTable.Columns.Add(pathCol);

#endregion

int currentRowIndex = 0;

//Get the first block of results
var resultTable = GetSearchResults(0, clientContext);

if (null != resultTable && resultTable.TotalRowsIncludingDuplicates > 0)
{
while (resultTable.TotalRowsIncludingDuplicates > resultDataTable.Rows.Count)
{
foreach (var resultRow in resultTable.ResultRows)
{
DataRow row = resultDataTable.NewRow();
row[“Title”] = resultRow[“Title”];
row[“Path”] = resultRow[“Path”];
resultDataTable.Rows.Add(row);
}

//Update the current row index
currentRowIndex = resultDataTable.Rows.Count;

resultTable = null;

resultTable = GetSearchResults(currentRowIndex, clientContext);

if (null != resultTable && resultTable.TotalRowsIncludingDuplicates > 0)
{
if (resultTable.RowCount <= 0)
break;
}
else
break;
}
}

Console.WriteLine(“Total Results: {0} “, resultDataTable.Rows.Count);
}
Console.ReadLine();
}

So, the above stuff will give you what you need. Hope this will help some newbie like me.

Advertisements

How to write a CAML query to find out a list of items with ID


Hi,

Few days ago I came around a problem where I have to query a SharePoint list for a bunch of items and I only have the ID for those. The ID field is a counter. I am using the WebService.GetListItems method for the query.

By googleing I came to know that I have to write the query in the following manner to fetch 1 item:

<Where>
 <Eq>
  <FieldRef Name=’ID’/>
  <Value Type=’Counter’>” + _slideid + “</Value>
 </Eq>
</Where>

Where the slide id is one of the IDs of from the slide list.

The query worked well but I wanted to extend it for n number of items. i.e. If I have a Slide ID array, I want to get the Items in one query. Here is the code snippet that does the trick:

XmlNodeList GetFilteredNodesFromSharepointBySlideID(string _tmpListName, string _tmpViewName, string[] _slideid)
        {
            XmlNodeList _tmplNodeLists = null;

            try
            { 

                XmlDocument doc = new XmlDocument();

                XmlNode query = doc.CreateNode(XmlNodeType.Element, “Query”, “”);

                String _query = “<Where>”;
                String _innerQ = “”;
                string _tempQ = “”;

                for (int i = 0; i < _slideid.Length; i++)
                {
                    _tempQ += “<Eq><FieldRef Name=’ID’/><Value Type=’Counter’>” + _slideid[i] + “</Value></Eq>”;

                    if (i >= 1)
                    {
                        _innerQ = “<Or>” + _tempQ + “</Or>”;
                        _tempQ = _innerQ;
                    }                   
                }

                _query += _tempQ + “</Where>”;

                query.InnerXml = _query;
              

                XmlNode templistItems =
                  MyWebService.GetListItems(_tmpListName, _tmpViewName, query,
                  null, null, null, null); //MyWebService is the Configured Web Service               

                doc.LoadXml(templistItems.OuterXml);

                _tmplNodeLists = doc.GetElementsByTagName(“z:row”); // Now you can return the node list as XMLNodes.

             }
            catch (Exception _e)
            {
                throw _e;
            }

            return _tmplNodeLists;
        }

 



I tried to input the slide ids 19,20,21,22,23 and the output of the CAML query is like below:

<Where>
  <Or>
   <Or>
    <Or>
     <Or>
      <Eq>
        <FieldRef Name=”ID” />
        <Value Type=”Counter”>19</Value>
       </Eq>
      <Eq>
        <FieldRef Name=”ID” />
        <Value Type=”Counter”>20</Value>
       </Eq>
     </Or>
      <Eq>
       <FieldRef Name=”ID” />
       <Value Type=”Counter”>21</Value>
      </Eq>
     </Or>
     <Eq>
      <FieldRef Name=”ID” />
      <Value Type=”Counter”>22</Value>
     </Eq>
    </Or>
    <Eq>
     <FieldRef Name=”ID” />
     <Value Type=”Counter”>23</Value>
    </Eq>
   </Or>
</Where>

I hope you can understand the utility of the script now. You can now extend it as you like.

Happy coding!