After researching several datagrid mechanisms for mvc3 web page, looking for powerfule filtering and sorting and paging built in, I went with actually a javascript implementation called SlickGrid.
https://github.com/mleibman/SlickGrid
There was another js grid actually that got very positive reviews also. I think there was a SO question asking for reviews but it's been a while since I did this research and I don't remember the name or why I chose this one over the others, or over .net controls. I just want to document how I used it for the future. It offers powerfully fast sorting paging and even As You Type filtering on datasets upwards of 100k I am told, and believe, although I have not had need to use it for more than an order of hundreds yet.
besides adding the source (i use slick.core, .dataview, .formatters (might be mine), and .pager) to your project, you init the grid like any other plugin. You set your Columns and options. Columns here is the important point.
<link href="@Url.Content("~/Content/slick.grid.css")" rel="stylesheet" type="text/css" /><link href="@Url.Content("~/Content/slick.pager.css")" rel="stylesheet" type="text/css" /><script src="@Url.Content("~/Content/js/jquery.event.drag-2.0.min.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.core.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.grid.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.dataview.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.formatters.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.pager.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/json2.js")" type="text/javascript"></script><script src="@Url.Content("~/Content/js/slick.mylib.js")" type="text/javascript"></script><script type="text/javascript">
var columns = [
{ id: "Name", name: "Name", field: "Name", width: 140, sortable: true },
{ id: "Email", name: "Email", field: "Email", width: 140, sortable: true }, { id: "Type", name: "Type", field: "Type", sortable: true }, { id: "Title", name: "Title", field: "Title", width: 150, sortable: true },
{ id: "RequestDate", name: "Request Date", field: "RequestDate", formatter: Slick.Formatters.Date, filter: false, sortable: true },
{ id: "DueDate", name: "Due Date", field: "DueDate", formatter: Slick.Formatters.Date, filter: false, sortable: true },
{ id: "AnotherDueDate", name: "Another Due Date", field: "AnotherDueDate", formatter: Slick.Formatters.Date, filter: false, sortable: true },
{ id: "Details", name: "Details", field: "Id", filter: false,
formatter: function (row, cell, value, columnDef, dataContext) {
return '<a href="/Home/ViewDetails/' + dataContext['Id'] + '">Details</a>'; } },
{ id: "selector", name: "Has Approval", field: "HasApproval", formatter: Slick.Formatters.Checkmark, cssClass: "centered", filter: false, sortable: true },
{ id: "HasDeadline", name: "Has Deadline", field: "HasDeadline", formatter: Slick.Formatters.Checkmark, cssClass: "centered", filter: false, sortable: true },
{ id: "AddFiles", name: "Add Files", field: "HtmlUploads", cssClass: "icons centered", filter: false,
formatter: function (row, cell, value, columnDef, dataContext) {
return '<span class="add-files" data-for="#add-files-text-' + dataContext['Id'] + '">Add</span>' +
'<span id="add-files-text-' + dataContext["Id"] + '" class="add-files-text">' +
value + '</span>'; } },
{ id: "HasCapital", name: "Has Capital", field: "HasCapital", cssClass: "centered", filter: false, sortable: true, formatter: Slick.Formatters.Checkmark },
{ id: "DownloadFiles", name: "Download", field: "HtmlDownloads", width: 80, filter: false, formatter: function (row, cell, value, columnDef, dataContext) {
return '' + value; } }
];
var options = {
enableColumnReorder: false,
forceFitColumns: true,
defaultColumnWidth: 60,
autoHeight: true,
rowHeight: 60,
showHeaderRow: true,
headerRowHeight: 37
};
</script>
<h2>Welcome to the Mike Hogg Something Manager</h2>
<div class="text clearfix">
<div class="button-cluster centered wide clearfix">
@Html.ActionLink("Submit New Something", "NewSomething", null, null, new { @class = "btn" })
@Html.ActionLink("View Calendar", "Index", "Calendar", null, new { @class = "btn" })
</div>
<br />
<p>Below is a list of submitted somethings currently in your queue. To upload a xyz or abc file, select your something and click on the "Add File" icon. You can also sort the somethings by clicking on the column header, or filter by typing in stuff</p>
</div>
<input type="hidden" id="message" value="used in getExport() above"/>
<div id="myGrid"></div>
<div id="myPager"></div>
And then in your js you set up your ajax method to get the json that populates the column, and sets the data to a placeholder control on your page. I think leiberman builds the sort event into the grid, but you need to write your own comparers, so mine is here. I think the basic filter was included but needs to be extended for your needs. I had a need for escaping HTML and found the escape map also on S.O.
var grid;
var sortcol = "RequestDate";
var sortdir = -1;
var columnFilters = {};var dataView = new Slick.Data.DataView({ inlineFilters: true });dataView.setPagingOptions({ pageSize: 8 });// my freshmen filter
function filter(item) {
for (var columnId in columnFilters) {
if (columnId !== undefined && columnFilters[columnId] !== "") {
var c = grid.getColumns()[grid.getColumnIndex(columnId)];
//if (item[c.field] != columnFilters[columnId]) { var field = item[c.field], myfilter = columnFilters[columnId];
if (field != null && myfilter != null) {
if (field.toString().toLowerCase().indexOf(myfilter.toString().toLowerCase()) == -1) {
return false;
}
} else { return field == myfilter; }
}
}
return true;
}
// my freshmen attempt at comparer (for SORT)
function comparer(a, b) {
var x = isNaN(a[sortcol]) ? a[sortcol].toLowerCase() : a[sortcol];
var y = isNaN(b[sortcol]) ? b[sortcol].toLowerCase() : b[sortcol];
if (x == null || x == "") {
if (y == null || y == "") {
return 0;
}
else return -1;
}
else if (y == null || y == "") return 1;
else return (x == y ? 0 : (x > y ? 1 : -1));
}
var entityMap = {
"&": "&",
"<": "<",
">": ">",
'"': '"',
"'": ''',
"/": '/'
};
function escapeHtml(string) {
return String(string).replace(/[&<>"'\/]/g, function (s) {
return entityMap[s];
});
}
/// ADDITIONAL
We had some cute image links for a popup menu in one of the cells but as you scroll and page through the grid, they needed to be recreated as this grid implementation actually dropped and added rows on and off the dom on the fly, so LoadButtons ...
// for those pencil icon popup menus for Add Files
function loadButtons() {
$(".add-files-text").dialog({
autoOpen: false,
title: "Add Files"
});
$(".add-files").button({
icons: { primary: 'ui-icon-pencil' },
text: false
});
$(".add-files").click(function () {
$($(this).attr("data-for")).dialog("open");return false;
});
}
// adds those filters to each column, special here to not add for certain columns, extended by 'filter:'
function updateHeaderRow() {
for (var i = 0; i < columns.length; i++) {
var header = grid.getHeaderRowColumn(columns[i].id);
$(header).empty();
if (columns[i].id !== "selector" && columns[i].filter != false) {
$("<input type='text'>")
.data("columnId", columns[i].id)
.val(columnFilters[columns[i].id])
.appendTo(header);
} else {
$("<div><span style='height:39px;display:block;'></span></div>").appendTo(header); }
}
if (grid.getOptions()["excelExport"] == true) {
// add Excel button
var header = grid.getHeaderRowColumn(columns[columns.length - 1].id);
$(header).empty();
$("<input type='image' src='../Content/img/Excel-icon.png' title='Export to Excel' onclick='getExport();'/>").appendTo(header); }
}
function getExport() {
$.ajax({
url: '/Home/DeploymentList',
type: "post",
data: JSON.stringify(dataView.getFilteredRows()),
dataType: "json",
contentType: "application/json; charset=utf-8",
success: function (result) { getCSVFileFromFormPost(result); }, // ajax post json data which server can read automatically, responds with a string, which ajax then POSTs inside a FORM, to which the server adds headers and returns, so browser can treat it as a download (save/open) error: function (xhr, textStatus, errorThrown) {
$("#message").html('Error occurred, ReadyState: ' + xhr.readyState + '; textStatus: ' + textStatus + '; ' + errorThrown);
}
});
return false;
}
function getCSVFileFromFormPost(result) {
$('<form action="/Home/GetCSVFile" method="post"><input type="hidden" name="csv" id="csv" value="' + escapeHtml(result) + '" /></form>').appendTo("body").submit();}
The Export to CSV function was a neat one, since we could send the dataset back to the server in json easily, and in MVC we could set up an Action to Deserialize that same json to a List of Models if we lined everything up right.
And "the call"...
$(function () {
$.post('/Home/GetDepData', function (data, textstatus) {
grid = new Slick.Grid("#myGrid", dataView, columns, options);var pager = new Slick.Controls.Pager(dataView, grid, $("#myPager")); grid.onSort.subscribe(function (e, args) {
sortdir = args.sortAsc ? 1 : -1;
sortcol = args.sortCol.field;
if ($.browser.msie && $.browser.version <= 8) {
// using temporary Object.prototype.toString override
// more limited and does lexicographic sort only by default, but can be much faster
dataView.fastSort(sortcol, args.sortAsc);
} else {
// using native sort with comparer
// preferred method but can be very slow in IE with huge datasets
dataView.sort(comparer, args.sortAsc);
}
});
// wire up model events to drive the grid
dataView.onRowCountChanged.subscribe(function (e, args) {
grid.updateRowCount();
grid.render();
loadButtons();// when filtered to fewer rows than before and no rows were changed
});
dataView.onRowsChanged.subscribe(function (e, args) {
grid.invalidateRows(args.rows);
grid.render();
loadButtons();// when filtered or paged
});
dataView.onPagingInfoChanged.subscribe(function (e, pagingInfo) {
var isLastPage = pagingInfo.pageNum == pagingInfo.totalPages - 1;
var enableAddRow = isLastPage || pagingInfo.pageSize == 0;
var options = grid.getOptions();
});
$(grid.getHeaderRow()).delegate(":input", "change keyup", function (e) {
columnFilters[$(this).data("columnId")] = $.trim($(this).val()); dataView.refresh();
});
// initialize the model after all the events have been hooked up
dataView.beginUpdate();
dataView.setItems(data, "Id");// Id capital I to match my unique model id property
dataView.setFilter(filter);
dataView.endUpdate();
updateHeaderRow();
loadButtons();
}, "json");
});
At the end of the included slick.formatters.js I added a couple of my own, the javascript time function being a keeper...
...
function YesNoFormatter(row, cell, value, columnDef, dataContext) {
return value ? "Yes" : "No";
}
function CheckmarkFormatter(row, cell, value, columnDef, dataContext) {
return value ? "<img src='../Content/img/slickimages/tick.png'>" : "";
}
function DateFormatter(row, cell, value, columnDef, dataContext) {
if (value !== null) {
var d = new Date(parseInt(value.substr(6, 13)));
return (d.getMonth() + 1) + "/" + d.getDate() + "/" + d.getFullYear() + ' ' + getAMPMTime(d);
} else return "";
}
Number.prototype.pad = function (len) {
return (new Array(len + 1).join("0") + this).slice(-len); }
function getAMPMTime(d) {
var d = new Date(d);
var hour = d.getHours();
var min = d.getMinutes().pad(2);
var ap = "AM";
if (hour > 11) { ap = "PM"; }
if (hour > 12) { hour = hour - 12; }
if (hour == 0) { hour = 12; }
if (hour == 12 && min == 0 && ap == "AM") return '';
return hour + ':' + min + ' ' + ap;
}