In one of my previous article: “Create a Custom SharePoint Webpart will have Kendo grid to fetch/add/update SharePoint list data through WebAPI”, there I explained already how to achieve the combined functionalities of batch editing & row filtering in Kendo UI. There I used the WebAPI to fetch/add/edit the data but now in this artifact let me implement the same functionality using Asp.net MVC to fetch/add/edit the database data.
Let’s first create an Asp.Net empty web solution.
Here we will be using Entity framework to connect with database, fetch/add the data into the database
Right click the project — > Manage Nuget packages. Search for Entity Framework and click on install.
Now we will be creating the data access layer. Create a folder named as “DAL” within the solution
Within the folder we will be creating a class named as “EmployeeDAL”, below is the code:
public class EmployeeDAL : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelbuilder)
{
modelbuilder.Entity<DepartmentModel>().ToTable("Department");
modelbuilder.Entity<EmployeeModel>().ToTable("Employee");
base.OnModelCreating(modelbuilder);
}
public DbSet<EmployeeModel> Employees { get; set; }
public DbSet<DepartmentModel> Departments { get; set; }
}
In web.config we will have to mention the connection string, the name attribute value will be same as DAL class name:
<connectionStrings>
<add name="EmployeeDAL" connectionString="Data Source=PC258340;Initial Catalog=EmployeeDB;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
Here are my EmployeeModel & DepartmentModel classes within the folder named as “Models”
public class EmployeeModel
{
[Key]
public int EmployeeId { get; set; }
[Required]
public string Name { get; set; }
[Required]
public int Salary { get; set; }
[Required]
public int DepartmentId { get; set; }
[Required]
public DateTime CreatedDate { get; set; }
}
public class DepartmentModel
{
[Key]
public int DepartmentId { get; set; }
public string DeptName { get; set; }
}
EmployeeBusinessLayer model will have two methods to Edit/Save multiple records to the database by using the entity framework.
public class EmployeeBusinessLayer
{
int num = 0;
public int EditEmployee(List<EmployeeModel> lstEM)
{
EmployeeDAL empDal = new EmployeeDAL();
foreach (var item in lstEM)
{
empDal.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
num = empDal.SaveChanges();
return num;
}
public int SaveEmployee(List<EmployeeModel> lstEM)
{
EmployeeDAL empDal = new EmployeeDAL();
foreach (var item in lstEM)
{
empDal.Employees.Add(item);
}
num = empDal.SaveChanges();
return num;
}
}
Run the application & call the below controller method will be creating the EmployeeDB database, Employee & Department tables automatically
[HttpGet]
public string GetDepartments()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
return JsonConvert.SerializeObject(objEmployeeDAL.Departments);
}
For adding record to the Employee & Department tables use the following script:
USE [EmployeeDB]
GO
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentId], [DeptName]) VALUES (1, N'Accounts')
INSERT [dbo].[Department] ([DepartmentId], [DeptName]) VALUES (2, N'Technical')
INSERT [dbo].[Department] ([DepartmentId], [DeptName]) VALUES (3, N'Finance')
INSERT [dbo].[Department] ([DepartmentId], [DeptName]) VALUES (4, N'Testing')
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeId], [Name], [Salary], [DepartmentId], [CreatedDate]) VALUES (1, N'Tarun1', 100000, 4, GETDATE())
INSERT [dbo].[Employee] ([EmployeeId], [Name], [Salary], [DepartmentId], [CreatedDate]) VALUES (2, N'Tarun2', 200000, 2, GETDATE())
INSERT [dbo].[Employee] ([EmployeeId], [Name], [Salary], [DepartmentId], [CreatedDate]) VALUES (3, N'Tarun3', 300000, 3, GETDATE())
INSERT [dbo].[Employee] ([EmployeeId], [Name], [Salary], [DepartmentId], [CreatedDate]) VALUES (4, N'Tarun4', 400000, 4, GETDATE())
SET IDENTITY_INSERT [dbo].[Employee] OFF
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
Under the Models folder we will be creating one more model named as “JsonpResult” to convert & return the data in Jasonp format
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;
namespace EmployeeTest.Models
{
public class JsonpResult : JsonResult
{
public string CallbackFunction { get; set; }
public Encoding ContentEncoding { get; set; }
public string ContentType { get; set; }
public object Data { get; set; }
public JsonpResult(object data) : this(data, null) { }
public JsonpResult(object data, string callbackFunction)
{
Data = data;
CallbackFunction = callbackFunction;
}
public override void ExecuteResult(ControllerContext context)
{
if (context == null) throw new ArgumentNullException("context");
HttpResponseBase response = context.HttpContext.Response;
response.ContentType = string.IsNullOrEmpty(ContentType) ? "application/x-javascript" : ContentType;
if (ContentEncoding != null) response.ContentEncoding = ContentEncoding;
if (Data != null)
{
HttpRequestBase request = context.HttpContext.Request;
var callback = CallbackFunction ?? request.Params["callback"] ?? "callback";
var serializer = new JavaScriptSerializer();
response.Write(callback.ToString() + "(" + serializer.Serialize(Data).ToString() + ")");
}
}
}
}
Now we will be creating Controller methods to Get/Save the data
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.BaseUrl = Request.Url.OriginalString;
return View();
}
[HttpGet]
public string GetDepartments()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
return JsonConvert.SerializeObject(objEmployeeDAL.Departments);
}
[HttpPost]
public JsonpResult GetEmployees(string filterSpan)
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
List<EmployeeModel> emp = objEmployeeDAL.Employees.ToList();
if (filterSpan == "today")
{
DateTime fromDateFx = DateTime.Now.Date;
DateTime toDateFx = DateTime.Now.AddDays(1).Date;
emp = emp.Where(i => i.CreatedDate >= fromDateFx && i.CreatedDate <= toDateFx).ToList();
}
if (filterSpan == "week")
{
DateTime fromDateFx = FirstDayOfWeek(DateTime.Now);
DateTime toDateFx = LastDayOfWeek(DateTime.Now);
emp = emp.Where(i => i.CreatedDate >= fromDateFx && i.CreatedDate <= toDateFx).ToList();
}
return new JsonpResult(emp);
}
[HttpPost]
public JsonpResult CreateEmployee([Bind(Prefix = "models")]string strResult)
{
var newRequest = new JavaScriptSerializer().Deserialize<List<EmployeeModel>>(strResult);
try
{
EmployeeBusinessLayer empBL = new EmployeeBusinessLayer();
empBL.SaveEmployee(newRequest);
return new JsonpResult(newRequest);
}
catch (Exception ex)
{
throw new Exception("Error in edit." + ex.Message);
}
}
[HttpPost]
public JsonpResult EditEmployee([Bind(Prefix = "models")]string strResult)
{
var editRequest = new JavaScriptSerializer().Deserialize<List<EmployeeModel>>(strResult);
try
{
EmployeeBusinessLayer empBL = new EmployeeBusinessLayer();
empBL.EditEmployee(editRequest);
return new JsonpResult(editRequest);
}
catch (Exception ex)
{
throw new Exception("Error in edit." + ex.Message);
}
}
private DateTime FirstDayOfWeek(DateTime date)
{
DayOfWeek fdow = CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek;
int offset = fdow - date.DayOfWeek;
DateTime fdowDate = date.AddDays(offset);
return fdowDate;
}
private DateTime LastDayOfWeek(DateTime date)
{
DateTime ldowDate = FirstDayOfWeek(date).AddDays(6);
return ldowDate;
}
}
In Global.asax.cs we will have the following configuration:
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
}
//In RouteConfig.cs we will have the following configuration
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}
Next we will be creating the Views
Code snippet of Shared — > _Layout.cshtml
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width" />
<title>@ViewBag.Title</title>
<link href="~/Content/Site.css" rel="stylesheet" />
<link href="http://kendo.cdn.telerik.com/2015.2.624/styles/kendo.common.min.css" rel="stylesheet">
<link href="http://kendo.cdn.telerik.com/2015.2.624/styles/kendo.rtl.min.css" rel="stylesheet">
<link href="http://kendo.cdn.telerik.com/2015.2.624/styles/kendo.default.min.css" rel="stylesheet">
<link href="http://kendo.cdn.telerik.com/2015.2.624/styles/kendo.dataviz.min.css" rel="stylesheet">
<link href="http://kendo.cdn.telerik.com/2015.2.624/styles/kendo.dataviz.default.min.css" rel="stylesheet">
<script src="https://kendo.cdn.telerik.com/2015.2.624/js/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2015.2.624/js/kendo.all.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2015.2.624/js/jszip.min.js"></script>
</head>
<body>
<header>
<div>
<div>
@RenderBody()
</div>
</div>
</header>
</body>
</html>
Code snippet of Home– > Index.cshtml
@model IEnumerable<KendoTest.Models.EmployeeModel>
@{
ViewBag.Title = "Employee";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Employee</h2>
<div id="outerWrapper">
<div id="grid"></div>
<script type="text/javascript">
var baseUrl = '@ViewBag.BaseUrl' + 'Home';
var departmentsDS = new kendo.data.DataSource({
transport: {
read: {
url: baseUrl + '/GetDepartments',
type: "GET",
contentType: "application/json",
async: false
}
},
schema: {
model: {
fields: {
DepartmentId: { type: "int" },
DeptName: { type: "string" },
}
}
}
});
var departments = {};
departmentsDS.fetch(function () {
departments = this.data();
});
function clearButtonHighlight() {
$('.k-grid-filterweek').css('background-color', '');
$('.k-grid-filtertoday').css('background-color', '');
$('.k-grid-filterall').css('background-color', '');
}
$(window).resize(function () {
resizeGrid();
});
function resizeGrid() {
$("#grid").height($(window).height() - 200);
$("#grid").width($(window).width() - 60);
}
$(document).ready(function () {
$(window).trigger("resize");
var dataSource = new kendo.data.DataSource({
transport: {
read: {
url: baseUrl + '/GetEmployees',
type: "POST",
contentType: "application/json",
dataType: "jsonp",
data: {
filterSpan: 'all'
}
},
update: {
url: baseUrl + "/EditEmployee",
type: "POST",
dataType: "jsonp",
complete: function (e) {
$("input[data-role='autocomplete'][data-text-field='Name']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
}
},
create: {
url: baseUrl + "/CreateEmployee",
type: "POST",
dataType: "jsonp"
, complete: function (e) {
$("input[data-role='autocomplete'][data-text-field='Name']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
}
},
parameterMap: function (options, operation) {
clearButtonHighlight();
if (operation == "read") {
return kendo.stringify(options);
}
if (operation == "create") {
$.each(options.models, function (index, element) {
options.models[index].ID = 0;
});
}
if (operation !== "read" && options.models) {
return { models: kendo.stringify(options.models) };
}
}
},
batch: true,
pageSize: 30,
schema: {
model: {
id: "EmployeeId",
fields: {
EmployeeId: { editable: false, type: "int" },
DepartmentId: { field: "DepartmentId", type: "string", editable: true },
Name: { type: "string", editable: true, validation: { required: true } },
Salary: { type: "number", editable: true },
CreatedDate: { type: "date", editable: false },
DateCreated: { type: "string", editable: false }
}
}
, parse: function (response) {
$.each(response, function (idx1, elem1) {
try {
if (elem1.CreatedDate !== null) {
var pDate = new Date(parseInt(elem1.CreatedDate.substr(6)));
elem1.DateCreated = pDate;
elem1.DateCreated.setHours(0);
elem1.DateCreated.setMinutes(0);
elem1.DateCreated.setSeconds(0);
}
}
catch (ex) {
}
});
return response;
}
}
});
$("#grid").kendoGrid({
dataSource: dataSource,
height: 550,
filterable: {
mode: "row"
},
sortable: true,
columns:
[
{
field: "DepartmentId",
title: "DeptName",
width: 200,
filterable: {
cell: {
template: function (input) {
input.element.kendoDropDownList({
dataSource: $.parseJSON(departments.toJSON().toString()),
dataTextField: "DeptName",
valuePrimitive: true,
dataValueField: "DepartmentId",
optionLabel: {
DeptName: "--Select Value--",
DepartmentId: "DepartmentId"
}
});
},
showOperators: false
}
},
template: "#= getDeptName(DepartmentId) #",
editor: function (container, options) {
$('<input required data-text-field="DeptName" data-value-field="DepartmentId" data-bind="value:' + options.field + '"/>')
.appendTo(container)
.kendoDropDownList({
autoBind: false,
dataSource: $.parseJSON(departments.toJSON().toString())
});
},
headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>DeptName</a>"
},
{
field: "Name",
title: "Name",
width: 200,
filterable: {
cell: {
enabled: true,
operator: "contains"
},
mode: "row"
},
editor: function serviceItemAutoCompleteEditor(container, options) {
$('<input data-text-field="Name" data-value-field="Name" data-bind="value:' + options.field + '"/>')
.appendTo(container)
.kendoAutoComplete({
suggest: true,
placeholder: "Select an item",
filter: "contains",
index: 1,
minLength: 1,
dataSource: {
data: $("#grid").data("kendoGrid").dataSource.data()
}
})
},
headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'> Name</a>"
},
{
field: "Salary",
title: "Salary",
width: 250,
filterable: {
cell: {
enabled: true,
showOperators: false
}
},
headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Salary</a>"
},
{
field: "DateCreated",
title: "Date Created",
filterable: {
cell: {
template: function (args) {
args.element.kendoDatePicker({
format: "{0:dd-MM-yyyy}"
});
}
}
},
template: '#= kendo.toString(kendo.parseDate(CreatedDate, "yyyy-MM-ddTHH:mm:ss.fffZ"),"dd-MM-yyyy") #',
groupable: false,
width: 150,
headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Date Created</a>"
}
],
navigatable: true,
pageable: true,
height: 550,
toolbar: ["create", "save", "cancel",
{ name: "filtertoday", text: "Today" },
{ name: "filterweek", text: "This Week" },
"excel",
{ name: "filterall", text: "All Items" }
],
excel: {
proxyURL: baseUrl + "/KendoSave",
fileName: "Kendo UI Grid Export.xlsx",
allPages: true
},
excelExport: function (e) {
var rows = e.workbook.sheets[0].rows;
for (var ri = 0; ri < rows.length; ri++) {
var cell = rows[ri].cells[0];
if (cell.value != "" && rows[ri].type == "data") {
cell.value = getDeptName(cell.value);
// Set the alignment
cell.hAlign = "left";
}
}
},
editable: true
});
$(".k-grid-filtertoday").click(function (e) {
var grid = $("#grid").data("kendoGrid");
grid.dataSource.page(1);
grid.dataSource.read({ filterSpan: 'today' });
grid.refresh();
$('.k-grid-filtertoday').css('background-color', '#7EA700');
});
$(".k-grid-filterweek").click(function (e) {
var grid = $("#grid").data("kendoGrid");
grid.dataSource.page(1);
grid.dataSource.read({ filterSpan: 'week' });
grid.refresh();
$('.k-grid-filterweek').css('background-color', '#7EA700');
});
$(".k-grid-filterall").click(function (e) {
var grid = $("#grid").data("kendoGrid");
grid.dataSource.page(1);
grid.dataSource.read({ filterSpan: 'all' });
grid.refresh();
$('.k-grid-filterall').css('background-color', '#7EA700');
});
$(".k-grid-save-changes").click(function (e) {
var grid = $("#grid").data("kendoGrid");
var errorMsg = "";
for (var cntItem = 0; cntItem < grid._data.length; cntItem++) {
if (!grid._data[cntItem].id) {
if (grid._data[cntItem].DepartmentId == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Department. n";
}
if (grid._data[cntItem].Name == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Name is required. n";
}
if (grid._data[cntItem].Salary == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Salary. n";
}
}
}
if (errorMsg != "")
{
alert(errorMsg);
return false;
}
});
});
function getDeptName(id) {
var dept = $.parseJSON(departments.toJSON().toString());
for (var idx = 0, length = dept.length; idx < length; idx++) {
if (dept[idx].DepartmentId.toString() === id) {
return dept[idx].DeptName;
}
}
}
</script>
</div>
Our application is ready, rebuild the solution & run.
The output will be looking like:
Clicking on This Week button to filter current week created data
As I mentioned in my previous pertained article about some resolutions on complex issues & custom functionalities, here I am giving the same way to make this compact, crispy & easy to relate.
Issue 1: After adding or editing the record the filter autocomplete text boxes were not refreshing, we added the below code to refresh the autocomplete textboxes after creating or editing the record
complete: function (e) {
$("input[data-role='autocomplete'][data-text-field='Name']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
}
Issue 2: In different time zone the datetime picker filter was not filtering the data properly because DateCreated had the empty value & CreatedDate values were populated with time whereas selected values from a datetime filter were with default time. So, as a solution what I did, first populate the DateCreated by CreatedDate & parse the DateCreated values with default time, so that both filter & grid are in same time format. Here is the code:
, parse: function (response) {
$.each(response, function (idx1, elem1) {
try {
if (elem1.CreatedDate !== null) {
var pDate = new Date(parseInt(elem1.CreatedDate.substr(6)));
elem1.DateCreated = pDate;
elem1.DateCreated.setHours(0);
elem1.DateCreated.setMinutes(0);
elem1.DateCreated.setSeconds(0);
}
}
catch (ex) {
}
});
return response;
}
Issue 3: At the time of editing the record & click on Save Changes the validation was not working properly. So I added custom validation
$(".k-grid-save-changes").click(function (e) {
var grid = $("#grid").data("kendoGrid");
var errorMsg = "";
for (var cntItem = 0; cntItem < grid._data.length; cntItem++) {
if (!grid._data[cntItem].id) {
if (grid._data[cntItem].DepartmentId == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Department. n";
}
if (grid._data[cntItem].Name == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Name is required. n";
}
if (grid._data[cntItem].Salary == "") {
errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Salary. n";
}
}
}
if (errorMsg != "")
{
alert(errorMsg);
return false;
}
});
Custom functionality1: Todays created data filter issue resolved by following code:
$(".k-grid-filtertoday").click(function (e) {
var grid = $("#grid").data("kendoGrid");
grid.dataSource.page(1);
grid.dataSource.read({ filterSpan: 'today' });
grid.refresh();
$('.k-grid-filtertoday').css('background-color', '#7EA700');
});
In Controller Action method based on the method parameter value we are filtering the data. Similarly for the Weekly data & all items.
Custom functionality2: Merge row editing & row filtering functionality has been achieved by following code: filterable: {
mode: "row"
}
Also, whatever I have written within filterable: {cell: {}}
Custom functionality3: Department filter should be a dropdown, here is the code:
filterable: {
cell: {
template: function (input) {
input.element.kendoDropDownList({
dataSource: $.parseJSON(departments.toJSON().toString()),
dataTextField: "DeptName",
valuePrimitive: true,
dataValueField: "DepartmentId",
optionLabel: {
DeptName: "--Select Value--",
DepartmentId: "DepartmentId"
}
});
},
showOperators: false
}
}
Happy Coding
Tarun Kumar Chatterjee
Leave a comment