In this post we will learn “How to create editable cell in jquery datatable”. Basically in all sites data are displaying in the table format or any other format. In oldest web development time basically we was edit the data in the new page. Now a day’s user or client does not want to go ahead one step for edit data. They save their time and want that the data is editable on table and save. That’s why improve in the plug-ins and made Table plug-in for jquery (DataTable). Using DataTable developer can make inline editing in table.
“The goal of DataTable is to enhance the accessibility of data in HTML tables.”
Let’s create editable cell in jquery datatable.
For create inline editing cell, we must need a set of data so first we will create a Database and table. In the table data will added from the backend. Create new Database of “Demo”.
Table: tbl_employee
CREATE TABLE [dbo].[tbl_employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Fname] [varchar](50) NULL,
[Lname] [varchar](50) NULL,
[JobCode] [nvarchar](50) NULL,
[Salary] [nvarchar](20) NULL,
[Phone] [nvarchar](15) NULL,
CONSTRAINT [PK_tbl_employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create new project. Open visual studio and click on File menu and click new then the New Project Model is open, select the web option from left panel and select “ASP.NET Web Application” and select the Framework from top of model. Give the proper name of project and click on OK button and in this second panel select the MVC template and finally click on OK button. Your new MVC project is created. Here we use MVC project for this tutorial but it’s not necessary to create MVC project for implement Datatable, you can create it in your web application but remember that your application used Jquery.
For this tutorial we will use entity framework for database operation, so first install the latest package of “Entity Framework” by Nuget Package. After installed the Entity Framework we will add Entity Model. So first create Model folder (if not exists) in the project. Right click on Model folder and select Add New Item and then Select “Data” option from the left panel and select ADO.NET Entity Data Model and follow the steps for add Entity model.
Open the controller folder and create new controller of “EmployeeList”. Right click on Controllers folder and Add new class. A EmployeeList Controller is created then Inherit the “Controller“. In the EmployeeList Controller create the constructor one is default constructor and second is parameterized constructor. We call the employee service in the controller for business logic. So create the instance in the Constructor.
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
using System.Web.Mvc;
using DatatableInlineEdit.Models;
using UI.Employee.Services;
namespace Employee.Controllers
{
public class EmployeeListController : Controller
{
private readonly EmployeeService employeeService;
private readonly EmployeeService serviceOfEmployee = new EmployeeService();
public EmployeeListController(EmployeeService employeeservice)
{
this.employeeService = employeeservice;
}
public EmployeeListController()
{
this.employeeService = serviceOfEmployee;
}
}
}
We have created the constructor and created the instance of employee service. Now create ActionResult Controller for Employee List. In this controller we return the view. So Right click View () and Select Add View. Give the appropriate name of View and select the template from template dropdown menu and select the options. If you want to create partial view then select “Create as a partial view” or you want to use layout page (master page) then select “Use a layout page” and finally click on Add button.
In the View folder “Employee List” folder is automatically created because our controller name is “Employee List” and View ( .cshtml ) file is created same as Action name.
ShowEmployeeList (.cshtml)
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Employee List</title>
</head>
<body>
<div>
<div class="container">
<br />
<div class="row">
<h4><b>Employee List</b></h4>
</div>
<hr />
<div>
<table id="employeeListGrid" class="table table-striped table-bordered dt-responsive nowrap dataTable no-footer dtr-inline" width="100%" cellspacing="0">
<thead>
<tr>
<th>Id</th>
<th>Fname</th>
<th>Lname</th>
<th>JobCode</th>
<th>Salary</th>
<th>Phone</th>
</tr>
</thead>
</table>
</div>
</div>
<br />
</div>
</body>
</html>
In view page we have created the table and set the data heading with “th” tag. Give the id of table because we bind the data in this table using Jquery Datatable. For creating editable cell in jquery datatable we must need some (.Js) and (.css) file. Download below files and add it in your project or inject it by link. We have downloaded all required files and add it in project.
Js Files
CSS Files
Add the above Js and Css file same as above sequence. Lets we add the code for bind the data by ajax call using Jquery Datatable.
<script type="text/javascript">
$(document).ready(function () {
var editIcon = function (data, type, row) {
if (type === 'display') {
if (data != null) {
return data + ' <i class="fa fa-pencil"/>';
}
else {
return '<i class="fa fa-pencil"/>';
}
}
return data;
};
$('#employeeListGrid').dataTable({
"destroy": true,
"searching": true,
"processing": true, -- this is showing load processing while load data.
"serverSide": true,
"filter": true, -- this is for the filter data.
"orderMulti": false, -- this is for multi column ascending and descending order.
"scrollX": true, -- this is for scrolling horizontal and vertical.
"pageLength": 10, -- this is for data per page.
"select": {
"info": false
},
"ajax": {
"url": "/EmployeeList/LoadData", -- call the controller action.
"type": "POST",
"cache": "false",
"datatype": "json"
},
"columns": [
{ "data": "Id", "name": "Id", "autowidth": true },
{ "data": "Fname", "name": "Fname", "autowidth": true },
{ "data": "Lname", "name": "Lname", "autowidth": true },
{ "data": "JobCode", "name": "JobCode", "autowidth": true },
{ "data": "Salary", "name": "Salary", "autowidth": true, "editField": "Salary", "render": editIcon },
{ "data": "Phone", "name": "Phone", "autowidth": true, "editField": "Phone", "render": editIcon },
], select: { style: 'os' },
});
})
</script>
In the Jquery Datatable we have call the LoadData action in Employee List Controller, so we will create “LoadData” action in Controller. From this action return the requested data. We will create all the methods or actions are “async”. For the Editable column we need to add “editField” property in Columns array.
public async Task<ActionResult> LoadData()
{
try
{
using (DemoEntities objEntity = new DemoEntities())
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
// Getting all employee data
List<tbl_employee> EmployeeList = await employeeService.listOfEmployees();
//Search
if (!string.IsNullOrEmpty(searchValue))
{
EmployeeList = EmployeeList.Where(d =>
(d.Fname != null && d.Fname.ToLower().Contains(searchValue.ToLower())) ||
(d.Lname != null && d.Lname.ToLower().Contains(searchValue.ToLower())) ||
(d.JobCode != null && d.JobCode.ToLower().Contains(searchValue.ToLower())) ||
(d.Salary != null && d.Salary.ToLower().Contains(searchValue.ToLower())) ||
(d.Phone != null && d.Phone.ToLower().Contains(searchValue.ToLower()))).ToList();
}
//Sorting
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
EmployeeList = SortByColumnWithOrder(sortColumn, sortColumnDir, EmployeeList);
}
//total number of rows count
recordsTotal = EmployeeList.Count();
//Paging
var employeeData = EmployeeList.Skip(skip).Take(pageSize).ToList();
//Returning Json Data
return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = employeeData });
}
}
catch (Exception ex)
{
throw;
}
}
private List<tbl_employee> SortByColumnWithOrder(string sortColumn, string sortColumnDir, List<tbl_employee> data)
{
// Sorting
switch (sortColumn)
{
case "Id":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList() : data.OrderBy(p => p.Id).ToList();
break;
case "Fname":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Fname).ToList() : data.OrderBy(p => p.Fname).ToList();
break;
case "Lname":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Lname).ToList() : data.OrderBy(p => p.Lname).ToList();
break;
case "JobCode":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.JobCode).ToList() : data.OrderBy(p => p.JobCode).ToList();
break;
case "Salary":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Salary).ToList() : data.OrderBy(p => p.Salary).ToList();
break;
case "Phone":
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Phone).ToList() : data.OrderBy(p => p.Phone).ToList();
break;
default:
data = sortColumnDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Fname).ToList() : data.OrderBy(p => p.Fname).ToList();
break;
}
return data;
}
From this controller get the list of employee from the employee service. In employee service we will write the business logic and Database related action. So let’s create the employee service, create new (Service) folder and then create employee service by right click on Service folder and add new class. When employee service is created then create the “async” method of “listOfEmployees”.
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
using DatatableInlineEdit.Models;
namespace UI.Employee.Services
{
public class EmployeeService
{
public async Task<List<tbl_employee>> listOfEmployees()
{
using (DemoEntities objEntity = new DemoEntities())
{
return await Task.FromResult(objEntity.tbl_employee.ToList());
}
}
}
}
Till now, we implemented the code for bind data using Jquery Datatable. Add the above code in your project step by step and then Run project and will see the output. There should be data display in datatable.
Let’s we write the code for inline editing cell. When you click on cell or editable icon the field is in editable mode if the value is string type then editable control is textbox. You can set editable control (checkbox, dropdown, etc). We will add the Textbox editable control for Salary and Phone fileds. Add the below code in script tag.
<script type="text/javascript">
var editor;
$(document).ready(function () {
editor = new $.fn.dataTable.Editor({
ajax: "/EmployeeList/UpdateDataTable", -- this is a updated value ajax call.
table: "#employeeListGrid", -- this is a table id.
idSrc: 'Id', -- this is most important 'here we need to give unique identifier key'.
formOptions: {
inline: {
onReturn: 'submit',
submit: 'changed', -- It is define that when the changes are made then should be call update methhod otherwise not.
} },
fields: [{
label: "Salary:",
name: "Salary"
},
{
label: "Phone:",
name: "Phone",
}],
});
})
//… This is a function to activate an inline edit on click of a table cell
$('#employeeListGrid').on('click', 'tbody td:not(:first-child)', function (e) {
editor.inline(this, {
submit: 'changed',
buttons: {
label: '>', fn: function () {
this.submit(function complete() {
//.. add your code here after complete this action.
}
);
},
},
}
);
});
</script>
The above code is send only updated cell value only with Id and action of “edit”. For updating the value call the “UpdateDataTable” action in Employee List controller, that we have ajax call in “$.fn.dataTable.Editor”. So let’s create the UpdateDatatable method in Employee List Controller.
[HttpPost]
public async Task<JsonResult> UpdateDataTable()
{
string fieldName = string.Empty;
tbl_employee objTblEmployee = null;
try
{
var action = Request.Form.GetValues("action").FirstOrDefault();
var updatedValue = Request.Form.GetValues(Request.Form.GetKey(1)).FirstOrDefault();
var updatedfileds = Request.Form.GetKey(1);
var splitedFields = updatedfileds.Split('[');
int ID = 0;
if (splitedFields.Length > 2)
{
var splitForId = splitedFields[1].Split(']');
if (splitForId.Length > 1)
{
ID = Convert.ToInt32(splitForId[0]);
}
var field = splitedFields[2].Split(']');
if (field.Length > 1)
{
fieldName = field[0];
}
}
if (ID != 0 && !string.IsNullOrEmpty(fieldName))
{
await employeeService.updateFieldOfEmployee(ID, fieldName, updatedValue);
}
}
catch (Exception ex)
{
return Json(data: "something went wrong", behavior: JsonRequestBehavior.AllowGet);
}
return Json(new { data = List<T> object });
}
We find the action, updated field and update value from the Request Form and by that information we can update the value for that field. An update value task we implement in the employee service method and call that function in controller like above code.
public async Task<string> updateFieldOfEmployee(int ID, string fieldName, string updatedValue)
{
string update = string.Empty;
using (DemoEntities objEntity = new DemoEntities())
{
tbl_employee objtblEmployee = objEntity.tbl_employee.Where(a => a.Id == ID).FirstOrDefault();
if (objtblEmployee != null)
{
if (fieldName == "Fname")
{
objtblEmployee.Fname = updatedValue;
}
if (fieldName == "Lname")
{
objtblEmployee.Lname = updatedValue;
}
if (fieldName == "JobCode")
{
objtblEmployee.JobCode = updatedValue;
}
if (fieldName == "Salary")
{
objtblEmployee.Salary = updatedValue;
}
if (fieldName == "Phone")
{
objtblEmployee.Phone = updatedValue;
}
objEntity.Entry(objtblEmployee).State = EntityState.Modified;
objEntity.SaveChanges();
update = "Updated";
}
}
return await Task.FromResult(update);
}
When your updating task is completed we must return the updated json to view for refreshing the updated table cell. If you do not return the updated json the datatable will not be update. In UpdateDataTable action we return Json( new { data = List object }); replacement of (List object) pass your update list of object. The Datatable automatically update the table while you return the list.
Follow the above instruction and add the code step by step in your project and run your project and try to editing cell value and update the cell value. If you want to make editable column or row or anything else then click on link of Learn More.
I hope this tutorial is useful for making inline editable cell using Jquery DataTable.
Thank’s
Hello, my name is Gautam Nagraj. I am one year old blogger.We create and find innovative solution.
gracias
ReplyDelete