ASP.NET MVC5 - Kendo UI Grid CRUD Operation Using ASP.NET Web API And EF

Introduction

 

 In this article, I will demonstrate how to perform CRUD operations in Kendo grid, using ASP.NET.MVC, ASP.NET WEB API, and Entity Framework.

 

Prerequisites 

• Visual Studio
• SQL Server
• Basic knowledge of ASP.NET MVC
• Basic knowledge of ASP.NET Web API
• Basic knowledge of Entity Framework
• Basic knowledge of Kendo UI
• Basic knowledge of jQuery
• Basic knowledge of CSS

Article Flow 

• Create a table in database with dummy values
• Create an ASP.NET Web API Empty project with CRUD Logics Using Entity Framework
• Create an ASP.NET MVC Empty project
• Create a Controller and View
• Enable Kendo UI Features

 Create a table in database with dummy values

 

First, we will create a table in SQL Server to populate a Kendo UI grid with data in ASP.NET MVC. I have created a table "Employee" with the following design. 

 

 

Execute the below query to create a table with the above design.

GO
CREATE TABLE [dbo].[Employee](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FullName] [nvarchar](150) NULL,
[Designation] [nvarchar](50) NULL,
[Phone] [nvarchar](20) NULL,
[Email] [nvarchar](50) NULL,
CONSTRAINT [PK_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

And, insert a few employee records like below.

 

 

 

Execute the below query to get the same records. 

GO
SET IDENTITY_INSERT [dbo].[Employee] ON
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (1, N'Gnanavel', N'Software Engineer', N'9876543210', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (2, N'Robert ', N'Software Engineer', N'9632884100', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (3, N'Muthu ', N'Software Engineer', N'7894563210', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (4, N'Ramar ', N'Sr. Software Engineer', N'4561230789', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (5, N'Gokul', N'Sr. Software Engineer', N'7412589630', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (6, N'Karthick', N'Application Developer', N'8523697410', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (7, N'Sharma', N'Application Developer', N'9845632107', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (8, N'Sheriff', N'Application Developer', N'7563241890', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (9, N'Anand', N'Application Developer', N'9875163240', N'[email protected]')
GO
INSERT [dbo].[Employee] ([ID], [FullName], [Designation], [Phone], [Email]) VALUES (10, N'Mubarak', N'Application Developer', N'7123456890', N'[email protected]')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Create an ASP.NET Web API Empty project with CRUD Logics Using Entity Framework

 

I already discussed how to create an Empty ASP.NET Web API project and  Implement WEB API scaffolding with Entity Framework, Refer Implement ASP.NET WEB API CRUD Logic Using Entity Framework Without Writing Code And Test It With Postman. While creating the project I named it as "API".Entity Framework implements the database first approach and In Meantime choose our created table with entity framework and configure with SQL table "Employee" from CSharpCorner database and with our API. Once you do those steps your API will be like below,

 

 

 

and the controller will be,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web.Http.Description;
using System.Web.Mvc;
using System.Data;
namespace Web_API.Controllers {
    /// <summary>
    /// Crud Operation Controller
    /// </summary>
    public class CRUDController: ApiController {
        private static CRUDEntities crud = new CRUDEntities();
        /// <summary>
        /// Get All Employee Details
        /// </summary>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/GetEmployees")]
        [System.Web.Http.HttpGet]
        public HttpResponseMessage GetEmployees() {
            var result = crud.Employees.ToList();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Get Employee Details
        /// </summary>
        /// <remarks>
        /// Get Employee Details based on empid
        /// </remarks>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/GetEmployee")]
        [System.Web.Http.HttpGet]
        public HttpResponseMessage GetEmployee(int employeeid) {
            var result = crud.Employees.Where(a => a.ID == employeeid).ToList();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Add new employee
        /// </summary>
        /// <remarks>
        /// Create new employee and return inserted employee details
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/AddEmployee")]
        [System.Web.Http.HttpPost]
        public HttpResponseMessage AddEmployee(Employee employee) {
            var result = crud.Employees.Add(employee);
            crud.SaveChanges();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Update Employee Details
        /// </summary>
        /// <remarks>
        /// Update Employee Details Based on empid
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/UpdateEmployee")]
        [System.Web.Http.HttpPut]
        public HttpResponseMessage UpdateEmployee(Employee employee) {
            Employee result = crud.Employees.Where(a => a.ID == employee.ID).FirstOrDefault();
            if (result != null) {
                result.FullName = employee.FullName;
                result.Designation = employee.Designation;
                result.Phone = employee.Phone;
                result.Designation = employee.Designation;
                result.Email = employee.Email;
                crud.Entry(result).State = EntityState.Modified;
                crud.SaveChanges();
            }
            return GetResultResponse(result);
        }
        /// <summary>
        /// Delete Employee
        /// </summary>
        /// <remarks>
        /// Delete Employee record based on empid
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/DeleteEmployee")]
        [System.Web.Http.HttpDelete]
        public void DeleteEmployee(Employee employee) {
            Employee result = crud.Employees.Where(a => a.ID == employee.ID).FirstOrDefault();
            if (result != null) {
                crud.Entry(result).State = EntityState.Deleted;
                crud.SaveChanges();
            }
        }
        /// <summary>
        /// Get Response for Each result
        /// </summary>
        /// <param name="Result"></param>
        /// <returns></returns>
        public HttpResponseMessage GetResultResponse(object Result) {
            HttpResponseMessage response = null;
            try {
                response = Request.CreateResponse(HttpStatusCode.OK, Result);
            } catch (Exception ex) {
                response = Request.CreateResponse(HttpStatusCode.BadRequest, Result);
            }
            return response;
        }
    }
}

And you might get successful services

 

Create an ASP.NET MVC Empty project

 

To create ASP.NET MVC empty project, follow the below steps one by one. Here, I have used Visual Studio 2013.

• Select New Project -> Visual C# -> Web -> ASP.NET Web Application and enter your application name. Here, I named it as "Application".
• Now, click OK.
• Then, select Empty MVC template and click OK to create the project.
• Once you click OK, the project will be created with the basic architecture of MVC.
• If you are not aware of how to create an Empty ASP.NET Web Application, please visit Step 1 and Step 2 to learn. Once you complete these steps, you will get the screen as below.

 

Create a Controller and View

 

Now, create an empty Controller and View. Here, I created a Controller with the name of "CrudController". Whenever we create an empty Controller, it is created with empty Index action method. And, create an empty View to this action method "Index".  

 

Enable Kendo UI Features

 

Here, we are going to enable the Kendo UI features with our application by adding the below CSS and JS in our shared _Layout View. 

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" type="text/javascript"></script>
<link href="~/Content/kendo/kendo.common.min.css" rel="stylesheet" />
<link href="~/Content/kendo/Kendo.custom.css" rel="stylesheet" />
<link href="~/Content/kendo/kendo.dataviz.min.css" rel="stylesheet" />
<link href="~/Content/kendo/kendo.default.min.css" rel="stylesheet" />
<link href="~/Content/kendo/kendo.rtl.min.css" rel="stylesheet" />
<script src="~/Scripts/kendo/kendo.all.min.js"></script>
<script src="~/Scripts/kendo/kendo.aspnetmvc.min.js"></script>

Now create a div which will act as Kendo grid

<div id="EmployeeGrid" style="width: 750px;"></div>

And add the below code to your view to perform CRUD operations

<script type="text/javascript">
    $(document).ready(function() {
        var ApiUrl = "http://localhost:50537/";
        dataSource = new kendo.data.DataSource({
            transport: {
                read: {
                    url: ApiUrl + "api/GetEmployees",
                    dataType: "json",
                },
                destroy: {
                    url: ApiUrl + "api/DeleteEmployee",
                    type: "DELETE"
                },
                create: {
                    url: ApiUrl + "api/AddEmployee",
                    type: "POST"
                },
                update: {
                    url: ApiUrl + "api/UpdateEmployee",
                    type: "PUT",
                    parameterMap: function(options, operation) {
                        if (operation !== "read" && options.models) {
                            return {
                                models: kendo.stringify(options.models)
                            };
                        }
                    }
                },
            },
            schema: {
                model: {
                    id: "ID",
                    fields: {
                        FullName: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Designation: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Phone: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Email: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                    }
                }
            }
        });
        $("#EmployeeGrid").kendoGrid({
            dataSource: dataSource,
            editable: "popup",
            groupable: true,
            filterable: true,
            sortable: true,
            pageable: {
                refresh: true,
                pageSizes: true,
                buttonCount: 5
            },
            edit: function(e) {
                e.container.find("input:first").hide();
            },
            toolbar: ["create"],
            columns: [{
                field: "ID",
                title: "ID",
                width: 25,
                filterable: false,
            }, {
                field: "FullName",
                title: "Name",
                width: 120,
            }, {
                field: "Designation",
                title: "Designation",
                filterable: {
                    multi: true,
                    search: true
                },
                width: 150,
            }, {
                field: "Phone",
                title: "Phone",
                filterable: true,
                width: 90,
            }, {
                field: "Email",
                title: "Email",
                width: 150,
                filterable: true
            }, {
                command: ["edit", {
                    name: "destroy",
                    text: "remove",
                }],
            }],
            height: "500px",
            pageable: {
                refresh: true,
                pageSizes: true,
                buttonCount: 5
            },
        }).data("kendoGrid");
    });
</script>
<style>
    .k-window {
        width: 300px !important;
    }
</style>

Detailed description 

•  var ApiUrl = "http://localhost:50537/" -> it represents our Web API url
• new kendo.data.DataSource( ->  it helps us to do remote data binding in Kendo Grid which is the response of the REST API developed using ASP.NET Web API using Entity Framework. The data source of the Kendo grid is constructed based on our complex JSON
• Transport-> It helps us to do the operations Create, Read, Update and Delete
• Transport Read-> It reads the data from respective API with the help of url; and dataType parameter represents what type of data we are reading from API
•  Transport Destroy-> It's used to delete the respective record by calling respective API action method and type parameter represents what type of HTTP request we are passing to the API, here we are passing the Delete request.
• Transport Create-> It helps us to create a new record database by calling respective API action method and Passing the model as a parameter to the respective action method and here the type will be Post.
• Transport update-> It helps us to update the existing record in a database by calling respective API action method using url and passing the model as a parameter to the respective action method and here the type will be Put. 
• schema model -> It will be the input parameter to the API and we will do the validation and represent the type of data from the user and in a model, id parameter represents the primary column field to skip editing and do tracking of the respective record.
• $("#EmployeeGrid").kendoGrid({ dataSource:-> Here, we are binding the remote data to the kendo grid data source
• editable: "popup",-> Here we will navigate to the popup during the addition and updating the record. One more option is to do the Add and Update the record in the grid itself using inline.
• toolbar: ["create"], -> It enables the Add new record functionality to the Kendo grid on top of the grid in toolbar
• columns:->  This parameter is represented which are the fields which need to be displayed in the grid
• command: ->It helps us to enable the important command of edit, delete and cancel to the Kendo Grid.

Now run your application

 

 

Add New Record

 

Click Add new record button in the toolbar of a grid to add a new record in Popup edit mode. 

 

 

Edit Record 

 

Click Edit button in the respective row of a grid to update existing record in Popup mode. 

 

 

 

 Delete Record

 

Click Remove button in the respective row of a grid to delete existing record in Popup mode.

 

 

 

Application -Complete View

<script type="text/javascript">
    $(document).ready(function() {
        var ApiUrl = "http://localhost:50537/";
        dataSource = new kendo.data.DataSource({
            transport: {
                read: {
                    url: ApiUrl + "api/GetEmployees",
                    dataType: "json",
                },
                destroy: {
                    url: ApiUrl + "api/DeleteEmployee",
                    type: "DELETE"
                },
                create: {
                    url: ApiUrl + "api/AddEmployee",
                    type: "POST"
                },
                update: {
                    url: ApiUrl + "api/UpdateEmployee",
                    type: "PUT",
                    parameterMap: function(options, operation) {
                        if (operation !== "read" && options.models) {
                            return {
                                models: kendo.stringify(options.models)
                            };
                        }
                    }
                },
            },
            schema: {
                model: {
                    id: "ID",
                    fields: {
                        FullName: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Designation: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Phone: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                        Email: {
                            editable: true,
                            nullable: false,
                            type: "string",
                            validation: {
                                required: true
                            }
                        },
                    }
                }
            }
        });
        $("#EmployeeGrid").kendoGrid({
            dataSource: dataSource,
            editable: "popup",
            groupable: true,
            filterable: true,
            sortable: true,
            pageable: {
                refresh: true,
                pageSizes: true,
                buttonCount: 5
            },
            edit: function(e) {
                e.container.find("input:first").hide();
            },
            toolbar: ["create"],
            columns: [{
                field: "ID",
                title: "ID",
                width: 25,
                filterable: false,
            }, {
                field: "FullName",
                title: "Name",
                width: 120,
            }, {
                field: "Designation",
                title: "Designation",
                filterable: {
                    multi: true,
                    search: true
                },
                width: 150,
            }, {
                field: "Phone",
                title: "Phone",
                filterable: true,
                width: 90,
            }, {
                field: "Email",
                title: "Email",
                width: 150,
                filterable: true
            }, {
                command: ["edit", {
                    name: "destroy",
                    text: "remove",
                }],
            }],
            height: "500px",
            pageable: {
                refresh: true,
                pageSizes: true,
                buttonCount: 5
            },
        }).data("kendoGrid");
    });
</script>
<style>
    .k-window {
        width: 300px !important;
    }
</style> @using (Html.BeginForm("Index", "Crud", FormMethod.Post, new { id = "EmployeeDetails" })) {
<div id="EmployeeGrid" style="width: 750px;"></div> }

_Layout.cshtml 

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CRUD Opeations</title> @Styles.Render("~/Content/css")
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" type="text/javascript"></script>
    <link href="~/Content/kendo/kendo.common.min.css" rel="stylesheet" />
    <link href="~/Content/kendo/Kendo.custom.css" rel="stylesheet" />
    <link href="~/Content/kendo/kendo.dataviz.min.css" rel="stylesheet" />
    <link href="~/Content/kendo/kendo.default.min.css" rel="stylesheet" />
    <link href="~/Content/kendo/kendo.rtl.min.css" rel="stylesheet" />
    <script src="~/Scripts/kendo/kendo.all.min.js"></script>
    <script src="~/Scripts/kendo/kendo.aspnetmvc.min.js"></script>
</head>

<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header"> <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button> @Html.ActionLink(" ASP.NET MVC5 - Kendo Grid CRUD Operation using Web API AND Entity Framework", "Index", "Crud", new { area = "" }, new { @class = "navbar-brand" }) </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav"> </ul>
            </div>
        </div>
    </div>
    <div class="container body-content"> @RenderBody()
        <hr /> </div>
</body>

</html>

API -Complete Controller 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web.Http.Description;
using System.Web.Mvc;
using System.Data;
namespace Web_API.Controllers {
    /// <summary>
    /// Crud Operation Controller
    /// </summary>
    public class CRUDController: ApiController {
        private static CRUDEntities crud = new CRUDEntities();
        /// <summary>
        /// Get All Employee Details
        /// </summary>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/GetEmployees")]
        [System.Web.Http.HttpGet]
        public HttpResponseMessage GetEmployees() {
            var result = crud.Employees.ToList();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Get Employee Details
        /// </summary>
        /// <remarks>
        /// Get Employee Details based on empid
        /// </remarks>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/GetEmployee")]
        [System.Web.Http.HttpGet]
        public HttpResponseMessage GetEmployee(int employeeid) {
            var result = crud.Employees.Where(a => a.ID == employeeid).ToList();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Add new employee
        /// </summary>
        /// <remarks>
        /// Create new employee and return inserted employee details
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/AddEmployee")]
        [System.Web.Http.HttpPost]
        public HttpResponseMessage AddEmployee(Employee employee) {
            var result = crud.Employees.Add(employee);
            crud.SaveChanges();
            return GetResultResponse(result);
        }
        /// <summary>
        /// Update Employee Details
        /// </summary>
        /// <remarks>
        /// Update Employee Details Based on empid
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/UpdateEmployee")]
        [System.Web.Http.HttpPut]
        public HttpResponseMessage UpdateEmployee(Employee employee) {
            Employee result = crud.Employees.Where(a => a.ID == employee.ID).FirstOrDefault();
            if (result != null) {
                result.FullName = employee.FullName;
                result.Designation = employee.Designation;
                result.Phone = employee.Phone;
                result.Designation = employee.Designation;
                result.Email = employee.Email;
                crud.Entry(result).State = EntityState.Modified;
                crud.SaveChanges();
            }
            return GetResultResponse(result);
        }
        /// <summary>
        /// Delete Employee
        /// </summary>
        /// <remarks>
        /// Delete Employee record based on empid
        /// </remarks>
        /// <param name="employee"></param>
        /// <returns></returns>
        [ResponseType(typeof(IEnumerable < Employee > ))]
        [System.Web.Http.Route("api/DeleteEmployee")]
        [System.Web.Http.HttpDelete]
        public void DeleteEmployee(Employee employee) {
            Employee result = crud.Employees.Where(a => a.ID == employee.ID).FirstOrDefault();
            if (result != null) {
                crud.Entry(result).State = EntityState.Deleted;
                crud.SaveChanges();
            }
        }
        /// <summary>
        /// Get Response for Each result
        /// </summary>
        /// <param name="Result"></param>
        /// <returns></returns>
        public HttpResponseMessage GetResultResponse(object Result) {
            HttpResponseMessage response = null;
            try {
                response = Request.CreateResponse(HttpStatusCode.OK, Result);
            } catch (Exception ex) {
                response = Request.CreateResponse(HttpStatusCode.BadRequest, Result);
            }
            return response;
        }
    }
}

I attached the demonstrated Application and API without the packages due to size limit. In the next article, we will see Kendo Grid and more features of CRUD in the next article.

 

Summary 

In this article, we discussed how to do the CRUD operation in Kendo UI Grid using ASP.NET Web API and Entity Framework in ASP.NET MVC5 Web Application.

 

I Hope it will help you. Your valuable feedback and comments about this article are always welcome.