Paging, Sorting, And Filtering With Partial View In ASP.NET MVC 5

Description

In my last series on Restful API in ASP.NET, we have successfully created an ASP.NET Web Application with the ASP.NET Web API Project Template. Now, in this article I am describing the use of Partial view in MVC 5 with the help of my old API application. We will change up the application with the new requirement.

So, let’s have a look at Partial View. Partial View performs as user controls (.ascx). With the help of Partial view, we can create a view which will be brought to pass in a parent view. Here in this article, we will perform Paging, Sorting and Filtering with the use of Partial View in the MVC 5.

Now, I am assuming that you have gone through my previous article so that I can easily create a new section to perform the current requirement.

Prerequisites

You must have Visual Studio 2013 or later version to work on MVC 5 based web applications.

Getting Started

In this section, we will categorized our application into the following three parts:

• Paging
• Sorting
• Filtering

Adding Web Project

First we have to add a Web Project in our solution. Please follow the steps below to perform:

Step 1: In the Solution Explorer, right click on the Solution and add a new Solution Folder named "Web”.


                                             Figure 1: Adding Solution Folder

Step 2: Now right click on the Web folder and click on "Add New Project”.


                                          Figure 2: Adding New Project

Step 3: Select the "Web" option from the left pane in the next wizard and click on "ASP.NET Web application”.


                                                Figure 3: Adding Web Application

Step 4: In the next "ASP.NET” wizard, select the MVC Project Template.


                                                Figure 4: One ASP.NET Wizard

That’s it. You have successfully created an MVC Project.

Paging

In this section we have to change the structure of the stored procs, methods in API; so that we can create a view to get the records. So, follow the following procedure to perform this section:

Step 1: Modify the Stored Procedure as the Paging requirement from the following script:

ALTER PROC [dbo].[CC_GetCricketerList]
    @PageNumber INT ,
    @PageSize INT
AS
    BEGIN
        SELECT  ROW_NUMBER() OVER ( ORDER BY ID DESC ) [RowNo] ,
                *,
                COUNT(ID) OVER ( ) [TotalCount]
        FROM    dbo.CricketerProfile (NOLOCK)
        ORDER BY ID
                OFFSET ( @PageNumber - 1 ) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    END;

Step 2: Modify the following methods as per the following classes:

CricketerProfile.CS

public class CricketerProfile : Result
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ODI { get; set; }
    public int Tests { get; set; }
    public int OdiRuns { get; set; }
    public int TestRuns { get; set; }
    public int Type { get; set; }
    public int TotalCount { get; set; }
}

CricketerDAL.CS

public List<CricketerProfile> GetCricketerList(int PageNumber, int PageSize)
{
    List<CricketerProfile> objGetCricketers = null;
    objDB = new SqlDatabase(ConnectionString);
    using (DbCommand objcmd = objDB.GetStoredProcCommand("CC_GetCricketerList"))
    {
        try
        {
            objDB.AddInParameter(objcmd, "@PageNumber", DbType.Int32, PageNumber);
            objDB.AddInParameter(objcmd, "@PageSize", DbType.Int32, PageSize);

            using (DataTable dataTable = objDB.ExecuteDataSet(objcmd).Tables[0])
            {
                objGetCricketers = ConvertTo<CricketerProfile>(dataTable);
            }
        }
        catch (Exception ex)
        {
            throw ex;
            return null;
        }
    }
    return objGetCricketers;
}

CricketerBL.CS

public List<CricketerProfile> GetCricketerList(int PageNumber, int PageSize)
{
    List<CricketerProfile> ObjCricketers = null;
    try
    {
        ObjCricketers = new CricketerDAL().GetCricketerList(PageNumber, PageSize);
    }
    catch (Exception)
    {

        throw;
    }
    return ObjCricketers;
}

Method in API:

[HttpGet, ActionName("GetCricketerList")]
public HttpResponseMessage GetCricketerList(int PageNumber, int PageSize)
{
    Result result;
    cricketerBL = new CricketerBL();
    try
    {
        var cricketerList = cricketerBL.GetCricketerList(PageNumber, PageSize);

        if (!object.Equals(cricketerList, null))
        {
            response = Request.CreateResponse<List<CricketerProfile>>(HttpStatusCode.OK, cricketerList);
        }
    }
    catch (Exception ex)
    {
        result = new Result();
        result.Status = 0;
        result.Message = ex.Message;
        response = Request.CreateResponse(HttpStatusCode.InternalServerError, result);
    }
    return response;
}

