jQuery DataTable Server Side paging, searching and exporting in ASP.Net MVC

SajidHussa
 
on Nov 22, 2022 12:12 AM
Sample_793488.zip
432 Views

I want show data on JQuery Datable Dynamically with option searching, filtering, export button, and paging all future server side 

How to achieve this in MVC5 c# using Ajax  

Populate ASP.Net GridView using jQuery DataTable plugin with Server Side data

This is show entry mission like 10 -20 -All

or second thing how to pass parameter like where comcode = ? and from date, to date then bind DataTable in html only not use GridView or extra control use simply use html table and in MVC

i use ApiController to bind them 

CompanyName = Dropdown (select dropdown )

fromDate = ?

toDate =?

Then hit submit button 

Then datable bind with the record which we fetched

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 23, 2022 01:41 AM
on Nov 23, 2022 01:42 AM

Hi SajidHussa,

Check this example. Now please take its reference and correct your code.

Set lengthMenu for specifying the lengths like 10, 20, All.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public JsonResult AjaxMethod()
    {
        NorthwindEntities entities = new NorthwindEntities();
        List<Customer> customers = entities.Customers.ToList();
        TableData dt = new TableData()
        {
            aaData = customers,
            iTotalDisplayRecords = customers.Count,
            iTotalRecords = customers.Count
        };
        System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
        js.MaxJsonLength = int.MaxValue;
        return Json(dt);
    }

    public class TableData
    {
        public int iTotalRecords { get; set; }
        public int iTotalDisplayRecords { get; set; }
        public List<Customer> aaData { get; set; }
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" / >
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.4.2/css/buttons.dataTables.min.css" / >
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" / >
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.4.2/js/dataTables.buttons.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.4.2/js/buttons.print.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
    <script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.colVis.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/fixedcolumns/3.2.6/js/dataTables.fixedColumns.min.js"></script>
</head>
<body class="container">
    <table id="tblCustomers" class="table table-responsive table-bordered">
        <thead>
            <tr>
                <th> Id</th>
                <th> Name</th>
                <th> City</th>
                <th> Country</th>
            </tr>
        </thead>
    </table>
    <script type="text/javascript">
        $(function () {
            $("#tblCustomers").DataTable({
                "dom": 'Blfrtip',
                "lengthMenu": [[10, 20, -1], [10, 20, 'All']],
                "bProcessing": true,
                "sAjaxSource": "/Home/AjaxMethod",
                "sServerMethod": "post",
                "aoColumns": [
                    { mData: "CustomerID", "bSortable": false },
                    { mData: "ContactName", "bSortable": true },
                    { mData: "City", "bSortable": true },
                    { mData: "Country", "bSortable": true }
                ],
                "iDisplayLength": 10,
                "buttons": [
                    { extend: 'print', text: 'Print', exportOptions: { columns: ':visible' } },
                    { extend: 'copy', text: 'Copy to clipboard', className: 'exportExcel', exportOptions: { modifier: { page: 'all' } } },
                    { extend: 'excel', text: 'Export to Excel', className: 'exportExcel', filename: 'Customers', exportOptions: { modifier: { page: 'all' } } },
                    { extend: 'csv', text: 'Export to CSV', className: 'exportExcel', filename: 'Customers', exportOptions: { modifier: { page: 'all' } } },
                    {
                        extend: 'pdf', text: 'Export to PDF', className: 'exportExcel', filename: 'Customers', orientation: 'landscape',
                        pageSize: 'LEGAL', exportOptions: { modifier: { page: 'all' }, columns: ':visible' }
                    }
                ]
            });
        });
    </script>
</body>
</html>

Screenshot