Jquery DataTable multisearch paging and Sorting in MVC server side

Previously I had shown how to use Jquery DataTable in MVC and Asp.Net

MVC:

• Jquery DataTable in MVC

Asp.Net:

• jquery DataTable PlugIn in ASP.Net using C#
• jquery datatable row grouping plugin
• jQuery DataTables and ASP.NET Integration
• search jquery datatable from external textbox in asp.net
• Colspan in Jquery DataTables using C# in asp.net GridView
• Jquery DataTable in Asp.Net with server side Paging and Filtering
• Jquery DataTable in Asp.Net with server side Paging and Filtering (fast)

Index.cshtml:

  


@{
    ViewBag.Title = "Home Page";
}
<div>
    <br />
    <center><h3>Items from Item Master Filtering Sorting Server Side</h3></center>
    @*<table class="table table-striped table-bordered table-hover table-condensed">*@
    <table id="tblItems">
        <thead>
            <tr>
                <th>Part Number</th>
                <th>Specification</th>
                <th>Description</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Part Number</th>
                <th>Specification</th>
                <th>Description</th>
            </tr>
        </tfoot>
    </table>

    @* jQuery DataTables css *@
    <link href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" rel="stylesheet" />
    <link href="~/Content/DataTables/css/dataTables.jqueryui.css" rel="stylesheet" />

    @* jQuery *@

    @section Scripts{
        <script src="~/Scripts/jquery-1.12.4.js"></script>
        <script src="~/Scripts/DataTables/jquery.dataTables.js"></script>
        @*<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>*@
        <script src="~/Scripts/DataTables/dataTables.jqueryui.js"></script>
        <script src="~/Scripts/jquery-ui-1.12.1.js"></script>
        <script src="~/Scripts/jquery.dataTables.columnFilter.js"></script>

        <script type="text/javascript">

            $(document).ready(function () {

                $('#tblItems tfoot th').each(function () {
                    var title = $(this).text();
                    $(this).html('<input class="FClass" style="width:inherit;" type="text" id="' + title.replace(' ', '_') + '" placeholder="Search ' + title + '" />');
                });

                var table = $('#tblItems').DataTable({
                    "ordering": true,
                    "lengthMenu": [[100, 250, 500, -1], [100, 250, 500, "All"]],
                    "pagingType": "full_numbers",
                    "scrollY": "350px",
                    "scrollX": true,
                    "bJQueryUI": true,

                    "processing": true,
                    "serverSide": true,
                    "orderMulti": false,

                    "ajax": {
                        "url": "/home/LoadDataIndex",
                        "type": "POST",
                        "datatype": "json"
                    },

                    "columns": [
                        { "data": "PartNumber", "name": "PartNumber", "autoWidth": false },
                        { "data": "Specification", "name": "Specification", "autoWidth": true },
                        { "data": "ItemDescription", "name": "ItemDescription", "autoWidth": true }                        
                    ]
                });
                                
                table.columns().every(function () {
                    var that = this;

                    $('input', this.footer()).on('keyup change', function () {
                        if (that.search() !== this.value) {
                            that
                                .search(this.value)
                                .draw();
                        }
                    });
                });
            });
        </script>
    }
</div>
 

ItemMasterModel.cs in Models Folder:

  


public class ItemMasterModel
{
 public string PartNumber { get; set; }
 public string Specification { get; set; }
 public string Description { get; set; }  
 public DateTime CreatedDate { get; set; }  
}
 

HomeController.cs:

  


VomsDBEntities entities = new VomsDBEntities();
public ActionResult Index()
{
	//var items = GetItems("", "", "");
	return View();
}


// With server side search Sorting
[HttpPost]
public ActionResult LoadDataIndex()
{
	var For = Request.Form;

	//var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
	//var isAddressSortable = Convert.ToBoolean(Request["bSortable_2"]);
	//var isTownSortable = Convert.ToBoolean(Request["bSortable_3"]);

	var Draw = Request.Form.GetValues("draw").FirstOrDefault();
	var Start = Request.Form.GetValues("start").FirstOrDefault();
	var Length = Request.Form.GetValues("length").FirstOrDefault();

	var SortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][data]").FirstOrDefault();
	var SortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();

	var PartNo = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault();
	var Spec = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();
	var Desc = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();

	int PageSize = Length != null ? Convert.ToInt32(Length) : 0;
	int Skip = Start != null ? Convert.ToInt32(Start) : 0;
	int TotalRecords = 0;

	var Items = (from a in entities.ItemMasters
				 .Where(x => x.ItemDescription.Contains(Desc) &&
				 x.Specification.Contains(Spec) &&
				 x.PartNumber.Contains(PartNo))
				 .Take(4000)
				 select new ItemMasterModel
				 {
					 PartNumber = a.PartNumber,
					 Specification = a.Specification,
					 ItemDescription = a.ItemDescription,
					 CreatedDate = a.CreatedDate
				 });

	if (!(string.IsNullOrEmpty(SortColumn) && string.IsNullOrEmpty(SortColumnDir)))
	{
		Items = Items.OrderBy(SortColumn + " " + SortColumnDir);
	}

	TotalRecords = Items.ToList().Count();
	var NewItems = Items.Skip(Skip).Take(PageSize).ToList();

	return Json(new { draw = Draw, recordsFiltered = TotalRecords, recordsTotal = TotalRecords, data = NewItems }, JsonRequestBehavior.AllowGet);
}