How to implement jQuery Datatable in ASP.NET MVC

Posted Date: Jul 06, 2020 Hits: 7701 Print

Creating ASP.NET MVC Empty Application

We will be using empty mvc 5.0 appliaction for this demo. To create ASP.NET MVC empty application:
Go to File â†’ New â†’ Project. A new window will be open as shown below.
Now go to Web and select .NET Framework 4.5 and give project name and click on OK .

Now new window will open as shown below.
Now Select Empty Template, check on MVC checkbox and click on OK.

Now, a new project will be created as shown below.

Adding Models

Go to Models folder and add new class file Employee.cs. Add 4 properties as shown below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace WebApplication1.Models

{

public class Employee

{

public int BusinessEntityID { get; set; }

public string NationalIDNumber { get; set; }

public string LoginID { get; set; }

public string JobTitle { get; set; }

}

Adding Data Access Layer

Create a DAL.cs class file in root directory of application. Here, add one method GetEmployeeList which will return list of employee. Here, we are using AdventureWorks2014 database for demo purpose. We are using Employee table for demo.

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using WebApplication1.Models;

namespace WebApplication1

{

public class DAL

{

public List<Employee> GetEmployeeList()

{

List<Employee> employeeList = null;

try

{

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))

{

employeeList = new List<Employee>();

SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID, NationalIDNumber, LoginID, JobTitle FROM EMPLOYEE", con);

cmd.CommandType = CommandType.Text;

con.Open();

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())

{

Employee e = new Employee();

e.BusinessEntityID = Convert.ToInt32(sdr["BusinessEntityID"].ToString());

e.NationalIDNumber = sdr["NationalIDNumber"].ToString();

e.LoginID = sdr["LoginID"].ToString();

e.JobTitle = sdr["JobTitle"].ToString();

employeeList.Add(e);

}

catch (Exception ex)

{

}

return employeeList;

}

Database and Table

As we are using AdventureWorks2014 database for demo purpose. We will use Employee table and use only 4 columns to display purpose as shown below.

Adding Controller

Next step is to add controller to application. Go to controller folder and add new empty controller named as Home controller. Replace all code with below code. First method is Index which will return index view and second method is EmployeeData which will return json data called from index view using jquery ajax method.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using WebApplication1.Models;

namespace WebApplication1.Controllers

{

public class HomeController : Controller

{

public ActionResult Index()

{

return View();

}

[HttpGet]

public ActionResult EmployeeData()

{

DAL obj = new DAL();

List<Employee> employeeList = obj.GetEmployeeList();

return Json(new { data = employeeList }, JsonRequestBehavior.AllowGet);

}

Adding View

Now, right click on home controller index action method, add new view and name Index. Select Empty template and uncheck Use layout page and click on Add as shown below. We are not using any Layout in this demo.

Now go to Views / Home folder, open Index.cshtml file. Write below code. Here, we have created html table with id myTable with column header as shown below. We are required 3 files i.e. jQuery js file, datatable js file and datatable css file.
Here, we are calling Home controller and EmployeeData index method using jQuery ajax.

Index.cshtml Code:

Layout = null;

}

<!DOCTYPE html>

<html>

<head>

<title>Index</title>

$(document).ready(function () {

$('#myTable').DataTable(

{

"ajax": {

"url": "/Home/EmployeeData",

"type": "Get",

"datatype": "json"

"columns": [

{ "data": "BusinessEntityID" },

{ "data": "NationalIDNumber" },

{ "data": "LoginID" },

{ "data": "JobTitle" }

]

});

</script>

</head>

<body>

<thead>

<tr>

<th>Business Entity ID</th>

<th>National ID Number</th>

<th>Login ID</th>

<th>Job Title</th>

</tr>

</thead>

</table>

</div>

</body>

</html>

Now, after running the application, output can be seen as below.
Below output is simple datatable without applying any fancy css.

Styling DataTable

As we can see above output is simple output, now add css class display to HTML table as shown below.

datatable normal output with display class

As we can see from above output, alternate row is coloured.

Semantic UI:
Now, we will use semantic ui js and css to style the datatable. For that, we need to add below js and css file as shown below.

Also we need to change css class of html table to ui celled table as shown below.

Output can be seen as below for Semantic UI.

datatable normal output with semantic ui

jQuery UI:
Now, we will use jQueryui js and css to style the datatable. For that, we need to add below js and css file as shown below.

Also we need to change css class of html table to display as shown below.

Output can be seen as below for jQueryui.

You can further style you DataTable by accessing below url.
https://datatables.net/examples/styling/

Interview

How to implement jQuery Datatable in ASP.NET MVC

Creating ASP.NET MVC Empty Application

Adding Models

Adding Data Access Layer

Database and Table

Adding Controller

Adding View

Styling DataTable

{{article.Title}}

{{article.Title}}