Implement custom paging in DataTable in ASP.Net MVC

Last Reply 12 days ago By dharmendr

Posted 16 days ago

hi

I want to fill datatable according to pagination click such as Kendo or others popular component

my structure like below

public ActionResult StockList(StockFormModel model)
{      
    model.Data = FillDataTable("SELECT NAME,SURNAME,ADRESS,CITY FROM Employeer");
    return View(model);
}

 

<div class="content-wrapper" style="background-color: white; overflow-x: hidden">
    @using (Html.BeginForm("EmployeList", "Employeer", FormMethod.Post))
    {
        <div class="card" style="height: 45px;margin-top:-3px">
            <div class="card-body col-12 bg-secondary">

                <div style="margin-top: -10px;margin-left: -15px">

                    <button type="submit" class="btn btn-sm btn-dark">Sorgula</button>


                </div>
            </div>
        </div>
    }
    <div class="row">
        <div class="col-12">

            <div class="table-responsive" style="font-size: 11px">

                <table id="tblCustomerList" class="table table-striped table-hover">
                    <thead>
                        <tr>
                            @foreach (DataColumn col in Model.Data.Columns)
                            {
                                <th>@col.ColumnName.ToUpper()</th>
                            }
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (DataRow row in Model.Data.Rows)
                        {
                            <tr>
                                @foreach (DataColumn col in Model.Data.Columns)
                                {
                                    <td>@row[col.ColumnName]</td>
                                }
                            </tr>
                        }
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#tblCustomerList').DataTable({                
                destroy:true,
                processing: true,
                select: true,
                paging: true,
                lengthChange: true,
                "lengthMenu": [[13, 25, 50, -1], [13, 25, 50, "All"]],
                searching: true,
                "order": [],
                info: false,
                responsive: true,
                autoWidth: false,
                "scrollX": true
            }).draw();
        })
    </script>

 

Posted 12 days ago Modified on 9 days ago

Hi alya14,

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

Database

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

Download Northwind Database

Model

public class CustomerModel
{
    public DataTable Data { get; set; }
    public int CurrentPageIndex { get; set; }
    public int PageCount { get; set; }
}

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Controller

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

    [HttpPost]
    public ActionResult Index(int currentPageIndex)
    {
        return View(this.GetCustomers(currentPageIndex));
    }

    private CustomerModel GetCustomers(int currentPage)
    {
        int maxRows = 10;
        int start = ((currentPage - 1) * maxRows) + 1;
        int end = maxRows * currentPage;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"SELECT CustomerID,ContactName,City,Country
                            FROM
                            (
                                SELECT ROW_NUMBER() OVER(ORDER BY(CustomerID)) RowNum,
                                CustomerID,
                                ContactName,
                                City,
                                Country FROM Customers
                            ) t
                            WHERE t.RowNum BETWEEN @Start AND @End";
        cmd.Parameters.AddWithValue("@Start", start);
        cmd.Parameters.AddWithValue("@End", end);

        CustomerModel customerModel = new CustomerModel();
        customerModel.Data = DataTableGetir(cmd);
        double pageCount = (double)((decimal)GetTotalRecordCount("SELECT COUNT(*) FROM Customers") / Convert.ToDecimal(maxRows));
        customerModel.PageCount = (int)Math.Ceiling(pageCount);
        customerModel.CurrentPageIndex = currentPage;

        return customerModel;
    }

    public DataTable DataTableGetir(SqlCommand cmd)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }

    public int GetTotalRecordCount(string query)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = con;
            con.Open();
            int total = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return total;
        }
    }
}

View

@model DataTable_Paging_MVC.Models.CustomerModel
@using System.Data;
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }

        .btn {
            border: none;
            outline: none;
            padding: 10px 15px;
            background-color: #B8DBFD;
            cursor: pointer;
        }

        .active, .btn:hover {
            background-color: #CBCBCB;
            color: black;
         }
    </style>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
</head>
<body>
    <div class="container">
        @using (Html.BeginForm("Index", "Home", FormMethod.Post))
        {
            <div class="row">
                <div class="col-12">
                    <div class="table-responsive" style="font-size: 11px">
                        <table id="tblCustomerList" class="table table-striped table-hover">
                            <thead>
                                <tr>
                                    @foreach (DataColumn col in Model.Data.Columns)
                                    {
                                        <th>@col.ColumnName.ToUpper()</th>
                                    }
                                </tr>
                            </thead>
                            <tbody>
                                @foreach (DataRow row in Model.Data.Rows)
                                {
                                    <tr>
                                        @foreach (DataColumn col in Model.Data.Columns)
                                        {
                                            <td>@row[col.ColumnName]</td>
                                        }
                                    </tr>
                                }
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <br />
            <table cellpadding="0" cellspacing="0">
                <tr>
                    @for (int i = 1; i <= Model.PageCount; i++)
                    {
                        <td>
                            @if (i != Model.CurrentPageIndex)
                            {
                                <a class="btn active" href="javascript:PagerClick(@i);">@i</a>
                            }
                            else
                            {
                                <span class="btn">@i</span>
                            }
                        </td>
                    }
                </tr>
            </table>
            <input type="hidden" id="hfCurrentPageIndex" name="currentPageIndex" />
        }
        <script type="text/javascript">
            function PagerClick(index) {
                document.getElementById("hfCurrentPageIndex").value = index;
                document.forms[0].submit();
            }
        </script>
    </div>
</body>
</html>

Screenshot