Sunday, November 26, 2017

Part 50 - Refresh DataTable After Performing Any Action in ASP.NET MVC



In this video you will be able to know how refresh datatable after performing any action like add edit delete etc. in server side processing 

If you are new to DataTables then please download latest version of Jquery DataTable. Click here to download the latest version of Jquery Datatable and 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 


# View Page (Index.cshtml)
Right click on your controller' s Index method and add a view. After adding view, replace content with below code. 

@model MVCTutorial.Models.EmployeeViewModel
@{
    ViewBag.Title = "Index";
    // Layout = null;
}

<div class="panel panel-body" style="min-height:256px">

    <div class="col-md-3">

        @{ Html.RenderAction("SideMenu", "Test");}

    </div>

    <div class="col-md-9">

        <div class="well">
            <a href="#" class="btn btn-primary" onclick="AddEditEmployee(0)">New</a>
        </div>

        <div class="well">
            <div class="col-md-3">
                <input type="text" id="EName" class="form-control" />
               
            </div>
            <div class="col-md-3">
                <a href="#" class="btn  btn-primary" onclick="FilterRecord()">Filter</a>
                
            </div>
            <div class="clearfix"></div>
        </div>
        <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 class="modal fade" id="myModal1">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">
                        <a href="#" class="close" data-dismiss="modal">&times;</a>
                        <h3 class="modal-title">AddEdit Employee</h3>
                    </div>
                    <div class="modal-body" id="myModalBodyDiv1">


                    </div>


                </div>

            </div>

        </div>

        <input type="hidden" id="hiddenEmployeeId" />
    </div>
</div>

<script>

    $(document).ready(function () {

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

        BindDataTable();
    })

    var oTable;
    var FilterRecord = function () {

        BindDataTable();
    }

    var BindDataTable = function (response) {

        if ($.fn.DataTable.isDataTable("#MyDataTable")) {
            debugger
            oTable.draw();
            //oTable.fnDraw();

        }
        else {
            debugger
            oTable=  $("#MyDataTable").DataTable({
                "bServerSide": true,
                "sAjaxSource": "/Test/GetEmployeeRecord",
                "fnServerData": function (sSource, aoData, fnCallback) {

                    var EName = $("#EName").val();

                    aoData.push({ "name": "EName", "value": EName });

                    $.ajax({

                        type: "Get",
                        data: aoData,
                        url: sSource,
                        success: fnCallback
                    })

                },
                "aoColumns": [

                    { "mData": "Name" },
                    { "mData": "DepartmentName" },
                    { "mData": "Address" },
                    {
                        "mData": "EmployeeId",
                        "render": function (EmployeeId, type, full, meta) {

                            return '<a href="#" onclick="AddEditEmployee(' + EmployeeId + ')"><i class="glyphicon glyphicon-pencil"></i></a>'
                        }
                    },


                ]

            });
        }
    }
    var AddEditEmployee = function (employeeId) {

        var url = "/Test/AddEditEmployee?EmployeeId=" + employeeId;

        $("#myModalBodyDiv1").load(url, function () {
            $("#myModal1").modal("show");

        })

    }
</script>



  # DataTablesParam Class.
Create a class named DataTableParams and copy below code into this. This class will be used for receiving dataTables default parameters. You can remove extra properties which are not useful to you. Also, please don't forget to copy EmployeeViewModel  from Part 20 of this tutorial series. 

public class DataTablesParam
    {
        public int iDisplayStart { get; set; }
        public int iDisplayLength { get; set; }
        public int iColumns { get; set; }
        public string sSearch { get; set; }
        public bool bEscapeRegex { get; set; }
        public int iSortingCols { get; set; }
        public int sEcho { get; set; }
        public List<string> sColumnNames { get; set; }
        public List<bool> bSortable { get; set; }
        public List<bool> bSearchable { get; set; }
        public List<string> sSearchValues { get; set; }
        public List<int> iSortCol { get; set; }
        public List<string> sSortDir { get; set; }
        public List<bool> bEscapeRegexColumns { get; set; }

        public DataTablesParam()
        {
            sColumnNames = new List<string>();
            bSortable = new List<bool>();
            bSearchable = new List<bool>();
            sSearchValues = new List<string>();
            iSortCol = new List<int>();
            sSortDir = new List<string>();
            bEscapeRegexColumns = new List<bool>();
        }

        public DataTablesParam(int iColumns)
        {
            this.iColumns = iColumns;
            sColumnNames = new List<string>(iColumns);
            bSortable = new List<bool>(iColumns);
            bSearchable = new List<bool>(iColumns);
            sSearchValues = new List<string>(iColumns);
            iSortCol = new List<int>(iColumns);
            sSortDir = new List<string>(iColumns);
            bEscapeRegexColumns = new List<bool>(iColumns);
        }


    }



# Controller Code (TestController.cs)
Create a Test controller and copy below code into this. 

using MVCTutorial.Models;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.Mvc;

namespace MVCTutorial.Controllers
{

    public class TestController : Controller
    {

        public ActionResult Index()
        {
            MVCTutorialEntities db = new MVCTutorialEntities();

            List<Department> list = db.Departments.ToList();
            ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");

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

            ViewBag.EmployeeList = listEmp;

            return View();
        }

        public ActionResult SideMenu()
        {
            return PartialView("SideMenu");
        }

        public JsonResult GetEmployeeRecord(DataTablesParam param, string EName)
        {

            MVCTutorialEntities db = new MVCTutorialEntities();
            List<EmployeeViewModel> List = new List<EmployeeViewModel>();

            int pageNo = 1;

            if (param.iDisplayStart >= param.iDisplayLength)
            {

                pageNo = (param.iDisplayStart / param.iDisplayLength) + 1;

            }

            int totalCount = 0;

            if (param.sSearch != null)
            {
                totalCount = db.Employees.Where(x => x.Name.Contains(param.sSearch) || x.Department.DepartmentName.Contains(param.sSearch) || x.Address.Contains(param.sSearch)).Count();

                List = db.Employees

                    .Where(x => x.Name.Contains(param.sSearch) || x.Department.DepartmentName.Contains(param.sSearch) || x.Address.Contains(param.sSearch))

                    .OrderBy(x => x.EmployeeId)
                    .Skip((pageNo - 1) * param.iDisplayLength)
                    .Take(param.iDisplayLength)

                    .Select(x => new EmployeeViewModel
               {
                   Name = x.Name,
                   EmployeeId = x.EmployeeId,
                   DepartmentId = x.DepartmentId,
                   DepartmentName = x.Department.DepartmentName,
                   Address = x.Address,
                   IsDeleted = x.IsDeleted
               }).ToList();
            }
            else if (EName != null)
            {
                totalCount = db.Employees.Where(x => x.Name.Contains(EName)).Count();

                List = db.Employees

                    .Where(x => x.Name.Contains(EName))

                    .OrderBy(x => x.EmployeeId)
                    .Skip((pageNo - 1) * param.iDisplayLength)
                    .Take(param.iDisplayLength)

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

            }
            else
            {
                totalCount = db.Employees.Count();

                List = db.Employees.OrderBy(x => x.EmployeeId).Skip((pageNo - 1) * param.iDisplayLength).Take(param.iDisplayLength).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(new
            {
                aaData = List,
                sEcho = param.sEcho,
                iTotalDisplayRecords = totalCount,
                iTotalRecords = totalCount

            }
                , JsonRequestBehavior.AllowGet);

        }

        [HttpPost]
        public ActionResult Index(EmployeeViewModel model)
        {
            try
            {
                MVCTutorialEntities db = new MVCTutorialEntities();
                List<Department> list = db.Departments.ToList();
                ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");

                if (model.EmployeeId > 0)
                {
                    //update
                    Employee emp = db.Employees.SingleOrDefault(x => x.EmployeeId == model.EmployeeId && x.IsDeleted == false);

                    emp.DepartmentId = model.DepartmentId;
                    emp.Name = model.Name;
                    emp.Address = model.Address;
                    db.SaveChanges();


                }
                else
                {
                    //Insert
                    Employee emp = new Employee();
                    emp.Address = model.Address;
                    emp.Name = model.Name;
                    emp.DepartmentId = model.DepartmentId;
                    emp.IsDeleted = false;
                    db.Employees.Add(emp);
                    db.SaveChanges();

                }
                return View(model);

            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public ActionResult AddEditEmployee(int EmployeeId)
        {
            MVCTutorialEntities db = new MVCTutorialEntities();
            List<Department> list = db.Departments.ToList();
            ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");

            EmployeeViewModel model = new EmployeeViewModel();

            if (EmployeeId > 0)
            {

                Employee emp = db.Employees.SingleOrDefault(x => x.EmployeeId == EmployeeId && x.IsDeleted == false);
                model.EmployeeId = emp.EmployeeId;
                model.DepartmentId = emp.DepartmentId;
                model.Name = emp.Name;
                model.Address = emp.Address;

            }
            return PartialView("Partial2", model);
        }


    }
}




All Code Factory



2 comments:

rmouniak said...

Nice post!
.Net Online Course

Unknown said...

Thanks for the powerful tutorial.. I wanted to findout.. on part 45 - 49 you talked about datatable.. I see the sorting is not working.. is there anything that i'm missing?