how to implement (Server-side) SearchPanes of DataTables in mvc core

Question

I need an example of a (Server-side Procssing) SearchPanes implementation of DataTables in mvc core

In the link I have an example of implementing datatables including operations such as Sorting, pagination and search but about the implementation of searchPanes please help me

Answer 1

You could use Juery Ajax to call the MVC action method and load data, then, setting the JQuery DataTable's "searchPanes", "columnDefs" and "dom: 'Pfrtip'," property to implement the SearchPanes function. Sample Code as below:

Code in the Action method:

    [HttpPost]
    public JsonResult GetData()
    {
        List<Employee> employees = new List<Employee>()
        {
            new Employee(){ DT_RowId="row_1", First_Name="Tiger", Last_Name="Nixon", Position="System Architect", Email="[email protected]", Office="Edinburgh", Extn="5421", Age="61", Salary="320800", Start_date="2011-04-25"},
            new Employee(){ DT_RowId="row_2", First_Name="Garrett", Last_Name="Winters", Position="Office Manager", Email="[email protected]", Office="London", Extn="7580", Age="61", Salary="13800", Start_date="2011-04-25"},
            new Employee(){ DT_RowId="row_3", First_Name="Timothy", Last_Name="Mooney", Position="System Architect", Email="[email protected]", Office="San Francisco", Extn="5384", Age="61", Salary="85675", Start_date="2011-04-25"},
            new Employee(){ DT_RowId="row_4", First_Name="Unity", Last_Name="Butler", Position="Marketing Designer", Email="[email protected]", Office="San Francisco", Extn="9421", Age="61", Salary="452500", Start_date="2011-04-25"},
            new Employee(){ DT_RowId="row_5", First_Name="Vivian", Last_Name="Harrell", Position="Financial Controller", Email="[email protected]", Office="London", Extn="7439", Age="61", Salary="67500", Start_date="2011-04-25"},
        };
         
        return Json(employees);
    }

Code in the View Page:

<link href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/searchpanes/1.1.1/css/searchPanes.dataTables.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/select/1.3.1/css/select.dataTables.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/searchpanes/1.1.1/js/dataTables.searchPanes.min.js"></script>
<script src="https://cdn.datatables.net/select/1.3.1/js/dataTables.select.min.js"></script>

<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Extn.</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Extn.</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

JQuery script:

<script>
        $(document).ready(function () {
            $(function () {
                $.ajax({
                    type: "POST",
                    url: "/Home/GetData", 
                    success: OnSuccess,
                    failure: function (response) {
                        alert(response.d);
                    },
                    error: function (response) {
                        alert(response.d);
                    }
                });
            });
            function OnSuccess(response) {
                $("#example").DataTable(
                    {
                        bLengthChange: true,
                        bFilter: true,
                        bSort: true,
                        bPaginate: true,
                        data: response,
                        searchPanes: {
                            columns: [2, 5],
                            layout: 'columns-2',
                        },
                        columnDefs: [{
                            searchPanes: {
                                show: true
                            },
                            targets: [5]
                        }],
                        dom: 'Pfrtip',
                        columns: [{
                            'data': null, render: function (data, type, row) {
                                // Combine the first and last names into a single table field
                                return data.first_Name + ' ' + data.last_Name;
                            }
                        },
                        { 'data': 'position' },
                        { 'data': 'office' },
                        { 'data': 'extn' },
                        { 'data': 'start_date' },
                        {
                            'data': 'salary',
                            render: $.fn.dataTable.render.number(',', '.', 0, '$')
                        }
                        ]
                    });
            };
        });
    </script>

The output as below:

More detail information, please check the following links:

Populate jQuery DataTables in Asp.net MVC

JQuery DataTables SearchPanes

DataTables SearchPanes Examples

Answer 2

Your server's response JSON should have an object named searchPane; the content of this object is something like this:

$searchPanes['options'][$key][]=["label" => $v['label'], "total" => $v['total'], "value" => $v['value'], "count" => 0];