DataTables serverside processing using SharePoint 2010 search service plus Reformatting columns

ahmedgadir

My SharePoint search started to return a huge number of records back so I decided to use the service side processing technique. I didn't see any example in the internet so I decided to publish mine.

Step 1
So here is my HTML Markup to create the page
[code]



ID
Name
Number
Supervising Partner
Billing address
Filing address
Status
Open Date
Close Date



[/code]

Step 2
And here is my Script top populate the grid from the Share Point service
[code]
function DisplayRelatedProject(QueryStr) {
var sQuery = "SCOPE:\"GADIR\" (GADIRCLIENTKEY:\"" + GADIRCLIENTNUMBER + "\")"
if ($("#OnlyOpenProjectsCheckbox").val() == "checked") {
sQuery = sQuery + " AND (GADIRProjectSMATSTATUS:\"OPEN\")\"";
}
var tableProjects = $('#Projects-datagrid').dataTable({
"fields": [{
"label": "OCG",
"name": "GADIRhasocg"
}, {
"label": "Project Name",
"name": "GADIRProjectsname"
}, {
"label": "Number",
"name": "GADIRProjectsnumber"
}, {
"label": "Supervising Partner",
"name": "GADIRProjectssuppartnerpreferredname"
}, {
"label": "Billing Atty",
"name": "GADIRProjectsbillattypreferredname"
}, {
"label": "Filing Atty",
"name": "GADIRProjectsfilattypreferredname"
}, {
"label": "Status",
"name": "GADIRProjectsmatstatus"
}, {
"label": "Open Date",
"name": "GADIRmatopendate"
}, {
"label": "Close Date",
"name": "GADIRmatclosedate"
}],
"bProcessing": true,
"bFilter": false,
"aaSorting": [
[7, "desc"]
],
"sDom": "Tfrtip",
"sPaginationType": "full_numbers",
"oTableTools": {},
"bServerSide": true,
"sAjaxSource": "/_Layouts/GADIR.ClientsProjects/ProjectsData.aspx",
"aoColumns": [{
"mData": "GADIRhasocg"
}, {
"mData": "GADIRProjectsname"
}, {
"mData": "GADIRProjectsnumber"
}, {
"mData": "GADIRProjectssuppartnerpreferredname"
}, {
"mData": "GADIRProjectsbillattypreferredname"
}, {
"mData": "GADIRProjectsfilattypreferredname"
}, {
"mData": "GADIRProjectsmatstatus"
}, {
"mData": "GADIRmatopendate"
}, {
"mData": "GADIRmatclosedate"
}],
"aoColumnDefs": [{
"fnRender": function (oObj) {
return hasOCG(oObj.aData.GADIRhasocg, oObj.aData.GADIRProjectsmatstatus);
},
"aTargets": [0]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectsname, "" + oObj.aData.GADIRProjectsname + "");
},
"aTargets": [1]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectsnumber, oObj.aData.GADIRProjectsnumber);
},
"aTargets": [2]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectssuppartnerpreferredname, "" + oObj.aData.GADIRProjectssuppartnerpreferredname + "");
},
"aTargets": [3]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectsbillattypreferredname, "" + oObj.aData.GADIRProjectsbillattypreferredname + "");
},
"aTargets": [4]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectsfilattypreferredname, "" + oObj.aData.GADIRProjectsfilattypreferredname + "");
},
"aTargets": [5]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRProjectsmatstatus, oObj.aData.GADIRProjectsmatstatus);
},
"aTargets": [6]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRmatopendate, oObj.aData.GADIRmatopendate);
},
"aTargets": [7]
}, {
"fnRender": function (oObj) {
return CheckForNull(oObj.aData.GADIRmatclosedate, oObj.aData.GADIRmatclosedate);
},
"aTargets": [8]
}],
"fnServerParams": function (aoData) {
aoData.push({
"name": "iQueryText",
"value": sQuery
});
}
});
}
[/code]

ahmedgadir

Step 3
If you notice, I am calling an aspx page from you grid in line("sAjaxSource": "/_Layouts/GADIR.ClientsProjects/ProjectsData.aspx",). This page is going to only contain JSON (Please make sure there isn't any HTML in that page)
So I created an application page that will call a method called GetProjectData(). That method is going to query the search service and convert the data to JSON and return it.
The page job is to take the JSON and display it.

[code]
protected void Page_Load(object sender, EventArgs e)
{

JavaScriptSerializer ser = new JavaScriptSerializer();
int iDisplayLength = Convert.ToInt32(HttpContext.Current.Request["iDisplayLength"]);
int iDisplayStart= Convert.ToInt32(HttpContext.Current.Request["iDisplayStart"]);
var data = ExternalServiceCalls.GetProjectData();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Write(data);
}
[/code]
Step 4
Here is my C# code querying the Share Point 2010 search service
Notice, I added a sorting logic and filtering logic. All the parameters that you will need are going to be in the HttpContext.Current.Request object

ahmedgadir

[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]