SharePoint Search with Pagination

Slalom Consultant Maarten Sundman

Slalom Consultant Maarten Sundman specializes in .NET, SharePoint, and Silverlight solutions and has experience in the Financial Services, Software, and Utilities and Energy sectors.

by Maarten Sundman

This is an example class for showing how to search SharePoint with paging support in the API. This function accepts a string containing the standard SharePoint SQL style search query:

ie: SELECT TOP 50 AccountName, Size, Rank, Path, Title, Description, Write FROM portal..scope() WHERE “scope” = ‘People’ AND CONTAINS (“LastName”,'”Doe”‘)   ORDER BY “AccountName” DESC

To page you can use the optional int rowCount and page overloads. Whenever page is specified it judges the start row as rowCount*page. So page 10 begins at row 500 if rowCount is 50.

Paged Search Example


    ///
<summary> /// search sharepoint using a predefined search query
 /// </summary>
    /// SQL like sharepoint search query
    /// SPSite to run against
    /// rowCount”>Optional: row limit
    /// Optional: page index
    ///
    public DataSet DefinedQuery(string query, SPSite site, int rowCount = 50, int page = 0)
    {
    DataSet dt = new DataSet();
    ServerContext srvContext = ServerContext.GetContext(site);
    int startRow = 0;
    if (page > 0)
    {
    startRow = page*rowCount;
    rowCount = rowCount * page;
    }
    //run query and pass values to allow for pagination
    using (FullTextSqlQuery runQry = new FullTextSqlQuery(srvContext)
    {
    ResultTypes = ResultType.RelevantResults,
    QueryText = query,
    RowLimit = rowCount,
    StartRow = startRow
    })
    {
    try
    {
    ResultTableCollection results = runQry.Execute();
    ResultTable resultTbl = results[ResultType.RelevantResults];
    //verify we have at least one result to work with
    if (resultTbl.RowCount >= 1)
    {
    //pull results in to our datatable and dataset
    DataTable tbl = dt.Tables.Add();
    tbl.Load(resultTbl);
    }
    resultTbl.Dispose();
    }
    catch (Exception ex)
    {
    string msg = ex.Message;
    }
    }
    return dt;
    }

– Maarten

4 Responses to SharePoint Search with Pagination

  1. Martin says:

    Hi. I have problems to sort people search results descending by LastName property. SharePoint Search 2010 only seems able to sort the results beginning with a-zA-Z. The Swedish letters å,ä,ö doesn´t work. ‘Å’ and ‘Ä’ are treated as ‘A’ and ‘Ö’ as ‘O’. I can´t find any solutions to this when searching around the internet.

    Do you have any ideas to share?

    • Maarten Sundman says:

      There are a few ways I can think of off the top of my head which I’ll list here in no particular order.

      First (simplest) – Modify the XSL of the search core results main web part. This would be the easiest method, however it has one drawback the sorting only applies to the current page of the result set. More details on how to go about this can be found here: http://blogs.perficient.com/microsoft/2008/12/how-to-group-search-results-by-a-property-value-in-sharepoint/ the example they have is with grouping, but it’s the same concept.

      Second (more complex) – You can use the method I have above, but use a custom linq sort to do the alphabet. Now, I haven’t had to do a custom sort using an alternative alphabet key so this example here can probably be optimized a lot by someone who has had to do that before (by the way I’ll probably re-post this code sample as a separate blog post). I also don’t know the swedish alphabet so please replace the numbers below with the correct ones 🙂 :

      public static DataTable SortBySwedishAlphabet(DataTable table, string SortByColumn, SortDirection direction)
      {
      IEnumerable q = from row in table.AsEnumerable() orderby
      //get the first character of the sort by column’s value
      ConvertCharacter(row[SortByColumn].ToString().Substring(0,1)) select row;
      if(direction == “DESC”)
      q = q.Reverse();
      return q.CopyToDataTable();
      }

      public static int ConvertCharacter(string Value)
      {
      string r = Value.ToUpper();
      r == “A” ? 0 :
      r == “B” ? 1 :
      r == “C” ? 2 :
      r == “D” ? 3 :
      r == “E” ? 4 :
      r == “F” ? 5 :
      r == “G” ? 6 :
      r == “H” ? 7 :
      r == “I” ? 8 :
      r == “J” ? 9 :
      r == “K” ? 10 :
      r == “L” ? 11 :
      r == “M” ? 12 :
      r == “N” ? 13 :
      r == “O” ? 14 :
      r == “P” ? 15 :
      r == “Q” ? 16 :
      r == “R” ? 17 :
      r == “S” ? 18 :
      r == “T” ? 19 :
      r == “U” ? 20 :
      r == “V” ? 21 :
      r == “W” ? 22 :
      r == “X” ? 23 :
      r == “Y” ? 24 :
      r == “Z” ? 25 :
      r == “Å” ? 26 :
      r == “Ä” ? 27 :
      r == “Ö” ? 28 :
      //handle any unexpected characters by putting them at the end of the result set
      29;
      return r
      }

  2. Martin says:

    Thanks for replying. I solved it in the following way: converted the resultset to a collection of ADPersons and apply sorting and paging.

    private IEnumerable GetResultAsPaging(IEnumerable results)
    {
    return results.
    OrderBy(p => p.LastName).
    Skip(paging.Skip).
    Take(PageSize);
    }

    • Maarten Sundman says:

      Awesome 🙂 I’m surprised it came up with the right order by when sharepoint is doing almost the same thing internally and was messing up.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: