In this article I will explain with an example, how to perform Custom Paging in WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC Razor.
Custom Paging will be performed at Database level inside the Stored Procedure using the ROW_NUMBER function.
The PageIndex and PageSize values will be passed to the Stored Procedure using Entity Framework and only the specific records will be fetched and displayed in WebGrid, thus reducing the bandwidth usage.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 
Stored Procedure
The following Stored Procedure needs to be created in the Northwind Database. The Stored Procedure accepts the PageIndex and PageSize input parameters and a RecordCount output parameter.
Paging is performed using the ROW_NUMBER function and the records from the database as per PageIndex and PageSize are returned.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
             ,[CustomerID]
             ,[CompanyName]
             ,[ContactName]
             ,[ContactTitle]
             ,[Address]
             ,[City]
             ,[Region]
             ,[PostalCode]
             ,[Country]
             ,[Phone]
             ,[Fax]
      INTO #Results
      FROM [Customers]
   
      SELECT @RecordCount = COUNT(*)
      FROM #Results
         
      SELECT *
      FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
   
      DROP TABLE #Results
END
 
 
Configuring and connecting Entity Framework to database
You will need to configure the Entity Framework in order to connect to the database.
Note: The complete details of configuring and using Entity Framework in ASP.Net MVC are provided in my article ASP.Net MVC: Entity Framework Database First Approach example.
 
Once the Entity Framework is configured, the next step is to import the Stored Procedure in the Entity Framework model. In order to do so, you will need to Right Click the Entity Model and select Update Model from Database option as shown below.
ASP.Net MVC: Custom Paging in WebGrid using Stored Procedure and Entity Framework
 
The above step will open the Update Wizard where the Stored Procedure (created earlier) is selected and Finish button is clicked.
ASP.Net MVC: Custom Paging in WebGrid using Stored Procedure and Entity Framework
 
Note: The above two steps are not required if the Stored Procedure has been added while creating the Entity Data Model.
 
Finally, in order to call the Stored Procedure using Entity Framework, the Stored Procedure needs to be imported as a Function. Thus, again Right Click and select Add and then click on Function Import option.
ASP.Net MVC: Custom Paging in WebGrid using Stored Procedure and Entity Framework
 
This will open the Add Function Import dialog window where the Function Name and the Stored Procedure to be imported as Function is selected.
Finally the Entity RadioButton is selected and the Customer Entity is chosen from the DropDown.
ASP.Net MVC: Custom Paging in WebGrid using Stored Procedure and Entity Framework
 
 
Model
The following Model class named CustomerModel consists of four properties.
1. Customers – List collection of the Customer Entity Data Model which will hold the records of the Customers.
2. PageIndex – Holds the value of the index of the Current Page.
3. PageSize – Holds the value of number of rows to be displayed per page.
4. RecordCount – Holds the value of the Total Records present in the Table.
public class CustomerModel
{
    public List<Customer> Customers { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int RecordCount { get; set; }
}
 
 
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, a dummy Customer record i.e. a record with Blank data is sent.
This is done in order to populate WebGrid with a Blank row and this Blank row will be used by jQuery AJAX to clone and populate more rows in the WebGrid.
 
Action method for handling jQuery AJAX operation
This Action method handles the call made from the jQuery AJAX function from the View.
Note: The following Action method handles AJAX calls and hence the return type is set to JsonResult.
 
An object of the CustomerModel class is created and then the records are fetched from the Customers table by calling the Stored Procedure using the GetCustomers function (created using Function Import earlier) and are assigned to the Customers property of the CustomerModel object.
The RecordCount output parameter value is fetched using ObjectParameter class and is assigned to the RecordCount property of the CustomerModel class.
Finally, the CustomerModel object is returned to the View as JSON object.
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        List<Customer> dummy = new List<Customer>();
        dummy.Add(new Customer());
        return View(dummy);
    }
 
    [HttpPost]
    public JsonResult AjaxMethod(int pageIndex)
    {
        NorthwindEntities entities = new NorthwindEntities();
        CustomerModel model = new CustomerModel();
        model.PageIndex = pageIndex;
        model.PageSize = 10;
        ObjectParameter recordCount = new ObjectParameter("RecordCount", typeof(int));
        model.Customers = entities.GetCustomers(model.PageIndex, model.PageSize, recordCount).ToList();
        model.RecordCount = Convert.ToInt32(recordCount.Value);
        return Json(model);
    }
}
 
 
View
Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that the Model will be available as a Collection.
The WebGrid is initialized with the Model i.e. IEnumerable collection of Customer Entity class objects as source.
Note: For more information on usage of WebGrid, please refer WebGrid Step By Step Tutorial with example in ASP.Net MVC.
 
