Paging and Sorting in MVC4 Using PagedList

In this tutorials i am going to showing Paging and Sorting in MVC4 Using PagedList . For this  Am using 

Step i am going to do here

1. using Visual studio 2012 MVC 4 version.
2. Sql Server 2012.
3. For css using bootstrap and jquery
4. Entity framework (4.4.0.0) references  
5. Reffrence the PagedList.mvc in project

Step:1 

Create a table in sql server database and create store procedure (script given bellow)

CREATE TABLE [dbo].[DepartmentLocation](
[LocationId] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Pincode] [nvarchar](50) NULL,
[PhoneNo] [nvarchar](50) NULL,
[CountryID] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
[LocationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create PROCEDURE [dbo].[Sp_Get_DepartmentList]
AS
Begin
SELECT DL.LocationId,
      DL.LocationName,
  DL. Address,
  DL.City,
  DL.Pincode,
  DL.CountryID,
      C.CountryName
from Country C inner join DepartmentLocation DL On C.CountryID=DL.CountryID

End

Step:2 Create Entity Data Model to connect to database

Right-click on your project in Solution Explorer then click on ADD New ADO.NET Entity Data Model. name as "MyContactBookEntities" ...

Step:3 Reffrence the Pagedlist dll and css to your projects

on your project in Solution Explorer then select Manage NuGet Packages.search Pagedlist.mvc  and install it.

Step:3

Add new Controller with name 'DepartmentLocationController.cs' and put the code given bellow.

DepartmentLocationController.cs

using AutoMapper;
using MVCContactBook.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PagedList;

namespace MVCContactBook.Controllers
{
      public class DepartmentLocationController : BaseController
          {
           public ActionResult GetDepartmentLocationList(string sortOn, string orderBy, string                                pSortOn, int? page)
           {          
            int recordsPerPage = 10;
            if (!page.HasValue)
            {
                page = 1; // set initial page value
                if (string.IsNullOrWhiteSpace(orderBy) || orderBy.Equals("asc"))
                {
                    orderBy = "desc";
                }
                else
                {
                    orderBy = "asc";
                }
            }          
            if (!string.IsNullOrWhiteSpace(sortOn) && !sortOn.Equals(pSortOn, StringComparison.CurrentCultureIgnoreCase))
            {
                orderBy = "asc";
            }

            ViewBag.OrderBy = orderBy;
            ViewBag.SortOn = sortOn;
        
            MyContactBookEntities dc = new MyContactBookEntities();
            var list = dc.Sp_Get_DepartmentList().AsQueryable();
                     
            switch (sortOn)
            {
                case "LocationName":
                    if (orderBy.Equals("desc"))
                    {
                        list = list.OrderByDescending(p => p.LocationName);
                    }
                    else
                    {
                        list = list.OrderBy(p => p.LocationName);
                    }
                    break;
                case "Address":
                    if (orderBy.Equals("desc"))
                    {
                        list = list.OrderByDescending(p => p.Address);
                    }
                    else
                    {
                        list = list.OrderBy(p => p.Address);
                    }
                    break;
                case "City":
                    if (orderBy.Equals("desc"))
                    {
                        list = list.OrderByDescending(p => p.City);
                    }
                    else
                    {
                        list = list.OrderBy(p => p.City);
                    }
                    break;                
                default:
                    list = list.OrderBy(p => p.LocationId);
                    break;
            }

            var finalList = list.ToList().ToPagedList(page.Value, recordsPerPage);
            return View(finalList);
        }
 }

}

Step:4

Add View against the action method name 'GetDepartmentLocationList' and put the code given bellow.

View
GetDepartmentLocationList.cshtml

@using PagedList;
@using PagedList.Mvc;
@model IPagedList<MVCContactBook.Sp_Get_DepartmentList_Result>
@{
    ViewBag.Title = "GetDepartmentLocationList";
}

<script src="~/Scripts/jquery-3.1.1.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
    <script>
        $(document).ready(function () {
            hideColumn = function (column) {
                $('tr').each(function () {
                    $(this).find('td,th').eq(column).hide();
                });
            };
            hideColumn(0);

        });
    </script>


<h2>Department Location List</h2>

<p>
    @Html.ActionLink("Create New", "AddNewLocation")
</p>

 <div id="no-more-tables">
<table  class="col-md-12 table-bordered table-striped table-condensed cf">
     <thead class="cf" style="border: 1px solid #ccc;">
    <tr>
        <th  class="numeric">
            @*@Html.DisplayNameFor(model => model.LocationId)*@
        </th>
        <th  style="text-align:center;">
            
            @Html.ActionLink("Location", "GetDepartmentLocationList", new { sortOn ="LocationName", orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
        </th>
         <th style="text-align:center;">
            @Html.ActionLink("Address", "GetDepartmentLocationList", new { sortOn ="Address", orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
        </th>
         <th style="text-align:center;">
            @Html.ActionLink("City", "GetDepartmentLocationList", new { sortOn ="City", orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
        </th>
        <th style="text-align:center;">
            Pincode
        </th>
      
        <th style="text-align:center;">
            Country
        </th>
        <th>Edit</th>
        <th>Delete</th>
    </tr>
         </thead>
      <tbody style="border: 1px thin black">

         
@foreach (var item in Model)
{
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.LocationId)
        </td>
        <td style="text-align:center;">
            @Html.DisplayFor(modelItem => item.LocationName)
        </td>
        <td style="text-align:center;">
            @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td style="text-align:center;">
            @Html.DisplayFor(modelItem => item.City)
        </td>
        <td style="text-align:center;">
            @Html.DisplayFor(modelItem => item.Pincode)
        </td>
      
        <td style="text-align:center;">
            @Html.DisplayFor(modelItem => item.CountryName)
        </td>
        <td style="text-align:center;">
            @Html.ActionLink(" ", "Edit", new { id = item.LocationId }, new { @class = "glyphicon glyphicon-edit", title = "Edit" }) 
            </td>

            <td style="text-align:center;">
            @Html.ActionLink(" ", "Delete", new { id = item.LocationId }, new { @class = "glyphicon glyphicon-trash", title = "Delete" })        
            
        </td>
    </tr>

}
          </tbody>
 </table>
    <div class="pagedList">
        @Html.PagedListPager(Model, page => Url.Action("GetDepartmentLocationList", new { page,
sortOn = ViewBag.SortOn, orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn }), PagedListRenderOptions.ClassicPlusFirstAndLast)
    </div>
   </div>