11th Oct 2013DataTables serverside processing using SharePoint 2010 search service plus Reformatting columns
[code]
public static string GetProjectData()
{
string QueryText = HttpContext.Current.Request.Params["iQueryText"];
String JsonData = String.Empty;
if (!String.IsNullOrEmpty(QueryText))
{
try
{
int iDisplayLength = 0;
int iDisplayStart = 0;
int sEcho = 0;
int.TryParse(HttpContext.Current.Request["iDisplayLength"], out iDisplayLength);
int.TryParse(HttpContext.Current.Request["iDisplayStart"], out iDisplayStart);
int.TryParse(HttpContext.Current.Request["sEcho"], out sEcho);
int iSortCol = Convert.ToInt32(HttpContext.Current.Request["iSortCol_0"]);
string sSortDir = HttpContext.Current.Request["sSortDir_0"];
int iSortingCols = Convert.ToInt32(HttpContext.Current.Request["iSortingCols"]);
string sStatus = HttpContext.Current.Request["sSearch_6"];
if (!String.IsNullOrEmpty(sStatus)) { QueryText = QueryText + " AND (PROJSTATUS:\"OPEN\")\""; }
SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy
(SPServiceContext.GetContext(SPContext.Current.Site));
KeywordQuery query = new KeywordQuery(proxy);
query.ResultsProvider = Microsoft.Office.Server.Search.Query.SearchProvider.Default;
query.QueryText = QueryText;
query.SelectProperties.Add("GADIRProjectskey");
query.SelectProperties.Add("GADIRProjectsname");
query.SelectProperties.Add("GADIRhasocg");
query.RowLimit = iDisplayLength;// int.MaxValue;
query.StartRow = iDisplayStart;
query.TrimDuplicates = false;
//Sorting
if (iSortingCols == 1)
{
if (sSortDir == "asc" && iSortCol == 0) { query.SortList.Add("GADIRhasocg", Microsoft.Office.Server.Search.Query.SortDirection.Ascending); }
if (sSortDir == "desc" && iSortCol == 0) { query.SortList.Add("GADIRhasocg", Microsoft.Office.Server.Search.Query.SortDirection.Descending); }
if (sSortDir == "asc" && iSortCol == 1) { query.SortList.Add("GADIRProjectsname", Microsoft.Office.Server.Search.Query.SortDirection.Ascending); }
if (sSortDir == "desc" && iSortCol == 1) { query.SortList.Add("GADIRProjectsname", Microsoft.Office.Server.Search.Query.SortDirection.Descending); }
}
//
query.ResultTypes |= ResultType.RelevantResults;
ResultTableCollection searchResults = query.Execute();
if (searchResults.Exists(ResultType.RelevantResults))
{
ResultTable searchResult = searchResults[ResultType.RelevantResults];
DataTable result = new DataTable();
result.TableName = "aaData";
result.Load(searchResult, LoadOption.OverwriteChanges);
JsonData = JsonConvert.SerializeObject(result, Formatting.None, new IsoDateTimeConverter() { DateTimeFormat = "MM/dd/yyyy" });
//build JSON
StringBuilder jdata = new StringBuilder();
jdata.Append("{");
//sEcho
jdata.Append("\"sEcho\":" + sEcho);
jdata.Append(",");
jdata.Append("\"iTotalRecords\":" + searchResult.TotalRows);
jdata.Append(",");
jdata.Append("\"iTotalDisplayRecords\":" + searchResult.TotalRows);
jdata.Append(",");
jdata.Append("\"aaData\":" + JsonData);
jdata.Append("}");
JsonData = jdata.ToString();
}
}
catch (Exception ex)
{
JsonData = String.Format("Error:\"{0}\"",ex.Message);
}
}
return JsonData;
}
[/code]