Angular and ASP.NET Web API

In this video we will discuss creating ASP.NET Web API service that retrieves employees data from a database table. In our next video we will discuss, how to call this ASP.NET Web API service using Angular

Step 1 : Execute the following SQL Script using SQL Server Management studio. This script creates

• EmployeeDB database
• Creates the Employees table and populate it with sample data

Create Database EmployeeDB

Go

Use EmployeeDB

Go

Create table Employees

(

     code nvarchar(50) primary key,

     name nvarchar(50),

     gender nvarchar(50),

     annualSalary decimal(18,3),

     dateOfBirth nvarchar(50)

)

Go

Insert into Employees values ('emp101', 'Tom', 'Male', 5500, '6/25/1988')

Insert into Employees values ('emp102', 'Alex', 'Male', 5700.95, '9/6/1982')

Insert into Employees values ('emp103', 'Mike', 'Male', 5900, '12/8/1979')

Insert into Employees values ('emp104', 'Mary', 'Female', 6500.826, '10/14/1980')

Insert into Employees values ('emp105', 'Nancy', 'Female', 6700.826, '12/15/1982')

Insert into Employees values ('emp106', 'Steve', 'Male', 7700.481, '11/18/1979')

Step 2 : To keep Angular and Web API projects separate, let's create a new project for our Web API Service. Right click on "Angular2Demo" solution in the Solution Explorer and select Add - New Project.

Step 3 : In the Add New Project window

1. Select "Visual C#" under "Installed - Templates"
2. From the middle pane select, ASP.NET Web Application
3. Name the project "EmployeeWebAPIService" and click "OK" 

Step 4 : On the next window, select "Web API" and click "OK". At this point you should have the Web API project created.

Step 5 : Add ADO.NET Entity Data Model to retrieve data from the database. Right click on "EmployeeWebAPIService" project and select Add - New Item

1. In the "Add New Item" window
2. Select "Data" from the left pane
3. Select ADO.NET Entity Data Model from the middle pane
4. In the Name text box, type EmployeeDataModel and click Add 

Step 6 : On the Entity Data Model Wizard, select "EF Designer from database" option and click next

Step 7 : On the next screen, click "New Connection" button

Step 8 : On "Connection Properties" window, set

1. Server Name = (local)
2. Authentication = Windows Authentication
3. Select or enter a database name = EmployeeDB
4. Click OK and then click Next

Step 9 : On the nex screen, select "Employees" table and click Finish.

Adding Web API Controller

1. Right click on the Controllers folder in EmployeeWebAPIService project and select Add - Controller

2. Select "Web API 2 Controller - Empty" and click "Add"

3. On the next screen set the Controller Name = EmployeesController and click Add

4. Copy and paste the following code in EmployeesController.cs

using System.Collections.Generic;

using System.Linq;

using System.Web.Http;

namespace EmployeeWebAPIService.Controllers

{

    public class EmployeesController : ApiController

    {

        public IEnumerable<Employee> Get()

        {

            using(EmployeeDBEntities entities = new EmployeeDBEntities())

            {

                return entities.Employees.ToList();

            }

        }

        public Employee Get(string code)

        {

            using (EmployeeDBEntities entities = new EmployeeDBEntities())

            {

                return entities.Employees.FirstOrDefault(e => e.code == code);

            }

        }

    }

}

At this point when you navigate to /api/employees you will see all the employees as expected. However, when you navigate to /api/employees/emp101, we expect to see employee whose employee code is emp101, but we still see the list of all employees. 
This is because the parameter name for the Get() method in EmployeesController is "code"

public Employee Get(string code)

{

    using (EmployeeDBEntities entities = new EmployeeDBEntities())

    {

        return entities.Employees.FirstOrDefault(e => e.code == code);

    }

}

but in the default Web API route in WebApiConfig.cs file the parameter name is {id}. Change this to "code" as shown below

config.Routes.MapHttpRoute(

    name: "DefaultApi",

    routeTemplate: "api/{controller}/{code}",

    defaults: new { code = RouteParameter.Optional }

);

With this change if we navigate to /api/employees/emp101 we see just that employee whose employee code is "emp101"