Part1 - How to retrieve data from the database in the ASP.NET Web API using Jquery




Introduction

In this post, I am going to explain How to retrieve data from the database in the ASP.NET Web API using Jquery.

This post is 1st part of a series called Getting Started with ASP.NET WEB API
  1. Part1 - How to retrieve data from the database in the ASP.NET Web API using Jquery
  2. Part 2 - How to retrieve data from the database in the ASP.NET Web API using Http Client
  3. Part 3 - How to post data with validation in the ASP.NET Web API using Jquery
  4. Part 4 - How to post data with validation in the ASP.NET Web API using Http Client
  5. Part 5 - How to upload files in the ASP.NET Web API using Jquery
  6. Part 6 - How to upload files in the ASP.NET Web API using Http Client
  7. Part 7 - How to retrieve and display data With Paging in the ASP.NET Web API using Jquery

Steps for Web API Application(Service)

Here in this example, the Web API application (service) is "WebApiExample".

Step - 1 : Create New Project (WebApi).

Go to File > New > Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Web API > Select view engine Razor > OK

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.
In this example, I have used one table as below


Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a new Api Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty API Controller"> Add.

Step-6: Add new action into the api controller for fetch data from database and return to the client application.

Here I have added "Get" Action into "ExampleController" api Controller. Please write this following code

using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace WebApiExample.Controllers
{
    public class ExampleController : ApiController
    {
        //Add Action for GET // for Fetch data from database and return to the Client
        public HttpResponseMessage Get()
        {
            List<Employee> allEmp = new List<Employee>();
            using (MyDatabaseEntities dc = new MyDatabaseEntities()) //here MyDatabaseEntities is our datacontext
            {
                allEmp = dc.Employees.OrderBy(a => a.FirstName).ToList(); // I have added linq code for fetch data, you can use Sql client for fetch data
                HttpResponseMessage response;
                response = Request.CreateResponse(HttpStatusCode.OK, allEmp);
                return response;
            }
        }
    }
}
                

Step-7: Add Application_BeginRequest event in the Global.asax.cs file for allow CORS (Cross-origin resource sharing)

protected void Application_BeginRequest()
{
    string[] allowedOrigin = new string[] { "http://localhost:2036" };
    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");
        //Need to add more later , will see when required
    }
}
                

Steps for Web Application (Client Application)

Here in this example, the client application is "WebApiClient.Web".

Step-8: Add an another New project for consume Web API services.

Here I have created a mvc4 application for used as our client application.
Go to Solution Explorer > Add > New Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Internet Application > Select view engine Razor > OK

Step-9: Add a new MVC Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Step-10: Add new action into your controller for get the view (page) for fetch data from web api and show.

Here I have added "Part1" Action into "Home" Controller. Please write this following code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace WebApiClient.Web.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Part1()
        {
            return View();
        }
    }
}
                

Step-11: Add view for the Action & design.

Right Click on Action Method (here right click on form action) > Add View... > Enter View Name > Select View Engine (Razor) > Add.

Step-12: Add jquery code for fetch and show data.

Jquery Code
<script>
    $(document).ready(function () {
        var apiBaseUrl = "http://localhost:1963/";
        $('#btnGetData').click(function () {
            $.ajax({
                url: apiBaseUrl + 'api/Example',
                type: 'GET',
                dataType: 'json',
                success: function (data) {
                    var $table = $('<table/>').addClass('table table-responsive table-striped table-bordered');
                    var $header = $('<thead/>').html('<tr><th>Name</th><th>Email</th><th>City</th><th>Country</th></tr>');
                    $table.append($header);
                    $.each(data, function (i, val) {
                        var $row = $('<tr/>');
                        $row.append($('<td/>').html(val.FirstName + ' ' + val.LastName));
                        $row.append($('<td/>').html(val.EmailID));
                        $row.append($('<td/>').html(val.City));
                        $row.append($('<td/>').html(val.Country));
                        $table.append($row);
                    });
                    $('#updatePanel').html($table);
                },
                error: function () {
                    alert('Error!');
                }
            });
        });
    });
</script>
                
Complete View
@{
    ViewBag.Title = &quot;Part1&quot;;
}

&lt;h2&gt;Part1 - Fetch data from WebApi using jquery&lt;/h2&gt;

&lt;div&gt;
    &lt;div style=&quot;padding:10px&quot;&gt;
        &lt;input id=&quot;btnGetData&quot; type=&quot;button&quot; value=&quot;Fetch data from WebApi&quot; class=&quot;btn btn-default&quot; /&gt;
    &lt;/div&gt;
    &lt;div id=&quot;updatePanel&quot; style=&quot;width:90%; margin:0 auto; padding:10px&quot;&gt;

    &lt;/div&gt;
&lt;/div&gt;
@section Scripts{
    &lt;script&gt;
        $(document).ready(function () {
            var apiBaseUrl = &quot;http://localhost:1963/&quot;;
            $(&#39;#btnGetData&#39;).click(function () {
                $.ajax({
                    url: apiBaseUrl + &#39;api/Example&#39;,
                    type: &#39;GET&#39;,
                    dataType: &#39;json&#39;,
                    success: function (data) {
                        var $table = $(&#39;&lt;table/&gt;&#39;).addClass(&#39;table table-responsive table-striped table-bordered&#39;);
                        var $header = $(&#39;&lt;thead/&gt;&#39;).html(&#39;&lt;tr&gt;&lt;th&gt;Name&lt;/th&gt;&lt;th&gt;Email&lt;/th&gt;&lt;th&gt;City&lt;/th&gt;&lt;th&gt;Country&lt;/th&gt;&lt;/tr&gt;&#39;);
                        $table.append($header);
                        $.each(data, function (i, val) {
                            var $row = $(&#39;&lt;tr/&gt;&#39;);
                            $row.append($(&#39;&lt;td/&gt;&#39;).html(val.FirstName + &#39; &#39; + val.LastName));
                            $row.append($(&#39;&lt;td/&gt;&#39;).html(val.EmailID));
                            $row.append($(&#39;&lt;td/&gt;&#39;).html(val.City));
                            $row.append($(&#39;&lt;td/&gt;&#39;).html(val.Country));
                            $table.append($row);
                        });
                        $(&#39;#updatePanel&#39;).html($table);
                    },
                    error: function () {
                        alert(&#39;Error!&#39;);
                    }
                });
            });
        });
    &lt;/script&gt;
}
                
Here I have done small changes in the layout page for looks the application good.

Step-13: Run Application.

Here we need to start both application as the client application will consume services from web api application.
Right Click on the solution > Properties > Select "Multiple startup projects" > Change Action of both Project to Start > Apply > Ok



How to retrieve data from the database in the ASP.NET Web API using Jquery

Hello ! My name is Sourav Mondal. I am a software developer working in Microsoft .NET technologies since 2010.

I like to share my working experience, research and knowledge through my site.

I love developing applications in Microsoft Technologies including Asp.Net webforms, mvc, winforms, c#.net, sql server, entity framework, Ajax, Jquery, web api, web service and more.