Grouping In Kendo Grid Using ASP.NET WEB API And Entity Framework

Gowtham K
7y
4k
0
4

Article

Introduction

This article tells about how to perform a grouping in Kendo Grid, using ASP.NET Web API. To explain it, I have created a RESTful GET Service, which is used to load the DataSource of Kendo Grid

Requirements

VS 2010 and above
SQL Server 2008 and above

Prerequisites

Basic knowledge of ASP.NET WebAPI, jQuery, Kendo UI.

This article flows, as per the following.

Set up the table
Creating an ASP.NET Web API Application.
Creating a Controller.
Testing the REST API.
Creating a HTML page and implementing the grouping in kendo Grid

Set up the table

For this article, I have created one table named EmployeeList, the design of which is shown below.

Figure 1

Employee List Table

Figure 2

Creating an ASP.NET WEB API Application

Create a Web API Application, using an installed Web template in Visual Studio, as shown below. In my case, I named the Application “KGridTemplate"

Figure

Figure 4

Creating model classes

Now, we will create Entity Framework models from the database tables.

Step 1

Right-click the Models folder, select Add -> ADO.NET Entity Data Model or select Add->New Item. In the "Add New Item" Window, select data in the left pane and ADO.NET Entity Data Model from the center pane. Name the new model file (In my case, I made it as Employee) and click Add.

Step 2

In the Entity Data Model wizard, select "EF Designer" from the database and click "Next".

Figure 5

Step 3

Click New Connection button. The Connection Properties Window will open.

Figure 6

Step 4

In Connection Properties Window, provide the name of the local Server, where the database was created (in this case, (DESKTOP-585QGBN)). After providing the Server name, select "Employee" from the available databases and click OK.

Figure 7

Step 5

You can use the default name for the connection to save the Web.Config file. Now, click Next.

Figure 8

Step 6

Select the table to generate the models for EmployeeList table and click Finish.

Figure 9

My database schema is shown in the figure given below.

Figure 10

Creating a Controller

Right click on Controller folder and add a new Web API 2 controller- Empty, as shown in the Figure 11. In my case, I named it as EmployeesController.cs.

Figure 11

Write the code given below in EmployeeController.cs

EmployeeController.cs

[RoutePrefix("api/Employee")]
public class EmployeeController: ApiController {
EmployeeEntities db = new EmployeeEntities();
[HttpGet]
[AllowAnonymous]
[Route("EmployeeList")]
public HttpResponseMessage GetEmployeeList() {
try {
return Request.CreateResponse(HttpStatusCode.OK, db.EmployeeLists, Configuration.Formatters.JsonFormatter);
} catch (Exception ex) {
return Request.CreateResponse(HttpStatusCode.OK, ex.Message, Configuration.Formatters.JsonFormatter);
}
}
}

Testing the API in Postman

API End Point /API/ Employee/ EmployeeList.
Type GET.

Figure 12

Now, our API is ready, let's create a Kendo Grid DataSource, using the API.

Creating a HTML page

Create one new HTML page in the Application, where we are going to implement Kendo Grid, using the RESTful Service. In my case, I named it as KendoGrid.html.

Remote DataSource in KendoGrid

Click here to learn more about remote DataSource in Kendo Grid

KendoGrid.html

<!DOCTYPE html>
<html>
<head>
<title>Kendo Grid</title>
<meta charset="utf-8" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.common.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.rtl.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.silver.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.mobile.all.min.css" />
<script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2017.1.118/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<div id="grid"></div>
<script>
$(document).ready(function() {
$("#grid").kendoGrid({
dataSource: {
type: "json",
transport: {
read: "/api/Employee/EmployeeList"
},
schema: {
model: {
fields: {
EmployeeID: {
type: "number"
},
FirstName: {
type: "string"
},
LastName: {
type: "string"
},
Company: {
type: "string"
}
}
}
},
},
filterable: true,
sortable: true,
pageable: true,
columns: [{
field: "EmployeeID",
filterable: false
}, {
field: "FirstName",
title: " First Name",
}, {
field: "LastName",
title: "Last Name"
}, {
field: "Company",
title: "Company",
}]
});
});
</script>
</div>
</body>
</html>

Result in the Browser

Figure 13

Grouping in Kendo Grid

Lets group the record in Grid, which is based on company

KendoGrid.html

<!DOCTYPE html>
<html>
<head>
<title>Kendo Grid</title>
<meta charset="utf-8" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.common.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.rtl.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.silver.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.mobile.all.min.css" />
<script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2017.1.118/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<div id="grid"></div>
<script>
$(document).ready(function() {
$("#grid").kendoGrid({
dataSource: {
type: "json",
transport: {
read: "/api/Employee/EmployeeList"
},
group: {
field: "Company",
aggregates: [{
field: "Company",
aggregate: "count"
}]
},
schema: {
model: {
fields: {
EmployeeID: {
type: "number"
},
FirstName: {
type: "string"
},
LastName: {
type: "string"
},
Company: {
type: "string"
}
}
}
},
},
filterable: true,
sortable: true,
pageable: true,
columns: [{
field: "EmployeeID",
filterable: false
}, {
field: "FirstName",
title: " First Name",
}, {
field: "LastName",
title: "Last Name"
}, {
field: "Company",
title: "Company",
groupHeaderTemplate: "Company: #= value # Total: #= count #"
}]
});
});
</script>
</div>
</body>
</html>

The group field in DataSource is used to group the record, which is based on the company field with the aggregate - count. The groupHeaderTemplate in the column is used to display the title in each group

Result in the Browser