/// <summary>
/// Class that encapsulates most common parameters sent by DataTables plugin
/// </summary>
public class jQueryDataTableParams
{
/// <summary>
/// Request sequence number sent by DataTable,
/// same value must be returned in response
/// </summary>
public string sEcho { get; set; }
/// <summary>
/// Text used for filtering
/// </summary>
public string sSearch { get; set; }
/// <summary>
/// Number of records that should be shown in table
/// </summary>
public int iDisplayLength { get; set; }
/// <summary>
/// First record that should be shown(used for paging)
/// </summary>
public int iDisplayStart { get; set; }
/// <summary>
/// Number of columns in table
/// </summary>
public int iColumns { get; set; }
/// <summary>
/// Number of columns that are used in sorting
/// </summary>
public int iSortingCols { get; set; }
/// <summary>
/// Comma separated list of column names
/// </summary>
public string sColumns { get; set; }
Following is the method to load data for datatable. Assuming that we are going to show firstname, lastname, company and jobtitle from member table.
public ActionResult GetDataForDatatable(jQueryDataTableParams param)
{
IQueryable<Member> memberCol = myDbContext.Member
.Select(m => m.FirstName,
m.LastName,
m.Company,
m.JobTitle)
.AsQueryable();
int totalCount = memberCol.Count();
IEnumerable<Member> filteredMembers = memberCol;
if (!string.IsNullOrEmpty(param.sSearch))
{
filteredMembers = memberCol
.Where(m => m.FirstName.Contains(param.sSearch) ||
m.LastName.Contains(param.sSearch) ||
m.Company.Contains(param.sSearch) ||
m.JobTitle.Contains(param.sSearch));
}
var sortIdx = Convert.ToInt32(Request["iSortCol_0"]);
Func<Member,string> orderingFunction = (m => sortIdx == 0 ? m.FirstName :
sortIdx == 1 ? m.LastName :
sortIdx == 2 ? m.Company :
m.JobTitle);
var sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
filteredMembers = filteredMembers.OrderBy(orderingFunction);
else
filteredMembers = filteredMembers.OrderByDescending(orderingFunction);
var displayedMembers = filteredMembers
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from a in displayedMembers
select new[] { a.FirstName, a.LastName, a.Company,
a.JobTitle, a.ID.ToString() };
return Json(new
{
sEcho = param.sEcho,
iTotalRecords = totalCount,
iTotalDisplayRecords = filteredMembers.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
}
Following is how we initialize our datatable and design the HTML.
-> Reference to jQuery js, jQuery Datatable js, jQuery Datatable css
<table id="tblMember" class="table" style="width: 100%">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Company</th>
<th>Job Title</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<script>
$(document).ready(function () {
// destroy existing datatable before, initiating
if (oTable != undefined) {
oTable.fnDestroy();
};
oTable = $('#tblMember').dataTable({
"bServerSide": true,
"sAjaxSource": '@Url.Action("GetDataForDatatable","Member")',
"bProcessing": true,
"aoColumns": [
{ "sName": "FirstName" },
{ "sName": "LastName" },
{ "sName": "Company" },
{ "sName": "JobTitle" },
{
"sName": "ID",
"bSearchable": false,
"bSortable": false,
'mRender': function (data, type, row) {
return '<a href="@Url.Action("Edit", "Member")/' + row[4] + '" class="btn btn-warning btn-xs" role="button">@labels.btn_edit</a>' + ' ' +
'<button onclick="confirmDelete(' + row[4] + ');" class="btn btn-danger btn-xs">@labels.btn_delete</button>';
}
},
]
});
}
</script>
Cant you provide de code.
ReplyDeleteThanks.