Part 45 - Display record from database using Jquery DataTables plugin

Friday, 11 August 2017

In this video you will be able to display data from DATABASE using jquery datatable plugin . before starting anything, please download latest version of Jquery DataTable. Click here to download the latest version of Jquery Datatable. You can watch my previous tutorial to get step by step DataTable plugin installation guide. you can visit here: Integrate JQuery DataTable plugin into Asp.net MVC

The expected output will be as what displayed in following image.

# View Page (Index.cshtml)

Right click on your controller' s Index method and add a view. After adding view, replace content with below code.

<div class="col-md-9">
        <table class="display" id="MyDataTable">
            <thead>
                <tr>
                    <th>
                        EmaployeeName
                    </th>
                    <th>
                        Department
                    </th>
                    <th>
                        Address
                    </th>
                    <th>
                        EmployeeId
                    </th>
                </tr>
            </thead>

            <tbody></tbody>

        </table>

    </div>

<script>

        $(document).ready(function () {

            // $("#MyDataTable").DataTable();

            GetEmployeeRecord();
        })
        var GetEmployeeRecord = function () {

            $.ajax({

                type: "Get",
                url: "/Test/GetEmployeeRecord",
                success: function (response) {

                    BindDataTable(response);

                }
            })

        }


        var BindDataTable = function (response) {

            $("#MyDataTable").DataTable({

                "aaData": response,
                "aoColumns": [

                    { "mData": "Name" },
                    { "mData": "DepartmentName" },
                    { "mData": "Address" },
                    { "mData": "EmployeeId" },


                ]

            });
        }


</script>

# ViewModel (EmployeeViewModel)

Create two table in database i.e. Employee and Department table. Insert few record into it. Then finally refresh your entity framework. Entity framework will automatically create Employee and Department class and if they are connected to each other then, You will see the virtual keyword referencing Department table in employee class.
Create a class named EmployeeViewModel and add same property into it as present in Employee class created by entity framework.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace MVCTutorial.Models
{
    public class EmployeeViewModel
    {
        public int EmployeeId { get; set; }

        [Required(ErrorMessage = "Enter Name")]
        public string Name { get; set; }

        [Required(ErrorMessage = "Enter Department")]
        public Nullable<int> DepartmentId { get; set; }

        [Required(ErrorMessage = "Enter Address")]
        public string Address { get; set; }

        public Nullable<bool> IsDeleted { get; set; }

        //Custom attribute
        public string DepartmentName { get; set; }
        public bool Remember { get; set; }
        public string SiteName { get; set; }
       
    }
}

# Controller Code (TestController.cs)

Create a controller and add below method into it.

  public JsonResult GetEmployeeRecord()
        {

            MVCTutorialEntities db = new MVCTutorialEntities();

            List<EmployeeViewModel> List = db.Employees.Select(x => new EmployeeViewModel
            {
                Name = x.Name,
                EmployeeId = x.EmployeeId,
                DepartmentId = x.DepartmentId,
                DepartmentName = x.Department.DepartmentName,
                Address=x.Address,
                IsDeleted = x.IsDeleted
            }).ToList();

            return Json(List, JsonRequestBehavior.AllowGet);

        }