Paging, Sorting does not maintain the Reordered columns
Paging, Sorting does not maintain the Reordered columns
Hi,
I have used Datatables with ASP.NET MVC for features such as Paging, Sorting, Filtering and Column Reordering.
Problem: The reordering alone is working fine, but when I change the page by clicking the paging buttons or the sort order by clicking the column names in the header, the column order is reset. If I initially have the column order as [1,3,0,2,4,5,6,7], the header columns show the correct order, but the data rows are being reset to [0,1,2,3,4,5,6,7] after paging/sorting.
The code that I have written is as given below.
JS:
var oReviews = $('#tbReports').DataTable({
"serverSide": true,
"sAjaxSource": "/Reports/GetReportData",
"processing": true,
"sDom": '<"top"lt><"bottom"i>r<"bottom"p><"clear">',
"aoColumns": [
{ "sName": "ReviewID" },
{ "sName": "Title" },
{ "sName": "CreatorName" },
{ "sName": "Reviewer" },
{ "sName": "Type"},
{ "sName": "RequestedStartDate" },
{ "sName": "RequestedDueDate" },
{ "sName": "WordCount" },
{ "sName": "Score" },
{ "sName": "SourceLanguageDescription" },
{ "sName": "TargetLanguageDescription" },
{ "sName": "StatusDescription" }
],
"colReorder": true,
"stateSave": true,
"stateSaveCallback": function (settings, data) {
$.ajax({
url: "/reports/savereorderedcolumns",
data: JSON.stringify(data),
dataType: "json",
type: "POST",
contentType: 'application/json; charset=utf-8'
});
},
"stateLoadCallback": function (settings) {
var o;
$.ajax({
url: "/reports/loadreorderedcolumns",
async: false,
dataType: "json",
type: "POST",
success: function (json) {
o = json;
}
});
return o;
}
});
C#(controller):
public class ReportsController : Controller
{
public ActionResult ReviewReports()
{
return View();
}
public ActionResult GetReportData(DataTableParamModel param, string title)
{
var data = //Populating data from DB here. removed the code
var result = from r in temp
select new[] { //Convert.ToString( ++i) + ".",
Convert.ToString(r.ReviewID),
r.Title,
r.CreatorName,
r.Reviewer,
r.Type.ToString().ToLower() == "file" || r.Type.ToString().ToLower() == "legacyfile" ? "File" : "Online",
r.RequestedStartDate.ToShortDateString(),
r.RequestedDueDate.ToShortDateString(),
r.WordCount.ToString(),
r.Score.ToString(),
r.SourceLanguageDescription,
r.TargetLanguageDescription,
r.StatusDescription
};
totalDisplayRecords = data.Total;
return Json(
new
{
sEcho = param.sEcho,
iTotalRecords = totalRecords,
iTotalDisplayRecords = totalDisplayRecords,
aaData = result
},
JsonRequestBehavior.AllowGet);
}
public void SaveReorderedColumns(GridState data)
{
//save re-ordered columns to the DB
}
public ActionResult LoadReorderedColumns()
{
//Simulating the DB load of the grid state.
//the structure of the GridState class is as is given here -->
GridState data = new GridState();
data.ColReorder = new int[12];
data.ColReorder[0] = 4;
data.ColReorder[1] = 1;
data.ColReorder[2] = 0;
data.ColReorder[3] = 3;
data.ColReorder[4] = 2;
data.ColReorder[5] = 5;
data.ColReorder[6] = 7;
data.ColReorder[7] = 6;
data.ColReorder[8] = 8;
data.ColReorder[9] = 9;
data.ColReorder[10] = 10;
data.ColReorder[11] = 11;
data.columns = new ColumnSearchInformation[12];
Search search = new Search();
search.caseInsensitive = true;
search.search = string.Empty;
search.regex = false;
search.smart = true;
data.search = search;
var order = from r in getTempList()
select new[] { (object)r.columnIndex, r.order };
data.order = order.ToArray<object>();
for (int i = 0; i < data.columns.Length; i++)
{
data.columns[i] = new ColumnSearchInformation();
data.columns[i].search = search;
data.columns[i].visible = true;
}
data.length = 10;
data.start = 0;
data.time = Convert.ToInt64(DateTime.Now.AddDays(5).ToString("yyyyMMddHHmmssffff"));
// return Json(data, JsonRequestBehavior.AllowGet);
return Json(
new
{
ColReorder = data.ColReorder,
length = data.length,
start = data.start,
time = data.time,
order = data.order,
search = search,
columns = data.columns
},
JsonRequestBehavior.AllowGet);
}
public List<OrderInformation> getTempList()
{
List<OrderInformation> t = new List<OrderInformation>();
OrderInformation t1 = new OrderInformation();
t1.columnIndex = 0;
t1.order = "asc";
t.Add(t1);
return t;
}
}
public class OrderInformation
{
public int columnIndex;
public string order;
}
public class GridState
{
public int[] ColReorder { get; set; }
public int length { get; set; }
public int start { get; set; }
public long time { get; set; }
public object[] order { get; set; }
//public OrderInformation[] order { get; set; }
public Search search { get; set; }
public ColumnSearchInformation[] columns { get; set; }
}
public class Search
{
public string search { get; set; }
public bool smart { get; set; }
public bool regex { get; set; }
public bool caseInsensitive { get; set; }
}
public class ColumnSearchInformation
{
public Search search { get; set; }
public bool visible { get; set; }
}
}
Regards,
Aniket
Answers
The GridState class is based on the structure provided here
https://datatables.net/reference/option/stateSaveCallback
The ColReorder property is not included on that page but I have included it in the GridState class as it was needed by the script.