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.

A Utility Method to Run stored procedures and Get results in a Data Table in C# ASP.NET


Hi,

I have just came into a problem where I need to execute many different stored procedures which may output in different ways. Instead of writing seperate codes for each of the stored procedures I build a utility method which will take the stored procedure name and the parameter list as input and it will return a Datatable objec populated as result.

Now we will go through the code:

//This procedure takes Stored procedure name and a Arraylist of parameters as argument.
//The array list is a colection of SqlParameter type of onjects
//I will explain it later

public DataTable GetData(string StoreProcName,ArrayList parameters)
{
SqlConnection _sqlConn = null;
DataTable _dt = new DataTable();
SqlDataAdapter _sqDa = null;

try
{

if (null == _connection)  //I have stored the connection string in this variable
{
throw new Exception(“Connection Null”);
}

//Open a new connection
_sqlConn = new SqlConnection(_connection);

_sqlConn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = _sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName; //Specify the stored procedure name

//If parameter list is non null the fill the parameter data for this
//Stored procedure.

if (null != parameters)
{
foreach (Object obj in parameters)
{
cmd.Parameters.Add((SqlParameter)obj);
}
}

//Open a new SQL Data Adapter type of object to populate the data table

_sqDa = new SqlDataAdapter(cmd);
_sqDa.Fill(_dt); //Fill the data table

_sqlConn.Close(); //Close the connection

return _dt;
}
catch (Exception ex)
{
return null;
}
finally
{
if (ConnectionState.Open == _sqlConn.State)
{
_sqlConn.Close();
}
}
}

Now I will let you know how to call the stored procedure.

ArrayList _parameterList = new ArrayList(); //Declare a Array List

SqlParameter parameter1 = new SqlParameter(); //Declare a SQL Parameter object

parameter1.ParameterName = “@fisrtinput”; // Give the parameter name
parameter1.SqlDbType = SqlDbType.VarChar;     // Give the data type
parameter1.Value = “First Value”; //Give the value
_parameterList.Add(parameter1); // Add it to the array list

SqlParameter parameter2 = new SqlParameter(); // Declare a second variable
parameter2.ParameterName = “@secondinput”; //Populate as before
parameter2.SqlDbType = SqlDbType.VarChar;
parameter2.Value = “Second_Value”;
_parameterList.Add(parameter2); //Add it to the Array list

Now the Array list has been populated. We will just call the Method with the Array List.

DataTable _dt = GetData(“PROCEDURE_NAME”,  _parameterList);

Now the data table is populated. You can work with that as you like.

Happy Coding 🙂