Introduction
In this blog, I will demonstrate how to retrieve the data from SQL database using jQuery AJAX in ASP.NET MVC5. I will use jQuery data table for searching, sorting, and paging to HTML table.
Step 1
Open SQL Server 2014 and create a database table.
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[Office] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salary] [nvarchar](50) NULL,
[Profile_Image] [nvarchar](100) NULL,
CONSTRAINT [PK_Employees] 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
Step 2
Open Visual Studio 2015, click on New Project, and create an empty web application project.
A New Project one window will appear; select Web from the left panel, choose ASP.NET Web Application, give a meaningful name to your project, and then click on OK. One more window will appear. Choose Empty check on MVC checkbox and click on OK.
Step 3
Click on Tools >> NuGet Package Manager and choose Manage NuGet Packages for Solution. After that, a window will appear. Choose Browse, type bootstrap, and install the package in the project. Similarly, type jQuery, and install the latest version of jQuery package in the project from NuGet. Then, close the NuGet Solution. Keep the useful files in Content and scripts folder.
Step 4
Add Entity Framework, right-click on Models folder, select Add. Then, select New Item and click on it.
After clicking on the New item, you will get a window. From there, select data from the left panel and choose ADO.NET Entity Data Model to give it a name DBModels (this name is not mandatory you can give any name) then click on Add. After you click on Add, a window wizard will open.
Choose EF Designer from database and click Next. After clicking on Next window will appear. Choose a New Connection. Another window will appear. Add your server name. If it is local, then enter dot (.). Choose your database and click on OK. The connection will get added. If you wish to save connect as you want, you can change the name of your connection below.
It will save the connection in web config, then click on Next. After clicking on NEXT, another window will appear. Choose database table name as shown in the below screenshot and click on Finish. Entity framework will be added with the respective class that gets generated under Models folder.
Following class will be added,
namespace MvcJQueryDataTable_Demo.Models
{
using System;
using System.Collections.Generic;
public partial class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public string Position { get; set; }
public string Office { get; set; }
public Nullable<int> Age { get; set; }
public string Salary { get; set; }
}
}
Step 5
Right click on Controllers folder select Add then choose Controller. After click on controller, a window will appear. Choose MVC5 Controller-Empty and click on Add. After clicking on Add, another window will appear with DefaultController change the name HomeController then click on Add. HomeController will be added under Controllers folder. Remember don’t change Controller its suffix for all controller change only highlight instead of Default just change Home.
Add following namespace
using MvcJQueryDataTable_Demo.Models;
Complete code of HomeController
using MvcJQueryDataTable_Demo.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
namespace MvcJQueryDataTable_Demo.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
public ActionResult GetData()
{
using (DBModel db=new DBModel())
{
List<Employee> employeeList = db.Employees.ToList<Employee>();
return Json(new { data = employeeList }, JsonRequestBehavior.AllowGet);
}
}
}
}
Step 6
Right click on Index action method in controller Add view window will appear with default Index name uncheck (use a Layout page) click on Add. As shown in below screenshot, the View will be added in views folder under Home folder with name Index.
Add the following styles and script file in head section of index view,
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.3.1.min.js"></script>
<script src="~/scripts/bootstrap.min.js"></script>
<link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="~/scripts/jquery.dataTables.min.js"></script>
<script src="~/scripts/dataTables.bootstrap4.min.js"></script>
Write following jquery script to retrieve data from database,
<script type="text/javascript">
$(document).ready(function () {
$('#dataTable').DataTable({
"ajax": {
"url": "/Home/GetData",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "Name" },
{ "data": "Position" },
{ "data": "Office" },
{ "data": "Age" },
{ "data": "Salary" }
]
});
});
</script>
Design the HTML table in index view to display data,
<body>
<div class="container py-4">
<h4 class="text-center text-uppercase">HOW TO RETRIEVE DATA FROM DATABASE USING JQUERY AJAX IN MVC5</h4>
<div id="dataTable_wrapper"></div>
<table id="dataTable" class="table table-bordered table-striped">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Salary</th>
</tr>
</thead>
</table>
</div>
</body>
Complete index view code
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.3.1.min.js"></script>
<script src="~/scripts/bootstrap.min.js"></script>
<link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="~/scripts/jquery.dataTables.min.js"></script>
<script src="~/scripts/dataTables.bootstrap4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#dataTable').DataTable({
"ajax": {
"url": "/Home/GetData",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "Name" },
{ "data": "Position" },
{ "data": "Office" },
{ "data": "Age" },
{ "data": "Salary" }
]
});
});
</script>
</head>
<body>
<div class="container py-4">
<h4 class="text-center text-uppercase">HOW TO RETRIEVE DATA FROM DATABASE USING JQUERY AJAX IN MVC5</h4>
<div id="dataTable_wrapper"></div>
<table id="dataTable" class="table table-bordered table-striped">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Salary</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
Step 7
Run the project by pressing Ctrl +F5.
![ASP.NET]()