Step 3: Now first add a reference of Models project in the Web Project.


                                             Figure 5: Adding Reference Wizard

Step 4: Add a class  named "CricketerInfo” in the Models folder of the Web Project and modify the code from the following code in that class:

public class CricketerInfo
{
   public List<BestCricketers.Models.CricketerProfile> cricketerProfile { get; set; }
}

Step 5: Build the solution.

Step 6: Right click on the Controllers folder and go to Add, then click Controller


                                          Figure 6: Adding Controller

Step 7: Now in the "Add Scaffold” wizard, select the "MVC 5 Controller - Empty":


                                                Figure 7: Add Scaffold Wizard

Step 8: In the next wizard, enter the name "CricketersController”


                                                   Figure 8: Add Controller

Step 9: Add the following keys in "Web.Config” file:

<add key="DefaultPageSize" value="5"/>
<add key="ApiUrl" value="http://localhost:6416/"></add>

Note: Please change the API Url as per your project.

Step 10: Add the following method in the "CricketersController"

/// <summary>
/// This method is used to get cricketers list.
/// </summary>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
[HttpGet, ActionName("GetBestCricketers")]
public async Task<ActionResult> GetBestCricketers(int? PageNumber, int? PageSize)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(PageNumber, null))
    {
        PageNumber = 1;
    }
    if (object.Equals(PageSize, null))
    {
        PageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    ViewBag.PageNumber = PageNumber;
    ViewBag.PageSize = PageSize;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={0}&PageSize={1}", PageNumber, PageSize)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<BestCricketers.Models.CricketerProfile>>(responseAsString);
    return View("~/Views/Cricketers/BestCricketers.cshtml", new CricketerInfo() { cricketerProfile = cricketersList });
}

Step 11: Go to Solution Explorer, right click on the web project, then click References


      Figure 9: Adding NuGet Package

Step 12: Search out PagedList.Mvc and install this package:

                                          Figure 10: Adding PagedList in MVC 5

Step 13: Now go to View, then Cricketers and add a new view in this folder:


                                             Figure 11: Adding View in MVC 5

Step 14: Add the jQuery Unobtrusive ajax from the NuGet Package as shown below:


                                          Figure 12: JQuery Unobtrusive Ajax

Step 15: Add the following code in the View:

@model BestCricketersWeb.Models.CricketerInfo
@using PagedList;
@{
    ViewBag.Title = "BestCricketers";
}
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
<h2>Best Cricketers</h2>

@using (Ajax.BeginForm("GetBestCricketersInfo", "Cricketers", null, new AjaxOptions() { LoadingElementId = "", HttpMethod = "Get", UpdateTargetId = "CricketersGrid" }))
{
    <div class="CricketersList">
        <div id="CricketersGrid">
            @Html.Partial("~/Views/Cricketers/_BestCricketersPartial.cshtml", new StaticPagedList<BestCricketers.Models.CricketerProfile>(Model.cricketerProfile, Convert.ToInt32(ViewBag.PageNumber), Convert.ToInt32(ViewBag.PageSize), Model.cricketerProfile.Count > 0 ? Model.cricketerProfile.FirstOrDefault().TotalCount : 0))
        </div>
    </div>
}

Step 16: Add the following code in the CricketersController.

/// <summary>
/// This method is used to get cricketers list.
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
[HttpGet, ActionName("GetBestCricketersInfo")]
public async Task<ActionResult> GetBestCricketersInfo (int? page, int? pageSize)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(page, null))
    {
        page = 1;
    }
    if (object.Equals(pageSize, null))
    {
        pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    ViewBag.PageNumber = page;
    ViewBag.PageSize = pageSize;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={0}&PageSize={1}", page, pageSize)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<CricketerProfile>>(responseAsString);
    return PartialView("~/Views/Cricketers/_BestCricketersPartial.cshtml", new StaticPagedList<CricketerProfile>(cricketersList, Convert.ToInt32(page), Convert.ToInt32(pageSize), cricketersList.Count > 0 ? cricketersList.FirstOrDefault().TotalCount : 0));
}

Step 17: Add the Partial View as in the following screenshot:


                                             Figure 13: Adding Partial View

Step 18: Add the following code in the partial view page:

