Implementing Static Pagination for MVC Table Data Using jQuery AJAX
Creating a Pagination Helper Class
First, let's create a pagination helper class in the System.Web.Mvc namespace. This class will generate pagination controls for our MVC application.
/// <summary>
/// Pagination helper for MVC applications
/// </summary>
/// <param name="htmlHelper">HTML helper instance</param>
/// <param name="baseUrl">Base URL for pagination links</param>
/// <param name="targetContainerId">ID of the container to load content into</param>
/// <param name="searchTerm">Search/filter term</param>
/// <param name="sortColumn">Column to sort by</param>
/// <param name="sortDirection">Sort direction (ASC or DESC), defaults to DESC</param>
/// <param name="totalItems">Total number of records</param>
/// <param name="currentPage">Current page number, defaults to 1</param>
/// <param name="itemsPerPage">Number of items per page, defaults to 20</param>
/// <returns>MvcHtmlString containing pagination controls</returns>
public static MvcHtmlString Pagination(this HtmlHelper htmlHelper, string baseUrl, string targetContainerId, string searchTerm, string sortColumn, string sortDirection, int totalItems, int? currentPage = 1, int? itemsPerPage = 20)
{
StringBuilder paginationBuilder = new StringBuilder();
int pageIndex = currentPage ?? 1;
int pageSize = itemsPerPage ?? 20;
int visiblePages = 10;
int startPage = pageIndex % visiblePages == 0 ? (pageIndex - visiblePages) : pageIndex - (pageIndex % visiblePages);
int totalPages = (totalItems + pageSize - 1) / pageSize;
baseUrl = baseUrl.TrimStart('/');
baseUrl = "/" + baseUrl;
if (string.IsNullOrWhiteSpace(sortDirection))
{
sortDirection = "DESC";
}
// First page link
if (pageIndex > visiblePages)
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >首页</a>", targetContainerId, 1, pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
// Previous page link
if (pageIndex > 1)
{
int prevPage = pageIndex - 1;
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >上一页</a>", targetContainerId, prevPage, pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
// Ellipsis for previous pages
if (pageIndex - visiblePages > 0)
{
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >...</a>", targetContainerId, (startPage + 1), pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
// Page numbers
for (int i = startPage; i < totalPages && i < startPage + visiblePages; i++)
{
int pageNum = i + 1;
if (pageIndex == pageNum)
{
paginationBuilder.AppendLine("<span class=\"current\">" + pageNum + "</span>");
}
else
{
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >{1}</a>", targetContainerId, pageNum, pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
}
// Ellipsis for next pages
if (pageIndex + visiblePages <= totalPages - 1)
{
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >...</a>", targetContainerId, (startPage + visiblePages + 1), pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
// Next page link
if (pageIndex < totalPages)
{
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >下一页</a>", targetContainerId, pageIndex + 1, pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
// Last page link
if (totalPages > visiblePages && pageIndex < totalPages)
{
paginationBuilder.AppendLine(String.Format("<a href='#' onclick=loadPageData('{0}','{1}','{2}','{3}','{4}','{5}','{6}') >尾页</a>", targetContainerId, totalPages, pageSize, baseUrl, sortColumn, sortDirection, searchTerm));
}
return new MvcHtmlString(paginationBuilder.ToString());
}Remember to place this class in the System.Web.Mvc namespace:
namespace System.Web.Mvc
{
public static class PaginationHelper
{
// Add the pagination method here
}
}Implementing AJAX Pagination with JavaScript
Next, let's create a JavaScript function to handle the AJAX requests for pagination:
// divId: ID of the container to load content into
// pageNum: Current page number
// itemsPerPage: Number of items to display per page
// baseUrl: Base URL for the pagination request
// sortColumn: Column to sort by
// sortDirection: Sort direction (ASC or DESC)
// searchValue: Search/filter term
function loadPageData(divId, pageNum, itemsPerPage, baseUrl, sortColumn, sortDirection, searchValue) {
if (searchValue === undefined) {
searchValue = "";
}
var requestUrl = baseUrl + "?searchTerm=" + searchValue + "&pageNum=" + pageNum + "&itemsPerPage=" + itemsPerPage + "&sortColumn=" + sortColumn + "&sortDirection=" + sortDirection;
var targetElement = "#" + divId;
$.ajax({
url: requestUrl,
async: false,
success: function (response) {
$(targetElement).html(response);
},
error: function (error) {
alert("Error loading pagination data");
}
});
}Setting Up MVC Views for Pagination
Now, let's set up the views to use our pagination functionality. We'll need two views: a main container view and a data view.
Main Container View
This view will render the data view and contain the pagination controls:
<div id="ContentContainer" style="width: 100%">
@{
Html.RenderAction("DataList");
}
</div>
<div class="pagination-controls">
@Html.Pagination("/Admin/DataList", "ContentContainer", "", sortColumn, sortDirection, totalRecords, currentPage, pageSize)
</div>The pagination controls use the CSS class "pagination-controls" where the current page is styled with "span.current".
Data View
This view displays the actual data and is called by the main container view:
@model IEnumerable<YourModel>
<table>
<thead>
<tr>
<th>@Html.ActionLink("Column1", "DataList", new { sortColumn = "Column1", sortDirection = sortDirection, searchTerm = searchTerm })</th>
<th>@Html.ActionLink("Column2", "DataList", new { sortColumn = "Column2", sortDirection = sortDirection, searchTerm = searchTerm })</th>
<!-- Add more columns as needed -->
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.Property1</td>
<td>@item.Property2</td>
<!-- Add more properties as needed -->
</tr>
}
</tbody>
</table>Server-Side Pagination with Stored Procedure
For improved performance with large datasets, consider implementing server-side pagination using a stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PaginatedData]
@sqlStatement NVARCHAR(4000), -- SQL statement to execute
@requestedPage INT = 1, -- Page number to display
@itemsPerPage INT = 10, -- Number of items per page
@totalRecords INT = 0 OUTPUT, -- Total number of records
@totalPages INT = 0 OUTPUT -- Total number of pages
AS
SET NOCOUNT ON
DECLARE @cursorHandle INT
EXEC sp_cursoropen @cursorHandle OUTPUT, @sqlStatement, @scrollopt = 1, @ccopt = 1, @rowcount = @totalPages OUTPUT
SELECT @totalRecords = @totalPages, @totalPages = CEILING(1.0 * @totalPages / @itemsPerPage)
, @requestedPage = (@requestedPage - 1) * @itemsPerPage + 1
SELECT @totalRecords AS totalRecords, @totalPages AS totalPages, @requestedPage AS currentPage
EXEC sp_cursorfetch @cursorHandle, 16, @requestedPage, @itemsPerPage
EXEC sp_cursorclose @cursorHandleThis stored procedure returns the total number of records, total pages, and the requested page of data, making it efficient for large datasets.
