Server Side Paging, Sorting, and Filtering Using the Kendo Grid and Entity Framework In ASP.NET MVC

In this article, we will learn about how we can retrieve data from serverside Paging, Sorting, and Filtering Using the Kendo UI Grid and Entity Framework in MVC ASP .NET applications.

Create a new project and select the MVC pattern.

kendo

Here we use Entity Framework 6 with MVC5 :

Firstly install the Entity framework from the Package manager Console

Install-Package EntityFramework
  • First here is our SQL table:

table

  • So for this tutorial first we created a new empty MVC application. In this we will add an ADO .NET Entity Data model to the Model folder as in the following:

  • Select Data -> ADO.NET Entity Data Model. 
  • Select “EF Designer from database” :

  • Then select the Server name and Database name.

Database

  • Select the click on “Next”.

DBEntities

  • Select “Entity Framework 6.x”.

Entitie

  • Then select the table.

  • Now, Model1.edmx has been added to the Model folder.
  • Let’s get started.

Now, Add a new -> DefaultController and add the code to it.

using ServerSide_KendoGrid_Demo.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ServerSide_KendoGrid_Demo.Controllers
{
    public class DefaultController: Controller
    {
        DBStudentEntities _context = new DBStudentEntities();
        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public JsonResult ServerSide()
        {
            try
            {
                string pageNo = Request.Params.GetValues("page").FirstOrDefault();
                int pageOffSet = (Convert.ToInt32(pageNo) - 1) * 10;
                string pageSize = Request.Params.GetValues("pagesize").FirstOrDefault();
                string sortColumn = Request.Params.GetValues("sort[0][field]") == null ? "" : Request.Params.GetValues("sort[0][field]").FirstOrDefault();
                string sortOrder = Request.Params.GetValues("sort[0][dir]").FirstOrDefault();
                string searchFilter = Request.Params.GetValues("filter[filters][2][value]") == null ? "" : Request.Params.GetValues("filter[filters][2] 
                                                                  [value]").FirstOrDefault();

                var data = _context.tblStudents.ToList();

                int totalRecords = data.Count;
                if (!string.IsNullOrEmpty(searchFilter) &&
                    !string.IsNullOrWhiteSpace(searchFilter))
                {
                    data = data.Where(r => r.StudentName != null && r.StudentName.ToUpper().Contains(searchFilter.ToUpper()) ||
                                           r.Class != null && r.Class.ToUpper().Contains(searchFilter.ToUpper()) ||
                                           r.Course != null && r.Course.ToUpper().Contains(searchFilter.ToUpper()))
                                      .ToList();
                }
                data = SortTableData(sortColumn, sortOrder, data);

                int recFilter = data.Count;
                data = data.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
                var modifiedData = data.Select(d =>
                    new
                    {
                        d.ID,
                        d.StudentName,
                        d.Class,
                        d.Course
                    }
                    );


                return Json(new
                {
                    total = totalRecords,
                    data = modifiedData
                }, JsonRequestBehavior.AllowGet);


            }
            catch (Exception ex)
            {
                return Json(new
                {
                    total = 0,
                    data = ""
                }, JsonRequestBehavior.AllowGet);
            };
        }
        private List<tblStudent> SortTableData(string order, string orderDir, List<tblStudent> data)
        {
            List<tblStudent> lst = new List<tblStudent>();
            try
            {
                switch (order)
                {
                    case "0":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.StudentName).ToList()
                                                                                                 : data.OrderBy(p => p.StudentName).ToList();
                        break;
                    case "1":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Class).ToList()
                                                                                                 : data.OrderBy(p => p.Class).ToList();
                        break;
                    case "2":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Course).ToList()
                                                                                                 : data.OrderBy(p => p.Course).ToList();
                        break;
                    default:
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.ID).ToList()
                                                                                                 : data.OrderBy(p => p.ID).ToList();
                        break;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            return lst;
        }
    }
}
  • Now, Firstly we can DefaultController -> Index.cshtml

        Index.cshtml File:

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<div id="main">
    <div id="grid"></div>
</div>

@section scripts{
    <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2020.3.1021/styles/kendo.default-v2.min.css" />
    <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script src="https://kendo.cdn.telerik.com/2020.3.1021/js/kendo.all.min.js"></script>
    <script>
        $(document).ready(function () {
            var table = null;
            if (table != null) {
                var grid = $("#grid").data("kendoGrid");
                grid.destroy();
                table = null;
            }
            table = $("#grid").kendoGrid({
                dataSource: {
                    transport: {
                        read: {
                            url: "/KendioGrid/Serverside",
                            type: "POST",
                            dataType: "json"
                        }
                    },
                    schema: {
                        data: "data",
                        total: "total"
                    },
                    serverPaging: true,
                    serverSorting: true,
                    sort: { field: "Date", dir: "asc" },
                    serverFiltering: true,
                    pageSize: 10,
                },
                scrollable: false,
                sortable: {
                    allowUnsort: false
                },
                toolbar: ["search"],
                pageable: {
                    numeric: true,
                    input: true
                },
                groupable: false,
                filterable: false,
                columns: [
                    { field: "ID", title: "ID" },
                    { field: "StudentName", title: "StudentName" },
                    { field: "Class", title: "Class" },
                    { field: "Course", title: "Course" }
                ]
            });
        });
    </script>
}

That’s it.

  OUTPUT

I hope you guys understand how we can do that.

Let me know if you face any difficulties.

Happy Coding {;} 😎

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories