Retrieve Records In jQuery Datatable Using Entity Framework And Bootstrap

Satyaprakash Samantaray
Nov 12, 2018

42.5k
0
9
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

Introduction

In this session, I will show you the steps to Implement jQuery Datatable in ASP.NET MVC application. There is no need to add filter data and pagination manually. You can get all these benefits by using Datatable. Datatable is open source.

Description

DataTable is a most powerful and easy to use jQuery plugin for displaying tabular data with features for Pagination, searching, State saving, and Multi-column sorting with data type detection and lots more with ZERO or minimal configuration.

Note

Before going through the session, I will suggest you to first visit the previous MVC related articles with backend knowledge.

A look into MVC in depth

To know more about Datatable visit the Datatable official site.

Datatable In Depth

Steps to be followed,

Step 1

Create a table named the employee. I have added its script including schema with data.

CREATE TABLE [dbo].[employee](
[FirstName] [varchar](15) NOT NULL,
[LastName] [varchar](20) NULL,
[Age] [int] NULL,
[Address] [varchar](30) NULL,
[City] [varchar](20) NULL,
[State] [varchar](20) NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[FirstName] 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
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Ajit', N'Mukesh', 36, N'2130 Boars Nest', N'Hazard Co', N'Georgia')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Ashwini', N'Bajaj', 29, N'2130 Boars Nest', N'Hazard Co', N'Georgia')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Beenna', N'Malik', 42, N'2130 Boars Nest', N'Hazard Co', N'Georgia')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Keshav', N'Malhotra', 26, N'Noida, Sector-12', N'Delhi', N'Uttar Pradesh')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Kulu', N'Samantaray', 38, N'Ghaziabad', N'Ghaziabad', N'Uttar Pradesh')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Raghav', N'Manoj', 27, N'2130 Boars Nest', N'Hazard Co', N'Georgia')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Ram', N'Prasad', 24, N'Hyd, NY', N'Hyderabad', N'Andhra Pradesh')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Satyaprakash', N'Samantaray', 27, N'Khordha, Bhubaneswar', N'Khordha', N'Odisha')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Sita', N'Goswami', 21, N'HSR Layout', N'Bangalore', N'Karnataka')
GO
INSERT [dbo].[employee] ([FirstName], [LastName], [Age], [Address], [City], [State]) VALUES (N'Venkat', N'Mukherjee', 34, N'Koramangala', N'Bengaluru', N'Karnataka')
GO

Step 2

Added Entity Data Model named SatyaDBModel.edmx.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add. A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >

Chose your data connection > select your database > next > Select tables (employee) > enter Model Namespace > Finish.

Step 3

Here, I have taken the index action method of the home controller to get the view where we will implement jQuery DataTable.

public ActionResult Index()
{
return View();
}

Step 4

I added another action method named loaddata for fetching data from the database using entity framework.

Code Ref

public ActionResult loaddata()
{
using (SatyaDBEntities dc = new SatyaDBEntities())
{
var data = dc.employees.OrderBy(a => a.FirstName).ToList();
return Json(new { data = data }, JsonRequestBehavior.AllowGet);
}
}

Code Description

Here, SatyaDBEntities is the name of the model namespace. Using the object of model namespace we can access the database object and its property.

If the database object (table) contains foreign key then you have to add the below line of code.

dc.Configuration.LazyLoadingEnabled = false;

Step 5

I have added a view for the action Index & design.

Code ref

@{
ViewBag.Title = "Index";
}
<h2 style="color: blue">Satyaprakash-jQuery Datatable Using ASP.NET MVC</h2>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
<div style="width:90%; margin:0 auto;">
<table id="myTable" align="center" border="1" cellpadding="4" cellspacing="4">
<thead>
<tr>
<th style="background-color: Yellow;color: blue">First Name</th>
<th style="background-color: Yellow;color: blue">Last Name</th>
<th style="background-color: Yellow;color: blue">Age</th>
<th style="background-color: Yellow;color: blue">Address</th>
<th style="background-color: Yellow;color: blue">City</th>
<th style="background-color: Yellow;color: blue">State</th>
</tr>
</thead>
</table>
</div>
<style>
tr.even {
background-color: #F5F5F5 !important;
}
</style>
@* Load bootstrap datatable css *@
<link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />
<link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" />
@* Load normal datatable css *@
@*<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />*@
@* Load bootstrap datatable js *@
@section Scripts{
<script src="//code.jquery.com/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
@* Load normal datatable js *@
@*<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>*@
<script>
$(document).ready(function () {
$('#myTable').DataTable({
"ajax": {
"url": "/home/loaddata",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "FirstName", "autoWidth": true },
{ "data": "LastName", "autoWidth": true },
{ "data": "Age", "autoWidth": true },
{ "data": "Address", "autoWidth": true },
{ "data": "City", "autoWidth": true },
{ "data": "State", "autoWidth": true }
]
});
});
</script>
}

Code Description

I can add datatable CDN reference to implement normal Datatable CSS and Datatable JS.

<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />

<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>

But here, I have added datatable CDN reference to implement bootstrap Datatable CSS and Datatable JS.

<link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />
<link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" />

<script src="//code.jquery.com/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>

Then I have added jQuery to load records and fetch into the datatable. Here, myTable is the ID of HTML table and in the URL section I have added the Home controller with controller action method to fetch records and in the columns section it contains all the properties of database objects.