Post
Ask Question

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.

  1. CREATE TABLE [dbo].[Employees](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Position] [nvarchar](50) NULL,  
  5.     [Office] [nvarchar](50) NULL,  
  6.     [Age] [int] NULL,  
  7.     [Salary] [nvarchar](50) NULL,  
  8.  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [ID] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
ASP.NET

Step 2

Open Visual Studio 2015, click on New Project, and create an empty web application project.

Screenshot for creating new project 1

ASP.NET

 

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

ASP.NET

 

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

ASP.NET

 

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

ASP.NET

 

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

ASP.NET

 

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

Screenshot for adding Entity Framework 3

ASP.NET

 Screenshot for adding EF

ASP.NET

 

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

ASP.NET

 

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

ASP.NET

 

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

ASP.NET

 

Screenshot for adding entity framework 8

ASP.NET

Following class will be added

  1. namespace RetrieveDataWebAPI_Demo.Models  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class Employee  
  7.     {  
  8.         public int ID { get; set; }  
  9.         public string Name { get; set; }  
  10.         public string Position { get; set; }  
  11.         public string Office { get; set; }  
  12.         public Nullable<int> Age { get; set; }  
  13.         public string Salary { get; set; }  
  14.     }  
  15. }  

Step 4

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

Screenshot web API controller 1

ASP.NET

 

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

Screenshot web API controller 1

ASP.NET

 

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

  1. using RetrieveDataWebAPI_Demo.Models;  

Write following code in Employee API controller,

  1. public HttpResponseMessage Get()  
  2.         {  
  3.             List<Employee> employeeList = new List<Employee>();  
  4.             using (DBModel dc = new DBModel())  
  5.             {  
  6.                 employeeList = dc.Employees.OrderBy(a => a.Name).ToList();  
  7.                 HttpResponseMessage response;  
  8.                 response = Request.CreateResponse(HttpStatusCode.OK, employeeList);  
  9.                 return response;  
  10.             }  
  11.         }  

Complete Web API controller code

  1. using RetrieveDataWebAPI_Demo.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Net;  
  6. using System.Net.Http;  
  7. using System.Web.Http;  
  8.   
  9. namespace RetrieveDataWebAPI_Demo.Controllers  
  10. {  
  11.     public class EmployeeController : ApiController  
  12.     {  
  13.         public HttpResponseMessage Get()  
  14.         {  
  15.             List<Employee> employeeList = new List<Employee>();  
  16.             using (DBModel dc = new DBModel())  
  17.             {  
  18.                 employeeList = dc.Employees.OrderBy(a => a.Name).ToList();  
  19.                 HttpResponseMessage response;  
  20.                 response = Request.CreateResponse(HttpStatusCode.OK, employeeList);  
  21.                 return response;  
  22.             }  
  23.         }  
  24.     }  
  25. }  

Step 5

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

Screenshot MVC controller 1

ASP.NET

 

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

Screenshot MVC controller 1

ASP.NET

 

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.

ASP.NET

 

MVC 5 Home controller

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace RetrieveDataWebAPI_Demo.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         // GET: Home  
  12.         public ActionResult Index()  
  13.         {  
  14.             return View();  
  15.         }  
  16.     }  
  17. }  

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

  1. <link href="~/Content/bootstrap.min.css" rel="stylesheet" />  
  2. <script src="~/scripts/jquery-3.3.1.min.js"></script>  
  3. <script src="~/scripts/bootstrap.min.js"></script>  
  4. <script src="~/scripts/GetData.js"></script>  

Design view with html

  1. <body>  
  2.     <div class="container py-4">  
  3.         <h5 class="text-center">HOW TO RETRIEVE DATA FROM DATABASE IN THE ASP.NET Web API USING JQUERY</h5>  
  4.         <div class="card">  
  5.             <div class="card-header bg-primary text-uppercase text-white">  
  6.                 <h5 class="card-title">Employee List</h5>  
  7.             </div>  
  8.             <div class="card-body">  
  9.                 <button id="btnGetData" type="button" class="btn btn-outline-primary rounded-0">Load Employee List</button>  
  10.             </div>  
  11.             <div id="updatePanel" style="padding:20px;">  
  12.             </div>  
  13.         </div>  
  14.     </div>  
  15. </body>  

Complete view code

  1. @{  
  2.     Layout = null;  
  3. }  
  4.   
  5. <!DOCTYPE html>  
  6.   
  7. <html>  
  8. <head>  
  9.     <meta name="viewport" content="width=device-width" />  
  10.     <title>Index</title>  
  11.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" />  
  12.     <script src="~/scripts/jquery-3.3.1.min.js"></script>  
  13.     <script src="~/scripts/bootstrap.min.js"></script>  
  14.     <script src="~/scripts/GetData.js"></script>  
  15. </head>  
  16. <body>  
  17.     <div class="container py-4">  
  18.         <h5 class="text-center">HOW TO RETRIEVE DATA FROM DATABASE IN THE ASP.NET MVC 5 USING JQUERY</h5>  
  19.         <div class="card">  
  20.             <div class="card-header bg-primary text-uppercase text-white">  
  21.                 <h5 class="card-title">Employee List</h5>  
  22.             </div>  
  23.             <div class="card-body">  
  24.                 <button id="btnGetData" type="button" class="btn btn-outline-primary rounded-0">Load Employee List</button>  
  25.             </div>  
  26.             <div id="updatePanel" style="padding:20px;">  
  27.             </div>  
  28.         </div>  
  29.     </div>  
  30. </body>  
  31. </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

ASP.NET

 

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.

ASP.NET

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

ASP.NET

 Step 8

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

Screenshot 1

ASP.NET

Screenshot 2

ASP.NET

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

ASP.NET

Screenshot 2

ASP.NET
  1. $(document).ready(function () {  
  2.     var apiBaseUrl = "http://localhost:50882/";  
  3.     $('#btnGetData').click(function () {  
  4.         $.ajax({  
  5.             url: apiBaseUrl + 'api/Employee',  
  6.             type: 'GET',  
  7.             dataType: 'json',  
  8.             success: function (data) {  
  9.                 var $table = $('<table/>').addClass('dataTable table table-bordered table-striped');  
  10.                 var $header = $('<thead/>').html('<tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Salary</th></tr>');  
  11.                 $table.append($header);  
  12.                 $.each(data, function (i, val) {  
  13.                     var $row = $('<tr/>');  
  14.                     $row.append($('<td/>').html(val.Name));  
  15.                     $row.append($('<td/>').html(val.Position));  
  16.                     $row.append($('<td/>').html(val.Office));  
  17.                     $row.append($('<td/>').html(val.Age));  
  18.                     $row.append($('<td/>').html(val.Salary));  
  19.                     $table.append($row);  
  20.                 });  
  21.                 $('#updatePanel').html($table);  
  22.             },  
  23.             error: function () {  
  24.                 alert('Error!');  
  25.             }  
  26.         });  
  27.     });  
  28. });  

Step 9

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

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

Step 10

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

Screenshot 1

ASP.NET

 

Screenshot 2

ASP.NET


Similar Articles