Jquery DataTable paging Sorting and Multi search with datepicker in MVC server side

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

MVC:

• Jquery DataTable in MVC
• Jquery DataTable multisearch paging and Sorting in MVC server side

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)

OutPut:

LoadDataDate.cshtml:

  

@{
    ViewBag.Title = "Home Page";
}
<div>
    <br />
    <center><h3>Items from Item Master Filtering Sorting With Date Server Side</h3></center>
    <table id="tblItems">
        <thead>
            <tr>
                <th>Part Number</th>
                <th>Specification</th>
                <th>Description</th>
                <th>Created Date</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Part Number</th>
                <th>Specification</th>
                <th>Description</th>
                <th>Created Date</th>
            </tr>
        </tfoot>
    </table>

    @* jQuery DataTables css *@    
    <link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />
    @*<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>*@

        <style type="text/css">
            .ui-datepicker-calendar tr, .ui-datepicker-calendar td, .ui-datepicker-calendar td a, .ui-datepicker-calendar th {
                font-size: inherit;
            }

            div.ui-datepicker {
                font-size: 10px;
            }

            .ui-datepicker-title span {
                font-size: 10px;
            }

            .my-style-class input[type=text] {
                color: green;
            }
        </style>

        <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/LoadDataWithDate",
                        "type": "POST",
                        "datatype": "json"
                    },

                    "columns": [
                        { "data": "PartNumber", "name": "PartNumber", "autoWidth": false },
                        { "data": "Specification", "name": "Specification", "autoWidth": true },
                        { "data": "ItemDescription", "name": "ItemDescription", "autoWidth": true },
                        {
                            "data": "CreatedDate",
                            "name": "CreatedDate",
                            //"type": "date-range",
                            "render": function (data) {
                                var data1 = data.replace("/Date(", "").replace(")/", "");
                                var date = new Date(parseInt(data1));
                                var month = date.getMonth() + 1;
                                return date.getDate() + "-" + (month.length > 1 ? month : "0" + month) + "-" + date.getFullYear();
                            },
                            "autoWidth": true
                        }
                    ]
                });
    
                $('#Created_Date').prop("readonly","readonly").datepicker({
                    dateFormat: 'dd-mm-yy',
                    changeMonth: true,
                    changeYear: true,
                    onClose: function () {
                        table.draw();
                    }
                });

                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:

  

using System.Linq.Dynamic;

public ActionResult LoadDataDate()
{
 //var items = GetItems("", "", "");
 return View();
}

// With server side search Sorting with DatePicker
[HttpPost]
public ActionResult LoadDataWithDate()
{
 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();
 var CreatedDate = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();
 DateTime CDate = DateTime.MinValue;
 if (CreatedDate != "")
  CDate = Convert.ToDateTime(CreatedDate).Date;

 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) &&
     EntityFunctions.TruncateTime(x.CreatedDate) >= CDate)
     //x.CreatedDate.Date >= CDate.Date)
     .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);
}