Hello fellow SharePoint developers!
If I were to ask you how many of you were faced with a performance issues while retrieving data from SharePoint? I know I was, and not once! Throughout my years of SharePoint-related custom developing I developed a great fondness for SharePoint search – performance is a treat, and the main drawback of content visibility not being instant is hardly a show stopper in most scenarios.
Today, I’d like to share with you my helper class that I wrote to ease my SharePoint search query constructions.
It basically is a static class that hosts a number of methods, main being GetSearchResultsTable. This accepts several parameters:
1) int startRow – usually should be passed 0, unless you are anticipating that there could be more than 500 results, will cover that scenario below
2) string filter – this is what you’d type into a search box in SharePoint search center, leaving empty will return you all People results (feel free to edit the code to do otherwise)
3) out bool hasMoreResults – out parameter saying that there are more results to be retrieved, what to do in such case, I will cover below
First of all, here is the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
using Microsoft.Office.Server.Search.Administration; using Microsoft.Office.Server.Search.Query; using Microsoft.SharePoint; using Microsoft.SharePoint.Administration; using System; using System.Collections.Generic; using System.Data; using System.Linq; namespace Project.SP2013.GenericClasses { public static class MySearchHelper { public class SearchSortParam { public string Property { get; set; } public bool Ascending { get; set; } public SearchSortParam(string property, bool ascending) { this.Property = property; this.Ascending = ascending; } } public static DataTable GetSearchResultsTable(int startRow, string filter, out bool hasMoreResults) { return McmSearchHelper.GetSearchResultsTable(startRow, filter, out hasMoreResults, null, null, true); } public static DataTable GetSearchResultsTable(int startRow, string filter, out bool hasMoreResults, bool trimDuplicates) { return McmSearchHelper.GetSearchResultsTable(startRow, filter, out hasMoreResults, null, null, trimDuplicates); } public static DataTable GetSearchResultsTable(int startRow, string filter, out bool hasMoreResults, List searchProperties, McmSearchHelper.SearchSortParam orderBy, bool trimDuplicates) { DataTable dataTable = null; hasMoreResults = false; bool innerHasMoreResults = false; SPSite adminSite = null; KeywordQuery keywordQuery; if (SPServiceContext.Current != null) { SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.Current); keywordQuery = new KeywordQuery(proxy); } else { adminSite = SPAdministrationWebApplication.Local.Sites[0]; keywordQuery = new KeywordQuery(adminSite); } keywordQuery.QueryText = (string.IsNullOrEmpty(filter) ? "contentclass=spspeople" : filter); keywordQuery.IsCachable = false; keywordQuery.SelectProperties.Clear(); if (searchProperties != null && searchProperties.Count > 0) { keywordQuery.SelectProperties.AddRange(searchProperties.ToArray()); } else { keywordQuery.SelectProperties.AddRange(new string[] { "FirstName", "LastName", "AboutMe", "AccountName", "Department", "JobTitle", "Path", "PreferredName", "WorkEmail", "WorkPhone", "PictureThumbnailURL", "PictureURL", "SPS-JobTitle", "Title", "SiteTitle", "ContentType", "Author", "WebId", "SiteId", "WebTemplate", "WebTemplateId", "ParentLink", "Filename", "Description" }); } keywordQuery.TrimDuplicates = trimDuplicates; keywordQuery.EnablePhonetic = true; keywordQuery.StartRow = startRow; keywordQuery.RowLimit = 500; if (orderBy != null) { keywordQuery.EnableSorting = true; keywordQuery.SortList.Add(orderBy.Property, orderBy.Ascending ? SortDirection.Ascending : SortDirection.Descending); } SearchExecutor searchExecutor = new SearchExecutor(); try { ResultTableCollection resultTableCollection = searchExecutor.ExecuteQuery(keywordQuery); IEnumerable source = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults); ResultTable resTable = null; // resultTables.FirstOrDefault(); for (int i = 0; i < source.Count(); i++) { if (source.ElementAt(i).RowCount > 0) { resTable = source.ElementAt(i); break; } } if (resTable != null && resTable.Table != null) { dataTable = resTable.Table; if (dataTable.Rows.Count == 500) { innerHasMoreResults = true; } } } catch (Exception ex) { UlsLogging.Current.LogError(UlsLogging.EventCategory.KEYWORD_SEARCH_QUERYING, ex.ToString()); } //}); if (adminSite != null) { adminSite.Dispose(); } hasMoreResults = innerHasMoreResults; return dataTable; } } } |
You will notice I am using McmUlsLogging static class that basically writes into ULS log in 14/15 hive. I have a post about it in case you are interested in the code! Here it is: SharePoint 2013 / 2010: Custom ULS Logging provider
Now, in case you will get TRUE in out parameter – you have more results waiting to be grabbed. Reason for splitting – KeywordQuery class can only return 500 items per one call!
In this case you should query your items in a while loop, looping while you have TRUE in the out parameter, and calling the GetSearchResults method with start row being incremented by 500 in each iteration, Like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
bool hasMoreResults = false; int startRow = 0; string query = "YOUR_QUERY"; DataTable dt = MySearchHelper.GetSearchResultsTable(startRow, query, out hasMoreResults); if (dt != null) { while (hasMoreResults) { startRow += 500; DataTable dtTemp = MySearchHelper.GetSearchResultsTable(startRow, query, out hasMoreResults); foreach (DataRow tempRow in dtTemp.Rows) { dt.Rows.Add(tempRow.ItemArray); } } } |
And that’s it, happy coding!