How To Retrieve Data From Database In ASP.NET Web API Using jQuery

Introduction

In this article, I will demonstrate how to retrieve data from database in ASP.NET Web API using jQuery.

Step 1

Open SQL Server 2014 and create database table to insert and retrieve the data.

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,
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.

Screenshot for creating new project 1

After clicking on New Project, one window will appear. Select Web from the left panel, choose ASP.NET Web Application, give a meaningful name of your project, then click on OK.

Screenshot for creating new project 2

After clicking on OK, one more window will appear. Choose Empty check on MVC and Web API checkbox and click on OK, as shown in the below screenshot.

Screenshot for creating new project 3

Step 3

Add Entity Framework, right click on Models folder, select Add, then select New Item and click on it.

Screenshot for adding Entity Framework 1

After clicking on New item, you will get a window from there. Select Data from the left panel and choose ADO.NET Entity Data Model, give it name DBModels (this name is not mandatory you can give any name) then click on Add.

Screenshot for adding Entity Framework 2

ASP.NET

After you click on Add, a window wizard will open. Choose EF Designer from the database and click Next.

Screenshot for adding Entity Framework 3

After clicking on Next, a window will get appear. Choose New Connection.

Screenshot for adding Entity Framework 3

Screenshot for adding EF

Another window will appear. Add your server name; if it is local, then enter dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 5

Connection will get added. If you wish save connection as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding entity framework 6

After clicking on NEXT another window will appear choose database table name as show in below screenshot then click in Finish. Entity framework will be added with respective class get generated under Models folder.

Screenshot for adding entity framework 7

Screenshot for adding entity framework 8

ASP.NET

Following class will be added

namespace RetrieveDataWebAPI_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 4

Right click on Controllers folder select Add then choose Controller as shown in below screenshot.

Screenshot web API controller 1

After clicking on controller a window will appear. Choose Web API-Empty click on Add.

Screenshot web API controller 1

After clicking on Add another window will appear with DefaultController. Change the name EmployeeController then click on Add. HomeController will be added under Controllers folder. Remember, don’t change Controller's suffix. For all controllers change only highlight , and instead of Default just change Home as shown int the below screenshot.

ASP.NET

Add namespace

using RetrieveDataWebAPI_Demo.Models;

Write following code in Employee API controller,

public HttpResponseMessage Get()
{
List<Employee> employeeList = new List<Employee>();
using (DBModel dc = new DBModel())
{
employeeList = dc.Employees.OrderBy(a => a.Name).ToList();
HttpResponseMessage response;
response = Request.CreateResponse(HttpStatusCode.OK, employeeList);
return response;
}
}

Complete Web API controller code

using RetrieveDataWebAPI_Demo.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
namespace RetrieveDataWebAPI_Demo.Controllers
{
public class EmployeeController : ApiController
{
public HttpResponseMessage Get()
{
List<Employee> employeeList = new List<Employee>();
using (DBModel dc = new DBModel())
{
employeeList = dc.Employees.OrderBy(a => a.Name).ToList();
HttpResponseMessage response;
response = Request.CreateResponse(HttpStatusCode.OK, employeeList);
return response;
}
}
}
}

Step 5

Right click on Controllers folder select Add then choose Controller as shown in th below screenshot.

Screenshot MVC controller 1

After clicking on controller a window will appear choose MVC 5-Empty click on Add.

Screenshot MVC controller 1

After clicking on Add another window will appear with DefaultController. Change the name to EmployeeController then click on Add. HomeController will be added under Controllers folder. Remember don’t change Controller's suffix; for all controllers change only highlight and instead of Default just change Home as shown in the below screenshot.

MVC 5 Home controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace RetrieveDataWebAPI_Demo.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
}
}

Step 6

Right click on Index action method in controller. Add view and a window will appear with default Index name unchecked (use a Layout page) and click on Add. View will be added in views folder under Home folder with name Index.

Add following scripts and styles 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>
<script src="~/scripts/GetData.js"></script>

Design view with html

<body>
<div class="container py-4">
<h5 class="text-center">HOW TO RETRIEVE DATA FROM DATABASE IN THE ASP.NET Web API USING JQUERY</h5>
<div class="card">
<div class="card-header bg-primary text-uppercase text-white">
<h5 class="card-title">Employee List</h5>
</div>
<div class="card-body">
<button id="btnGetData" type="button" class="btn btn-outline-primary rounded-0">Load Employee List</button>
</div>
<div id="updatePanel" style="padding:20px;">
</div>
</div>
</div>
</body>

Complete 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>
<script src="~/scripts/GetData.js"></script>
</head>
<body>
<div class="container py-4">
<h5 class="text-center">HOW TO RETRIEVE DATA FROM DATABASE IN THE ASP.NET MVC 5 USING JQUERY</h5>
<div class="card">
<div class="card-header bg-primary text-uppercase text-white">
<h5 class="card-title">Employee List</h5>
</div>
<div class="card-body">
<button id="btnGetData" type="button" class="btn btn-outline-primary rounded-0">Load Employee List</button>
</div>
<div id="updatePanel" style="padding:20px;">
</div>
</div>
</div>
</body>
</html>

Step 7

Click on Tools select NuGet Package Manager then choose Manage NuGet Packages for Solution and click on it.

Screenshot for NuGet Package

After that a window will appear; choose Browse type bootstrap and install package in the project.

Similarly type JQuery and install the latest version of JQuery package in project from NuGet then close NuGet Solution.

Keep useful file in Content and scripts folder, as shown below.

Step 8

Right click on scripts folder, add script file name as GetData.

Screenshot 1

Screenshot 2

Write the following jquery code to retrieve data from the database API.

Change API URL to the URL that is generated for your project. Right click on project and click on properties.

Screenshot 1

Screenshot 2

$(document).ready(function () {
var apiBaseUrl = "http://localhost:50882/";
$('#btnGetData').click(function () {
$.ajax({
url: apiBaseUrl + 'api/Employee',
type: 'GET',
dataType: 'json',
success: function (data) {
var $table = $('<table/>').addClass('dataTable table table-bordered table-striped');
var $header = $('<thead/>').html('<tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Salary</th></tr>');
$table.append($header);
$.each(data, function (i, val) {
var $row = $('<tr/>');
$row.append($('<td/>').html(val.Name));
$row.append($('<td/>').html(val.Position));
$row.append($('<td/>').html(val.Office));
$row.append($('<td/>').html(val.Age));
$row.append($('<td/>').html(val.Salary));
$table.append($row);
});
$('#updatePanel').html($table);
},
error: function () {
alert('Error!');
}
});
});
});

Step 9

Double-click on Globle.aspx file from your project and add the following code.

protected void Application_BeginRequest()
{
string[] allowedOrigin = new string[] { "http://localhost:50882/" };
var origin = HttpContext.Current.Request.Headers["Origin"];
if (origin != null && allowedOrigin.Contains(origin))
{
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", origin);
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "GET,POST");
}
}

Step 10

Save and build project. Now, run the project by pressing ctrl+F5.

Screenshot 1

Screenshot 2

ASP.NET

8 Comments

Join the conversation! Your thoughts help the community grow.

Mo Amory6y ago
Can you please make this in .Net core?
Sandeep Sahoo7y ago
Super article!! with step by step images its quite understandable.
Bhavesh Jadav7y ago
Great job, super.
Pravas Ranjan7y ago
Can you please make this article using Angular with .net core
Pravas Ranjan7y ago
Nice article Farhan Bhai
- Farhan Ahmed7y ago
  Thank you bhai...
maha lakshmi8y ago
Nice article
- Farhan Ahmed8y ago
  Thank you......