@model PagedList.IPagedList<BestCricketers.Models.CricketerProfile>
@using PagedList.Mvc

<table class="table-responsive table">
    <thead>
        <tr>
            <th>Name</th>
            <th>ODI</th>
            <th>Tests</th>
            <th>ODI Runs</th>
            <th>Test Runs</th>
        </tr>
    </thead>
    <tbody>
        @if (Model.Count > 0)
        {
            foreach (var cricketer in Model)
            {
                <tr>
                    <td>@cricketer.Name</td>
                    <td>@cricketer.ODI</td>
                    <td>@cricketer.Tests</td>
                    <td>@cricketer.OdiRuns</td>
                    <td>@cricketer.TestRuns</td>
                </tr>
            }
        }
        else
        {
            <tr>
                <td>
                    No Data Found
                </td>
            </tr>
        }
    </tbody>
</table>
@if (Model.TotalItemCount > Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DefaultPageSize"]))
{
    <div class="pagingBox">
        <input id="HiddenPageSize" name="PageSize" type="hidden" />
        <input id="HiddenPage" name="Page" type="hidden" />
        <span class="selectBoxes display_none_mobile">
            @Html.DropDownList("PageSize", new SelectList(new Dictionary<string, int> { { "10", 10 }, { "20", 20 } }, "Key", "Value", Convert.ToString(ViewBag.PageSize)), new { id = "pagesizelist" })
        </span>
        <div class="pagerecord display_none_mobile">
            Records

            Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
        </div>

        @Html.PagedListPager(Model, page => Url.Action("GetBestCricketersInfo", "Cricketers",
        new
        {
            page,
            currentFilter = ViewBag.CurrentFilter,
            pageSize = ViewBag.PageSize
        }),
        PagedListRenderOptions.EnableUnobtrusiveAjaxReplacing(new PagedListRenderOptions
        {
             Display = PagedListDisplayMode.IfNeeded,
             MaximumPageNumbersToDisplay = 5
        },
        new AjaxOptions
        {
             InsertionMode = InsertionMode.Replace,
             HttpMethod = "Get",
             UpdateTargetId = "CricketersGrid",
             LoadingElementId = "divProcessing"
        }))
    </div>

    <div id="divProcessing" class="processingButton" style="display: none;">
        <img width="31" height="31" alt="" class="LoadingImage" src="~/Images/ajax-loader-round.gif" />
    </div>
}

Step 19: Add the following link to the Views/Shared/_Layout Page.

<li>@Html.ActionLink("Cricketers", "GetBestCricketers","Cricketers")</li>

Step 20: Build the solution and start both your projects as in the following screenshot:


                                     Figure 14: Setting Multiple Startup Projects

Step 21: Now run the application and click on the Cricketers:


                                                Figure 15: Cricketers View

As you can see in the above screenshot that the records are displayed with the paging. Paging Numbers are based upon the total count of records and your page size defined in the"Web.Config."

Step 22: Now click on Page No. 2 from below and you can see that the second page records will display.


                                       Figure 16: Cricketers View with Paging

Note: Here every time the request is going to the database and records are fetched instead of getting whole data at first time and then perform paging on that grid. Getting whole data at one time may slow your data performance.”

That’s it for the paging. Now we will proceed in the next section.

Sorting

In this section we will sort the data over ODI, Tests, ODI Runs, Tests Runs of player. We have to change the database and methods to do that. So, follow the steps below to perform that.

Step 1: Change the stored procedure from the following script:

ALTER PROC[dbo].[CC_GetCricketerList]
@PageNumber INT,
@PageSize INT,
@SortExp VARCHAR(20)
AS
BEGIN;
WITH CteCricketer
AS(SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortExp = 'ODI_Asc'
    THEN ODI END ASC, CASE WHEN @SortExp = 'ODI_Desc'
    THEN ODI END DESC, CASE WHEN @SortExp = 'Tests_Asc'
    THEN Tests END ASC, CASE WHEN @SortExp = 'Tests_Desc'
    THEN Tests END DESC, CASE WHEN @SortExp = 'OdiRuns_Asc'
    THEN ODIRuns END ASC, CASE WHEN @SortExp = 'OdiRuns_Desc'
    THEN ODIRuns END DESC, CASE WHEN @SortExp = 'TestRuns_Asc'
    THEN TestRuns END ASC, CASE WHEN @SortExp = 'TestRuns_Desc'
    THEN TestRuns END DESC, CASE WHEN @SortExp = ''
    THEN ID END ASC)[RowNo], * FROM dbo.CricketerProfile(NOLOCK))
