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