The WebGrid will be populated with data from SQL Server database using jQuery AJAX and JSON.
The GetCustomers JavaScript function is called inside the jQuery document ready event handler and also when the Pager Button is clicked.
Inside the GetCustomers JavaScript function, the value of the Page Index is sent to the Controller’s Action method using a jQuery AJAX call.
The Controller’s Action method returns a JSON object of the CustomerModel class which contains the List of Customers, the current Page Index, the Page Size and the total Record Count.
Using this JSON object and the dummy HTML row, the HTML Table is populated with the records returned by the Entity Framework.
The Pager is populated using the ASPSnippets_Pager jQuery Plugin.
@model IEnumerable<Customer>
 
@{
    Layout = null;
    WebGrid webGrid = new WebGrid(source: Model, canSort: false, canPage: false);
}
 
<!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;
        }
 
        table {
            border: 1px solid #ccc;
            border-collapse: collapse;
            background-color: #fff;
        }
 
        table th {
            background-color: #B8DBFD;
            color: #333;
            font-weight: bold;
        }
 
        table th, table td {
            padding: 5px;
            border: 1px solid #ccc;
        }
 
        table, table table td {
            border: 0px solid #ccc;
        }
 
        .Pager span {
            text-align: center;
            color: #333;
            display: inline-block;
            width: 20px;
            background-color: #B8DBFD;
            margin-right: 3px;
            line-height: 150%;
            border: 1px solid #B8DBFD;
        }
 
        .Pager a {
            text-align: center;
            display: inline-block;
            width: 20px;
            background-color: #ccc;
            color: #333;
            border: 1px solid #ccc;
            margin-right: 3px;
            line-height: 150%;
            text-decoration: none;
        }
    </style>
</head>
<body>
    <h4>Customers</h4>
    <hr/>
    @webGrid.GetHtml(
        htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
        columns: webGrid.Columns(
                 webGrid.Column("CustomerID", "CustomerID"),
                 webGrid.Column("ContactName", "ContactName"),
                 webGrid.Column("City", "City"),
                 webGrid.Column("Country", "Country")))
 
    <br/>
    <div class="Pager"></div>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="~/Scripts/ASPSnippets_Pager.min.js"></script>
    <script type="text/javascript">
    $(function () {
        GetCustomers(1);
    });
    $("body").on("click", ".Pager .page", function () {
        GetCustomers(parseInt($(this).attr('page')));
    });
    function GetCustomers(pageIndex) {
       $.ajax({
            type: "POST",
            url: "/Home/AjaxMethod",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccess,
            failure: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
    };
    function OnSuccess(response) {
        var model = response;
        var row = $("#WebGrid tbody tr:last-child").clone(true);
        $("#WebGrid tbody tr").remove();
        $.each(model.Customers, function () {
            var customer = this;
            $("td", row).eq(0).html(customer.CustomerID);
            $("td", row).eq(1).html(customer.ContactName);
            $("td", row).eq(2).html(customer.City);
            $("td", row).eq(3).html(customer.Country);
            $("#WebGrid").append(row);
            row = $("#WebGrid tbody tr:last-child").clone(true);
        });
        $(".Pager").ASPSnippets_Pager({
            ActiveCssClass: "current",
            PagerCssClass: "pager",
            PageIndex: model.PageIndex,
            PageSize: model.PageSize,
            RecordCount: model.RecordCount
        });
    };
    </script>
</body>
</html>
 
 
Screenshot
ASP.Net MVC: Custom Paging in WebGrid using Stored Procedure and Entity Framework
 
 
Downloads
Download Code