JQuery Datatables - .Net Core Part 1

In this series I'll start by demonstrating configuring JQuery Datatables with .Net Core. In the following parts adding the ability to integrate Bootstrap, Bootstrap dialog, right click context menus, Toastr and full CRUD functionality.

To begin I'll be using the Contoso University sample app available on Github, this application will provide a good starting point with a basic ASPNET Core MVC application using the Entity Framework.

Contoso University Sample App

Download the completed project from GitHub by downloading or cloning the aspnet/Docs repository and navigating to aspnetcore\data\ef-mvc\intro\samples\cu-final in your local file system.

ASPNET Docs Repository

After downloading the project, open the Visual Studio Solution and create the database by entering dotnet ef database update at a command-line prompt. As an alternative you can use Package Manager Console

Display The Package Manager Console

Execute the Database Update Command

Once the database update command has been executed, you can verify the database has been created by opening the SQL Server Object Explorer.

SQL Server Object Explorer

Next browse the databases and verify ContosoUniversity3 exists.

Databases

Run the application and verify that everything works.

Contoso Home Page

Select the Courses link in the top navigation

Courses Page

Replace the HTML Table with JQuery Datatables

Add Jquery Datatables to the Courses View (index.cshtml)

1. Download the following css and javascript files. 
1. JQuery 1.x
2. Bootstrap
3. Datatables
2. Extract these files to the following locations...

CSS and Javascript

• Add references to the following files in the _Layouts.cshtml page. 

References

Update the Courses Index View

    @model IEnumerable<ContosoUniversity.Models.Course>

    @ 

    {
        ViewData["Title"] = "Courses";
    }

    <h2> Courses < /h2>

    <p>
    <a asp - action = "Create" > Create New < /a> 

    </p> 

    <table id = "course-table"class = "table" >
    <thead>
    <tr>
    <th>@Html.DisplayNameFor(model => model.CourseID) </th>

    <th>@Html.DisplayNameFor(model => model.Title) </th> 

    <th>@Html.DisplayNameFor(model => model.Credits) </th>

    </tr> 

    </thead> 

    </table> 

    <script>
    loadTable();

    function loadTable() {
    table = $('#course-table').DataTable({
        'select': {
            'style': 'single'
        },
        "dom": 'lfrtip',
        "sScrollX": "100%",
        "serverSide": true,
        "ajax": {
            "url": '/Courses/LoadData',
            "type": "POST",
            "datatype": "json"
        },
        "columns": [{
                "data": "courseID"
            },
            {
                "data": "title"
            },
            {
                "data": "credits"
            }
        ]
    });
    } 

    </script>

Add the Datatables Controller Method



        [HttpPost]
        public IActionResult LoadData()
        {
            try
            {
                var draw = HttpContext.Request.Form["draw"].FirstOrDefault();

                // Skip number of Rows count  
                var start = Request.Form["start"].FirstOrDefault();

                // Paging Length 10,20  
                var length = Request.Form["length"].FirstOrDefault();

                // Sort Column Name  
                var sortColumn = Request.Form["columns[" + 
                Request.Form["order[0][column]"].FirstOrDefault() + 
                "][name]"].FirstOrDefault();

                // Sort Column Direction (asc, desc)  
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();

                // Search Value from (Search box)  
                var searchValue = Request.Form["search[value]"].FirstOrDefault();

                //Paging Size (10, 20, 50,100)  
                int pageSize = length != null ? Convert.ToInt32(length) : 0;

                int skip = start != null ? Convert.ToInt32(start) : 0;

                int recordsTotal = 0;

                // getting all Customer data  
                IQueryable<Course> customerData = (from course in _context.Courses
                                    select course);
                //Sorting  
                if (!string.IsNullOrEmpty(sortColumn))
                {
                    customerData = customerData.OrderBy(sortColumn + " " + 
                    sortColumnDirection);
                }
                //Search  
                if (!string.IsNullOrEmpty(searchValue))
                {
                    customerData = customerData.Where(m => m.Title.Contains(searchValue));
                }

                //total number of rows counts   
                recordsTotal = customerData.Count();
                //Paging   
                var data = customerData.Skip(skip).Take(pageSize).ToList();
                //Returning Json Data  
                return Json(new { draw = draw, recordsFiltered = recordsTotal, 
                recordsTotal = recordsTotal, data = data });

            }
            catch (Exception)
            {
                throw;
            }

        }

Run the application

The courses data should now be displayed in a JQuery Datatables format.