SELECT CteCricketer.RowNo,
    CteCricketer.ID,
    CteCricketer.Name,
    CteCricketer.ODI,
    CteCricketer.Tests,
    CteCricketer.ODIRuns,
    CteCricketer.TestRuns, (SELECT COUNT(CteCricketer.ID) FROM CteCricketer) AS TotalCount
FROM CteCricketer
ORDER BY CteCricketer.RowNo
OFFSET(@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
END;

Step 2: Add the parameters in the CricketerDAL and CricketerBL as we have added in the previous section here and change the method of API from the highlighted code below:

[HttpGet, ActionName("GetCricketerList")]
public HttpResponseMessage GetCricketerList(int PageNumber, int PageSize, string SortExpression)
{
    Result result;
    cricketerBL = new CricketerBL();
    try
    {
        var cricketerList = cricketerBL.GetCricketerList(PageNumber, PageSize, SortExpression);

        if (!object.Equals(cricketerList, null))
        {
            response = Request.CreateResponse<List<CricketerProfile>>(HttpStatusCode.OK, cricketerList);
        }
    }
    catch (Exception ex)
    {
        result = new Result();
        result.Status = 0;
        result.Message = ex.Message;
        response = Request.CreateResponse(HttpStatusCode.InternalServerError, result);
    }
    return response;
}

Step 3: Now change the CricketersController in web project from the highlighted code below: 

[HttpGet, ActionName("GetBestCricketers")]
public async Task<ActionResult> GetBestCricketers(int? PageNumber, int? PageSize, string SortExpression)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(PageNumber, null))
    {
        PageNumber = 1;
    }
    if (object.Equals(PageSize, null))
    {
        PageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    if (string.IsNullOrEmpty(SortExpression))
    {
        SortExpression = string.Empty;
    }
    ViewBag.PageNumber = PageNumber;
    ViewBag.PageSize = PageSize;
    ViewBag.CurrentSort = SortExpression;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={0}&PageSize={1}&SortExpression={2}", PageNumber, PageSize, SortExpression)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<BestCricketers.Models.CricketerProfile>>(responseAsString);
    return View("~/Views/Cricketers/BestCricketers.cshtml", new CricketerInfo() { cricketerProfile = cricketersList });
}

/// <summary>
/// This method is used to get cricketers list.
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="SortExpression"></param>
/// <returns></returns>
[HttpGet, ActionName("GetBestCricketersInfo")]
public async Task<ActionResult> GetBestCricketersInfo(int? page, int? pageSize, string SortExpression)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(page, null))
    {
        page = 1;
    }
    if (object.Equals(pageSize, null))
    {
        pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    if (string.IsNullOrEmpty(SortExpression))
    {
        SortExpression = string.Empty;
    }
    ViewBag.PageNumber = page;
    ViewBag.PageSize = pageSize;
    ViewBag.CurrentSort = SortExpression;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={0}&PageSize={1}&SortExpression={2}", page, pageSize, SortExpression)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<CricketerProfile>>(responseAsString);
    return PartialView("~/Views/Cricketers/_BestCricketersPartial.cshtml", new StaticPagedList<CricketerProfile>(cricketersList, Convert.ToInt32(page), Convert.ToInt32(pageSize), cricketersList.Count > 0 ? cricketersList.FirstOrDefault().TotalCount : 0));
}

Step 4: Now change the partial view code from the highlighted code below: 

@model PagedList.IPagedList<BestCricketers.Models.CricketerProfile>
@using PagedList.Mvc

<table class="table-responsive table">
    <thead>
        <tr>
            <th>Name</th>
            <th>
                @Ajax.ActionLink("ODI", "GetBestCricketersInfo", new
                {
                  SortExpression = ViewBag.CurrentSort == "ODI_Asc" ? "ODI_Desc" : "ODI_Asc",
                  currentFilter = ViewBag.CurrentFilter,
                  pageSize = ViewBag.PageSize

                }, new AjaxOptions
                {
                  LoadingElementId = "divProcessing",
                  InsertionMode = InsertionMode.Replace,
                  HttpMethod = "Get",
                  UpdateTargetId = "CricketersGrid"
               })
            </th>
            <th>
                @Ajax.ActionLink("Tests", "GetBestCricketersInfo", new
                {
                  SortExpression = ViewBag.CurrentSort == "Tests_Asc" ? "Tests_Desc" : "Tests_Asc",
                  currentFilter = ViewBag.CurrentFilter,
                  pageSize = ViewBag.PageSize

                }, new AjaxOptions
                {
                  LoadingElementId = "divProcessing",
                  InsertionMode = InsertionMode.Replace,
                  HttpMethod = "Get",
                  UpdateTargetId = "CricketersGrid"
              })
            </th>
            <th>
                @Ajax.ActionLink("ODI Runs", "GetBestCricketersInfo", new
               {
                  SortExpression = ViewBag.CurrentSort == "OdiRuns_Asc" ? "OdiRuns_Desc" : "OdiRuns_Asc",
                  currentFilter = ViewBag.CurrentFilter,
                  pageSize = ViewBag.PageSize

              }, new AjaxOptions
              {
                  LoadingElementId = "divProcessing",
                  InsertionMode = InsertionMode.Replace,
                  HttpMethod = "Get",
                  UpdateTargetId = "CricketersGrid"
              })
            </th>
            <th>
                @Ajax.ActionLink("Test Runs", "GetBestCricketersInfo", new
                {
                  SortExpression = ViewBag.CurrentSort == "TestRuns_Asc" ? "TestRuns_Desc" : "TestRuns_Asc",
                  currentFilter = ViewBag.CurrentFilter,
                  pageSize = ViewBag.PageSize

              }, new AjaxOptions
              {
                  LoadingElementId = "divProcessing",
                  InsertionMode = InsertionMode.Replace,
                  HttpMethod = "Get",
                  UpdateTargetId = "CricketersGrid"
              })
            </th>
        </tr>
    </thead>
    <tbody>
        @if (Model.Count > 0)
        {
            foreach (var cricketer in Model)
            {
                <tr>
                    <td>@cricketer.Name</td>
                    <td>@cricketer.ODI</td>
                    <td>@cricketer.Tests</td>
                    <td>@cricketer.OdiRuns</td>
                    <td>@cricketer.TestRuns</td>
                </tr>
            }
        }
        else
        {
            <tr>
                <td>
                    No Data Found
                </td>
            </tr>
        }
    </tbody>
</table>
@if (Model.TotalItemCount > Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DefaultPageSize"]))
{
    <div class="pagingBox">
        <input id="HiddenPageSize" name="PageSize" type="hidden" />
        <input id="HiddenPage" name="Page" type="hidden" />
        <span class="selectBoxes display_none_mobile">
            @Html.DropDownList("PageSize", new SelectList(new Dictionary<string, int> { { "10", 10 }, { "20", 20 } }, "Key", "Value", Convert.ToString(ViewBag.PageSize)), new { id = "pagesizelist" })
        </span>
        <div class="pagerecord display_none_mobile">
            Records

            Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
        </div>

        @Html.PagedListPager(Model, page => Url.Action("GetBestCricketersInfo", "Cricketers",
            new
            {
                page,
                SortExpression = ViewBag.CurrentSort,
                currentFilter = ViewBag.CurrentFilter,
                pageSize = ViewBag.PageSize
             }),
             PagedListRenderOptions.EnableUnobtrusiveAjaxReplacing(new PagedListRenderOptions
             {
                  Display = PagedListDisplayMode.IfNeeded,
                  MaximumPageNumbersToDisplay = 5
              },
              new AjaxOptions
              {
                   InsertionMode = InsertionMode.Replace,
                   HttpMethod = "Get",
                   UpdateTargetId = "CricketersGrid",
                   LoadingElementId = "divProcessing"
              }))
    </div>

    <div id="divProcessing" class="processingButton" style="display: none;">
        <img width="31" height="31" alt="" class="LoadingImage" src="~/Images/ajax-loader.gif" />
    </div>
}

Step 5: Now run the application and after opening the Cricketers page, click on the ODI link as shown below:


                                 Figure 17: Perform Sorting on MVC

When you click on the ODI link, the records will be sorted as ascending order of ODI matches


                                                Figure 18: Sorting in MVC

Note: I have created sorting on four fields. You can change out the condition based upon your situation. In this type of sorting, whole data is not sorted. The data is sorted on that page only but when you open the next page, the database will be called again and return the data as in the sorted order because we are sending the SortExpression also.

Filtering

In this section we will filter the data over Cricketer Name. We have to change the database and methods to do that. So, follow the steps below to perform that.

Step 1: Change the stored procedure from the following script:

ALTER PROC[dbo].[CC_GetCricketerList]
@PageNumber INT,
@PageSize INT,
@SortExp VARCHAR(20),
    @SearchText NVARCHAR(150)
AS
BEGIN;
WITH CteCricketer
AS(SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortExp = 'ODI_Asc'
    THEN ODI END ASC, CASE WHEN @SortExp = 'ODI_Desc'
    THEN ODI END DESC, CASE WHEN @SortExp = 'Tests_Asc'
    THEN Tests END ASC, CASE WHEN @SortExp = 'Tests_Desc'
    THEN Tests END DESC, CASE WHEN @SortExp = 'OdiRuns_Asc'
    THEN ODIRuns END ASC, CASE WHEN @SortExp = 'OdiRuns_Desc'
    THEN ODIRuns END DESC, CASE WHEN @SortExp = 'TestRuns_Asc'
    THEN TestRuns END ASC, CASE WHEN @SortExp = 'TestRuns_Desc'
    THEN TestRuns END DESC, CASE WHEN @SortExp = ''
    THEN ID END ASC)[RowNo], * FROM dbo.CricketerProfile(NOLOCK) WHERE Name LIKE '%' + @SearchText + '%')
SELECT CteCricketer.RowNo,
    CteCricketer.ID,
    CteCricketer.Name,
    CteCricketer.ODI,
    CteCricketer.Tests,
    CteCricketer.ODIRuns,
    CteCricketer.TestRuns, (SELECT COUNT(CteCricketer.ID) FROM CteCricketer) AS TotalCount
FROM CteCricketer
ORDER BY CteCricketer.RowNo
OFFSET(@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Step 2: Add the parameters in the CricketerDAL and CricketerBL and change the method of API from the highlighted code below: 

[HttpGet, ActionName("GetCricketerList")]
public HttpResponseMessage GetCricketerList(int PageNumber, int PageSize, string SortExpression, string SearchText)
{
    Result result;
    cricketerBL = new CricketerBL();
    try
    {
        var cricketerList = cricketerBL.GetCricketerList(PageNumber, PageSize, SortExpression, SearchText == "undefined" ? string.Empty : SearchText);

        if (!object.Equals(cricketerList, null))
        {
            response = Request.CreateResponse<List<CricketerProfile>>(HttpStatusCode.OK, cricketerList);
        }
    }
    catch (Exception ex)
    {
        result = new Result();
        result.Status = 0;
        result.Message = ex.Message;
        response = Request.CreateResponse(HttpStatusCode.InternalServerError, result);
    }
    return response;
}

Step 3: Now change the CricketersController in web project from the highlighted code below: 

public async Task<ActionResult> GetBestCricketers(int? PageNumber, int? PageSize, string SortExpression, string SearchText)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(PageNumber, null))
    {
        PageNumber = 1;
    }
    if (object.Equals(PageSize, null))
    {
        PageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    if (string.IsNullOrEmpty(SortExpression))
    {
        SortExpression = string.Empty;
    }
    if (string.IsNullOrEmpty(SearchText))
    {
        SearchText = "undefined";
    }
    ViewBag.PageNumber = PageNumber;
    ViewBag.PageSize = PageSize;
    ViewBag.CurrentSort = SortExpression;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m}&PageSize={1}&SortExpression={2}&SearchText={3}", PageNumber, PageSize, SortExpression, SearchText)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<BestCricketers.Models.CricketerProfile>>(responseAsString);
    return View("~/Views/Cricketers/BestCricketers.cshtml", new CricketerInfo() { cricketerProfile = cricketersList });
}

