TechnoTips : Part 45 - Display record from database using Jquery DataTables plugin

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);

        }

All Code Factory

2 comments:

Unknown said...: Sir,

Error getting.. DataTable is not a function.
Tried all JS.. Normal JS n DataTable JS...with correct sequence.

Not working.. N may will not work further operations you have given related to DataTable.; April 8, 2018 at 10:47 PM
Reid Corcoran, Jr. said...: Hi Ashish,
I like the videos on datatables, thank you! Can you point me to some real-world examples where there is a textbox or a ddl to send a parameter to load the datatable, before the search. I want to have a datatable that loads from a large sql table of data, thousands of rows. So I want the user to request probably from textbox or a ddl to get a datatable of ONLY rows in a certain city, for example. Is it better to post the textbox/ddl choice to another view, or an ajax post to a partial view on the same page?
Thank you!; June 21, 2018 at 8:42 AM

TechnoTips

Labels

Friday, August 11, 2017

Part 45 - Display record from database using Jquery DataTables plugin

2 comments: