Sunday, August 27, 2017

Part 48 - JQuery server side processing -Search functionality

In this video you will be able to know how to implement search functionality in server side processing in JQuery Datatables. 

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 

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. 

@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>
        <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 BindDataTable = function (response) {

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

                    $.ajax({

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

                },
                "aoColumns": [

                    { "mData": "Name" },
                    { "mData": "DepartmentName" },
                    { "mData": "Address" },
                    {
                        "mData": "EmployeeId",
                        "render": function (EmployeeId, type, full, meta) {
                            debugger
                            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. Here in GetEmployeeRecord method
you will receive the search text in sSearch attribute. Based on that you can write code for searching 

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

            MVCTutorialEntities db = new MVCTutorialEntities();
            List<EmployeeViewModel> List = new List<EmployeeViewModel>();
            
            if (param.sSearch != null)
            {

                List = db.Employees.Where(x => x.Name.Contains(param.sSearch) || x.Department.DepartmentName.Contains(param.sSearch) || x.Address.Contains(param.sSearch)).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
            {
                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(new
            {
                aaData = List,
                sEcho = param.sEcho,
                iTotalDisplayRecords = List.Count(),
                iTotalRecords = List.Count()

            }
                , 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


1 comment:

ziril said...

show entries dropdown in datatable server side not showing the correct number of data?