/// <summary>
/// This method is used to get cricketers list.
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="SortExpression"></param>
/// <returns></returns>
[HttpGet, ActionName("GetBestCricketersInfo")]
public async Task<ActionResult> GetBestCricketersInfo(int? page, int? pageSize, string SortExpression, string SearchText)
{
    List<CricketerProfile> cricketersList = new List<CricketerProfile>();
    var httpClient = new HttpClient();
    httpClient.BaseAddress = new Uri(ConfigurationManager.AppSettings["ApiUrl"]);
    httpClient.DefaultRequestHeaders.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    HttpResponseMessage response;

    if (object.Equals(page, null))
    {
        page = 1;
    }
    if (object.Equals(pageSize, null))
    {
        pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultPageSize"]);

    }
    if (string.IsNullOrEmpty(SortExpression))
    {
        SortExpression = string.Empty;
    }
    if (string.IsNullOrEmpty(SearchText))
    {
        SearchText = "undefined";
    }
    ViewBag.PageNumber = page;
    ViewBag.PageSize = pageSize;
    ViewBag.CurrentSort = SortExpression;

    response = httpClient.GetAsync(string.Format("api/Cricketers/GetCricketerList?PageNumber={paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m}&PageSize={1}&SortExpression={2}&SearchText={3}", page, pageSize, SortExpression, SearchText)).Result;

    response.EnsureSuccessStatusCode();
    var responseAsString = await response.Content.ReadAsStringAsync();
    cricketersList = JsonConvert.DeserializeObject<List<CricketerProfile>>(responseAsString);
    return PartialView("~/Views/Cricketers/_BestCricketersPartial.cshtml", new StaticPagedList<CricketerProfile>(cricketersList, Convert.ToInt32(page), Convert.ToInt32(pageSize), cricketersList.Count > paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m ? cricketersList.FirstOrDefault().TotalCount : paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m));
}

Step 4: Now change the Main view (BestCricketers.cshtml) code from the highlighted code below: 

@using (Ajax.BeginForm("GetBestCricketersInfo", "Cricketers", null, new AjaxOptions() { LoadingElementId = "divProcessing", HttpMethod = "Get", UpdateTargetId = "CricketersGrid" }, htmlAttributes: new { @id = "cricketerInfo" }))
{
    <div id="SearchCricketer" style="float:right;">
        <input type="text" class="form-control" id="TxtSearchCricketer" placeholder="Search" />
    </div>
    <div class="clearfix"></div>
    <div id="CricketersGrid">
        <div id="divProcessing" class="processingButton" style="display: none;">
            <img width="31" height="31" alt="" class="LoadingImage" src="~/Images/ajax-loader.gif" />
        </div>
        @Html.Partial("~/Views/Cricketers/_BestCricketersPartial.cshtml", new StaticPagedList<BestCricketers.Models.CricketerProfile>(Model.cricketerProfile, Convert.ToInt32(ViewBag.PageNumber), Convert.ToInt32(ViewBag.PageSize), Model.cricketerProfile.Count > paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m ? Model.cricketerProfile.FirstOrDefault().TotalCount : paging-sorting-and-filtering-with-partial-view-in-Asp-Net-m))
    </div>
}

<script type="text/javascript">
    var timer;
    $("#TxtSearchCricketer").on('keyup', function (event) {
        clearTimeout(timer);  //clear any running timeout on key up
        timer = setTimeout(function () { //then give it a second to see if the user is finished
            $.ajax({
                url:'@Url.Action("GetBestCricketersInfo","Cricketers")',
                dataType:"html",
                data:{page:@ViewBag.PageNumber,pageSize:@ViewBag.PageSize, SortExpression:'@ViewBag.CurrentSort', SearchText:$('#TxtSearchCricketer').val()},
                type:"Get",
                success: function (data) {
                    $("#divProcessing").hide();
                    $('#CricketersGrid').html(data);
                },
                beforeSend: function () {
                    $("#divProcessing").show();
                }
            })
        }, 5paging-sorting-and-filtering-with-partial-view-in-Asp-Net-mpaging-sorting-and-filtering-with-partial-view-in-Asp-Net-m);
    });
</script>

Note: I have created an Ajax call to perform the filtering in MVC grid. You can apply any other procedure to call the Action in Controller.

Step 5: Now run the application and you can see the Search Textbox.


                                                Figure 19: Filtering in MVC

Enter the name to filter the records.


Figure 20: Filtered Records in MVC

Summary

So far this article describes hpw to perform Paging, Sorting, and Filtering with the help of Partial Views in the ASP.NET MVC Application. You also learned the use of PagedList in View and Partial view. Thanks for reading this article. Happy coding! 

 

Read more articles on ASP.NET:

• MVC 5.0 Application Using Entity Framework DB First Approach - Part 8
• Restful API In ASP.NET: Getting Started