How to use Jquery DataTable Plugin in ASP.NET MVC

Hello Friends, In this tutorial we are going to see how to use Jquery DataTable plugin in ASP.NET MVC. Jquery DataTable plugin is widely used for showing Data in Grid. The plugin has many features like sorting, paging, searching.

After completing this tutorial you will be able to understand:

• How to use Jquery DataTable Plugin in ASP.NET MVC.

What you’ll do?

• Create an ASP.NET MVC application that will use the Jquery DataTable Plugin to display Data with paging, Searching, and Sorting.

Required JS files :

<script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="http://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

Required  CSS Files:

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

Example of using the DataTable function.

  $('#tblExample').DataTable();

Where tblExample is the id of your HTML Table.

How to use Jquery DataTable Plugin in ASP.NET MVC?

Let’s start by creating Database and Table and insert some dummy data to display in Data Table.

1- Create a Database and Table

CREATE DATABASE Debugonweb

USE [Debugonweb]
GO

/****** Object: Table [dbo].[Employee] Script Date: 08-10-2018 22:59:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[Gender] [varchar](10) NULL,
[Address] [varchar](50) NULL,
[State] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Status] [varchar](20) NULL,
CONSTRAINT [PK__Employee__3214EC274AA9A371] PRIMARY KEY CLUSTERED
(
[EmployeeID] 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

SET ANSI_PADDING OFF
GO

2- Insert Dummy Data

USE [Debugonweb]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (1, N'Vinod', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (2, N'Manoj', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (3, N'Ansh', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (4, N'Ritesh', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (5, N'Vishwas', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (6, N'Ansh', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (7, N'Varali', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (8, N'Nitin', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (9, N'Vikas', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (10, N'Ritwik', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (11, N'Arohi', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Address], [State], [City], [Status]) VALUES (12, N'Ketan', N'Male', N'Varanasi', N'UP', N'Varanasi', N'Active')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Create ASP.NET MVC application:

Step 1- Open Visual Studio and Create an ASP.NET MVC JqueryDataTableExample.

Step 2- Choose an Empty template and click OK.

Step 3- I am using Entity Framework for fetching records from SQL Server so we need to Install the Entity Framework. Goto Tool > Library Package Manager > Manage NuGet Packages For Solutions.

Step 4-  Right Click on the Model folder and Create a class with the name Employee and write the following code in Employee class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
namespace JqueryDataTableExample.Models
{

[Table("Employee")]
public class Employee
{
[Key]
public Int64 EmployeeID { get; set; }
public string EmployeeName { get; set; }
public string Gender { get; set; }
public string Address { get; set; }
public string State { get; set; }
public string City { get; set; }
public string Status { get; set; }

}
}

Step 5-  Right Click on the Model Folder and Create a Class EmployeeDbContext and write the following code in EmployeeDbContext class.

using System;
 using System.Collections.Generic;
 using System.Data.Entity;
 using System.Linq;
 using System.Web;

namespace JqueryDataTableExample.Models
 {
 public class EmployeeDbContext : DbContext
 {
 public DbSet<Employee> Employee { get; set; }
 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 Database.SetInitializer<EmployeeDbContext>(null);
 base.OnModelCreating(modelBuilder);
 }
 }
 }

Step 6- Add the following Connection String in the web.config class with the name EmployeeDbContext.

<connectionStrings>
 <add name="EmployeeDbContext" connectionString="data source=DESKTOP-CN5IQI9\SQLEXPRESS;Initial Catalog=Debugonweb;integrated security=True;" providerName="System.Data.SqlClient" />

</connectionStrings>

Step 7- Right Click on the Controller folder and Create a Controller with the name HomeController.

using JqueryDataTableExample.Models;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.Mvc;

namespace JqueryDataTableExample.Controllers
 {
 public class HomeController : Controller
 {
 //
 // GET: /Home/
 EmployeeDbContext objEmployeeDbContext = new EmployeeDbContext();

public ActionResult Index()
 {
 List<Employee> lstEmployees = objEmployeeDbContext.Employee.ToList();
 return View(lstEmployees);
 }

}
 }

Step 8-  Right Click on the Index Action method and add a View with the name Index.

@model IEnumerable<JqueryDataTableExample.Models.Employee>

@{
 Layout=null;
 }
 <html>
 <head>
 <title>
 Jquery Data Table
 </title>

<script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
 <script src="http://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
 <link href="http://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet">
 <script type="text/javascript">

$(document).ready( function () {
 $('#tblEmployee').DataTable();
 } );
 </script>
 </head>

<body>
 <table class="col-md-12 table-bordered table-striped table-condensed cf" id="tblEmployee">
 <thead class="cf">
 <tr>
 <th>
 @Html.DisplayNameFor(model => model.EmployeeName)
 </th>
 <th>
 @Html.DisplayNameFor(model => model.Gender)
 </th>
 <th>
 @Html.DisplayNameFor(model => model.Address)
 </th>
 <th>
 @Html.DisplayNameFor(model => model.State)
 </th>
 <th>
 @Html.DisplayNameFor(model => model.City)
 </th>
 <th>
 @Html.DisplayNameFor(model => model.Status)
 </th>

</tr>

</thead>
 <tbody>
 @foreach (var item in Model)
 {
 <tr>
 <td data-title="Employee Name">
 @Html.DisplayFor(modelItem => item.EmployeeName)
 </td>
 <td data-title="Gender">
 @Html.DisplayFor(modelItem => item.Gender)
 </td>
 <td data-title="Address">
 @Html.DisplayFor(modelItem => item.Address)
 </td>
 <td data-title="State">
 @Html.DisplayFor(modelItem => item.State)
 </td>
 <td data-title="City">
 @Html.DisplayFor(modelItem => item.City)
 </td>
 <td data-title="Status">
 @Html.DisplayFor(modelItem => item.Status)
 </td>

</tr>
 }
 </tbody>
 </table>
 </body>
 </html>

Build and run the application.

Output:

View More:

• Save Data using Entity Framework in ASP.NET MVC.
• Display CSV Data in ASP.NET MVC.
• Bind DropDownlist using Entity Framework in ASP.NET MVC.
• Introduction to ASP.NET Web API.

Conclusion:

I hope this is a useful topic for you to learn. Please don’t hesitate to comment for any technical help. Your feedback and suggestions are always welcome.

Thank You.